Need optimization advice on 15k user forum...

[RobFerrari]

1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)

Dedicated Server.

2. your server specs. For example:

cpu speed/type single or dual cpus): dual Intel \ P4 Xeon \ 2.8Ghz
how much memory installed: 2GB ram
hard drive type/configuration: 3 Seagate \ 73GB:SCSI:Ultra320 15kRPM \ RAID 5
linux distributor or windows version: Redhat Enterprise Linux - OS ES 3.0
apache/IIS version: Apache/1.3.33
PHP version: 4.3.10
MySQL version: mysql 4.0.22

3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?

No.

4. if possible how mysql was compiled/installed

Standard install by host on new server.

5. your top stats

20:18:10 up 13 days, 2:49, 1 user, load average: 0.95, 0.81, 0.74
129 processes: 102 sleeping, 1 running, 26 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 5.4% 0.0% 4.2% 0.0% 0.1% 1.3% 88.7%
cpu00 4.7% 0.0% 3.9% 0.0% 0.3% 2.5% 88.2%
cpu01 5.1% 0.0% 4.9% 0.0% 0.1% 0.0% 89.6%
cpu02 6.3% 0.0% 4.3% 0.0% 0.0% 2.7% 86.4%
cpu03 5.5% 0.0% 3.7% 0.0% 0.0% 0.0% 90.6%
Mem: 2055364k av, 2022144k used, 33220k free, 0k shrd, 85616k buff
1404460k actv, 270008k in_d, 30904k in_c
Swap: 2048276k av, 578232k used, 1470044k free 1430348k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
21375 nobody 25 0 0 0 0 Z 0.6 0.0 0:00 3 httpd <defunct>
21391 nobody 24 0 0 0 0 Z 0.5 0.0 0:00 0 httpd <defunct>
19414 root 24 0 3356 3140 1604 S 0.4 0.1 50:34 3 httpd
21374 nobody 25 0 8172 7976 4728 S 0.4 0.3 0:00 0 httpd
19098 mysql 15 0 444M 145M 1456 S 0.1 7.2 0:00 3 mysqld
21383 nobody 24 0 6972 6772 4192 S 0.1 0.3 0:00 1 httpd
1 root 15 0 116 84 56 S 0.0 0.0 0:20 0 init
2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0 migration/0
3 root RT 0 0 0 0 SW 0.0 0.0 0:00 1 migration/1
4 root RT 0 0 0 0 SW 0.0 0.0 0:00 2 migration/2
5 root RT 0 0 0 0 SW 0.0 0.0 0:00 3 migration/3
6 root 15 0 0 0 0 SW 0.0 0.0 0:00 3 keventd
7 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd/0
8 root 34 19 0 0 0 SWN 0.0 0.0 0:00 1 ksoftirqd/1
9 root 34 19 0 0 0 SWN 0.0 0.0 0:00 2 ksoftirqd/2
10 root 34 19 0 0 0 SWN 0.0 0.0 0:00 3 ksoftirqd/3
13 root 15 0 0 0 0 SW 0.0 0.0 0:00 3 bdflush
11 root 15 0 0 0 0 SW 0.0 0.0 9:48 1 kswapd
12 root 15 0 0 0 0 SW 0.0 0.0 5:06 2 kscand
14 root 15 0 0 0 0 SW 0.0 0.0 1:02 1 kupdated
15 root 25 0 0 0 0 SW 0.0 0.0 0:00 2 mdrecoveryd
23 root 15 0 0 0 0 SW 0.0 0.0 7:59 0 kjournald
78 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 khubd
381 root 15 0 0 0 0 SW 0.0 0.0 0:00 2 kjournald
1145 root 15 0 240 216 156 S 0.0 0.0 0:09 2 syslogd
1149 root 15 0 60 4 0 S 0.0 0.0 0:00 0 klogd
1159 root 15 0 248 236 184 S 0.0 0.0 0:47 2 irqbalance
1188 root 15 0 168 152 108 S 0.0 0.0 0:02 1 mdadm
1253 nobody 15 0 712 268 148 S 0.0 0.0 0:08 0 proftpd
1288 root 15 0 480 336 228 S 0.0 0.0 0:33 3 sshd
1302 root 15 0 416 368 292 S 0.0 0.0 0:01 3 xinetd
1320 root 15 0 1888 1004 604 S 0.0 0.0 0:18 3 chkservd
1370 mailnull 15 0 556 292 212 S 0.0 0.0 0:03 2 exim
1374 mailnull 16 0 340 4 0 S 0.0 0.0 0:00 1 exim
1381 root 15 0 664 604 388 S 0.0 0.0 1:15 3 antirelayd
1447 root 15 0 176 152 96 S 0.0 0.0 0:02 0 crond
1492 xfs 15 0 1904 60 32 S 0.0 0.0 0:00 0 xfs
1518 root 17 0 968 904 644 S 0.0 0.0 3:49 0 sentarus-hostd
1533 daemon 15 0 192 168 124 S 0.0 0.0 0:00 3 atd
1541 root 15 0 3448 744 348 S 0.0 0.0 0:02 1 cppop
1767 mailnull 15 0 2860 892 464 S 0.0 0.0 1:32 2 eximstats
1797 root 21 0 3612 848 364 S 0.0 0.0 0:04 0 cpsrvd
1956 root 34 19 13308 11M 904 S N 0.0 0.5 6:42 0 cpanellogd
1961 root 15 0 3272 360 172 S 0.0 0.0 0:08 2 cppop
1969 nobody 25 0 728 4 0 S 0.0 0.0 0:00 3 entropychat
1973 nobody 25 0 240 4 0 S 0.0 0.0 0:00 0 melange
2038 cpanel 18 0 860 656 288 S 0.0 0.0 0:00 3 stunnel-4.04loc
2093 mailman 15 0 3248 992 472 S 0.0 0.0 0:00 3 mailmanctl
2101 root 15 0 120 64 60 S 0.0 0.0 0:00 1 rhnsd
2116 root 15 0 1692 688 380 S 0.0 0.0 0:03 3 ipalert_statd
2125 mailman 15 0 3040 1056 244 S 0.0 0.0 3:30 3 python2
2126 mailman 15 0 3064 1068 260 S 0.0 0.0 3:43 0 python2
2127 mailman 15 0 3044 1024 244 S 0.0 0.0 3:26 1 python2
2128 mailman 15 0 3040 1052 244 S 0.0 0.0 3:38 2 python2
2129 mailman 15 0 3084 1076 244 S 0.0 0.0 3:31 0 python2

6. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf so post the contents inside of my.cnf (minus any passwords of course). If on Windows server if you don't have that file you need to log into telnet and as root user type

port = 3306
socket = /var/lib/mysql/mysql.sock


# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
max_connections = 500
key_buffer = 384M
max_allowed_packet = 2M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_type = 1
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

7. your mysql extended-status output which shows entire server's mysql activity status (preferably with a day or 2 worth of mysql uptime for better indication of real activity) either:

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 30 |
| Aborted_connects | 598 |
| Bytes_received | 2712646647 |
| Bytes_sent | 3563038880 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 2183744 |
| Com_change_master | 0 |
| Com_check | 164 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 15202 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 2 |
| Com_grant | 4 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 510969 |
| Com_insert_select | 1101 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 3257 |
| Com_optimize | 164 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 23685 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 2228804 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 14 |
| Com_show_fields | 0 |
| Com_show_grants | 1 |
| 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 | 642 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 10 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 129 |
| Com_show_variables | 34 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 3257 |
| Com_update | 1736998 |
| Connections | 2183867 |
| Created_tmp_disk_tables | 2086 |
| Created_tmp_tables | 43208 |
| Created_tmp_files | 4 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 102962 |
| Handler_read_first | 518881 |
| Handler_read_key | 174628942 |
| Handler_read_next | 173947589 |
| Handler_read_prev | 2751947 |
| Handler_read_rnd | 30382155 |
| Handler_read_rnd_next | 169279631 |
| Handler_rollback | 0 |
| Handler_update | 3132938 |
| Handler_write | 1816029 |
| Key_blocks_used | 375052 |
| Key_read_requests | 426434487 |
| Key_reads | 1210129 |
| Key_write_requests | 849963 |
| Key_writes | 772026 |
| Max_used_connections | 151 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 430 |
| Open_files | 567 |
| Open_streams | 0 |
| Opened_tables | 1118 |
| Questions | 13704349 |
| Qcache_queries_in_cache | 5859 |
| Qcache_inserts | 2217834 |
| Qcache_hits | 4812858 |
| Qcache_lowmem_prunes | 37387 |
| Qcache_not_cached | 10970 |
| Qcache_free_memory | 10583544 |
| Qcache_free_blocks | 1898 |
| Qcache_total_blocks | 13967 |
| Rpl_status | NULL |
| Select_full_join | 579 |
| Select_full_range_join | 0 |
| Select_range | 404577 |
| Select_range_check | 0 |
| Select_scan | 120797 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 26 |
| Sort_merge_passes | 0 |
| Sort_range | 386515 |
| Sort_rows | 43890598 |
| Sort_scan | 47954 |
| Table_locks_immediate | 9028114 |
| Table_locks_waited | 18261 |
| Threads_cached | 7 |
| Threads_created | 53574 |
| Threads_connected | 6 |
| Threads_running | 1 |
| Uptime | 190350 |
+--------------------------+------------+

8. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?

PhotoClassifieds
PhpAdsNew

9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?

Average = 200, Max = 350

cookie timeout = 900

10. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

http://www.ferrarichat.com/forum/phpinfo.php

11. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150

12. what version of vB are you running ?

3.0.7

13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log

You can use this command to find such files

Quote:
find / -size +2000000k

Only tar backup files.

Thank you very much,
rob

[RobFerrari]

Just a note that the forum has been running really slow lately. Thank you.

[RobFerrari]

I think we found the reason for the major slow down, some log files had grown to over 2.4G in...

/usr/local/apache/domlogs

Why didn't the "find / -size +2000000k" find them?

That found all my tar backups and there were about 10 items that it wouldn't say what they are. I can't remember what it said, but something like "not available or no access".

Although this might be the major slow down, I would still like an overall performance analysis.

Thank you.

[RobFerrari]

Another interesting stat I found in Statistics - Top...

Record online users 919 (02-28-2005 03:37 PM)

Not sure if that is an error or not, I never see it over 300-400.

[eva2000]

You set your keybuffer way too high and are swapping to disk by 500+ MB! It could be due to the tar backups being >2GB in size when loaded into memory which isn't enough. How you backing up tarring these backups ?

try this new /etc/my.cnf below and restart mysql server


[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
safe-show-database
skip-innodb
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 = 7200
connect_timeout = 10
tmp_table_size = 64M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 64M
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


install eaccelerator from http://eaccelerator.net/InstallEacceleratorUk read http://www.vbulletin.com/forum/showpost.php?p=805040&postcount=441

as to unusally high vb user record could be search engine spiders/bots indexing your site ?

[RobFerrari]

as to unusally high vb user record could be search engine spiders/bots indexing your site ?

Thanks for your other advice! I will give it a try.

Do spiders slow the server down much? Any way to block them?

[Zachery]

They are guests, so they are as intensive as any guests can be, blocking them would be a bad idea unless you don't care about being listed in google, msn, yahoo, ect.

[RobFerrari]

install eaccelerator from http://eaccelerator.net/InstallEacceleratorUk read http://www.vbulletin.com/forum/showpost.php?p=805040&postcount=441

I already use Zend, so this isn't really worth it to me, is it?

Thanks.

Server is much faster now after cleaning out the log files and changing the my.cnf. Any other advice?

[eva2000]

yes eaccelerator isn't same as Zend so install it ;)

[RobFerrari]

yes eaccelerator isn't same as Zend so install it ;)

I still think you are joking, I have Zend Performance Suite, so don't think I should also install eaccelerator. Seems like they're doing the exact same thing.

[BamaStangGuy]

I still think you are joking, I have Zend Performance Suite, so don't think I should also install eaccelerator. Seems like they're doing the exact same thing.
It is NOT the same lol.

[RobFerrari]

It is NOT the same lol.

Can someone please explain the inside joke going on?

[BamaStangGuy]

Can someone please explain the inside joke going on?
What joke? eaccelerator caches php scripts zend doesn't... simple as that. That is where you gain all your performance. It lowers cpu usage. I would install eacclerator and get rid of zend all together as eaccelerator is basically the same as zend BUT eaccelerator caches php scripts so that they do not have to be called every page load.

http://mustangevolution.com/php.php

Scroll down and find eaccelerator... it shows you how much memory is being used to store the scripts and how many scripts have been cached

[eva2000]

I still think you are joking, I have Zend Performance Suite, so don't think I should also install eaccelerator. Seems like they're doing the exact same thing.
oh misread it didn't see the zend performance suite heh... so yeah you don't need eaccelerator

[RobFerrari]

How about these numbers? I think it is pretty good for only one server and a couple other apps running too.

0.62 0.56 0.52 | 330 Users Online (243 members and 87 guests)

[eva2000]

that's looking good but the mysql extended-status output question #7 is also needed to see how you're doing