Hi, I have a database server and i'm getting a lot of failed connection attempts when users are using my site.
I need help badly :) My tables all have primary keys and my queries uses them or at least an index so i have fairly optimized queries. The site gets about 8000-9000 members online at round the same time so I'm not sure if it's too much for the db. No matter what changes i try in the my.cnf the Max used connections is always around 1033 but that max is reached well before peak times as when peak times comes it doesn't go higher so i end up with a lot of failed connections. Below are some data I figured would come in handy this is from having the database online for just 3 hours:
8 gigs Ram
Dual Xeon processor
Failed attempts 115,612 29,718.19 40.20 %
Aborted 290,468 74,665.11 100.99 %
Total 287,624 73,934.05 100.00 %
Total ø per hour ø per minute ø per second
24,844,858 6,386,396.92 106,439.95 1,774.00
Max used connections 1082
Open tables 5000
Open files 5229
Open streams 0
Opened tables 7584
Created tmp disk tables 703
Created tmp tables 976483
Created tmp files 55
Threads cached 61
Threads created 120944
Threads connected 1021
Threads running 22
Handler read rnd 28188883
Handler read rnd next 193420205
Key read requests 619785900
Key reads 415910
Qcache queries in cache 27279
Qcache inserts 12854166
Qcache hits 5461364
Qcache lowmem prunes 22137
Qcache not cached 370565
Qcache free memory 72573336
Qcache free blocks 20215
Qcache total blocks 74930
Slow queries 5062
Sort merge passes 26
Sort range 923143
Sort rows 64210638
Sort scan 621404
Table locks immediate 14692036
Table locks waited 2122146
My.cnf
-------datadir=/var/lib/my
sql
socket=/var/lib/mysql/mysq
l.sock
set-variable = max_connections=2500
set-variable = interactive_timeout=30
set-variable = wait_timeout=15
set-variable = thread_cache_size=256
set-variable = connect_timeout=30
set-variable = key_buffer=256M
set-variable = join_buffer=1M
set-variable = record_buffer=512k
set-variable = sort_buffer=1M
set-variable = table_cache=5000
set-variable = thread_concurrency=4
set-variable = tmp_table_size=16M
set-variable = query_cache_type=1
set-variable = query_cache_limit=1M
set-variable = query_cache_size=100M
set-variable = max_allowed_packet=2M
set-variable = max_connect_errors=250
set-variable = max_user_connections=2500
log-slow-queries = /var/log/mysql/mysql-slow.
log
long_query_time = 5
[mysql.server]
user=mysql
basedir=/var/lib
thanks for the assistance in advance.
Start Free Trial