We have a big mysql performance issue on a production environment with 18 "front" servers sending mysql queries to a unique mysql server. We are using Redis on each front server for a number of things, but there is still one important job done only on the mysql server. So we have 5 processes on each of the 18 servers, sending big select queries to our mysql server. The where condition is changing for each query because the result is cached on each ad server in a redis cache and reused when the where condition would be the same. Since we have a different caching system, this query is using the SQL_NO_CACHE option. There are other queries running on the system, many small writes and a few large reads and writes, but most of the load comes from this query. The query is quite big: it uses 17 tables, some of them with several million records, the query can return up to 5000 records, each record is quite large. But the query is well optimized and runs in 0.02s when the load is low. With our usual system load, the query runs on average in 1.5s. We are handling query queues on each ad server and monitoring the average time a query stays in the queue before it’s executed. Usually a query stays in a queue about 2 minutes. But lately, with our traffic growing and after adding a few servers, the average execution time increased to 2s and the time-in-queue exceeded 20 minutes. So we decided to try some fine tuning...
Our mysql server is a 24 cores running Debian 6 with 64G RAM and MySQL 5.1 (InnoDB only). Since the CPU and RAM were not used at 100% we thought there could be another bottleneck. We noticed that we had many TCP sockets on the server in the time_wait state and thought that maybe the number of TCP sockets available was a bottleneck so we changed from using a single IP address to using 5 IP addresses for our mysql server. We also changed the TCP sockets lifetime from 2 hours to 2 minutes. After 2 hours, the performance increased very nicely: the query average execution time went down from 2s to 0.5s and the time-in-queue went down from 20 minutes to 10 minutes… but after one and a half day the execution time went back up to 15s and the time in queue to 20 minutes. We have tried reversing all the changes in order to go back to the previous situation but it didn’t work.
We are looking for a senior DBA with good experience of InnoDB/Debian who could help us fix this performance issue, then help us find the bottlenecks and optimize the tunning of our mysql server.
Here is our my.cnf file:
# Example MySQL config file for very large systems. # # This is for a large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is @localstatedir@) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/run/mysqld/mysqld.sock # The MySQL server [mysqld] skip-name-resolve skip-federated default_time_zone = 'Europe/Paris' innodb_locks_unsafe_for_binlog = 1 transaction-isolation=READ-UNCOMMITTED #innodb_force_recovery = 4 #skip-grant-tables #skip-networking #replicate-ignore-db = mysql user = mysql port = 3306 old_passwords = false max_connect_errors = 10000 socket = /var/run/mysqld/mysqld.sock pid-file = /var/run/mysqld/mysqld.pid datadir = /var/lib/mysql/prod #basedir = /usr tmpdir = /var/tmp #skip-locking # LC 28-06-2012 Passage a 300M apres calcul des index des table MyISAM : # select sum(INDEX_LENGTH/1024/1024) from information_schema.tables where engine="MyISAM"; key_buffer = 300M max_allowed_packet = 128M # Modif LC 28-06-2012 car pas assez de tables cachees 80000 -> 160000 -> 40000 -> 20000 -> 1000 # Ajout de table_open_cache le nouveau nom du parametre #table_cache = 6000 table_open_cache = 24000 # max_connections * 18 ( 18 tables used in targeting query ) + delta table_definition_cache = 512 sort_buffer_size = 32M read_buffer_size = 10M read_rnd_buffer_size = 3M myisam_sort_buffer_size = 1M thread_cache_size = 64 query_cache_type = 1 query_cache_limit = 128M query_cache_size = 512M query_cache_min_res_unit = 8K join_buffer_size = 4M max_connections = 200 low_priority_updates = 1 # JBN : On the slave, updates are more important than reads concurrent_insert = 2 wait_timeout = 300 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 48 #thread_concurrency = 24 # #Temporary tables # # Modif LC 11-06-2012 car trop de tables temporaires 1024 -> 8192 #tmp_table_size = 1024M #max_heap_table_size = 1024M tmp_table_size = 8192M max_heap_table_size = 8192M max_tmp_tables = 64 # # log paths # log-error = /var/log/mysql/error.log slow_query_log_file = /var/log/mysql/mysql-slow.log slow_query_log = 1 long_query_time = 1 log-queries-not-using-indexes # This is a master server in a master/slave replication (id=1 + binlogging) server-id = 1 #log_bin = /var/lib/mysql/mysql-bin.log expire_logs_days = 1 max_binlog_size = 1G #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name #relay_log = mysql-relay #relay_log_space_limit = 50G # Fulltext search ft_min_word_len = 3 # # Language variables # character-set-server = utf8 collation-server = utf8_general_ci language = /usr/share/mysql/english # # InnoDB parameter # ignore_builtin_innodb plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so innodb_file_format=barracuda # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = @localstatedir@/ innodb_data_file_path = ibdata1:512M:autoextend innodb_table_locks = OFF innodb_support_xa = 0 innodb_autoinc_lock_mode = 2 # WARNING : binlog-format = ROW IS MANDATORY # binlog_format = row innodb_flush_log_at_trx_commit = 2 #innodb_log_group_home_dir = @localstatedir@/ #innodb_log_arch_dir = @localstatedir@/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high # LC 28-06-2012 innodb_buffer_pool_size 12G -> 24G -> 18G # KHA 29-06-2012 -> 12G ( 18G too big ) innodb_buffer_pool_size = 48G innodb_additional_mem_pool_size = 32M innodb_log_file_size = 128M innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 250 innodb_file_per_table # LC 12-06-2012 innodb_thread_concurrency 64-> 24 innodb_thread_concurrency = 12 #innodb_thread_concurrency = 64 # LC 17-06-2012 Ajout innodb_flush_method=O_DIRECT pour voir l'impact perf au niveau RAID hardware # A tester innodb_flush_method=O_DIRECT [mysqldump] quick max_allowed_packet = 16M # Language variables needed for backup / restores default-character-set = utf8 [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates # Language variables needed for backup / restores default-character-set = utf8 [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout # Cyb tuning [mysqld] log-warnings=2 # logs more information to log-error
Thank you in advance for your help.
We have discovered the cause of the performance degradation: a partner started sending us much more detailed data, increasing significantly the number of rows returned by our query and the number of row examined. In order to avoid this we archived old data and removed it from the 2 main tables and we replaced the LIMIT 5000 by LIMIT 500. Our service is a bit degraded but we can live with that, the average query response time dropped down to 5s, which is acceptable and allows us to work.
Now that we understand that our changes were not responsible for the performance drop, we are going to re-apply them (more innodb threads and mySQL listening on multiple IP addresses).