Andreas
Tue 11th Jan '05, 4:33pm
1) Dedicated Server
2) Dual Xeon 2.4 Ghz
2 GB RAM
2x Maxtor 6Y080P0
(Only 1 HD currently being used.
What should be put on the 2nd HD?)
Debian Linux, Kernel Version 2.4.26
Apache 1.3.26
PHP 4.3.10
mySQL 4.1.8
3) No InnoDB
4) N/A
5)
20:53:38 up 198 days, 2:42, 1 user, load average: 1.21, 1.44, 1.53
276 processes: 275 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 37.6% user, 7.9% system, 0.0% nice, 54.4% idle
Mem: 2069816K total, 1838456K used, 231360K free, 46804K buffers
Swap: 1999856K total, 17808K used, 1982048K free, 1269704K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
1761 mysql 9 0 72964 71M 3412 S 14.6 3.5 2:15 mysqld
11524 www-data 9 0 8372 8196 5964 S 12.7 0.3 0:00 apache
11540 www-data 9 0 8412 8236 5968 S 10.7 0.3 0:00 apache
11282 www-data 9 0 8716 8540 6176 S 7.3 0.4 0:03 apache
11343 www-data 9 0 9104 8928 6580 S 7.1 0.4 0:03 apache
11387 www-data 9 0 9284 9108 5964 S 7.1 0.4 0:03 apache
3090 mysql 9 0 72964 71M 3412 S 6.9 3.5 1:31 mysqld
10693 www-data 9 0 9916 9740 7368 S 6.9 0.4 0:13 apache
11351 www-data 9 0 9240 9064 6736 S 6.5 0.4 0:03 apache
11166 www-data 9 0 9812 9636 7252 S 6.3 0.4 0:08 apache
29722 mysql 9 0 72964 71M 3412 S 5.1 3.5 3:03 mysqld
11437 www-data 9 0 8572 8396 6196 S 5.0 0.4 0:02 apache
11505 www-data 9 0 8788 8612 6264 S 4.8 0.4 0:00 apache
11512 www-data 11 0 8168 7992 5772 S 4.8 0.3 0:00 apache
6)
# You can copy this to one of:
# /etc/mysql/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
log = /var/log/mysql.log
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
7)
Tue Jan 11 21:11:59 CET 2005
21:12:01 up 198 days, 3:01, 1 user, load average: 1.84, 2.07, 1.86
282 processes: 277 sleeping, 4 running, 1 zombie, 0 stopped
Mem: 2069816K total, 1897528K used, 172288K free, 50228K buffers
Swap: 1999856K total, 17772K used, 1982084K free, 1322000K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
29441 mysql 14 0 73776 72M 3412 R 4.7 3.5 3:03 mysqld
Http processes currently running = 2
Mysql processes currently running = 72
Netstat information summary
1 FIN_WAIT1
3 SYN_RECV
15 LISTEN
19 FIN_WAIT2
142 TIME_WAIT
166 ESTABLISHED
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 16 |
| Aborted_connects | 24 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 557896527 |
| Bytes_sent | 1509021164 |
| Com_admin_commands | 2 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 312228 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 3157 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 25989 |
| Com_insert_select | 994 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 11 |
| Com_optimize | 0 |
| 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 | 10423 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 607762 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 1 |
| Com_show_errors | 0 |
| Com_show_fields | 37 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 4 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 90 |
| Com_show_variables | 46 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 11 |
| Com_update | 563272 |
| Com_update_multi | 0 |
| Connections | 250122 |
| Created_tmp_disk_tables | 7426 |
| Created_tmp_files | 5202 |
| Created_tmp_tables | 52340 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 20642 |
| Handler_discover | 0 |
| Handler_read_first | 93688 |
| Handler_read_key | 151723639 |
| Handler_read_next | 153929414 |
| Handler_read_prev | 1147339 |
| Handler_read_rnd | 19851616 |
| Handler_read_rnd_next | 805131849 |
| Handler_rollback | 0 |
| Handler_update | 2098695 |
| Handler_write | 31217351 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 14497 |
| Key_read_requests | 359181917 |
| Key_reads | 705580 |
| Key_write_requests | 867294 |
| Key_writes | 92803 |
| Max_used_connections | 84 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 515 |
| Open_streams | 0 |
| Open_tables | 381 | 37% of table_cache in use
| Opened_tables | 392 |
| Qcache_free_blocks | 1919 |
| Qcache_free_memory | 7522288 |
| Qcache_hits | 760412 |
| Qcache_inserts | 598800 |
| Qcache_lowmem_prunes | 6624 |
| Qcache_not_cached | 9020 |
| Qcache_queries_in_cache | 2559 |
| Qcache_total_blocks | 7957 |
| Questions | 2534718 |
| Rpl_status | NULL |
| Select_full_join | 137 |
| Select_full_range_join | 0 |
| Select_range | 75932 |
| Select_range_check | 0 |
| Select_scan | 167028 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 17 | (execution time > 10 secs)
| Sort_merge_passes | 2601 |
| Sort_range | 77704 |
| Sort_rows | 204382647 |
| Sort_scan | 69413 |
| Table_locks_immediate | 1950783 |
| Table_locks_waited | 30562 |
| Threads_cached | 47 |
| Threads_connected | 17 |
| Threads_created | 84 |
| Threads_running | 3 |
| Uptime | 21759 | 6 hrs 2 mins 39 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.001964 (Cache hit = 99.998036%)
Key Writes/Key Write Requests = 0.107003
Connections/second = 11.495 (/hour = 41382.380)
KB received/second = 25.039 (/hour = 90139.804)
KB sent/second = 67.726 (/hour = 243814.091)
Temporary Tables Created/second = 2.405 (/hour = 8659.589)
Opened Tables/second = 0.018 (/hour = 64.856)
Slow Queries/second = 0.001 (/hour = 2.813)
% of slow queries = 0.001%
Queries/second = 116.491 (/hour = 419366.000)
Now this is a bit more up to date (but currently the forums are empty, as it is 2am here)
hu Jan 13 02:13:42 CET 2005
02:13:43 up 199 days, 8:03, 0 users, load average: 0.28, 0.26, 0.23
182 processes: 181 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 2069816K total, 1566868K used, 502948K free, 33700K buffers
Swap: 1999856K total, 88912K used, 1910944K free, 1239748K cached
Http processes currently running = 2
Mysql processes currently running = 72
Netstat information summary
15 LISTEN
23 ESTABLISHED
26 TIME_WAIT
30 FIN_WAIT2
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 20 |
| Aborted_connects | 127 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 2204657639 |
| Bytes_sent | 3610816378 |
| Com_admin_commands | 2 |
| Com_alter_db | 0 |
| Com_alter_table | 5 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1217550 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 13716 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 102910 |
| Com_insert_select | 4017 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 24 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 1 |
| Com_replace | 41315 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 2377327 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 14 |
| Com_show_errors | 0 |
| Com_show_fields | 142 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 28 |
| 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 | 5 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 531 |
| Com_show_variables | 242 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 24 |
| Com_update | 2210897 |
| Com_update_multi | 10 |
| Connections | 978149 |
| Created_tmp_disk_tables | 27135 |
| Created_tmp_files | 18840 |
| Created_tmp_tables | 201040 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 92071 |
| Handler_discover | 0 |
| Handler_read_first | 371692 |
| Handler_read_key | 582838416 |
| Handler_read_next | 585582364 |
| Handler_read_prev | 7275493 |
| Handler_read_rnd | 70196676 |
| Handler_read_rnd_next | 3185431433 |
| Handler_rollback | 0 |
| Handler_update | 9667916 |
| Handler_write | 111661605 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 14497 |
| Key_read_requests | 1377695231 |
| Key_reads | 2524027 |
| Key_write_requests | 3276532 |
| Key_writes | 380605 |
| Max_used_connections | 263 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1053 |
| Open_streams | 0 |
| Open_tables | 1024 | 100% of table_cache in use
| Opened_tables | 1848 |
| Qcache_free_blocks | 1367 |
| Qcache_free_memory | 13763072 |
| Qcache_hits | 3003758 |
| Qcache_inserts | 2338342 |
| Qcache_lowmem_prunes | 31103 |
| Qcache_not_cached | 39210 |
| Qcache_queries_in_cache | 1318 |
| Qcache_total_blocks | 4665 |
| Questions | 9951388 |
| Rpl_status | NULL |
| Select_full_join | 751 |
| Select_full_range_join | 0 |
| Select_range | 299980 |
| Select_range_check | 0 |
| Select_scan | 654466 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 3 |
| Slow_queries | 373 | (execution time > 10 secs)
| Sort_merge_passes | 9430 |
| Sort_range | 310557 |
| Sort_rows | 738932377 |
| Sort_scan | 265995 |
| Table_locks_immediate | 7642848 |
| Table_locks_waited | 146753 |
| Threads_cached | 61 |
| Threads_connected | 3 |
| Threads_created | 2397 |
| Threads_running | 1 |
| Uptime | 126261 | 1 day 11 hrs 4 mins 21 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.001832 (Cache hit = 99.998168%)
Key Writes/Key Write Requests = 0.116161
Connections/second = 7.747 (/hour = 27889.344)
KB received/second = 16.610 (/hour = 59794.739)
KB sent/second = 16.610 (/hour = 59794.739)
Temporary Tables Created/second = 1.592 (/hour = 5732.126)
Opened Tables/second = 0.015 (/hour = 52.691)
Slow Queries/second = 0.003 (/hour = 10.635)
% of slow queries = 0.004%
Queries/second = 78.816 (/hour = 283737.629)
8) vBulletin, phpAdsNew and a custom portal script based on vBulletin
9) Maximum was 760, average is about 400 based on a 15 minute cookie timeout
10) http://www.planet-liebe.de/phpinfo.php
11)
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
12) vB3 RC3 with patches
It's getting slow when there are > 500 users, although load doesn't go over 4 and Swap isn't being used much.
Any hints to get this faster?
2) Dual Xeon 2.4 Ghz
2 GB RAM
2x Maxtor 6Y080P0
(Only 1 HD currently being used.
What should be put on the 2nd HD?)
Debian Linux, Kernel Version 2.4.26
Apache 1.3.26
PHP 4.3.10
mySQL 4.1.8
3) No InnoDB
4) N/A
5)
20:53:38 up 198 days, 2:42, 1 user, load average: 1.21, 1.44, 1.53
276 processes: 275 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 37.6% user, 7.9% system, 0.0% nice, 54.4% idle
Mem: 2069816K total, 1838456K used, 231360K free, 46804K buffers
Swap: 1999856K total, 17808K used, 1982048K free, 1269704K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
1761 mysql 9 0 72964 71M 3412 S 14.6 3.5 2:15 mysqld
11524 www-data 9 0 8372 8196 5964 S 12.7 0.3 0:00 apache
11540 www-data 9 0 8412 8236 5968 S 10.7 0.3 0:00 apache
11282 www-data 9 0 8716 8540 6176 S 7.3 0.4 0:03 apache
11343 www-data 9 0 9104 8928 6580 S 7.1 0.4 0:03 apache
11387 www-data 9 0 9284 9108 5964 S 7.1 0.4 0:03 apache
3090 mysql 9 0 72964 71M 3412 S 6.9 3.5 1:31 mysqld
10693 www-data 9 0 9916 9740 7368 S 6.9 0.4 0:13 apache
11351 www-data 9 0 9240 9064 6736 S 6.5 0.4 0:03 apache
11166 www-data 9 0 9812 9636 7252 S 6.3 0.4 0:08 apache
29722 mysql 9 0 72964 71M 3412 S 5.1 3.5 3:03 mysqld
11437 www-data 9 0 8572 8396 6196 S 5.0 0.4 0:02 apache
11505 www-data 9 0 8788 8612 6264 S 4.8 0.4 0:00 apache
11512 www-data 11 0 8168 7992 5772 S 4.8 0.3 0:00 apache
6)
# You can copy this to one of:
# /etc/mysql/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
log = /var/log/mysql.log
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
7)
Tue Jan 11 21:11:59 CET 2005
21:12:01 up 198 days, 3:01, 1 user, load average: 1.84, 2.07, 1.86
282 processes: 277 sleeping, 4 running, 1 zombie, 0 stopped
Mem: 2069816K total, 1897528K used, 172288K free, 50228K buffers
Swap: 1999856K total, 17772K used, 1982084K free, 1322000K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
29441 mysql 14 0 73776 72M 3412 R 4.7 3.5 3:03 mysqld
Http processes currently running = 2
Mysql processes currently running = 72
Netstat information summary
1 FIN_WAIT1
3 SYN_RECV
15 LISTEN
19 FIN_WAIT2
142 TIME_WAIT
166 ESTABLISHED
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 16 |
| Aborted_connects | 24 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 557896527 |
| Bytes_sent | 1509021164 |
| Com_admin_commands | 2 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 312228 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 3157 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 25989 |
| Com_insert_select | 994 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 11 |
| Com_optimize | 0 |
| 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 | 10423 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 607762 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 1 |
| Com_show_errors | 0 |
| Com_show_fields | 37 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 4 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 90 |
| Com_show_variables | 46 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 11 |
| Com_update | 563272 |
| Com_update_multi | 0 |
| Connections | 250122 |
| Created_tmp_disk_tables | 7426 |
| Created_tmp_files | 5202 |
| Created_tmp_tables | 52340 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 20642 |
| Handler_discover | 0 |
| Handler_read_first | 93688 |
| Handler_read_key | 151723639 |
| Handler_read_next | 153929414 |
| Handler_read_prev | 1147339 |
| Handler_read_rnd | 19851616 |
| Handler_read_rnd_next | 805131849 |
| Handler_rollback | 0 |
| Handler_update | 2098695 |
| Handler_write | 31217351 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 14497 |
| Key_read_requests | 359181917 |
| Key_reads | 705580 |
| Key_write_requests | 867294 |
| Key_writes | 92803 |
| Max_used_connections | 84 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 515 |
| Open_streams | 0 |
| Open_tables | 381 | 37% of table_cache in use
| Opened_tables | 392 |
| Qcache_free_blocks | 1919 |
| Qcache_free_memory | 7522288 |
| Qcache_hits | 760412 |
| Qcache_inserts | 598800 |
| Qcache_lowmem_prunes | 6624 |
| Qcache_not_cached | 9020 |
| Qcache_queries_in_cache | 2559 |
| Qcache_total_blocks | 7957 |
| Questions | 2534718 |
| Rpl_status | NULL |
| Select_full_join | 137 |
| Select_full_range_join | 0 |
| Select_range | 75932 |
| Select_range_check | 0 |
| Select_scan | 167028 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 17 | (execution time > 10 secs)
| Sort_merge_passes | 2601 |
| Sort_range | 77704 |
| Sort_rows | 204382647 |
| Sort_scan | 69413 |
| Table_locks_immediate | 1950783 |
| Table_locks_waited | 30562 |
| Threads_cached | 47 |
| Threads_connected | 17 |
| Threads_created | 84 |
| Threads_running | 3 |
| Uptime | 21759 | 6 hrs 2 mins 39 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.001964 (Cache hit = 99.998036%)
Key Writes/Key Write Requests = 0.107003
Connections/second = 11.495 (/hour = 41382.380)
KB received/second = 25.039 (/hour = 90139.804)
KB sent/second = 67.726 (/hour = 243814.091)
Temporary Tables Created/second = 2.405 (/hour = 8659.589)
Opened Tables/second = 0.018 (/hour = 64.856)
Slow Queries/second = 0.001 (/hour = 2.813)
% of slow queries = 0.001%
Queries/second = 116.491 (/hour = 419366.000)
Now this is a bit more up to date (but currently the forums are empty, as it is 2am here)
hu Jan 13 02:13:42 CET 2005
02:13:43 up 199 days, 8:03, 0 users, load average: 0.28, 0.26, 0.23
182 processes: 181 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 2069816K total, 1566868K used, 502948K free, 33700K buffers
Swap: 1999856K total, 88912K used, 1910944K free, 1239748K cached
Http processes currently running = 2
Mysql processes currently running = 72
Netstat information summary
15 LISTEN
23 ESTABLISHED
26 TIME_WAIT
30 FIN_WAIT2
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 20 |
| Aborted_connects | 127 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 2204657639 |
| Bytes_sent | 3610816378 |
| Com_admin_commands | 2 |
| Com_alter_db | 0 |
| Com_alter_table | 5 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1217550 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 13716 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 102910 |
| Com_insert_select | 4017 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 24 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 1 |
| Com_replace | 41315 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 2377327 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 14 |
| Com_show_errors | 0 |
| Com_show_fields | 142 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 28 |
| 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 | 5 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 531 |
| Com_show_variables | 242 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 24 |
| Com_update | 2210897 |
| Com_update_multi | 10 |
| Connections | 978149 |
| Created_tmp_disk_tables | 27135 |
| Created_tmp_files | 18840 |
| Created_tmp_tables | 201040 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 92071 |
| Handler_discover | 0 |
| Handler_read_first | 371692 |
| Handler_read_key | 582838416 |
| Handler_read_next | 585582364 |
| Handler_read_prev | 7275493 |
| Handler_read_rnd | 70196676 |
| Handler_read_rnd_next | 3185431433 |
| Handler_rollback | 0 |
| Handler_update | 9667916 |
| Handler_write | 111661605 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 14497 |
| Key_read_requests | 1377695231 |
| Key_reads | 2524027 |
| Key_write_requests | 3276532 |
| Key_writes | 380605 |
| Max_used_connections | 263 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1053 |
| Open_streams | 0 |
| Open_tables | 1024 | 100% of table_cache in use
| Opened_tables | 1848 |
| Qcache_free_blocks | 1367 |
| Qcache_free_memory | 13763072 |
| Qcache_hits | 3003758 |
| Qcache_inserts | 2338342 |
| Qcache_lowmem_prunes | 31103 |
| Qcache_not_cached | 39210 |
| Qcache_queries_in_cache | 1318 |
| Qcache_total_blocks | 4665 |
| Questions | 9951388 |
| Rpl_status | NULL |
| Select_full_join | 751 |
| Select_full_range_join | 0 |
| Select_range | 299980 |
| Select_range_check | 0 |
| Select_scan | 654466 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 3 |
| Slow_queries | 373 | (execution time > 10 secs)
| Sort_merge_passes | 9430 |
| Sort_range | 310557 |
| Sort_rows | 738932377 |
| Sort_scan | 265995 |
| Table_locks_immediate | 7642848 |
| Table_locks_waited | 146753 |
| Threads_cached | 61 |
| Threads_connected | 3 |
| Threads_created | 2397 |
| Threads_running | 1 |
| Uptime | 126261 | 1 day 11 hrs 4 mins 21 secs
+--------------------------+------------+
Key Reads/Key Read Requests = 0.001832 (Cache hit = 99.998168%)
Key Writes/Key Write Requests = 0.116161
Connections/second = 7.747 (/hour = 27889.344)
KB received/second = 16.610 (/hour = 59794.739)
KB sent/second = 16.610 (/hour = 59794.739)
Temporary Tables Created/second = 1.592 (/hour = 5732.126)
Opened Tables/second = 0.015 (/hour = 52.691)
Slow Queries/second = 0.003 (/hour = 10.635)
% of slow queries = 0.004%
Queries/second = 78.816 (/hour = 283737.629)
8) vBulletin, phpAdsNew and a custom portal script based on vBulletin
9) Maximum was 760, average is about 400 based on a 15 minute cookie timeout
10) http://www.planet-liebe.de/phpinfo.php
11)
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
12) vB3 RC3 with patches
It's getting slow when there are > 500 users, although load doesn't go over 4 and Swap isn't being used much.
Any hints to get this faster?