Hi. I get about 100,000 users per minute on my site.
We use InnoDB. The DB server is a 32GB server with 2x quad core servers on RAID 10. Containing 5 disks of SCSI 15k RPM. Top quality hardware!
We have about 150 tables in 11 databases. The table with the highest number of records is 500,000. Not rocket science really. From my judgment this is a fairly small amount of data?!
And still the website crawls under this heavy load!
We’re optimizing our slow SQLs from the log (only a few) but I also want to make sure our config is geared to take this kind of traffic.
Would love to hear from the gurus on this forum! My conf is below, as is my SHOW INNODB STATUS. Thanks!
safe-show-databasebind-address = 10.16.30.70port = 3306datadir = /var/lib/mysqlset-variable=max_allowed_packet=512Mlog-error = /var/log/mysqld.log # Logging enabledinnodb_buffer_pool_size = 11Ginnodb_flush_method=O_DIRECT # Remove double bufferinginnodb_support_xa = 0innodb_log_buffer_size=8Minnodb_commit_concurrency = 0innodb_concurrency_tickets = 500innodb_flush_log_at_trx_commit = 0innodb_lock_wait_timeout = 8innodb_thread_concurrency = 16innodb_log_group_home_dir = /var/lib/mysql/innodb_log_arch_dir = /var/lib/mysql/innodb_additional_mem_pool_size = 20Mskip-bdbskip-lockingskip-name-resolvequery_cache_limit=2Gquery_cache_size=200M # Was 2G, limit and tryquery_cache_type=1table_cache=10Mthread_cache_size=256thread_concurrency=16 max_connections=5000 #max_user_connections=1500wait_timeout=10interactive_timeout=15key_buffer_size=9Gjoin_buffer=512M #read_rnd_buffer_size=32M #max_heap_table_size=2Gtmp_table_size=512sort_buffer_size=1G #max_connect_errors=10character-set-server=utf8default-collation=utf8_unicode_cilog-queries-not-using-indexes=1log_slow_queries=/var/log/mysql-slow.loglong_query_time=3#[myisamchk]key_buffer = 9Gsort_buffer = 1Gread_buffer = 32M
For show status I’ll update this thread shortly. We have just restarted our DB. But my concern is that the mysql-tuning.pl script is showing me that my maximum allocated memory is about 2000% more than what I have available!! Why?