New, Need Help


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:


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.


[B]tfittsy wrote on Fri, 16 March 2007 20:50[/B]

0 Qcache_free_blocks0 Qcache_free_memory0 Qcache_hits0 Qcache_inserts0 Qcache_lowmem_prunes0 Qcache_not_cached0 Qcache_queries_in_cache0 Qcache_total_blocks0 Threads_cached0.00% Threads_cache_hitrate_%64 Open_tables4,399 k Opened_tables

Enable the Query cache Enable the Thread cache Enable the Table cache

Those would speed things up substantially.

Please post output of SHOW VARIABLES, so we could comment the server settings.