Often the server load is high

Published by: rose 2009-01-07

High server load when approving Pictures - PhotoPost Community::
For some reason, when I do batch approving of at least 12 pics or more, My sever load doubles and even triples. I have to hurry and restart apache.
http://www.photopost.com/forum/photopost-pro-bug-reports/132265-high-server-load-when-approving-pictures.htmlHOME

I have a vb forum in my own server, the specification of the server is 2.8 Dual Xeon, 4GB ram and with a big capacity, and the load is always high due to the big traffic on my forum, my database is 6.1GB and the visitors are many, and the current running processes is 61 simulations. I need a solution of my problem, what is the action that should I do.

High server load, sending tons of email?::
10 posts - Last post: Mar 11, 2008High load at random moments that disrupts other sites and causes the server to freeze up. Site info: - SMF1.1.4 - TP0.9.8 - 2300 users
http://www.simplemachines.org/community/index.php?topic=225784.0HOME

Re: Overscheduling DOES happen with high web server load.::
I hope you're accounting for the fact that often there _are_ many simultaneous Prev by thread: Re: Overscheduling DOES happen with high web server load.
http://www.linuxhq.com/lnxlists/linux-kernel/lk_9905_01/msg00876.htmlHOME

----------------------------------

Hello Nasser,

Here are the answers to the questions that vBulletin requested. You will need to answer Questions 9 and 12.

1. This is a dedicated server.

2. Dual Xeon 2.6 GHz CPUs
4 GB RAM
73 GB SCSI non-RAID
Fedora Core release 1
Apache 1.3.33
PHP 4.3.10 w/ Zend Optimizer 2.5.7
MySQL server version 4.0.22-standard

3. I do not believe that there are any InnoDB tables.

4. MySQL was installed via RPM during cPanel installation.

5. Output from top:

00:29:24 up 3:14, 1 user, load average: 0.72, 0.69, 0.71
84 processes: 82 sleeping, 1 running, 1 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 16.8% 0.0% 2.8% 0.0% 0.0% 0.0% 380.0%
cpu00 7.9% 0.0% 1.3% 0.0% 0.0% 0.0% 90.6%
cpu01 0.0% 0.0% 0.3% 0.0% 0.0% 0.0% 99.6%
cpu02 0.5% 0.0% 0.5% 0.0% 0.0% 0.0% 98.8%
cpu03 8.3% 0.0% 0.5% 0.0% 0.0% 0.0% 91.0%
Mem: 4139056k av, 2245140k used, 1893916k free, 0k shrd, 58692k buff
1125448k active, 1013680k inactive
Swap: 2048276k av, 0k used, 2048276k free 1940704k cached

This is with the vBulletin forum turned off.

6. /etc/my.cnf:

[mysqld]
set-variable = max_connections=950
safe-show-database

7. mysqladmin extended-status output:

+--------------------------+-----------+
Variable_name Value
+--------------------------+-----------+
Aborted_clients 0
Aborted_connects 1
Bytes_received 7461087
Bytes_sent 878591021
Com_admin_commands 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 4029
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 39
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 1121
Com_insert_select 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 93
Com_optimize 0
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 11
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 30256
Com_set_option 21
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 1
Com_show_databases 2
Com_show_fields 90
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 469
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 3
Com_show_innodb_status 0
Com_show_tables 2
Com_show_variables 0
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 93
Com_update 1757
Connections 3978
Created_tmp_disk_tables 4
Created_tmp_tables 54
Created_tmp_files 3
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 2956
Handler_read_first 3699
Handler_read_key 732209
Handler_read_next 568858
Handler_read_prev 3663
Handler_read_rnd 160740
Handler_read_rnd_next 805771262
Handler_rollback 0
Handler_update 2079
Handler_write 67497
Key_blocks_used 7793
Key_read_requests 2629313
Key_reads 12560
Key_write_requests 7944
Key_writes 2028
Max_used_connections 11
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 64
Open_files 119
Open_streams 0
Opened_tables 183
Questions 41965
Qcache_queries_in_cache 0
Qcache_inserts 0
Qcache_hits 0
Qcache_lowmem_prunes 0
Qcache_not_cached 0
Qcache_free_memory 0
Qcache_free_blocks 0
Qcache_total_blocks 0
Rpl_status NULL
Select_full_join 7
Select_full_range_join 0
Select_range 6503
Select_range_check 0
Select_scan 14900
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 233
Sort_rows 4071834
Sort_scan 4068
Table_locks_immediate 37547
Table_locks_waited 32
Threads_cached 0
Threads_created 3977
Threads_connected 3
Threads_running 1
Uptime 5153
+--------------------------+-----------+

