Hello, I'm kind of newbie in linux and server things- I've an website which has 2500+ concurrent visitors and all of them are using database, so I've load balanced the httpd with 4 cloud servers (15GB each) and using 1 mysql server (32GB RAM) However, the mysql server is always 70-90% free but taking ages to response to the sql queries, after discussing with some friends- some said that its the reason probably because I do not have my.cnf perfectly configured for 32GB RAM's server. I've attached my.cnf current configuration and I'm using Debian 5 Lenny + MySQL 5.1.60 (I do not have http or any other services installed on this server) using nginx to connect this database server using private IP. [mysqld] local-infile=0 tmpdir = /dev/shm #safe-show-database max_connections = 650 max_user_connections=50 key_buffer_size = 512M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 4000 thread_cache_size = 384 wait_timeout = 40 connect_timeout = 20 tmp_table_size = 64M max_heap_table_size = 64M max_allowed_packet = 64M net_buffer_length = 16384 max_connect_errors = 10 thread_concurrency = 8 concurrent_insert = 2 #table_lock_wait_timeout only for mysql5 table_lock_wait_timeout = 40 read_rnd_buffer_size = 786432 bulk_insert_buffer_size = 8M query_cache_limit = 7M query_cache_size = 64M query_cache_type = 1 query_prealloc_size = 262144 query_alloc_block_size = 65536 transaction_alloc_block_size = 8192 transaction_prealloc_size = 4096 max_write_lock_count = 16 skip-name-resolve skip-locking [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 384M sort_buffer = 384M read_buffer = 256M write_buffer = 256M Code (markup):
1) Start with the mysql slow log and identify what queries are slow. Inserts? Complex selects? Everything? 2) Try using explain to analyse queries and figure out if there are index related issues on any of your key tables and columns. This will give you a better perspective on how to handle the outputs of the scripts mentioned above. What sort of CMS are you using and how large is your dataset (ignoring indexes). We serve 5 million plus pages a day using 3 4GB mysql servers (2 - multi-master + 1 slave for backups / batch processing) without any major issues... with the caveat that the dataset and the index pretty much fit into memory and many queries have been hand tweaked to ensure that they're hitting the correct indexes.
Forgot to mention that I'm assuming all your tables are MyISAM, you're running a 64bit build of the OS and MySQL. You might want to also visit the MySQL Performance Blog that the guys behind Percona maintain for additional tips.
Something to keep in mind here is that myisam is pretty much un-tune-able. You can adjust a few caches but you're not going to get a whole lot out of it past a certain point. If you seriously need to tune the installation, switch to InnoDB, or go to Maria or Percona XtraDB. http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/
have you tried to trace route this? if you can figure out the private internal IP if your other server is also rackspace then it will go faster because if you're just connecting to the world and back in at that point.