Has anyone looked into making sure the MySQL server is configured optimally? I'm not a MySQL expert, but I was reading over the MySQL documentation and there are a number of parameters that can be adjusted (like cache sizes).
There is also a MySQL command "SHOW STATUS" that might help to shed some light on where the problems are. It reports things like number of cache misses and number of times it had to wait for a table lock.
So, could someone with database access send me the results of running the following two MySQL commands "SHOW STATUS" and "SHOW VARIABLES" (or post the output to the list for everyone if they look interesting). I'll examine them and report my findings.
Here is a link to what SHOW STATUS reports: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Adm...
and some information on MySQL server optimizing: http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation...
As I said, I'm not an expert, but maybe there are some parameters that can be tweaked (if they haven't been already) to improve performance in the short term while a longer-term solution is worked on.
Regards, Steve
Steve Rawlinson wrote:
So, could someone with database access send me the results of running the following two MySQL commands "SHOW STATUS" and "SHOW VARIABLES" (or post the output to the list for everyone if they look interesting). I'll examine them and report my findings.
SHOW STATUS: Aborted_clients 2182 Aborted_connects 136 Bytes_received 352728396 Bytes_sent 542290646 Com_admin_commands 0 Com_alter_table 0 Com_analyze 0 Com_backup_table 0 Com_begin 0 Com_change_db 1182929 Com_change_master 0 Com_check 0 Com_commit 0 Com_create_db 2 Com_create_function 0 Com_create_index 0 Com_create_table 0 Com_delete 49039 Com_drop_db 0 Com_drop_function 0 Com_drop_index 0 Com_drop_table 0 Com_flush 0 Com_grant 0 Com_insert 56839 Com_insert_select 577 Com_kill 0 Com_load 0 Com_load_master_table 0 Com_lock_tables 2 Com_optimize 0 Com_purge 0 Com_rename_table 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_rollback 1128135 Com_select 50875647 Com_set_option 2 Com_show_binlogs 0 Com_show_create 2 Com_show_databases 0 Com_show_fields 3 Com_show_grants 0 Com_show_keys 0 Com_show_logs 0 Com_show_master_status 0 Com_show_open_tables 0 Com_show_processlist 0 Com_show_slave_status 0 Com_show_status 1033074 Com_show_tables 0 Com_show_variables 4 Com_slave_start 0 Com_slave_stop 0 Com_truncate 0 Com_unlock_tables 0 Com_update 1538149 Connections 150023 Created_tmp_disk_tables 3227 Created_tmp_tables 159832 Created_tmp_files 4444 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_delete 521655 Handler_read_first 70887 Handler_read_key 121827385 Handler_read_next 472585953 Handler_read_prev 75011 Handler_read_rnd 27712353 Handler_read_rnd_next 283536234 Handler_update 22732289 Handler_write 128189283 Key_blocks_used 15582 Key_read_requests 2973620399 Key_reads 8490571 Key_write_requests 85209981 Key_writes 6021786 Max_used_connections 250 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables 64 Open_files 106 Open_streams 0 Opened_tables 544468 Questions 54979099 Select_full_join 1 Select_full_range_join 0 Select_range 38149 Select_range_check 0 Select_scan 33379 Slave_running OFF Slave_open_temp_tables 0 Slow_launch_threads 23 Slow_queries 5197 Sort_merge_passes 6675 Sort_range 65131 Sort_rows 20281227 Sort_scan 13879 Table_locks_immediate 51901895 Table_locks_waited 622366 Threads_cached 0 Threads_created 150022 Threads_connected 90 Threads_running 1 Uptime 369589
SHOW VARIABLES:
back_log 50 basedir /usr/local/mysql/ binlog_cache_size 32768 character_set latin1 character_sets latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 datadir /usr/local/mysql/var/ delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 0 have_bdb NO have_gemini NO have_innodb NO have_isam YES have_raid NO have_openssl NO init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 16773120 language /usr/local/mysql/share/mysql/english/ large_files_support ON locked_in_memory OFF log OFF log_update OFF log_bin OFF log_slave_updates OFF log_long_queries OFF long_query_time 10 low_priority_updates OFF lower_case_table_names 0 max_allowed_packet 1047552 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 250 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options 0 myisam_sort_buffer_size 8388608 net_buffer_length 7168 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file /usr/local/mysql/var/www.wikipedia.org.pid port 3306 protocol_version 10 record_buffer 131072 record_rnd_buffer 131072 query_buffer_size 0 safe_show_database OFF server_id 1 slave_net_timeout 3600 skip_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket /tmp/mysql.sock sort_buffer 524280 sql_mode 0 table_cache 64 table_type MYISAM thread_cache_size 0 thread_stack 65536 transaction_isolation READ-COMMITTED timezone UTC tmp_table_size 33554432 tmpdir /tmp/ version 3.23.51 wait_timeout 28800
-- brion vibber (brion @ pobox.com)
Here are the results of my analysis of the MySQL server configuration. Keep in mind that I am not a database or MySQL expert while you are reading this. All the values reported are from SHOW STATUS and SHOW VARIABLES commands run by Brion earlier today. They reflect data collected from the past 4.3 days.
First some, general statistics of interest:
There were 5197 "Slow_queries" meaning that they took longer than 10 seconds (real time, not CPU) to process. There were 54,979,099 queries during this period, so only 0.009% took longer than 10 seconds.
Table_locks_immediate 51901895 Table_locks_waited 622366
Showing that 1.2% of all table locks were blocked by another lock.
The current server configuration looks to be using the defaults for a medium size server as provided with MySQL distribution in the file "my-medium.cnf". My overall recommendation is to change to the "my-large.cnf" defaults if there are sufficient resource. It is "for large systems with 512M RAM where the system mainly runs MySQL." Assuming the server is only running Wikipedia and has the reported 2 Gig of RAM, this shouldn't be a problem.
I have found two serious problems with the current setup that might explain the poor performance. If switching to the my-large.cnf defaults is not possible, then I suggest trying at least these two changes and seeing if performance improves.
The following list is straight out of the MySQL documentation. I will show the relevant variables from SHOW STATUS and SHOW VARIABLES, describe their meaning and comment:
* If Opened_tables is big, then your table_cache variable is probably too small.
table_cache 64 Open_tables 64 Opened_tables 544468
This is the first serious problem. "The table_cache is the number of open tables for all threads. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table." Therefore, even though we only have a few tables, we will need many more open_tables.
The Opened_tables (note the "ed") value is high and shows the number of cache misses. Everything I have read about configuring the MySQL server suggests that getting the table_cache size correct is one of the two best things you can do to improve performance.
I suggest the table_cache be increased to 256, the default value from my-large.cnf.
See the following for more information on the table_cache http://www.databasejournal.com/features/mysql/article.php/10897_1402311_3 (this is also a really good article on optimizing MySQL's configuration in general) http://www.mysql.com/documentation/mysql/bychapter/index.html#Table_cache
* If Key_reads is big, then your key_buffer_size variable is probably too small. The cache hit rate can be calculated with Key_reads/Key_read_requests.
key_buffer_size 16M Key_read_requests 2973620399 Key_reads 8490571 (cache hit rate = 0.0028)
"The key_buffer_size affects the size of the index buffers and the speed of index handling, particularly reading." We seem to be doing pretty well here. The MySQL manual (and other sources) say that "Key_reads/Key_read_request ratio should normally be < 0.01." This is the other most important thing to get correct.
* If Handler_read_rnd is big, then you probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler_read_rnd 27712353 Handler_read_rnd_next 283536234
These values are high, which says that we could probably stand to improve our indexes and queries. Improving this would require a lot of work so it should be one of the last things considered (or part of a longer term solution).
* If Threads_created is big, you may want to increase the thread_cache_size variable. The cache hit rate can be calculated with Threads_created/Connections.
thread_cache_size 0 Threads_created 150022 Connections 150023
We aren't performing any thread caching. This is the second problem that I think should be fixed. A cache size of zero is the default for my-medium.cnf but the recommended size in my-large.cnf is 8.
A related problem I noticed was that it looks like we're reaching the maximum number of connections. I couldn't find any discussion about this or if it was even a bad thing, but it may be worth noting.
max_connections 250 Max_used_connections 250
* If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size variable to get the temporary tables memory-based instead of disk based.
tmp_table_size 32M Created_tmp_disk_tables 3227 Created_tmp_tables 159832 Created_tmp_files 4444
Created_tmp_disk_tables are the "number of implicit temporary tables on disk created while executing statements" and Created_tmp_tables are memory-based. Obviously it is bad if you have to go to disk instead of memory. About 2% of temp tables go to disk, which doesn't seem too bad but increasing the tmp_table_size probably couldn't hurt either.
From all of this analysis, I think the best thing we could do would be to
try using the my-large.cnf defaults if possible. Minimally, we should try increasing the table_cache and turning on thread caching to see if that helps.
For further analysis, it might be useful to collect statistics for only a period of poor performance. That might highlight some of the problems better.
Hopefully this was helpful, my apologies for it being so lengthy.
Best Regards, Steve Rawlinson
Steve Rawlinson wrote:
The current server configuration looks to be using the defaults for a medium size server as provided with MySQL distribution in the file "my-medium.cnf". My overall recommendation is to change to the "my-large.cnf" defaults if there are sufficient resource. It is "for large systems with 512M RAM where the system mainly runs MySQL." Assuming the server is only running Wikipedia and has the reported 2 Gig of RAM, this shouldn't be a problem.
Sounds reasonable, I'll switch the config file tonight.
-- brion vibber (brion @ pobox.com)
When I switched the config file, I forgot to add in the max_connections setting. Apparently the default is a mere 100, and I didn't notice that it didn't get set in my-large.cnf. I bumped it back to our previous setting of 250.
-- brion vibber (brion @ pobox.com)
On Fri, 20 Sep 2002, Brion VIBBER wrote:
When I switched the config file, I forgot to add in the max_connections setting. Apparently the default is a mere 100, and I didn't notice that it didn't get set in my-large.cnf. I bumped it back to our previous setting of 250.
Sorry, I missed that custom max_connections setting as well.
Any reports on performance yet? I'm eager to see if the new settings have made any difference (for better or worse). Maybe it's still too early to tell, but if anyone has anything to report over the next few days I'd appreciate hearing about it.
Regards, Steve Rawlinson
wikitech-l@lists.wikimedia.org