7. vB is essentially the only thing on the server using PHP and MySQL. Any other usage is not significant.

9. I don't know. You will have to answer this question.

10. http://alamuae.com/phpinfo.php

11. httpd.conf settings

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: High ::
In fact, write performance is often worse than that of a single server. Table 25-1. High Availability, Load Balancing, and Replication Feature Matrix
http://www.postgresql.org/docs/8.3/static/high-availability.htmlHOME

MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150

12. Version 3 of vBulletin, I believe.

13. The domlog for the domain was 2 GB. This has been fixed.

why empty pms ? their disk space used is very very little...

as to attachments you can move these out into the filesystem in vB 3.0.x which is recommended http://www.vbulletin.com/docs/html/main/attachment_storage

have you upgraded PHP/MySQL as advised yet ? see if you can safely upgrade to latest linux 2.6.x smp kernel too.

it does sound like a 2nd dedicated dual cpu database server may be needed

I have received a reply from the company that owns the server about my forum problem, this is the reply:

I have gathered some information from running mtop that you may wish to pass on to vBulletin in search of improvements. Several queries are running slowly, and one is run so frequently that the system may benefit from optimization:

Frequent:
SELECT ... FROM thread,user WHERE thread.lastposter=user.username AND thread.open

Slow:
Time Query
21 SELECT ... FROM thread AS thread LEFT JOIN user ON (user.username = thread.lastpo
61 DELETE FROM attachment USING attachment AS attachment, post AS p, thread AS t WHE
17 SELECT ... FROM session AS session LEFT JOIN user AS user ON(user.userid = sessio
12 SELECT ... FROM thread
15 SELECT ... FROM thread,user WHERE thread.lastposter=user.username AND thread.open

-----------
So what is your reply to this message?

Hi eva2000

We don't know how would like us to explain to you that we've done this before, and after 2 months later ALL the members including the Admin's them self even couldn't upload any attachments

about upgrading the PHP I'll upgrade it from 4.3.10 to 4.3.11 NOW, and we'll do what you asked us to upgrade, but moving the attachments from MySQL to filesystem, no way that we'll do this after what we faced of problem from the last time that we've done it, and we're not ready to lose members + attachments again

Thanks a lot

:) :)

Won't adding an index on dateline for the post table help in this case?

thes last msg . from Help Desk my server
-----------------
The server load broached the 50 mark and did not look at all like it was going to recover. I shut down MySQL and, while it was down, adjusted the temp_table_size setting to 3072 MB. This should accommodate both of the largest tables in memory simultaneously.

We will continue monitoring the server to see if this last adjustment helps. If it does not, we will need to consider alternate solutions--perhaps moving the database to a dedicated backend server.

We need the full query, the shortened version is kinda useless since we dont know what they were looking at and for all I know one of the queries could be causing the locking and the others are extremely fast but are stuck waiting on the first one to finish.

nope PHP still says PHP 4.3.10 http://alamuae.com/phpinfo.php not 4.3.11

also type in ssh telnet

mysqladmin -u root -p version

and see what mysql version is installed should be 4.0.24 if it's upgraded

backup your forum using mysqldump command http://www.sitepoint.com/article/678 before moving attachments to filesystem ;)

Your problem is a lack of any mysql configuration.

For the time being, you can try this my.cnf

[mysqld]
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 = 1548
thread_cache_size = 64
wait_timeout = 1400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
skip-innodb

