MY SQL configuration
ASK:
We have a database Server Configuration:
4GB RAM
600GB Hard Disk
Xeon Processor 1.3 Ghz.
We are barely able to have 100 concurrent users!!! What are we doing wrong.
I know I need to configure mysql_query cache, mysql_limit_size and table_cache. But what should be the formula, and how do we go about checking the same.
Below is the details of our my.ini file.
[mysqld]
datadir=/database/data
socket=/var/lib/mysql/mysql.sock
set-variable=max_connections=2000
set-variable = max_allowed_packet=64M
default-storage-engine = innodb
log-bin=/database/data/mysql-bin
old_passwords=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Answer:
Try this configuration and read my comments inside it
#you really don't need more than 200 connections
max_connections = 200
#this can be safely reduced to 32 or even 16M if you have few (0-20) or small (<1gb total) databases
key_buffer = 128M
myisam_sort_buffer_size = 32M
join_buffer_size = 2M
read_buffer_size = 1M
sort_buffer_size = 8M
#use total number of database tables +20% (ALL databases) - if you just have a single drupal database 100-150 will be enough
table_cache = 2000
thread_cache_size = 32
wait_timeout = 3600
connect_timeout = 4
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
#if you get a long query selecting a lot of data you might want to increase this
query_cache_limit = 32M
#play with this until you get 80% cache filled after mysql server has been running for 24h+, if this is a dedicated database server you can go nuts with this up to 70% of RAM, but it there is no queries to be cached this will be pretty useless. Better stay under 512M and use rest for memcached
query_cache_size = 96M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
open_files_limit = 8196
key_buffer_size = 64M
thread_stack = 128Kadd the rest of the config (user, pid, passwords, etc) from you current configuration
If you can't handle it on your own, or need someone to fine tune the settings let me know - I'll have a look.
