Very slow forum

[bpn]

Hi, can you please help me here Eva2000 ?
My forum is very slow....it can easily take a minute or two sometimes
to load the mainpage, and the other pages.
The normal amount of users is about 200-300 currently online, about 12k members and 72k posts.

I do have another site on that server which has about 150k visitors
per day, and it´s loading a normal 200kb static html site.


Ppl can post images at my site, and I'm using the mod that will save
the pictures as files, and not keep them in the DB.
I have about 15.000 pictures in my folder, if that could be the reason ?

My server is a p4 1.6ghz
Ram: 1024
OS:Free BSD 4.6
MYSql 3.23.51
PHP 4.2.2 ('./configure' '--with-apxs=/usr/local/apache/bin/apxs' '--with-mysql=/usr/local')
vBulletin 2.2.6
Latest php accelerator is also installed


My TOP command during low peak hours: (can easily reach a load of 40 sometimes in peak hours)
last pid: 11000; load averages: 6.24, 7.77, 8.07 up 3+09:41:31 20:37:16
370 processes: 3 running, 339 sleeping, 28 zombie
CPU states: 37.3% user, 0.0% nice, 25.4% system, 6.0% interrupt, 31.3% idle
Mem: 361M Active, 108M Inact, 181M Wired, 6480K Cache, 108M Buf, 310M Free
Swap: 1024M Total, 6396K Used, 1018M Free


My httpd.conf:

Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 900
MaxRequestsPerChild 1000


My Extended Status:
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 2 |
| Aborted_connects | 2 |
| Bytes_received | 40754080 |
| Bytes_sent | 509413660 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 13752 |
| 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 | 3125 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 3217 |
| Com_insert_select | 6 |
| Com_kill | 0 |
| Com_load | 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 | 20 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 223190 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 37282 |
| Connections | 27713 |
| Created_tmp_disk_tables | 8 |
| Created_tmp_tables | 2817 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 4269 |
| Handler_read_first | 2303 |
| Handler_read_key | 1668689 |
| Handler_read_next | 3385477 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 971296 |
| Handler_read_rnd_next | 8453562 |
| Handler_update | 31290 |
| Handler_write | 441958 |
| Key_blocks_used | 2557 |
| Key_read_requests | 7377267 |
| Key_reads | 2549 |
| Key_write_requests | 7205 |
| Key_writes | 2699 |
| Max_used_connections | 65 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 413 |
| Open_files | 395 |
| Open_streams | 0 |
| Opened_tables | 419 |
| Questions | 308269 |
| Select_full_join | 10 |
| Select_full_range_join | 0 |
| Select_range | 48164 |
| Select_range_check | 0 |
| Select_scan | 16098 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 79 |
| Slow_queries | 434 |
| Sort_merge_passes | 0 |
| Sort_range | 46415 |
| Sort_rows | 982207 |
| Sort_scan | 9548 |
| Table_locks_immediate | 307721 |
| Table_locks_waited | 13762 |
| Threads_cached | 0 |
| Threads_created | 27712 |
| Threads_connected | 32 |
| Threads_running | 30 |
| Uptime | 7375 |
+--------------------------+-----------+




My my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock


# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
skip-innodb
set-variable = key_buffer=64M
set-variable = max_allowed_packet=16M
set-variable = table_cache=1280
set-variable = sort_buffer=2M
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=64M
set-variable = interactive_timeout = 600
set-variable = wait_timeout = 300
set-variable = connect_timeout = 10
log-bin
server-id = 1

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

#safe-updates
set-variable= connect_timeout =2

[isamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout


Do you have any idea of what could be wrong ?

Cheers,
bpn

[jphilipson]

Your maxclients in apache should be set lower than 200 for starters. You can also give a bit more of that free memory to mysql.

[bpn]

Thanks, but can you be more precise on how to do it ?
And what do you mean by...
"maxclients in apache should be set lower than 200 for starters"


Btw, forgot to say that during the day my remaining memory is only about 50mb.

bpn

[bpn]

Don't you have any suggestions Eva2000 ?

[jphilipson]

In your httpd.conf, change maxclients to 150 and restart apache. Also, keep in mind, thats not the beefiest server (p4 williamettes just ain't great server chips), and with a few hundred online its gonna work. But your maxclients in apache is way to high, change that and you should see some improvement. Defualt is 150, maybe your box could handle 200 ... but 900... lol were talking a quad xeon there.

[jphilipson]

also, try changing you my.cnf to..


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=350
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=7200
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[mysql.server]
user=mysql
basedir=/var/lib

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

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M


restart mysql and see how it goes... but change the MaxClients in httpd.conf to 150 first .. thats the biggest problem i see.

[tcs]

If you have allot of file in the forums area MYSql will cause you board to dog out bad too..

[bpn]

Thanks, I´ll give that a try :)

Ohh, and thank you too eva2000.....I really appreciated your suggestions.....way to go :rolleyes:

[eva2000]

Originally posted by bpn
Thanks, I´ll give that a try :)

