Hi eva2000 and friends,
Hope you guys can help me out here. Just bought a new DB-server and need some help tweaking this baby :D
These are dedicated servers
2-server set-up
WEBSERVER (forum + phpAdsNew + some small websites)
Dual Xeon 3.2/533
4 GB RAM
2x SATA
Fedora Core 2/CPANEL-WHM
Apache 1.3.34
PHP 4.4.2
eAccelerator v0.9.3
DB
Dual Xeon 2.6/533
6 GB RAM
3x SCSI/U320/15k (raid 0)
Cent OS 4.2
MySQL 4.0.25
The webserver and DB-server are connected true a gigabit crosscable.
no inno-db tables
Not sure, I used cPanel
When the forum hits 1500 users online the load goes to 20. These are my top stats at the time i've had 350 users online (1800s cookie timeout)
top - 02:39:40 up 9 days, 23:57, 1 user, load average: 0.79, 0.64, 0.61
Tasks: 203 total, 3 running, 200 sleeping, 0 stopped, 0 zombie
Cpu0 : 7.5% us, 2.3% sy, 0.0% ni, 89.5% id, 0.3% wa, 0.0% hi, 0.3% si
Cpu1 : 2.6% us, 0.7% sy, 0.0% ni, 96.1% id, 0.7% wa, 0.0% hi, 0.0% si
Cpu2 : 20.1% us, 2.6% sy, 0.0% ni, 76.0% id, 1.0% wa, 0.0% hi, 0.3% si
Cpu3 : 5.9% us, 1.0% sy, 0.0% ni, 91.5% id, 1.6% wa, 0.0% hi, 0.0% si
Mem: 4155116k total, 1627988k used, 2527128k free, 253348k buffers
Swap: 2096440k total, 0k used, 2096440k free, 687852k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2067 nobody 15 0 81908 19m 74m S 4.9 0.5 0:41.15 /usr/local/apache/bin/httpd -DSSL
2015 nobody 15 0 82876 19m 74m S 4.6 0.5 0:37.67 /usr/local/apache/bin/httpd -DSSL
2112 nobody 15 0 81840 17m 74m S 4.6 0.4 0:35.27 /usr/local/apache/bin/httpd -DSSL
2033 nobody 15 0 82000 18m 74m S 3.9 0.4 0:35.40 /usr/local/apache/bin/httpd -DSSL
2042 nobody 15 0 82192 19m 74m R 3.6 0.5 0:35.43 /usr/local/apache/bin/httpd -DSSL
2019 nobody 16 0 81864 19m 74m S 2.3 0.5 0:38.11 /usr/local/apache/bin/httpd -DSSL
2009 nobody 15 0 81812 19m 74m S 1.6 0.5 0:37.18 /usr/local/apache/bin/httpd -DSSL
2011 nobody 15 0 83040 21m 74m S 1.6 0.5 0:37.87 /usr/local/apache/bin/httpd -DSSL
2028 nobody 15 0 81868 17m 74m S 1.6 0.4 0:39.10 /usr/local/apache/bin/httpd -DSSL
2081 nobody 16 0 81852 18m 74m S 1.0 0.5 0:36.74 /usr/local/apache/bin/httpd -DSSL
2124 nobody 15 0 82152 20m 74m S 1.0 0.5 0:36.45 /usr/local/apache/bin/httpd -DSSL
14346 root 16 0 2684 1004 1624 R 1.0 0.0 0:00.19 top
1696 mysql 15 0 78920 17m 3760 S 0.7 0.4 0:26.69 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/blablablabla.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
2010 nobody 16 0 82556 20m 74m R 0.7 0.5 0:38.50 /usr/local/apache/bin/httpd -DSSL
2006 nobody 15 0 81916 18m 74m S 0.3 0.5 0:38.72 /usr/local/apache/bin/httpd -DSSL
2008 nobody 15 0 81944 18m 74m S 0.3 0.5 0:39.92 /usr/local/apache/bin/httpd -DSSL
2022 nobody 15 0 81876 20m 74m S 0.3 0.5 0:41.28 /usr/local/apache/bin/httpd -DSSL
2037 nobody 15 0 81916 19m 74m S 0.3 0.5 0:42.29 /usr/local/apache/bin/httpd -DSSL
2041 nobody 15 0 82236 20m 74m S 0.3 0.5 0:41.33 /usr/local/apache/bin/httpd -DSSL
2084 nobody 15 0 81932 20m 74m S 0.3 0.5 0:33.53 /usr/local/apache/bin/httpd -DSSL
2120 nobody 15 0 81876 18m 74m S 0.3 0.4 0:36.66 /usr/local/apache/bin/httpd -DSSL
13991 monadam 16 0 8128 2292 6876 S 0.3 0.1 0:00.09 sshd: monadam@pts/34
1 root 15 0 3132 464 1320 S 0.0 0.0 1:15.54 init [3]
2 root RT 0 0 0 0 S 0.0 0.0 0:02.60 [migration/0]
3 root 34 19 0 0 0 S 0.0 0.0 0:00.04 [ksoftirqd/0]
4 root RT 0 0 0 0 S 0.0 0.0 0:06.55 [migration/1]
5 root 34 19 0 0 0 S 0.0 0.0 0:00.05 [ksoftirqd/1]
6 root RT 0 0 0 0 S 0.0 0.0 17:33.14 [migration/2]
7 root 34 19 0 0 0 S 0.0 0.0 0:00.03 [ksoftirqd/2]
8 root RT 0 0 0 0 S 0.0 0.0 0:46.70 [migration/3]
9 root 34 19 0 0 0 S 0.0 0.0 0:00.03 [ksoftirqd/3]
10 root 5 -10 0 0 0 S 0.0 0.0 0:00.03 [events/0]
11 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [events/1]
12 root 5 -10 0 0 0 S 0.0 0.0 0:00.14 [events/2]
13 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [events/3]
14 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [khelper]
15 root 9 -10 0 0 0 S 0.0 0.0 0:00.00 [kacpid]
40 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [kblockd/0]
41 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [kblockd/1]
42 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [kblockd/2]
43 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [kblockd/3]
44 root 15 0 0 0 0 S 0.0 0.0 0:00.00 [khubd]
57 root 16 0 0 0 0 S 0.0 0.0 0:15.62 [kswapd0]
58 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [aio/0]
59 root 12 -10 0 0 0 S 0.0 0.0 0:00.00 [aio/1]
60 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [aio/2]
61 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [aio/3]
173 root 16 0 0 0 0 S 0.0 0.0 0:00.34 [kseriod]
210 root 12 -10 0 0 0 S 0.0 0.0 0:00.00 [ata/0]
211 root 6 -10 0 0 0 S 0.0 0.0 0:00.00 [ata/1]
212 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [ata/2]
213 root 6 -10 0 0 0 S 0.0 0.0 0:00.00 [ata/3]
217 root 15 0 0 0 0 S 0.0 0.0 0:31.57 [kjournald]
714 root 15 0 0 0 0 S 0.0 0.0 0:00.00 [kjournald]
715 root 15 0 0 0 0 S 0.0 0.0 0:38.87 [kjournald]
716 root 15 0 0 0 0 S 0.0 0.0 0:03.33 [kjournald]
717 root 15 0 0 0 0 S 0.0 0.0 1:03.73 [kjournald]
718 root 15 0 0 0 0 S 0.0 0.0 4:59.58 [kjournald]
1456 root 16 0 2168 460 1236 S 0.0 0.0 0:33.55 irqbalance
1481 root 16 0 2564 560 1300 S 0.0 0.0 0:04.86 rpc.idmapd
1560 root 19 0 2772 464 1240 S 0.0 0.0 0:00.00 /usr/sbin/acpid
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
datadir=/var/lib/mysql
socket = /var/lib/mysql/mysql.sock
bind-address=192.168.1.3
skip-locking
skip-innodb
max_connections = 900
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 = 256
interactive_timeout = 20
wait_timeout = 120
connect_timeout = 5
tmp_table_size = 128M
max_allowed_packet = 16M
max_connect_errors = 100
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 1M
query_cache_size = 96M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
[mysqld_safe]
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=64M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysql]
no-auto-rehash
#safe-updates
[mysqlhotcopy]
interactive-timeout
+--------------------------+------------+
Variable_name Value
+--------------------------+------------+
Aborted_clients 155212
Aborted_connects 1986
Bytes_received 3434276621
Bytes_sent 1961066375
Com_admin_commands 10890588
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 17882649
Com_change_master 0
Com_check 1
Com_commit 0
Com_create_db 1
Com_create_function 0
Com_create_index 0
Com_create_table 16
Com_delete 1197448
Com_delete_multi 0
Com_drop_db 1
Com_drop_function 0
Com_drop_index 0
Com_drop_table 1
Com_flush 18
Com_grant 9
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 4938817
Com_insert_select 41495
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 8
Com_optimize 854
Com_purge 0
Com_rename_table 0
Com_repair 852
Com_replace 2078605
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 43811068
Com_set_option 507
Com_show_binlog_events 0
Com_show_binlogs 20
Com_show_create 499
Com_show_databases 57
Com_show_fields 522
Com_show_grants 2
Com_show_keys 34
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 2846
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 6
Com_show_innodb_status 0
Com_show_tables 826
Com_show_variables 117
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 8
Com_update 24380217
Com_update_multi 0
Connections 6997556
Created_tmp_disk_tables 125623
Created_tmp_tables 556981
Created_tmp_files 11811
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 3275484
Handler_read_first 531739
Handler_read_key 3307571091
Handler_read_next 2857882235
Handler_read_prev 32984099
Handler_read_rnd 3437893949
Handler_read_rnd_next 2420164951
Handler_rollback 0
Handler_update 32920623
Handler_write 23371365
Key_blocks_used 93763
Key_read_requests 2098549553
Key_reads 11997009
Key_write_requests 16855342
Key_writes 13886950
Max_used_connections 447
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 664
Open_files 718
Open_streams 0
Opened_tables 8180
Questions 152824218
Qcache_queries_in_cache 11067
Qcache_inserts 43212676
Qcache_hits 51568907
Qcache_lowmem_prunes 382102
Qcache_not_cached 579805
Qcache_free_memory 79181808
Qcache_free_blocks 9112
Qcache_total_blocks 31538
Rpl_status NULL
Select_full_join 2310
Select_full_range_join 167
Select_range 9700520
Select_range_check 0
Select_scan 5448677
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 124
Sort_merge_passes 5897
Sort_range 8972552
Sort_rows 3452519853
Sort_scan 2148583
Table_locks_immediate 130716893
Table_locks_waited 3177120
Threads_cached 170
Threads_created 5704
Threads_connected 114
Threads_running 1
Uptime 849048
+--------------------------+------------+
Uptime: 849048 Threads: 114 Questions: 152824219 Slow queries: 124 Opens: 8180 Flush tables: 1 Open tables: 664 Queries per second avg: 179.995
mysqladmin Ver 8.40 Distrib 4.0.25, for pc-linux-gnu on i686
Server version 4.0.25-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 9 days 19 hours 50 min 48 sec
I also use PhpAdsNew (2 mlj AdViews a day)
average users 1500; max 2200 (1800s cookie timeout)
http://www.mon-amsterdam.nl/phpinfo.php
KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 4
MinSpareServers 30
MaxSpareServers 75
StartServers 35
MaxClients 288
MaxRequestsPerChild 10000
SendBufferSize 53248
vB 3.5.2 (with 3.5.3 security-patch, no hacks)
Don't have any large files, larget domlog file = 300mb
is the only problem with "unblocked with 'mysqladmin flush-hosts', causing the load on the webserver to further increase." messages ?
problem might not even be your mysql server if the error is this
Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html
it could be a network TCP/IP issue on web server or between web and db server which you web host must investigate
f you get the following error, it means that mysqld has received many connect requests from the host 'host_name' that have been interrupted in the middle:
The number of interrupted connect requests allowed is determined by the value of the max_connect_errors system variable. After max_connect_errors failed requests, mysqld assumes that something is wrong (for example, that someone is trying to break in), and blocks the host from further connections until you execute a mysqladmin flush-hosts command or issue a FLUSH HOSTS statement.
Need Website Help? Greenjar.com - Atlanta Usability ::
Website Help & Improvement â ¢ Tweak user-effectiveness â ¢ Website Conversions â ¢ Application design â ¢ Full Redesigns â ¢ Supercharge your email program
http://www.greenjar.com/HOME |
If you get this error message for a given host, you should first verify that there isn't anything wrong with TCP/IP connections from that host. If you are having network problems, it does you no good to increase the value of the max_connect_errors variable.
if it is, then in my.cnf raise the value of this setting
from
max_connect_errors = 10
to
max_connect_errors = 100000
restart mysql
if it doesn't help still with that unblocked message, then the problem is network related between web server and mysql server and you need to get your web host to investigate it
hmmm PHP says you're still in 2.6.9-22 smp http://www.mon-amsterdam.nl/phpinfo.php maybe recompile PHP as well
can you post output you get from ssh telnet command
uname -a
on both servers
Sorry for the misunderstanding. That php file in the post above is on the Cpanel webserver serving only the static files for vb and als serving phpAdsNew web+db.
This is the phpinfo for the server serving the vb files:
- http://81.173.99.131/phpinfo.php
Grtz,
Hidjra
sounds like server is just under powered
can you post the top stats and mysqlreport http://www.vbulletin.com/forum/showthread.php?t=175177 output
we now know more that we did a few months ago, your problem is centOS + 2.6.9-22 linux bug read http://www.vbulletin.com/forum/showthread.php?t=171071 you need to either downgrade to 2.6.9-11 kernel or upgrade to 2.6.15-4 latest kernelOuch, sorry, forgot that little thingy. I already updated the kernel to 2.6.15.4. ;)
Hidjra
is the only problem with "unblocked with 'mysqladmin flush-hosts', causing the load on the webserver to further increase." messages ?
problem might not even be your mysql server if the error is this
http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html
it could be a network TCP/IP issue on web server or between web and db server which you web host must investigate
if it is, then in my.cnf raise the value of this setting
from
max_connect_errors = 10
to
max_connect_errors = 100000
restart mysql
if it doesn't help still with that unblocked message, then the problem is network related between web server and mysql server and you need to get your web host to investigate it
Hi eva2001,
I don't have the 'unblocked message' issue anymore. Don't really know what made them go away but it seems that it wasn't the reason for the high load, as i still have those occasionaly :(
Grtz,
Hidjra
hmmm PHP says you're still in 2.6.9-22 smp http://www.mon-amsterdam.nl/phpinfo.php maybe recompile PHP as well
can you post output you get from ssh telnet command
uname -a
on both servers
1.Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
datadir=/var/lib/mysql
socket = /var/lib/mysql/mysql.sock
bind-address=192.168.1.3
skip-locking
safe-show-database
skip-innodb
max_connections = 800
key_buffer = 192M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 512
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 256M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
thread_concurrency = 4
query_cache_limit = 2M
query_cache_size = 192M
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
[mysqlhotcopy]
interactive-timeout
2. Remove eaccelerator and install APC Cache lastest version from http://pecl.php.net/package/APC. Read install guide at http://www.vbulletin.com/forum/showthread.php?t=165367
3. Upgrade to vB 3.5.3 http://www.vbulletin.com/forum/showthread.php?t=166391 if you're on vB 3.5.x
4. change httpd.conf values from
KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 4
MinSpareServers 30
MaxSpareServers 75
StartServers 35
MaxClients 288
MaxRequestsPerChild 10000
SendBufferSize 53248
to
KeepAlive Off
MaxKeepAliveRequests 150
KeepAliveTimeout 8
MinSpareServers 15
MaxSpareServers 25
StartServers 15
MaxClients 200
MaxRequestsPerChild 10000
SendBufferSize 131072 or 65536
restart apache server
5. If you just upgraded to vB 3.5.x try to disable these 4 options:
Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?
Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums
Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread
Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search
6. make sure phpadsnew has persistent connections disabled/off and vB config.php has usepconnect = 0
let this run for a few days and then come back and repost output for top and mysqladmin -u root -p ext stat output
what about db server uname -a output ?
output for the db-server
Linux db1.hidjra.nl 2.6.15.4 #1 SMP Fri Feb 24 03:02:53 CET 2006 i686 i686 i386 GNU/Linux
Thnx,
Hidjra
Hi eva200,
I don't have mysqlreport installed, i'll do that, meanwhile, here is some other info:
Phil Sheridan: Eagles need defensive help | Philadelphia ::
It pained Eagles tight end Brent Celek to talk about it, and not just because his lower lip was swollen to the size of a snowtube - courtesy of a cheap shot by
http://www.philly.com/philly/sports/20100110_Sheridan__Eagles_need_defensive_help.htmlHOME |
Fahlstad Design â ¢ Forum WordPress themes CSS I need ::
I need help with some small tweaks on: December 8, 2006, 22:08 ( I think I might actually be able to figure this one out on my own.) 3. I want the header
http://www.fahlstad.se/wp-forum/HOME |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Variable_name Value
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
back_log 50
basedir /
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert ON
connect_timeout 5
convert_character_set
datadir /var/lib/mysql/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb NO
have_crypt YES
have_innodb DISABLED
have_isam YES
have_raid NO
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_buffer_pool_size 8388608
innodb_data_file_path
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir
innodb_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_table_locks ON
interactive_timeout 20
join_buffer_size 1044480
key_buffer_size 205520896
language /usr/share/mysql/english/
large_files_support ON
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 67107840
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 800
max_connect_errors 100
max_delayed_threads 20
max_insert_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 67108864
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 4410
pid_file /var/lib/mysql/db1.hidjra.nl.pid
log_error
port 3306
protocol_version 10
query_alloc_block_size 32768
query_cache_limit 8388608
query_cache_size 201326592
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 65536
range_alloc_block_size 2048
read_buffer_size 1044480
read_only OFF
read_rnd_buffer_size 520192
rpl_recovery_rank 0
server_id 0
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
Thnx eva2k, i'll look into this :)
Keep up the good work. The web just wouldn't be the same if it wasn't for guys like you :D
Grtz,
Hidjra
No probs let us know how it goes :)
Hi eva2000,
I've had the DB running for a couple of days now. I did had to set persistant connections back to 1, because the forum seemed to be slower. I did manage to move phpAdsNew to a new webserver and a new MySQL-server. So vb is now the only thing runnning on the webserver and the DB-server. I also installed a new server to handle with all the static files (images, js en css). So the webserver only handles with php. I tried APC, but that seemed to cause the load to increase to 60-70, so i went back to eAccelerator.
I already tweaked apache by disabling hostnamelookups, symlinks, AllowOveride, CustomLog, directoryindex, extendedstatus, serversignature, sendbuffersize etc.
And PHP by editing things like magic_quotes_gpc, expose_php, register_argc_argv, always_populate_raw_post_data, output_buffering, memory_limit, max_execution_time etc.
I also changed the network buffer in tcp_wmem and tcp_mem.
The webserver is still slow on some times. And as soon as i restart mysql the load of the webserver drops to a reasonable (?) 10-15 :( instead of 30-40.
From time to time the DB-server needs to be unblocked with 'mysqladmin flush-hosts', causing the load on the webserver to further increase.
This is my MySQL STATUS-output:
+--------------------------+------------+
Variable_name Value
+--------------------------+------------+
Aborted_clients 49697
Aborted_connects 7045
Bytes_received 2129617798
Bytes_sent 1080602859
Com_admin_commands 7495665
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 7558238
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 880223
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_flush 4
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 2903440
Com_insert_select 27257
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 11
Com_optimize 550
Com_purge 0
Com_rename_table 0
Com_repair 551
Com_replace 441218
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 29772012
Com_set_option 632
Com_show_binlog_events 0
Com_show_binlogs 4
Com_show_create 671
Com_show_databases 91
Com_show_fields 673
Com_show_grants 78
Com_show_keys 30
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 1594
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_innodb_status 0
Com_show_tables 843
Com_show_variables 139
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 11
Com_update 9159196
Com_update_multi 2601
Connections 74725
Created_tmp_disk_tables 83541
Created_tmp_tables 411991
Created_tmp_files 8184
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 1890638
Handler_read_first 941589
Handler_read_key 1948296020
Handler_read_next 1181647666
Handler_read_prev 22448027
Handler_read_rnd 2218512641
Handler_read_rnd_next 3071499359
Handler_rollback 0
Handler_update 11800655
Handler_write 15880505
Key_blocks_used 187526
Key_read_requests 2074066304
Key_reads 5665027
Key_write_requests 13418324
Key_writes 9783383
Max_used_connections 338
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 705
Open_files 781
Open_streams 0
Opened_tables 6976
Questions 79571571
Qcache_queries_in_cache 31028
Qcache_inserts 29352909
Qcache_hits 28778807
Qcache_lowmem_prunes 69742
Qcache_not_cached 418979
Qcache_free_memory 156397528
Qcache_free_blocks 20219
Qcache_total_blocks 82505
Rpl_status NULL
Select_full_join 1723
Select_full_range_join 76
Select_range 6844541
Select_range_check 0
Select_scan 3082876
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 137
Sort_merge_passes 4087
Sort_range 6203753
Sort_rows 2234104027
Sort_scan 847006
Table_locks_immediate 81635810
Table_locks_waited 1574008
Threads_cached 258
Threads_created 339
Threads_connected 81
Threads_running 2
Uptime 476610
+--------------------------+------------+
TOP-status of DB-server
top - 21:03:43 up 40 days, 17:37, 1 user, load average: 0.79, 0.77, 0.78
Tasks: 419 total, 1 running, 418 sleeping, 0 stopped, 0 zombie
Cpu0 : 16.2% us, 10.6% sy, 0.0% ni, 72.8% id, 0.0% wa, 0.3% hi, 0.0% si
Cpu1 : 9.5% us, 7.9% sy, 0.0% ni, 81.6% id, 1.0% wa, 0.0% hi, 0.0% si
Cpu2 : 2.6% us, 2.0% sy, 0.0% ni, 95.4% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu3 : 3.9% us, 3.9% sy, 0.0% ni, 92.1% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 6233756k total, 6143772k used, 89984k free, 76060k buffers
Swap: 2096440k total, 144k used, 2096296k free, 5434380k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
28942 mysql 15 0 575m 487m 1556 S 7.3 8.0 4:43.00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15134 mysql 16 0 575m 487m 1556 S 3.0 8.0 12:39.34 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
16596 mysql 15 0 575m 487m 1556 S 2.3 8.0 3:36.50 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
16631 mysql 15 0 575m 487m 1556 S 2.3 8.0 8:18.93 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15613 mysql 15 0 575m 487m 1556 S 2.0 8.0 10:42.30 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
16579 mysql 15 0 575m 487m 1556 S 2.0 8.0 8:18.39 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
23496 mysql 16 0 575m 487m 1556 S 2.0 8.0 4:05.62 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15123 mysql 15 0 575m 487m 1556 S 1.7 8.0 10:04.06 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15128 mysql 15 0 575m 487m 1556 S 1.7 8.0 7:57.56 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15620 mysql 15 0 575m 487m 1556 S 1.7 8.0 7:06.21 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15155 mysql 16 0 575m 487m 1556 S 1.3 8.0 15:33.48 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15519 mysql 16 0 575m 487m 1556 S 1.3 8.0 12:25.37 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
16623 mysql 16 0 575m 487m 1556 S 1.3 8.0 11:37.79 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
23510 mysql 15 0 575m 487m 1556 S 1.3 8.0 4:25.74 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
28925 mysql 15 0 575m 487m 1556 S 1.3 8.0 4:28.69 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15509 mysql 16 0 575m 487m 1556 S 1.0 8.0 8:39.17 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15545 mysql 15 0 575m 487m 1556 S 1.0 8.0 12:56.35 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15565 mysql 15 0 575m 487m 1556 S 1.0 8.0 14:23.00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15582 mysql 15 0 575m 487m 1556 S 1.0 8.0 14:51.83 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15592 mysql 15 0 575m 487m 1556 S 1.0 8.0 5:49.16 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
16605 mysql 16 0 575m 487m 1556 S 1.0 8.0 13:39.31 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
28935 mysql 15 0 575m 487m 1556 S 1.0 8.0 9:46.74 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
11538 root 16 0 2948 1220 748 R 1.0 0.0 0:00.13 top
15164 mysql 15 0 575m 487m 1556 S 0.7 8.0 8:46.44 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15504 mysql 16 0 575m 487m 1556 S 0.7 8.0 10:25.98 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15515 mysql 15 0 575m 487m 1556 S 0.7 8.0 7:29.73 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15516 mysql 16 0 575m 487m 1556 S 0.7 8.0 13:14.43 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15523 mysql 16 0 575m 487m 1556 S 0.7 8.0 10:52.22 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15547 mysql 16 0 575m 487m 1556 S 0.7 8.0 8:19.02 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15548 mysql 15 0 575m 487m 1556 S 0.7 8.0 11:10.09 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15563 mysql 15 0 575m 487m 1556 S 0.7 8.0 7:26.83 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db1.hidjra.nl.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
15639 mysql 15 0 575m 487m 1556 S 0.7 8.0 0:00.00 [kblockd/2]
42 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [kblockd/3]
55 root 13 -10 0 0 0 S 0.0 0.0 0:00.00 [aio/0]
56 root 9 -10 0 0 0 S 0.0 0.0 0:00.00 [aio/1]
57 root 13 -10 0 0 0 S 0.0 0.0 0:00.00 [aio/2]
58 root 9 -10 0 0 0 S 0.0 0.0 0:00.00 [aio/3]
43 root 15 0 0 0 0 S 0.0 0.0 0:00.00 [khubd]
54 root 15 0 0 0 0 S 0.0 0.0 1:58.37 [kswapd0]
131 root 15 0 0 0 0 S 0.0 0.0 0:00.00 [kseriod]
198 root 21 0 0 0 0 S 0.0 0.0 0:00.00 [scsi_eh_0]
199 root 15 0 0 0 0 S 0.0 0.0 0:00.00 [aacraid]
215 root 15 0 0 0 0 S 0.0 0.0 0:03.65 [kjournald]
1047 root 6 -10 3492 460 380 S 0.0 0.0 0:00.03 udevd
1242 root 6 -10 0 0 0 S 0.0 0.0 0:00.00 [kauditd]
1279 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [kmirrord]
1280 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 [kmir_mon]
1303 root 19 0 0 0 0 S 0.0 0.0 0:00.00 [kjournald]
1304 root 15 0 0 0 0 S 0.0 0.0 0:11.37 [kjournald]
1305 root 15 0 0 0 0 S 0.0 0.0 0:23.96 [kjournald]
1306 root 15 0 0 0 0 S 0.0 0.0 0:02.40 [kjournald]
1307 root 15 0 0 0 0 S 0.0 0.0 9:48.91 [kjournald]
1931 root 16 0 2168 596 504 S 0.0 0.0 0:08.24 syslogd -m 0
1935 root 16 0 2324 468 404 S 0.0 0.0 0:00.01 klogd -x
1945 root 16 0 3416 472 404 S 0.0 0.0 0:06.03 irqbalance
1989 root 16 0 5984 1004 840 S 0.0 0.0 0:08.70 rpc.idmapd
2057 root 21 0 2164 548 476 S 0.0 0.0 0:00.00 /usr/sbin/acpid
2066 root 16 0 8656 2100 1644 S 0.0 0.0 0:00.20 cupsd
2131 root 18 0 2960 836 696 S 0.0 0.0 0:00.00 xinetd -stayalive -pidfile /var/run/xinetd.pid
2247 root 16 0 11592 5048 3004 S 0.0 0.1 0:41.91 /usr/local/apache/bin/httpd -DSSL
2257 root 16 0 5524 1120 716 S 0.0 0.0 0:02.34 crond
2418 dbus 16 0 3744 1224 1080 S 0.0 0.0 0:00.06 dbus-daemon-1 --system
Thnx eva!
Grtz,
Hidjra
what about db server uname -a output ?
we now know more that we did a few months ago, your problem is centOS + 2.6.9-22 linux bug read http://www.vbulletin.com/forum/showthread.php?t=171071 you need to either downgrade to 2.6.9-11 kernel or upgrade to 2.6.15-4 latest kernel
No probs let us know how it goes :)
 Where was the last debate on wednesday with Mccain and Obama?
 INSTANCE / WAITING FOR SPRING
|
Need some help tweaking this one