Mar
Wed
17
1. Is this a dedicated or shared server?
Dedicated
2. Server Specs
Single processor Pentium 4, 1.6Ghz generic. 512MB of 266 DDR
80 GB EIDE HDD
Apache/1.3.26 (Unix) mod_gzip/1.3.19.1a PHP/4.3.6
Linux 7.3
3. innodb?
I don't think I'm running any of these tables.
4. Mysql was installed via RPM
5. Top Stats:
1:50pm up 15:05, 1 user, load average: 0.66, 0.57, 0.83
205 processes: 202 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: 14.5% user, 2.9% system, 0.0% nice, 82.4% idle
Mem: 481152K av, 477204K used, 3948K free, 48K shrd, 1900K buff
Swap: 1662688K av, 41060K used, 1621628K free 148044K cached
The server is nowhere as busy right now as it would be at night.
6. Mysql config variables:
port = 3306
socket = /tmp/mysql.sock
log-slow-queries
skip-locking
datadir=/var/lib/mysql
max_connections = 500
key_buffer = 96M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 30
connect_timeout = 1200
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 8M
query_cache_type = 1
long_query_time = 3
#skip-innodb
[mysqld_safe]
open_files_limit = 8192
err-log=/var/log/mysqld.log
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
7. Mysql extended status output:
I'll update this with another one when I can since it's only been running for a short time since my last restart.
Tue Oct 26 14:03:39 EDT 2004
2:03pm up 15:19, 1 user, load average: 0.40, 0.49, 0.63
162 processes: 161 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 481152K av, 420192K used, 60960K free, 48K shrd, 2152K buff
Swap: 1662688K av, 30336K used, 1632352K free 157928K cached
Http processes currently running = 89
Mysql processes currently running = 46
Netstat information summary
2 CLOSE_WAIT
2 FIN_WAIT1
4 FIN_WAIT2
9 LISTEN
17 SYN_RECV
34 ESTABLISHED
86 TIME_WAIT
+---------------------------+-----------------+
Variable_name Value
+---------------------------+-----------------+
Aborted_clients 1448
Aborted_connects 0
Bytes_received 10733967
Bytes_sent 187174180
Com_admin_commands 1569
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 4076
Com_change_master 0
Com_check 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_delete 396
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 283
Com_insert_select 7
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 256
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 16888
Com_set_option 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 0
Com_show_databases 3
Com_show_fields 0
Com_show_grants 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 1
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 4
Com_show_innodb_status 0
Com_show_tables 102
Com_show_variables 3
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 8843
Connections 2852
Created_tmp_disk_tables 2
Created_tmp_tables 1294
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 1
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 190
Handler_read_first 2707
Handler_read_key 454053
Handler_read_next 3300715
Handler_read_prev 8749
Handler_read_rnd 124370
Handler_read_rnd_next 4610553
Handler_rollback 0
Handler_update 74806
Handler_write 78428
Key_blocks_used 10444
Key_read_requests 1333375
Key_reads 10406
Key_write_requests 2309
Key_writes 2225
Max_used_connections 31
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 502 49% of table_cache in use
Open_files 972
Open_streams 0
Opened_tables 508
Questions 56446
Qcache_queries_in_cache 890
Qcache_inserts 16852
Qcache_hits 23471
Qcache_lowmem_prunes 0
Qcache_not_cached 55
Qcache_free_memory 6033624
Qcache_free_blocks 360
Qcache_total_blocks 2221
Rpl_status NULL
Select_full_join 0
Select_full_range_join 1
Select_range 2374
Select_range_check 0
Select_scan 3838
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 5 (execution time > 3 secs)
Sort_merge_passes 0
Sort_range 2205
Sort_rows 235461
Sort_scan 1406
Table_locks_immediate 38598
Table_locks_waited 72
Threads_cached 19
Threads_created 32
Threads_connected 13
Threads_running 1
Uptime 1807 30 mins 7 secs
+---------------------------+-----------------+
Key Reads/Key Read Requests = 0.007804 (Cache hit = 99.992196%)
Key Writes/Key Write Requests = 0.963621
Connections/second = 1.578 (/hour = 5681.904)
KB received/second = 5.801 (/hour = 20882.789)
KB sent/second = 101.155 (/hour = 364157.831)
Temporary Tables Created/second = 0.716 (/hour = 2577.975)
Opened Tables/second = 0.281 (/hour = 1012.064)
Slow Queries/second = 0.003 (/hour = 9.961)
% of slow queries = 0.009%
Queries/second = 31.237 (/hour = 112454.676)
8. VB is not the only thing on the server. I actually have three sites running vb on this machine. 2 of them are VB3, and have almost no traffic. This site running 2.3.3 is by far the busiest site I have on here. I'm also running phpadsnew on two of the sites, including the busy one.
9. My cookie timeout is 5400 seconds, and I average 375. I've had a max of 862. Usually in a 15 minute period, I have about 150 users online.
10. http://www.dieseltruckresource.com/dev/admin/extras/phpinfo.php
11. Apache Variables:
Keepalive = on
MaxKeepAliveRequests 1000000
KeepAliveTimeout 120
MinSpareServers 10
MaxSpareServers 50
StartServers 50
12. VB Version 2.3.3
I hope this helps.
Good to hear :) Remember after server upgrade to come back for a tune up :D
http://www.vbulletin.com/forum/showthread.php?t=119221
# Time: 041027 8:31:59
# User@Host: scripts[scripts] @ localhost
# Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 10368
SELECT COUNT(*) AS posts FROM post
LEFT JOIN thread ON thread.threadid=post.threadid WHERE post.visible=1 AND
1=1 AND NOT (thread.forumid='86') AND post.userid='18533';
# Time: 041027 8:32:06
# User@Host: scripts[scripts] @ localhost
# Query_time: 7 Lock_time: 0 Rows_sent: 25 Rows_examined: 5234
SELECT
post.postid, thread.visible
FROM
post LEFT JOIN thread ON thread.threadid=post.threadid
WHERE
post.visible=1 AND thread.visible=1 AND 1=1 AND NOT (thread.forumid='
86') AND post.userid='18533'
ORDER BY
post.dateline DESC,post.dateline DESC
LIMIT 0, 25;
yup turck mmcache has been known to do just that :D
Sorry, I'll turn it back on.
sorry about that seems your thread slipped through some how :o
As to your problems I'd do the following first in this exact order
1. upgrade MySQL to 4.0.20 or 4.0.22 (skip 4.0.21)
2. upgrade PHP from 4.3.6 to 4.3.9
3. change /etc/my.cnf to below and restart mysql server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-slow-queries
skip-locking
datadir=/var/lib/mysql
skip-innodb
max_connections = 650
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 = 3600
connect_timeout = 10
tmp_table_size = 32M
bulk_insert_buffer_size = 8M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
4. install Turck Mmcache http://www.vbulletin.com/forum/showthread.php?t=75878
5. Upgrade to Apache 1.3.31 - you didn't provide the maxclients value in httpd.conf ? can you provide it ?
After all is done test above for 1-7 days to see how it goes, then
6. upgrade to vB 2.3.5 or 3.0.3
Tue Oct 26 20:34:06 EDT 2004
8:34pm up 21:49, 0 users, load average: 1.36, 1.01, 0.81
246 processes: 243 sleeping, 3 running, 0 zombie, 0 stopped
Mem: 481152K av, 472216K used, 8936K free, 36K shrd, 1720K buff
Swap: 1662688K av, 215644K used, 1447044K free 60164K cached
Http processes currently running = 142
Mysql processes currently running = 81
Netstat information summary
1 FIN_WAIT1
3 FIN_WAIT2
9 LISTEN
26 TIME_WAIT
124 ESTABLISHED
+---------------------------+-----------------+
Variable_name Value
+---------------------------+-----------------+
Aborted_clients 22172
Aborted_connects 0
Bytes_received 165088756
Bytes_sent 2743831320
Com_admin_commands 24898
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 60783
Com_change_master 0
Com_check 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_delete 6011
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 3903
Com_insert_select 102
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 4067
Com_replace_select 2
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 253881
Com_set_option 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 0
Com_show_databases 9
Com_show_fields 10
Com_show_grants 0
Com_show_keys 3
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 3
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 39
Com_show_innodb_status 0
Com_show_tables 213
Com_show_variables 41
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 135102
Connections 43178
Created_tmp_disk_tables 41
Created_tmp_tables 19999
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 1
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 5473
Handler_read_first 42638
Handler_read_key 6293540
Handler_read_next 52845307
Handler_read_prev 253544
Handler_read_rnd 2766462
Handler_read_rnd_next 75418816
Handler_rollback 0
Handler_update 1357246
Handler_write 2283647
Key_blocks_used 26033
Key_read_requests 18089673
Key_reads 25316
Key_write_requests 35714
Key_writes 33890
Max_used_connections 137
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 829 81% of table_cache in use
Open_files 1259
Open_streams 0
Opened_tables 835
Questions 856653
Qcache_queries_in_cache 1465
Qcache_inserts 253556
Qcache_hits 360413
Qcache_lowmem_prunes 4772
Qcache_not_cached 590
Qcache_free_memory 4716960
Qcache_free_blocks 935
Qcache_total_blocks 3983
Rpl_status NULL
Select_full_join 24
Select_full_range_join 9
Select_range 34669
Select_range_check 0
Select_scan 58920
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 176 (execution time > 3 secs)
Sort_merge_passes 0
Sort_range 32099
Sort_rows 3062183
Sort_scan 22225
Table_locks_immediate 559098
Table_locks_waited 1521
Threads_cached 31
Threads_created 138
Threads_connected 37
Threads_running 1
Uptime 25236 7 hrs 36 secs
+---------------------------+-----------------+
Key Reads/Key Read Requests = 0.001399 (Cache hit = 99.998601%)
Key Writes/Key Write Requests = 0.948928
Connections/second = 1.711 (/hour = 6159.486)
KB received/second = 6.388 (/hour = 22998.431)
KB sent/second = 83.102 (/hour = 299165.621)
Temporary Tables Created/second = 0.792 (/hour = 2852.924)
Opened Tables/second = 0.033 (/hour = 119.116)
Slow Queries/second = 0.007 (/hour = 25.107)
% of slow queries = 0.021%
Queries/second = 33.946 (/hour = 122204.422)
#If you have any other info about this subject , Please add it free.# |
- edit
