MarkPW
Wed 4th Jul '07, 3:03pm
I've just upgraded my server to 2GB RAM from 1GB and I'm seeing no real performance improvement, therefore I'd like to re-configure my MySQL settings and implement any suggestions you might have.
I would be very grateful for your assistance eva.
Server specs:
Dedicated...
P4 2.8Ghz, 2GB RAM
250GB SATA, non raid
CentOS 4.3
Apache/1.3.37
MySQL 4.1.21 (ISAM)
vB Version: 3.6.4
MySQL config:
[mysqld]
safe-show-database
#old_passwords
back_log = 100
skip-innodb
max_connections = 500
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 256
wait_timeout = 45
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
query_cache_limit = 2M
query_cache_size = 8M
query_cache_type = 2
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=5
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
[mysqlhotcopy]
interactive-timeoutHow MySQL was installed: cPanel
Top Stats:
top - 16:18:47 up 15:52, 1 user, load average: 7.80, 5.97, 6.37
Tasks: 199 total, 3 running, 181 sleeping, 13 stopped, 2 zombie
Cpu(s): 87.8% us, 12.2% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 2074960k total, 2036560k used, 38400k free, 50660k buffers
Swap: 2048276k total, 144k used, 2048132k free, 1199824k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7737 mysql 15 0 116m 36m 2644 S 5.0 1.8 22:01.30 mysqld
3317 root 16 0 33308 29m 2088 S 0.0 1.4 1:00.63 spamd
3804 root 16 0 30464 26m 2040 S 0.0 1.3 0:01.38 spamd
3298 root 16 0 28352 24m 1888 S 0.0 1.2 0:01.99 spamd
11746 nobody 15 0 31424 20m 3808 S 0.0 1.0 6:46.72 httpd
14201 nobody 16 0 31548 20m 3296 S 0.0 1.0 0:21.39 httpd
4328 nobody 16 0 30676 19m 3820 S 0.0 1.0 8:03.73 httpd
3373 nobody 16 0 29428 18m 3812 S 0.0 0.9 7:39.02 httpd
21534 nobody 16 0 29212 18m 3796 S 0.0 0.9 4:03.19 httpd
3372 nobody 15 0 29268 18m 3800 S 0.0 0.9 8:08.24 httpd
4335 nobody 16 0 28884 18m 3808 S 0.0 0.9 7:29.32 httpd
11743 nobody 16 0 28892 18m 3800 S 0.0 0.9 6:15.81 httpd
14402 nobody 16 0 29240 17m 3112 S 0.0 0.9 0:03.73 httpd
3382 nobody 15 0 28348 17m 3804 S 1.2 0.9 8:56.23 httpd
4334 nobody 16 0 27984 17m 3796 S 6.2 0.9 8:29.11 httpd
11753 nobody 15 0 27928 17m 3812 S 0.6 0.9 7:37.72 httpd
3371 nobody 15 0 28328 17m 3400 S 0.6 0.9 8:15.75 httpd
3374 nobody 15 0 27856 17m 3812 S 0.6 0.9 9:01.19 httpd
3561 nobody 15 0 27464 17m 4016 S 0.0 0.8 7:36.57 httpd
5893 nobody 15 0 27424 16m 3812 S 0.6 0.8 8:00.36 httpd
3375 nobody 16 0 27384 16m 3824 S 0.0 0.8 8:20.62 httpd
15243 nobody 16 0 28120 16m 3124 S 0.0 0.8 0:04.64 httpd
14178 nobody 15 0 26884 16m 3708 S 0.6 0.8 0:23.18 httpd
14185 nobody 16 0 26776 15m 3296 S 0.0 0.8 0:19.86 httpd
10479 nobody 16 0 26740 15m 3160 S 0.0 0.8 0:11.01 httpd
4333 nobody 16 0 25900 15m 3816 S 0.0 0.8 8:06.26 httpd
3381 nobody 15 0 25624 15m 3984 S 0.0 0.8 8:09.81 httpd
11742 nobody 15 0 25536 15m 4008 S 0.0 0.8 6:57.68 httpd
15354 nobody 15 0 26424 15m 3132 S 0.6 0.7 0:06.00 httpd
4329 nobody 15 0 25520 15m 3808 S 0.0 0.7 7:59.27 httpd
9074 nobody 15 0 25496 15m 3836 D 0.0 0.7 7:34.46 httpd
11744 nobody 15 0 25500 14m 3800 S 0.6 0.7 6:35.32 httpd
7752 nobody 15 0 25564 14m 3720 S 0.0 0.7 1:34.80 httpd
3379 nobody 15 0 25248 14m 3808 S 0.0 0.7 8:20.76 httpd
15542 nobody 15 0 25372 14m 3692 S 0.0 0.7 0:02.92 httpd
10437 nobody 15 0 25496 14m 3392 S 0.6 0.7 0:59.30 httpd
14175 nobody 16 0 25548 14m 3324 S 0.0 0.7 0:17.63 httpd
13657 nobody 15 0 25492 14m 3300 S 0.6 0.7 0:29.57 httpd
14184 nobody 15 0 25476 14m 3292 S 0.0 0.7 0:23.03 httpd
14331 nobody 15 0 25408 14m 3300 S 7.5 0.7 0:18.87 httpd
15519 nobody 15 0 25600 14m 3108 S 1.9 0.7 0:02.44 httpd
14177 nobody 15 0 25276 14m 3304 S 0.6 0.7 0:23.21 httpd
13564 nobody 15 0 25220 14m 3292 S 0.0 0.7 0:16.69 httpd
13677 nobody 15 0 25164 14m 3292 S 0.0 0.7 0:07.86 httpd
14204 nobody 16 0 25148 14m 3300 S 0.0 0.7 0:20.33 httpd
15222 nobody 15 0 25096 14m 3288 S 0.0 0.7 0:05.43 httpd
14181 nobody 16 0 25100 14m 3288 S 0.0 0.7 0:13.62 httpd
15320 nobody 15 0 25200 14m 3264 S 0.6 0.7 0:06.36 httpd
14203 nobody 15 0 25124 14m 3164 S 1.2 0.7 0:13.94 httpd
15318 nobody 15 0 25128 14m 3172 S 1.9 0.7 0:06.08 httpd
15291 nobody 16 0 25128 13m 3164 S 0.0 0.7 0:05.71 httpd
15642 nobody 15 0 25096 13m 3104 S 0.0 0.7 0:01.44 httpd
15527 nobody 15 0 25012 13m 3112 S 0.6 0.7 0:02.77 httpd
my.cnf:
[mysqld]
safe-show-database
#old_passwords
back_log = 100
skip-innodb
max_connections = 500
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 256
wait_timeout = 45
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
query_cache_limit = 2M
query_cache_size = 8M
query_cache_type = 2
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=5
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
[mysqlhotcopy]
interactive-timeout
mysqladmin -u root -p var ext stat ver:
max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 4110 |
| pid_file | /var/lib/mysql/server.mydomain.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 32768 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 8388608 |
| query_cache_type | DEMAND |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 163840 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | BST |
| table_cache | 1800 |
| table_type | MyISAM |
| thread_cache_size | 256 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 134217728 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.22-standard-log |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 45 |
+---------------------------------+----------------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 72 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 114862877 |
| Bytes_sent | 2743377238 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 58890 |
| 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 | 3591 |
| 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 | 7716 |
| Com_insert_select | 2 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 734 |
| 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 | 688 |
| 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 | 284273 |
| Com_set_option | 35 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 1 |
| Com_show_charsets | 6 |
| Com_show_collations | 6 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 1 |
| Com_show_databases | 6 |
| Com_show_errors | 0 |
| Com_show_fields | 3 |
| Com_show_grants | 2 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 1 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 37 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 9 |
| Com_show_variables | 13 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 734 |
| Com_update | 70221 |
| Com_update_multi | 2815 |
| Connections | 58911 |
| Created_tmp_disk_tables | 2877 |
| Created_tmp_files | 9224 |
| Created_tmp_tables | 10650 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 5407 |
| Handler_discover | 0 |
| Handler_read_first | 87191 |
| Handler_read_key | 6222574 |
| Handler_read_next | 43590041 |
| Handler_read_prev | 36591 |
| Handler_read_rnd | 421850 |
| Handler_read_rnd_next | 72645354 |
| Handler_rollback | 0 |
| Handler_update | 77657 |
| Handler_write | 1953291 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 50382 |
| Key_blocks_used | 7609 |
| Key_read_requests | 19421833 |
| Key_reads | 10434 |
| Key_write_requests | 30831 |
| Key_writes | 22211 |
| Max_used_connections | 74 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 507 |
| Open_streams | 0 |
| Open_tables | 403 |
| Opened_tables | 409 |
| Qcache_free_blocks | 2 |
| Qcache_free_memory | 8376368 |
| Qcache_hits | 2351 |
| Qcache_inserts | 8895 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 275368 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 8 |
| Questions | 490995 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 95926 |
| Select_range_check | 0 |
| Select_scan | 18493 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 7 |
| Slow_queries | 51 |
| Sort_merge_passes | 4612 |
| Sort_range | 20822 |
| Sort_rows | 2038341 |
| Sort_scan | 2827 |
| Table_locks_immediate | 501124 |
| Table_locks_waited | 7825 |
| Threads_cached | 52 |
| Threads_connected | 22 |
| Threads_created | 74 |
| Threads_running | 1 |
| Uptime | 11345 |
+----------------------------+------------+
Uptime: 11345 Threads: 21 Questions: 491017 Slow queries: 51 Opens: 409 Flush tables: 1 Open tables: 403 Queries per second avg: 43.280
mysqladmin Ver 8.41 Distrib 4.1.22, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.22-standard-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 3 hours 9 min 5 sec
Threads: 21 Questions: 491017 Slow queries: 51 Opens: 409 Flush tables: 1 Open tables: 403 Queries per second avg: 43.280
mysqlreport --all -tab /statistics.txt:
MySQL 4.1.22-standard-l uptime 0 5:5:50 Wed Jul 4 18:31:11 2007
__ Key __________________________________________________ _______________
Buffer used 8.20M of 64.00M %Used: 12.81
Current 15.57M %Usage: 24.32
Write ratio 0.726
Read ratio 0.000
__ Questions __________________________________________________ _________
Total 788.45k 43.0/s
DMS 592.40k 32.3/s %Total: 75.14
Com_ 97.33k 5.3/s 12.35
COM_QUIT 94.66k 5.2/s 12.01
QC Hits 4.06k 0.2/s 0.52
-Unknown 17 0.0/s 0.00
Slow 69 0.0/s 0.01 %DMS: 0.01
DMS 592.40k 32.3/s 75.14
SELECT 456.46k 24.9/s 57.89 77.05
UPDATE 116.64k 6.4/s 14.79 19.69
INSERT 12.35k 0.7/s 1.57 2.09
DELETE 5.74k 0.3/s 0.73 0.97
REPLACE 1.21k 0.1/s 0.15 0.20
Com_ 97.33k 5.3/s 12.35
change_db 94.70k 5.2/s 12.01
lock_tables 1.24k 0.1/s 0.16
unlock_tabl 1.24k 0.1/s 0.16
__ SELECT and Sort __________________________________________________ ___
Scan 30.96k 1.7/s %SELECT: 6.78
Range 151.21k 8.2/s 33.13
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 4.73k 0.3/s
Sort range 35.01k 1.9/s
Sort mrg pass 7.85k 0.4/s
__ Query Cache __________________________________________________ _______
Memory usage 10.99k of 8.00M %Used: 0.13
Block Fragmnt 16.67%
Hits 4.06k 0.2/s
Inserts 14.93k 0.8/s
Insrt:Prune 14.93k:1 0.8/s
Hit:Insert 0.27:1
__ Table Locks __________________________________________________ _______
Waited 12.17k 0.7/s %Total: 1.50
Immediate 801.69k 43.7/s
__ Tables __________________________________________________ ____________
Open 420 of 1800 %Cache: 23.33
Opened 426 0.0/s
__ Connections __________________________________________________ _______
Max used 74 of 500 %Max: 14.80
Total 94.70k 5.2/s
__ Created Temp __________________________________________________ ______
Disk table 4.44k 0.2/s
Table 16.91k 0.9/s
File 15.70k 0.9/s
__ Threads __________________________________________________ ___________
Running 1 of 22
Cached 52 of 256 %Hit: 99.92
Created 74 0.0/s
Slow 7 0.0/s
__ Aborted __________________________________________________ ___________
Clients 72 0.0/s
Connects 0 0/s
__ Bytes __________________________________________________ _____________
Sent 2.69G 146.6k/s
Received 184.41M 10.0k/sIs your vB the only thing on the server?
Yes, however I've expanded it across my site (so that users remain logged in etc).
How many average and max concurrent users on your vB forum?
350-400 users online / 15 timeout
phpinfo: here (http://markw.fireflyinternet.co.uk/phpinfo.html)
Apache config:
KeepAlive Off
Timeout 30
MaxKeepAliveRequests 100
KeepAliveTimeout 5
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
Maxrequestsperchild 0No log files above 2GB
uname -a
Linux server.myserver.com 2.6.9-42.0.3.EL #1 Fri Oct 6 05:59:54 CDT 2006 i686 i686 i386 GNU/Linuxulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 32751
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimitedcat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.80GHz
stepping : 9
cpu MHz : 2814.762
cache size : 512 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips : 5630.32
Thank you eva.
I would be very grateful for your assistance eva.
Server specs:
Dedicated...
P4 2.8Ghz, 2GB RAM
250GB SATA, non raid
CentOS 4.3
Apache/1.3.37
MySQL 4.1.21 (ISAM)
vB Version: 3.6.4
MySQL config:
[mysqld]
safe-show-database
#old_passwords
back_log = 100
skip-innodb
max_connections = 500
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 256
wait_timeout = 45
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
query_cache_limit = 2M
query_cache_size = 8M
query_cache_type = 2
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=5
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
[mysqlhotcopy]
interactive-timeoutHow MySQL was installed: cPanel
Top Stats:
top - 16:18:47 up 15:52, 1 user, load average: 7.80, 5.97, 6.37
Tasks: 199 total, 3 running, 181 sleeping, 13 stopped, 2 zombie
Cpu(s): 87.8% us, 12.2% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 2074960k total, 2036560k used, 38400k free, 50660k buffers
Swap: 2048276k total, 144k used, 2048132k free, 1199824k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7737 mysql 15 0 116m 36m 2644 S 5.0 1.8 22:01.30 mysqld
3317 root 16 0 33308 29m 2088 S 0.0 1.4 1:00.63 spamd
3804 root 16 0 30464 26m 2040 S 0.0 1.3 0:01.38 spamd
3298 root 16 0 28352 24m 1888 S 0.0 1.2 0:01.99 spamd
11746 nobody 15 0 31424 20m 3808 S 0.0 1.0 6:46.72 httpd
14201 nobody 16 0 31548 20m 3296 S 0.0 1.0 0:21.39 httpd
4328 nobody 16 0 30676 19m 3820 S 0.0 1.0 8:03.73 httpd
3373 nobody 16 0 29428 18m 3812 S 0.0 0.9 7:39.02 httpd
21534 nobody 16 0 29212 18m 3796 S 0.0 0.9 4:03.19 httpd
3372 nobody 15 0 29268 18m 3800 S 0.0 0.9 8:08.24 httpd
4335 nobody 16 0 28884 18m 3808 S 0.0 0.9 7:29.32 httpd
11743 nobody 16 0 28892 18m 3800 S 0.0 0.9 6:15.81 httpd
14402 nobody 16 0 29240 17m 3112 S 0.0 0.9 0:03.73 httpd
3382 nobody 15 0 28348 17m 3804 S 1.2 0.9 8:56.23 httpd
4334 nobody 16 0 27984 17m 3796 S 6.2 0.9 8:29.11 httpd
11753 nobody 15 0 27928 17m 3812 S 0.6 0.9 7:37.72 httpd
3371 nobody 15 0 28328 17m 3400 S 0.6 0.9 8:15.75 httpd
3374 nobody 15 0 27856 17m 3812 S 0.6 0.9 9:01.19 httpd
3561 nobody 15 0 27464 17m 4016 S 0.0 0.8 7:36.57 httpd
5893 nobody 15 0 27424 16m 3812 S 0.6 0.8 8:00.36 httpd
3375 nobody 16 0 27384 16m 3824 S 0.0 0.8 8:20.62 httpd
15243 nobody 16 0 28120 16m 3124 S 0.0 0.8 0:04.64 httpd
14178 nobody 15 0 26884 16m 3708 S 0.6 0.8 0:23.18 httpd
14185 nobody 16 0 26776 15m 3296 S 0.0 0.8 0:19.86 httpd
10479 nobody 16 0 26740 15m 3160 S 0.0 0.8 0:11.01 httpd
4333 nobody 16 0 25900 15m 3816 S 0.0 0.8 8:06.26 httpd
3381 nobody 15 0 25624 15m 3984 S 0.0 0.8 8:09.81 httpd
11742 nobody 15 0 25536 15m 4008 S 0.0 0.8 6:57.68 httpd
15354 nobody 15 0 26424 15m 3132 S 0.6 0.7 0:06.00 httpd
4329 nobody 15 0 25520 15m 3808 S 0.0 0.7 7:59.27 httpd
9074 nobody 15 0 25496 15m 3836 D 0.0 0.7 7:34.46 httpd
11744 nobody 15 0 25500 14m 3800 S 0.6 0.7 6:35.32 httpd
7752 nobody 15 0 25564 14m 3720 S 0.0 0.7 1:34.80 httpd
3379 nobody 15 0 25248 14m 3808 S 0.0 0.7 8:20.76 httpd
15542 nobody 15 0 25372 14m 3692 S 0.0 0.7 0:02.92 httpd
10437 nobody 15 0 25496 14m 3392 S 0.6 0.7 0:59.30 httpd
14175 nobody 16 0 25548 14m 3324 S 0.0 0.7 0:17.63 httpd
13657 nobody 15 0 25492 14m 3300 S 0.6 0.7 0:29.57 httpd
14184 nobody 15 0 25476 14m 3292 S 0.0 0.7 0:23.03 httpd
14331 nobody 15 0 25408 14m 3300 S 7.5 0.7 0:18.87 httpd
15519 nobody 15 0 25600 14m 3108 S 1.9 0.7 0:02.44 httpd
14177 nobody 15 0 25276 14m 3304 S 0.6 0.7 0:23.21 httpd
13564 nobody 15 0 25220 14m 3292 S 0.0 0.7 0:16.69 httpd
13677 nobody 15 0 25164 14m 3292 S 0.0 0.7 0:07.86 httpd
14204 nobody 16 0 25148 14m 3300 S 0.0 0.7 0:20.33 httpd
15222 nobody 15 0 25096 14m 3288 S 0.0 0.7 0:05.43 httpd
14181 nobody 16 0 25100 14m 3288 S 0.0 0.7 0:13.62 httpd
15320 nobody 15 0 25200 14m 3264 S 0.6 0.7 0:06.36 httpd
14203 nobody 15 0 25124 14m 3164 S 1.2 0.7 0:13.94 httpd
15318 nobody 15 0 25128 14m 3172 S 1.9 0.7 0:06.08 httpd
15291 nobody 16 0 25128 13m 3164 S 0.0 0.7 0:05.71 httpd
15642 nobody 15 0 25096 13m 3104 S 0.0 0.7 0:01.44 httpd
15527 nobody 15 0 25012 13m 3112 S 0.6 0.7 0:02.77 httpd
my.cnf:
[mysqld]
safe-show-database
#old_passwords
back_log = 100
skip-innodb
max_connections = 500
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 256
wait_timeout = 45
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
query_cache_limit = 2M
query_cache_size = 8M
query_cache_type = 2
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=5
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
[mysqlhotcopy]
interactive-timeout
mysqladmin -u root -p var ext stat ver:
max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 4110 |
| pid_file | /var/lib/mysql/server.mydomain.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 32768 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 8388608 |
| query_cache_type | DEMAND |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 163840 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | BST |
| table_cache | 1800 |
| table_type | MyISAM |
| thread_cache_size | 256 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 134217728 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.22-standard-log |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 45 |
+---------------------------------+----------------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 72 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 114862877 |
| Bytes_sent | 2743377238 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 58890 |
| 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 | 3591 |
| 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 | 7716 |
| Com_insert_select | 2 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 734 |
| 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 | 688 |
| 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 | 284273 |
| Com_set_option | 35 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 1 |
| Com_show_charsets | 6 |
| Com_show_collations | 6 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 1 |
| Com_show_databases | 6 |
| Com_show_errors | 0 |
| Com_show_fields | 3 |
| Com_show_grants | 2 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 1 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 37 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 9 |
| Com_show_variables | 13 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 734 |
| Com_update | 70221 |
| Com_update_multi | 2815 |
| Connections | 58911 |
| Created_tmp_disk_tables | 2877 |
| Created_tmp_files | 9224 |
| Created_tmp_tables | 10650 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 5407 |
| Handler_discover | 0 |
| Handler_read_first | 87191 |
| Handler_read_key | 6222574 |
| Handler_read_next | 43590041 |
| Handler_read_prev | 36591 |
| Handler_read_rnd | 421850 |
| Handler_read_rnd_next | 72645354 |
| Handler_rollback | 0 |
| Handler_update | 77657 |
| Handler_write | 1953291 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 50382 |
| Key_blocks_used | 7609 |
| Key_read_requests | 19421833 |
| Key_reads | 10434 |
| Key_write_requests | 30831 |
| Key_writes | 22211 |
| Max_used_connections | 74 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 507 |
| Open_streams | 0 |
| Open_tables | 403 |
| Opened_tables | 409 |
| Qcache_free_blocks | 2 |
| Qcache_free_memory | 8376368 |
| Qcache_hits | 2351 |
| Qcache_inserts | 8895 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 275368 |
| Qcache_queries_in_cache | 2 |
| Qcache_total_blocks | 8 |
| Questions | 490995 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 95926 |
| Select_range_check | 0 |
| Select_scan | 18493 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 7 |
| Slow_queries | 51 |
| Sort_merge_passes | 4612 |
| Sort_range | 20822 |
| Sort_rows | 2038341 |
| Sort_scan | 2827 |
| Table_locks_immediate | 501124 |
| Table_locks_waited | 7825 |
| Threads_cached | 52 |
| Threads_connected | 22 |
| Threads_created | 74 |
| Threads_running | 1 |
| Uptime | 11345 |
+----------------------------+------------+
Uptime: 11345 Threads: 21 Questions: 491017 Slow queries: 51 Opens: 409 Flush tables: 1 Open tables: 403 Queries per second avg: 43.280
mysqladmin Ver 8.41 Distrib 4.1.22, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.22-standard-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 3 hours 9 min 5 sec
Threads: 21 Questions: 491017 Slow queries: 51 Opens: 409 Flush tables: 1 Open tables: 403 Queries per second avg: 43.280
mysqlreport --all -tab /statistics.txt:
MySQL 4.1.22-standard-l uptime 0 5:5:50 Wed Jul 4 18:31:11 2007
__ Key __________________________________________________ _______________
Buffer used 8.20M of 64.00M %Used: 12.81
Current 15.57M %Usage: 24.32
Write ratio 0.726
Read ratio 0.000
__ Questions __________________________________________________ _________
Total 788.45k 43.0/s
DMS 592.40k 32.3/s %Total: 75.14
Com_ 97.33k 5.3/s 12.35
COM_QUIT 94.66k 5.2/s 12.01
QC Hits 4.06k 0.2/s 0.52
-Unknown 17 0.0/s 0.00
Slow 69 0.0/s 0.01 %DMS: 0.01
DMS 592.40k 32.3/s 75.14
SELECT 456.46k 24.9/s 57.89 77.05
UPDATE 116.64k 6.4/s 14.79 19.69
INSERT 12.35k 0.7/s 1.57 2.09
DELETE 5.74k 0.3/s 0.73 0.97
REPLACE 1.21k 0.1/s 0.15 0.20
Com_ 97.33k 5.3/s 12.35
change_db 94.70k 5.2/s 12.01
lock_tables 1.24k 0.1/s 0.16
unlock_tabl 1.24k 0.1/s 0.16
__ SELECT and Sort __________________________________________________ ___
Scan 30.96k 1.7/s %SELECT: 6.78
Range 151.21k 8.2/s 33.13
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 4.73k 0.3/s
Sort range 35.01k 1.9/s
Sort mrg pass 7.85k 0.4/s
__ Query Cache __________________________________________________ _______
Memory usage 10.99k of 8.00M %Used: 0.13
Block Fragmnt 16.67%
Hits 4.06k 0.2/s
Inserts 14.93k 0.8/s
Insrt:Prune 14.93k:1 0.8/s
Hit:Insert 0.27:1
__ Table Locks __________________________________________________ _______
Waited 12.17k 0.7/s %Total: 1.50
Immediate 801.69k 43.7/s
__ Tables __________________________________________________ ____________
Open 420 of 1800 %Cache: 23.33
Opened 426 0.0/s
__ Connections __________________________________________________ _______
Max used 74 of 500 %Max: 14.80
Total 94.70k 5.2/s
__ Created Temp __________________________________________________ ______
Disk table 4.44k 0.2/s
Table 16.91k 0.9/s
File 15.70k 0.9/s
__ Threads __________________________________________________ ___________
Running 1 of 22
Cached 52 of 256 %Hit: 99.92
Created 74 0.0/s
Slow 7 0.0/s
__ Aborted __________________________________________________ ___________
Clients 72 0.0/s
Connects 0 0/s
__ Bytes __________________________________________________ _____________
Sent 2.69G 146.6k/s
Received 184.41M 10.0k/sIs your vB the only thing on the server?
Yes, however I've expanded it across my site (so that users remain logged in etc).
How many average and max concurrent users on your vB forum?
350-400 users online / 15 timeout
phpinfo: here (http://markw.fireflyinternet.co.uk/phpinfo.html)
Apache config:
KeepAlive Off
Timeout 30
MaxKeepAliveRequests 100
KeepAliveTimeout 5
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
Maxrequestsperchild 0No log files above 2GB
uname -a
Linux server.myserver.com 2.6.9-42.0.3.EL #1 Fri Oct 6 05:59:54 CDT 2006 i686 i686 i386 GNU/Linuxulimit -aH
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 32751
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimitedcat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.80GHz
stepping : 9
cpu MHz : 2814.762
cache size : 512 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips : 5630.32
Thank you eva.