PDA

View Full Version : [Problems with keepalive on] Server optimisation request


Pyro
Tue 22nd Nov '05, 3:05pm
I just switched my existing vBulletin to my new bought server and have the problem that when I turn "KeepAlive" to on and "KeepAliveTimeout" to 15 some pages (especially forums front page and message page with WYSIWYG editor) stop while loading somewhere in the middle of the pages. The pages are then loaded after some seconds completely. All pages from forum (not those of photopost) are busy for ~15 sec. after loading. This happens more in Firefox then in IE.
That's why I turned off "keep alive" at the moment -> problems seem to be solved completely.
But I think I could optimize a lot - so please have a deep look:

1. dedicated or shared virtual server.
DEDICATED

2. server specs:
IBM xSeries 235
CPU: SINGLE (!) XEON 3.2 GHz CPU (upgrade to dual next year)
(how to turn on Hyperthreading?!)

Memory: 2GB RAM (upgrade to 4GB next year)
HDD: 3 x 36 GB SCSI 10rpm at RAID5
OS: Fedora Linux 4
(all Apache/PHP/MySQL etc. modules were downloaded/installed from official Fedora servers - these are the most actual compiled packages available for Fedora)
apache/IIS version: 2.0.54 (Fedora)
PHP version: 5.0.4 (Fedora)
MySQL version: 4.1.14 (Fedora)
eAccalerator: not available as compiled version for Fedora, so not installed yet

3. do you have any innodb type databases/tables on your server ?
phpmyadmin says type MylSAM for all tables - is that the correct answer?

4. if possible how mysql was compiled/installed
automatic install/upgrade from Fedora servers, no compilation by ourself

5. your top stats
top - 20:07:10 up 5 days, 7:07, 1 user, load average: 0.23, 0.20, 0.18
Tasks: 82 total, 1 running, 81 sleeping, 0 stopped, 0 zombie
Cpu(s): 5.3% us, 0.5% sy, 0.0% ni, 94.2% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 2074784k total, 1984044k used, 90740k free, 86088k buffers
Swap: 2031608k total, 804k used, 2030804k free, 1647332k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26148 apache 15 0 29464 17m 8640 S 8.7 0.9 0:01.20 httpd
26153 apache 15 0 29624 17m 8684 S 1.3 0.9 0:00.79 httpd
2019 mysql 16 0 87076 59m 3308 S 0.3 3.0 79:21.88 mysqld
26167 apache 16 0 29332 17m 8628 S 0.3 0.8 0:00.52 httpd
1 root 16 0 1748 572 492 S 0.0 0.0 0:01.97 init
2 root RT 0 0 0 0 S 0.0 0.0 0:00.11 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/0
4 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.09 migration/1
6 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/1
7 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/1
8 root 10 -5 0 0 0 S 0.0 0.0 0:00.01 events/0
9 root 10 -5 0 0 0 S 0.0 0.0 0:00.03 events/1
10 root 20 -5 0 0 0 S 0.0 0.0 0:00.01 khelper
11 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 kthread
14 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
113 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kblockd/0


6. your mysql configuration variables located at /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=latin1

[mysql.server]
user=mysql
basedir=/var/lib
default-character-set=latin1

[mysqld]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
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 = 1800
thread_cache_size = 384
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 32M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 2
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
old_passwords=1
skip-locking
skip-networking
set-variable=local-infile=0
default-character-set=latin1

[mysqld_safe]
open_files_limit = 8192
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

7. your mysql extended-status output

