| New, Need Help [message #943] |
Fri, 16 March 2007 20:50  |
 tfittsy
Messages: 1
Registered: March 2007 |
Junior Member |
|
|
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 Variable
1 Flush_commands
299 Slow_queries
643 Handler_commit
670 k Handler_delete
0 Handler_discover
24 M Handler_read_first
1,744 M Handler_read_key
862 M Handler_read_next
12 M Handler_read_prev
131 M Handler_read_rnd
1,020 M Handler_read_rnd_next
567 Handler_rollback
2,033 M Handler_update
375 M Handler_write
0 Qcache_free_blocks
0 Qcache_free_memory
0 Qcache_hits
0 Qcache_inserts
0 Qcache_lowmem_prunes
0 Qcache_not_cached
0 Qcache_queries_in_cache
0 Qcache_total_blocks
308 Slow_launch_threads
0 Threads_cached
4 Threads_connected
1,376 k Threads_created
1 Threads_running
0.00% Threads_cache_hitrate_%
0 Binlog_cache_disk_use
0 Binlog_cache_use
Begin Temporary data
355 k Created_tmp_disk_tables
4,984 Created_tmp_files
17 M Created_tmp_tables
0 Delayed_insert_threads
0 Delayed_writes
0 Not_flushed_delayed_rows
0 Key_blocks_not_flushed
5,713 Key_blocks_unused
1,952 Key_blocks_used
235.87G Key_read_requests
29 M Key_reads
11 M Key_write_requests
289 k Key_writes
30.26% Key_buffer_fraction_%
2,664 k Select_full_join
0 Select_full_range_join
12 k Select_range
0 Select_range_check
24 M Select_scan
NULL Rpl_status
0 Slave_open_temp_tables
0 Slave_retried_transactions
OFF Slave_running
2,490 Sort_merge_passes
15 k Sort_range
188 M Sort_rows
17 M Sort_scan
64 Open_tables
4,399 k Opened_tables
165 M Table_locks_immediate
312 k Table_locks_waited
116 Open_files
0 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.
|
|
|
|
| Re: New, Need Help [message #971 is a reply to message #943 ] |
Thu, 22 March 2007 09:53  |
tanj
Messages: 16
Registered: March 2007
Location: France |
Junior Member |
|
|
|
Please post output of SHOW VARIABLES, so we could comment the server settings.
|
|
|
Members
Search
Help
Register
Login
Home