Ohh, and thank you too eva2000.....I really appreciated your suggestions.....way to go :rolleyes: sorry bpn... i've been side tracked a bit with personal pc problems :o

reason why you're forum was slow because of these 2 values in my.cnf

log-bin
server-id = 1

removing them will reduce load by alot

2nd is as already mentioned maxclients is set too high... reducing to 100 or 150 and restarting apache is recommended

jphilipson's recommended my.cnf is about right for your server, might want to increase max_connections from 350 to 450 instead

also upgrade to PHP 4.2.3

[cerebro]

Originally posted by jphilipson
also, try changing you my.cnf to..
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=350
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=7200
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[mysql.server]
user=mysql
basedir=/var/lib

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

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M


restart mysql and see how it goes... but change the MaxClients in httpd.conf to 150 first .. thats the biggest problem i see.


I have some problems whit this config...my mysql is not in /var/lib/mysql is un /blabla/lalala
so?? when i use this config...the process doesnt start. what i hace to change to make it work.

[eva2000]

Originally posted by cerebro
I have some problems whit this config...my mysql is not in /var/lib/mysql is un /blabla/lalala
so?? when i use this config...the process doesnt start. what i hace to change to make it work. what's your current my.cnf ? locate where your mysqladmin client is and your mysql data files are

[cerebro]

Originally posted by eva2000
what's your current my.cnf ? locate where your mysqladmin client is and your mysql data files are That is the problem...i try to find my default my.cnf and i cant find it
find / | grep my.cnf ..nothing happend :(

the funny thing is when i delete /etc/my.cnf the mysql start just fine...but when i create /etc/my.cnf i get a error and the mysqld crash.
i thing that the problem is becauso the default directory of the myslq is changed...so the /etc/my.cnf doesnt work.

if there a way to find out the config of the mysql...or see where are the default config...

PD: Sorry for my english...i speak spanish. :P

[eva2000]

Originally posted by cerebro
That is the problem...i try to find my default my.cnf and i cant find it
find / | grep my.cnf ..nothing happend :(

the funny thing is when i delete /etc/my.cnf the mysql start just fine...but when i create /etc/my.cnf i get a error and the mysqld crash.
i thing that the problem is becauso the default directory of the myslq is changed...so the /etc/my.cnf doesnt work.

if there a way to find out the config of the mysql...or see where are the default config...

PD: Sorry for my english...i speak spanish. :P if you don't have my.cnf at /etc/my.cnf then you need to create a new one.. mysql can run without one... but if you have wrong settings and paths in /etc/my.cnf it can crash mysql like you have experienced...


try this my.cnf instead

[mysqld]
set-variable = max_connections=350
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=7200
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[safe_mysqld]
open_files_limit=8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[cerebro]

Originally posted by eva2000
if you don't have my.cnf at /etc/my.cnf then you need to create a new one.. mysql can run without one... but if you have wrong settings and paths in /etc/my.cnf it can crash mysql like you have experienced...


try this my.cnf instead i just use that my.cnf and works fine.
i hope that the maxclient limit is not to high, and the too many conecttions erros is gone..
in a couple of hour i tell you.

Thanks <SPAN style="COLOR: #666696">eva2000!!</SPAN>

[eva2000]

Originally posted by cerebro
i just use that my.cnf and works fine.
i hope that the maxclient limit is not to high, and the too many conecttions erros is gone..
in a couple of hour i tell you.

Thanks <SPAN style="COLOR: #666696">eva2000!!</SPAN> maxclients for most vBs should not be set higher than 100 to 150

[cerebro]

Pls tell me a good values for this:
Simultaneous sessions limit ???
Refresh = ???? (in Who is online)

Timeout for Cookie ??





The forum has 4.300 members...and 180.000 treads