+--------------------------------+-----------+
| Variable_name | Value |
+--------------------------------+-----------+
| Aborted_clients | 6 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 594684728 |
| Bytes_sent | 859423981 |
| 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 | 340475 |
| 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 | 33029 |
| 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 | 49050 |
| Com_insert_select | 388 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| 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 | 51404 |
| 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 | 821675 |
| Com_set_option | 68 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 2 |
| Com_show_charsets | 17 |
| Com_show_collations | 17 |
| Com_show_column_types | 0 |
| Com_show_create_db | 7 |
| Com_show_create_table | 0 |
| Com_show_databases | 8 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 2 |
| Com_show_innodb_status | 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_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 | 93 |
| Com_show_variables | 80 |
| 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 | 0 |
| Com_unlock_tables | 0 |
| Com_update | 205819 |
| Com_update_multi | 0 |
| Connections | 539822 |
| Created_tmp_disk_tables | 502 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 37961 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 2 |
| Handler_commit | 0 |
| Handler_delete | 26029 |
| Handler_discover | 0 |
| Handler_read_first | 77196 |
| Handler_read_key | 19050607 |
| Handler_read_next | 64602019 |
| Handler_read_prev | 172223 |
| Handler_read_rnd | 21801855 |
| Handler_read_rnd_next | 482362668 |
| Handler_rollback | 0 |
| Handler_update | 5285587 |
| Handler_write | 55631059 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 14497 |
| Key_read_requests | 37004446 |
| Key_reads | 21405 |
| Key_write_requests | 136179 |
| Key_writes | 106508 |
| Max_used_connections | 20 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 299 |
| Open_streams | 0 |
| Open_tables | 172 |
| Opened_tables | 172 |
| Qcache_free_blocks | 4250 |
| Qcache_free_memory | 16591960 |
| Qcache_hits | 1362910 |
| Qcache_inserts | 809927 |
| Qcache_lowmem_prunes | 10105 |
| Qcache_not_cached | 11765 |
| Qcache_queries_in_cache | 8624 |
| Qcache_total_blocks | 21701 |
| Questions | 5381120 |
| Rpl_status | NULL |
| Select_full_join | 40 |
| Select_full_range_join | 0 |
| Select_range | 138686 |
| Select_range_check | 0 |
| Select_scan | 154286 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slave_retried_transactions | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 202045 |
| Sort_rows | 53253833 |
| Sort_scan | 71341 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 2045757 |
| Table_locks_waited | 342 |
| Threads_cached | 18 |
| Threads_connected | 2 |
| Threads_created | 44 |
| Threads_running | 1 |
| Uptime | 456236 |
+--------------------------------+-----------+

8. is your vB the only thing on the server?
No, I use PhotoPost 4.8x too

9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
Average users: 100-130, peeks during season: 250-500 (upto 1000 expected within next year)
900 Seconds session time

10.
Will send to you via PN since it contains password

11.
Timeout 120

<IfModule prefork.c>
StartServers 8
MinSpareServers 8
MaxSpareServers 15
ServerLimit 256
MaxClients 180
MaxRequestsPerChild 1000
</IfModule>

<IfModule worker.c>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>

MaxClients 150
MaxKeepAliveRequests 100
MinSpareServers 5
MaxSpareServers 10
StartServers 5
KeepAliveTimeout 15
KeepAlive Off
MaxRequestsPerChild 1000
12. what version of vB are you running ?

vB 3.0.3 with security patches upto 3.0.10
Update is not immediatly possible because important layout modifications and because for 3.5.x I would have to upgrade PhotoPost too. I never will do this again but wanted to switch to vBAdvanced Gallery (now Photopost too :()

13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits
There seem to be no such files

MANY THANKS IN ADVANCE!

Pyrotechnically yours
Pyro

eva2000
Wed 23rd Nov '05, 4:16am
my.cnf and mysql looks fine i'd try

1. downgrade to PHP 4.4.1 or upgrade to PHP 5.0.5 - 4.4.1 is what i'd recommend

2. Install APC Cache lastest version from http://pecl.php.net/package/APC

3. change httpd.conf

from

<IfModule prefork.c>
StartServers 8
MinSpareServers 8
MaxSpareServers 15
ServerLimit 256
MaxClients 180
MaxRequestsPerChild 1000
</IfModule>

to

<IfModule prefork.c>
StartServers 15
MinSpareServers 15
MaxSpareServers 20
ServerLimit 256
MaxClients 200
MaxRequestsPerChild 500
</IfModule>

restart apache server

and in httpd.conf comment out and unload some of the apache modules you don't need or use

4. upgrade properly to vB 3.0.10

Pyro
Wed 23rd Nov '05, 5:11am
Thank you George,

of course I will do your suggestions regarding 1./2./4.

Regarding point 3.
-----------------
I must admit that I don't know what module prefork.c is and if I really have loaded/installed it. The lines of code were in the httpd.conf from Apache and I did not comment them out.

So, how do I find out if I have module prefork.c running and are the lines

<IfModule worker.c>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>


and MaxClients 150
MaxKeepAliveRequests 100
MinSpareServers 5
MaxSpareServers 10
StartServers 5
KeepAliveTimeout 15
KeepAlive Off
MaxRequestsPerChild 1000 ignored, so that I don't have to change?

Last: what is with the "KeepAlive" - its said that it would be "good" for the webserver to keep the pipe open, but it is not set in module prefork.c.

So, turn it on in module prefork.c or leave it turned off in the "normal" settings (to avoid my hanging problems) or what?

Sorry for the confusion,
thanks a lot
Pyro

eva2000
Wed 23rd Nov '05, 8:08am
phpinfo.php shows prefork

if you restart apache and edit the keepalive timeout and keepalive outside of prefork tags they should show changes in phpinfo

as to prefork i believe that is default also for apache2 unless you deliberately compiled apache2 with --with-mpm=worker or --with-mpm=perchild