Hi,
I am running a pretty small MySQL Database (92 tables, biggest table is under 35000 rows, average is 2000 rows, only 40 of them have over 250 rows)
But performance seems sub-obtimal. I’m running the database on a dedicated server with Core 2 Duo and 2GB Ram. The only other duties of the server are a standard web server. I noticed that mysql is using about 35% of my cpu usage (which I guess is only 17% on dual core). I can definelty spare it, but it still seems high and I would like to optimize it my DB and queries as much as possible.
Below is what I get for runtime variables.
Value Variable1 Flush_commands299 Slow_queries643 Handler_commit670 k Handler_delete0 Handler_discover24 M Handler_read_first1,744 M Handler_read_key862 M Handler_read_next12 M Handler_read_prev131 M Handler_read_rnd1,020 M Handler_read_rnd_next567 Handler_rollback2,033 M Handler_update375 M Handler_write0 Qcache_free_blocks0 Qcache_free_memory0 Qcache_hits0 Qcache_inserts0 Qcache_lowmem_prunes0 Qcache_not_cached0 Qcache_queries_in_cache0 Qcache_total_blocks308 Slow_launch_threads0 Threads_cached4 Threads_connected1,376 k Threads_created1 Threads_running0.00% Threads_cache_hitrate_%0 Binlog_cache_disk_use0 Binlog_cache_use Begin Temporary data355 k Created_tmp_disk_tables4,984 Created_tmp_files17 M Created_tmp_tables0 Delayed_insert_threads0 Delayed_writes0 Not_flushed_delayed_rows0 Key_blocks_not_flushed5,713 Key_blocks_unused1,952 Key_blocks_used235.87G Key_read_requests29 M Key_reads11 M Key_write_requests289 k Key_writes30.26% Key_buffer_fraction_%2,664 k Select_full_join0 Select_full_range_join12 k Select_range0 Select_range_check24 M Select_scanNULL Rpl_status0 Slave_open_temp_tables0 Slave_retried_transactionsOFF Slave_running2,490 Sort_merge_passes15 k Sort_range188 M Sort_rows17 M Sort_scan64 Open_tables4,399 k Opened_tables165 M Table_locks_immediate312 k Table_locks_waited116 Open_files0 Open_streams
From what I read, I’m most concerned about:
slow_queries
Handler_read_rnd
Handler_read_rnd_next
slow_launch_threads
Created_tmp_disk_tables
Select_full_join
Sort_merge_passes
Opened_tables
Table_locks_waited
I’m on MySql 4.1.21 and can’t move to 5+ for at least a few more months. I realize a lot of it is probably that my queries aren’t optimized so if someone can tell me what I need to do to log the slow queries and the ones that cause hanlder_read_rnd and rnd_next and select_full_join to be so high so that I can find and fix them I would apprecaite it.
Also, if someone can tell me how to change my settings (and what I should change them to) to maximize performance and memory usage (the whole database should easily fit in ram) and anything else I can do to speed things up.
Thanks.