Yet another help me tune my server thread
Dual Xeon 2.40GHz w/hyperthreading
4GB of ram (just upgraded from 2)
and a pair of 73GB SCSI drives. MySQL on drive two and the rest on drive 1
I run several decent size forums (heavy MySQL calls)
and I am launching another site shortly that will be very SQL intensive. We will likely cluster some servers later, but I need this to work for a few months.
Here is the output of my stats script that I have.
PHP Code:
Wed Dec 14 20:03:19 MST 2005
20:03:20 up 8 days, 23:56, 0 users, load average: 12.75, 13.97, 13.42
212 processes: 210 sleeping, 2 running, 0 zombie, 0 stopped
total 37.9% 0.0% 10.9% 0.0% 0.4% 5.8% 44.7%
cpu00 44.6% 0.0% 36.8% 0.0% 0.9% 0.0% 17.4%
cpu01 32.0% 0.0% 3.8% 0.0% 0.0% 0.9% 63.1%
cpu02 37.8% 0.0% 0.9% 0.0% 0.0% 20.3% 40.7%
cpu03 37.2% 0.0% 1.9% 0.0% 0.9% 1.9% 57.8%
Mem: 4063284k av, 4024588k used, 38696k free, 0k shrd, 59944k buff
3118072k actv, 590252k in_d, 65284k in_c
Swap: 2048276k av, 376424k used, 1671852k free 3377716k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
13121 mysql 16 0 200M 123M 1980 D 0.9 3.1 17:47 0 mysqld
10825 nobody 16 0 13452 11M 3188 R 0.9 0.3 1:56 2 httpd
23262 nobody 20 0 1116 1116 772 R 0.7 0.0 0:00 1 top
Http processes currently running = 43
Mysql processes currently running = 49
Netstat information summary
1 SYN_SENT
1 1 TIME_WAIT
1 5 TIME_WAIT
1 59 ESTABLISHED
1 59 LAST_ACK
1 70 TIME_WAIT
1 9 ESTABLISHED
3 FIN_WAIT2
35 ESTABLISHED
52 LISTEN
90 TIME_WAIT
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 1142 |
| Aborted_connects | 691 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 2240356535 |
| Bytes_sent | 4181261350 |
| Com_admin_commands | 11 |
| Com_alter_db | 0 |
| Com_alter_table | 125 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 9 |
| Com_change_db | 1717279 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 9 |
| Com_create_db | 5 |
| Com_create_function | 0 |
| Com_create_index | 37 |
| Com_create_table | 341 |
| Com_dealloc_sql | 0 |
| Com_delete | 115288 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 2 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 4 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 37 |
| Com_grant | 46 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 311717 |
| Com_insert_select | 12 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 309 |
| Com_optimize | 7387 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 41490 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 2 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1834609 |
| Com_set_option | 2733 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 16 |
| Com_show_charsets | 215 |
| Com_show_collations | 215 |
| Com_show_column_types | 0 |
| Com_show_create_db | 26 |
| Com_show_create_table | 424 |
| Com_show_databases | 141 |
| Com_show_errors | 0 |
| Com_show_fields | 300 |
| Com_show_grants | 21 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 72 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 1401 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 4 |
| Com_show_tables | 6574 |
| Com_show_variables | 549 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_send_long_data | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_close | 0 |
| Com_truncate | 2 |
| Com_unlock_tables | 309 |
| Com_update | 662913 |
| Com_update_multi | 292 |
| Connections | 692482 |
| Created_tmp_disk_tables | 38300 |
| Created_tmp_files | 817 |
| Created_tmp_tables | 86654 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 1 |
| Delayed_writes | 2189 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 72912 |
| Handler_discover | 0 |
| Handler_read_first | 379255 |
| Handler_read_key | 57964144 |
| Handler_read_next | 63459527 |
| Handler_read_prev | 1467344 |
| Handler_read_rnd | 16001443 |
| Handler_read_rnd_next | 583135383 |
| Handler_rollback | 0 |
| Handler_update | 6524473 |
| Handler_write | 173441034 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 104292 |
| Key_blocks_used | 84868 |
| Key_read_requests | 167528003 |
| Key_reads | 5715681 |
| Key_write_requests | 4307467 |
| Key_writes | 1298634 |
| Max_used_connections | 42 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 2755 |
| Open_streams | 0 |
| Open_tables | 1496 | 100% of table_cache in use
| Opened_tables | 11606 |
| Qcache_free_blocks | 5522 |
| Qcache_free_memory | 16517664 |
| Qcache_hits | 3988693 |
| Qcache_inserts | 1751545 |
| Qcache_lowmem_prunes | 144075 |
| Qcache_not_cached | 82220 |
| Qcache_queries_in_cache | 8805 |
| Qcache_total_blocks | 28492 |
| Questions | 9384869 |
| Rpl_status | NULL |
| Select_full_join | 173 |
| Select_full_range_join | 11 |
| Select_range | 311479 |
| Select_range_check | 0 |
| Select_scan | 382172 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slave_retried_transactions | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 115 | (execution time > 10 secs)
| Sort_merge_passes | 408 |
| Sort_range | 250016 |
| Sort_rows | 24766577 |
| Sort_scan | 115850 |
| Table_locks_immediate | 4395324 |
| Table_locks_waited | 2785 |
| Threads_cached | 39 |
| Threads_connected | 4 |
| Threads_created | 42 |
| Threads_running | 2 |
| Uptime | 420338 | 4 days 20 hrs 45 mins 38 secs
+----------------------------+------------+
Key Reads/Key Read Requests = 0.034118 (Cache hit = 99.965882%)
Key Writes/Key Write Requests = 0.301484
Connections/second = 1.647 (/hour = 5930.787)
KB received/second = 4.989 (/hour = 17961.126)
KB sent/second = 4.989 (/hour = 17961.126)
Temporary Tables Created/second = 0.206 (/hour = 742.151)
Opened Tables/second = 0.028 (/hour = 99.400)
Slow Queries/second = 0.000 (/hour = 0.985)
% of slow queries = 0.001%
Queries/second = 22.327 (/hour = 80377.050)
MySQL Query Cache hits = 3988693/5822458(69%)