[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

Make sure you are running at least 3.0.3 patched

Not that it matters but this helped me out alot...
Thanks.
That's good to hear :)

Hi eva2000

about all the upgrades that you asked us to do, we've done it, and its upgraded from the support help desk of FastServers.Net

about moving the attachment from the MySQL to the filesystem in 3.0.x, i'v done it before 8 monthes ago, and I lost 1 year attachments of my members, plus the members in my forum in that time couldn't upload their attachments

yes, it was working with me for 2 months, but after the 2 months the members couldn't upload anything and error was showing to them, which I had a very hard time, which I had to change the way again from filesystem to MySQL again, and am not ready to lose AGAIN my members attachment and face to same problem and lose any attachment from my forum

so, I want to empty these 4 tabels:

pm 1,335,172 MyISAM 63.8 MB 47,022 Bytes
pmtext 526,413 MyISAM 306.3 MB 1,141 KB
postindex 69,701,315 MyISAM 1.5 GB
word 2,232,375 MyISAM 167.7 MB

which are more bigger than the attachment table !!

Thanks a lot

Hi MarcoH64

we couldn't understand what you are talking about ? can you make your post lel bit clear, please ??

Thanks a lot

index

I know it's not an answer but maybe can help

http://www.al-anwar.net/vb/showthread.php?t=4033

You need to tweak your mysql server settings currently most of it's at mysql default settings which won't do. Also 2GB domlog file probably hit your server max file size limit causing further cpu load spikes and swapping to disk by over 1GB of ram i see.

1. upgrade MySQL to 4.0.24
2. upgrade PHP from 4.3.10 to 4.3.11
3. edit your /etc/my.cnf to below and restart mysql

[mysqld]
safe-show-database
skip-locking
skip-innodb
max_connections = 650
key_buffer = 96M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 64
wait_timeout = 1200
connect_timeout = 10
tmp_table_size = 128M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384

[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 eaccelerator from http://eaccelerator.net/InstallEacceleratorUk read http://www.vbulletin.com/forum/showpost.php?p=805040&postcount=441

Not that it matters but this helped me out alot...
Thanks.

nope PHP still says PHP 4.3.10 http://alamuae.com/phpinfo.php not 4.3.11

also type in ssh telnet

mysqladmin -u root -p version

PHP has been upgraded to version 4.3.11

thes last msg . from Help Desk my server : fastservers.net
-----------------

max_connections is set to 650. Here is the entire config file contents:

root@serv2 [~]# more /etc/my.cnf
[mysqld]
safe-show-database
skip-locking
skip-innodb
max_connections = 650
key_buffer = 96M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 64
wait_timeout = 1200
connect_timeout = 10
tmp_table_size = 3072M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384

[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
root@serv2 [~]#

The problem is still showing to be the query "SELECT ... FROM post WHERE dateline >= 1116878400". When the load is below 5 or so, this query takes around 8 seconds to run. What we have seen is multiple instances of this query queueing up. This is probably driven the temp table to disk, as each copy of the table in memory is around 900 MB. Once there are more than three of these queries in memory, the maximum temp tables in memory is reached, at which point the data is driven to disk and we see performance degradation. The time that we had to intervene this queries were taking upwards of 90 seconds to run.

We will continue to watch the server. Perhaps vBulletin might have some thoughts on further optimizing at this point?

--------------
About my database I would like to empty these four tables, if there is any critical problem if I empty them, please inform me or if it is normal send me the command that empty the tables without any errors.

pm 1,335,172 MyISAM 63.8 MB 47,022 Bytes
pmtext 526,413 MyISAM 306.3 MB 1,141 KB
postindex 69,701,315 MyISAM 1.5 GB -
word 2,232,375 MyISAM 167.7 MB -

I would like to reduce the number of the attachments for a specific forum not all forums, please give me the command that empty the attachments from one specific forum.

attachment 24,563 MyISAM 1.5 GB 55,384 Bytes

well i asked a developer to comment on this, but the attachment deleting query could be slow due to the amount and size of your attachments table, hence why recommending you move attachments out to the filesystem.

About my database I would like to empty these four tables, if there is any critical problem if I empty them, please inform me or if it is normal send me the command that empty the tables without any errors.

pm 1,335,172 MyISAM 63.8 MB 47,022 Bytes
pmtext 526,413 MyISAM 306.3 MB 1,141 KB
postindex 69,701,315 MyISAM 1.5 GB -
word 2,232,375 MyISAM 167.7 MB -

I would like to reduce the number of the attachments for a specific forum not all forums, please give me the command that empty the attachments from one specific forum.

attachment 24,563 MyISAM 1.5 GB 55,384 Bytes