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. 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

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)