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