unixman
Thu 11th Oct '01, 12:40pm
I posted this over in the MySQL forum, then I realized that this is probably a better place - so here it is:
I can see a slow access time for VB (or MySQL underneath) if the system were getting nailed, but consider this:
5:37pm up 55 days, 8:08, 2 users, load average: 0.35, 0.34, 0.35
Sometimes, it can take 60 seconds or more for a simple forum page to load ... moan. It may be possible that this is a bandwidth issue with my ISP - still checking into that. However, I'm wondering if there may be something tweakable in my MySQL configuration. Any feedback welcome ...
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=100
set-variable = key_buffer=32M
set-variable = join_buffer=6M
set-variable = max_allowed_packet=2M
set-variable = table_cache=512
set-variable = sort_buffer=4M
set-variable = record_buffer=6M
set-variable = myisam_sort_buffer_size=8M
set-variable = thread_cache_size=16
set-variable = thread_stack=128K
And extended-status:
5:38pm up 55 days, 8:10, 2 users, load average: 0.23, 0.29, 0.33
105 processes: 100 sleeping, 1 running, 4 zombie, 0 stopped
Mem: 384296K av, 375520K used, 8776K free, 0K shrd, 4288K buff
Swap: 2227508K av, 141164K used, 2086344K free 142948K cached
Wed Oct 10 17:38:43 EDT 2001
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 1471 |
| Aborted_connects | 8 |
| Bytes_received | 4249737451 |
| Bytes_sent | 23033664 |
| Connections | 2351067 |
| Created_tmp_disk_tables | 240533 |
| Created_tmp_tables | 592561 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 2 |
| Handler_delete | 143806 |
| Handler_read_first | 1329442 |
| Handler_read_key | 113296464 |
| Handler_read_next | 454346503 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 122969425 |
| Handler_read_rnd_next | 1589184431 |
| Handler_update | 2274071 |
| Handler_write | 232204446 |
| Key_blocks_used | 969 |
| Key_read_requests | 38312 |
| Key_reads | 968 |
| Key_write_requests | 139 |
| Key_writes | 131 |
| Max_used_connections | 81 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 45 | 9% of table_cache in use
| Open_files | 81 |
| Open_streams | 0 |
| Opened_tables | 1133 |
| Questions | 26515692 |
| Select_full_join | 245191 |
| Select_full_range_join | 18 |
| Select_range | 3702046 |
| Select_range_check | 0 |
| Select_scan | 3542675 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 34 |
| Slow_queries | 3213 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 3478289 |
| Sort_rows | 133712376 |
| Sort_scan | 1679833 |
| Threads_cached | 4 |
| Threads_created | 3405 |
| Threads_connected | 21 |
| Threads_running | 1 |
| Uptime | 2575279 | 29 days 19 hrs 21 mins 19 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.025266 (Cache hit = 99.974734%)
Key Writes/Key Write Requests = 0.942446
Connections/second = 0.913 (/hour = 3286.573)
KB received/second = 0.814 (/hour = 2931.622)
KB sent/second = 0.009 (/hour = 31.443)
Temporary Tables Created/second = 0.230 (/hour = 828.345)
Opened Tables/second = 0.000 (/hour = 1.584)
Slow Queries/second = 0.001 (/hour = 4.491)
% of slow queries = 0.012%
Queries/second = 10.296 (/hour = 37066.466)
This is running Linux 2.04 - with the improved VM - so don't flip out when you see the swap space use. This version of the kernel doesn't release swap space when something is swapped back in - it does it much differently (and more efficiently). I've noticed a huge increase in system performance over previous 2.x kernels - FWIW.
Anything jump out at anyone?
Created_tmp_tables seems a bit high - not sure what is causing it, or how I can reduce it, though. Slow queries are under 1%.
Cheers.
I can see a slow access time for VB (or MySQL underneath) if the system were getting nailed, but consider this:
5:37pm up 55 days, 8:08, 2 users, load average: 0.35, 0.34, 0.35
Sometimes, it can take 60 seconds or more for a simple forum page to load ... moan. It may be possible that this is a bandwidth issue with my ISP - still checking into that. However, I'm wondering if there may be something tweakable in my MySQL configuration. Any feedback welcome ...
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=100
set-variable = key_buffer=32M
set-variable = join_buffer=6M
set-variable = max_allowed_packet=2M
set-variable = table_cache=512
set-variable = sort_buffer=4M
set-variable = record_buffer=6M
set-variable = myisam_sort_buffer_size=8M
set-variable = thread_cache_size=16
set-variable = thread_stack=128K
And extended-status:
5:38pm up 55 days, 8:10, 2 users, load average: 0.23, 0.29, 0.33
105 processes: 100 sleeping, 1 running, 4 zombie, 0 stopped
Mem: 384296K av, 375520K used, 8776K free, 0K shrd, 4288K buff
Swap: 2227508K av, 141164K used, 2086344K free 142948K cached
Wed Oct 10 17:38:43 EDT 2001
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 1471 |
| Aborted_connects | 8 |
| Bytes_received | 4249737451 |
| Bytes_sent | 23033664 |
| Connections | 2351067 |
| Created_tmp_disk_tables | 240533 |
| Created_tmp_tables | 592561 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 2 |
| Handler_delete | 143806 |
| Handler_read_first | 1329442 |
| Handler_read_key | 113296464 |
| Handler_read_next | 454346503 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 122969425 |
| Handler_read_rnd_next | 1589184431 |
| Handler_update | 2274071 |
| Handler_write | 232204446 |
| Key_blocks_used | 969 |
| Key_read_requests | 38312 |
| Key_reads | 968 |
| Key_write_requests | 139 |
| Key_writes | 131 |
| Max_used_connections | 81 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 45 | 9% of table_cache in use
| Open_files | 81 |
| Open_streams | 0 |
| Opened_tables | 1133 |
| Questions | 26515692 |
| Select_full_join | 245191 |
| Select_full_range_join | 18 |
| Select_range | 3702046 |
| Select_range_check | 0 |
| Select_scan | 3542675 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 34 |
| Slow_queries | 3213 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 3478289 |
| Sort_rows | 133712376 |
| Sort_scan | 1679833 |
| Threads_cached | 4 |
| Threads_created | 3405 |
| Threads_connected | 21 |
| Threads_running | 1 |
| Uptime | 2575279 | 29 days 19 hrs 21 mins 19 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.025266 (Cache hit = 99.974734%)
Key Writes/Key Write Requests = 0.942446
Connections/second = 0.913 (/hour = 3286.573)
KB received/second = 0.814 (/hour = 2931.622)
KB sent/second = 0.009 (/hour = 31.443)
Temporary Tables Created/second = 0.230 (/hour = 828.345)
Opened Tables/second = 0.000 (/hour = 1.584)
Slow Queries/second = 0.001 (/hour = 4.491)
% of slow queries = 0.012%
Queries/second = 10.296 (/hour = 37066.466)
This is running Linux 2.04 - with the improved VM - so don't flip out when you see the swap space use. This version of the kernel doesn't release swap space when something is swapped back in - it does it much differently (and more efficiently). I've noticed a huge increase in system performance over previous 2.x kernels - FWIW.
Anything jump out at anyone?
Created_tmp_tables seems a bit high - not sure what is causing it, or how I can reduce it, though. Slow queries are under 1%.
Cheers.