SQL Server Security, Performance & Tuning (SSQA.net)::
SSQA - Databases Performance, Security, High Availability & Business Continuity in SQL Server invisible contributions visible success
http://sqlserver-qa.net/blogs/perftune/archive/tags/sql+server/blogs/default.aspxHOME |
Hi,
I need help with server optimization. We are constantly facing server load issues. (whenever the forum is running.)
We have tracker running along with the forum too. (we are using XBT tracker)
Following are the details as mentioned in sticky thread by Steve Machol
1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)
It is basically a dedicated server i.e. we have root acccess but there are 2 more sites running along with it. (Very low traffic sites)
2. Server specs.
Dual Xeon 2.4 GHz
2 GB RAM
2 x 73 GB SCSI drives
Redhat Enterprise Linux 3
Apache 1.3.34
PHP 4.4.2
MySQL 4.1.14 Standard
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, we don't
4. if possible how mysql was compiled/installed
It is installed through cpanel's installer..
5. your top stats
11:42:13 up 1 day, 8:15, 2 users, load average: 1.19, 1.15, 1.12
274 processes: 271 sleeping, 2 running, 1 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 0.5% 28.2% 1.3% 0.0% 2.5% 0.4% 66.6%
cpu00 0.1% 2.9% 0.5% 0.3% 9.7% 0.5% 85.4%
cpu01 0.0% 17.7% 2.3% 0.0% 0.5% 0.3% 78.8%
cpu02 0.0% 7.9% 0.1% 0.0% 0.0% 0.9% 90.8%
cpu03 2.1% 84.2% 2.3% 0.0% 0.0% 0.0% 11.1%
Mem: 2055436k av, 2036096k used, 19340k free, 0k shrd, 82092k buff
1462212k actv, 278076k in_d, 31912k in_c
Swap: 2040212k av, 0k used, 2040212k free 1308400k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
20874 mysql 31 6 134M 134M 1656 R N 24.8 6.7 600:06 3 mysqld
6601 nobody 19 4 23052 22M 2660 S N 2.3 1.1 4:28 0 httpd
31675 root 16 0 7756 7752 1184 S 1.0 0.3 4:34 3 xbt_tracker
19753 mysql 22 6 134M 134M 1656 S N 0.8 6.7 0:04 0 mysqld
6599 nobody 20 4 13640 13M 2676 S N 0.3 0.6 5:07 2 httpd
1 root 15 0 460 460 400 S 0.0 0.0 0:06 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 1 keventd
7 root 36 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 1 bdflush
11 root 15 0 0 0 0 SW 0.0 0.0 0:21 0 kswapd
12 root 15 0 0 0 0 SW 0.0 0.0 0:42 0 kscand
14 root 15 0 0 0 0 SW 0.0 0.0 0:06 3 kupdated
15 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd
23 root 25 0 0 0 0 SW 0.0 0.0 0:00 3 scsi_eh_0
24 root 25 0 0 0 0 SW 0.0 0.0 0:00 3 scsi_eh_1
27 root 15 0 0 0 0 SW 0.0 0.0 0:05 1 kjournald
82 root 24 0 0 0 0 SW 0.0 0.0 0:00 1 khubd
879 root 15 0 0 0 0 SW 0.0 0.0 0:03 2 kjournald
924 root 15 0 0 0 0 SW 0.0 0.0 0:15 2 kjournald
962 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 kjournald
998 root 15 0 0 0 0 SW 0.0 0.0 0:10 0 kjournald
1050 root 15 0 0 0 0 SW 0.0 0.0 0:08 2 kjournald
1075 root 15 0 0 0 0 SW 0.0 0.0 0:12 1 kjournald
1092 root 15 0 0 0 0 SW 0.0 0.0 0:20 2 kjournald
3071 root 15 0 568 568 484 S 0.0 0.0 0:02 2 syslogd
3075 root 22 0 436 436 372 S 0.0 0.0 0:00 0 klogd
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).
#[mysqld]
#set-variable = max_connections=2000
#safe-show-database
#old lines end here
#DO NOT MODIFY THE FOLLOWING COMMENTED LINES!
#Created with ELS from www.nsonetworks.com (http://www.nsonetworks.com)
#els-build=4.1
[mysqld]
datadir=/var/lib/mysql
skip-locking
skip-innodb
skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=32M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=200
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=64M ## 64MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=1M ## 1MB for every 1GB of RAM
read_buffer_size=1M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM
thread_concurrency=2 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1
collation-server=latin1_general_ci
old-passwords
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
One Tip A Day - What Will You Learn Today?::
Daily tips on how you can get more from your PC, the internet, mobile and other devices. And Avi Files With Vista Media Center. Download Amazing High Dynamic Range Wallpapers
http://www.onetipaday.comHOME |
More about High-Volume Web Sites h-Volume Web Sites Sites::
Server grids facilitate workload management by eliminating the need to plan for and clustering, WebSphere Application Server, Version 5 can help with virtualization and get
http://www.redbooks.ibm.com/redbooks/pdfs/sg246562.pdfHOME |
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[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)
http://www. .com/mysql_stat.php
8. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
2 more sites using PHP and mysql but both are low traffic sites...
9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
200 - 250 users at a time, cookie timeout is 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. .com/php_info.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 250
AND Maxrequestsperchild value = 0
12. what version of vB are you running ?
3.5.3
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
I found one log file size hitting 2gig, its a gz file - size is 2.8 gig (I deleted it just now since it was not needed)
I hope these details are enough.
Please let me know if you want me to propvide any other details.
Regards,
Nikunj
We found the problem with the script , as per you pointed it was locking other queries. Its been fixed and now froum is running pretty smooth.
Thanx
1. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards
[mysqld]
safe-show-database
datadir=/var/lib/mysql
skip-locking
skip-innodb
skip-networking
old_passwords
back_log = 50
skip-innodb
max_connections = 650
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 = 128M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 4
query_cache_limit = 2M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
default-storage-engine = MyISAM
server-id=1
collation-server=latin1_general_ci
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
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. 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. change httpd.conf values for
from
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 250
AND Maxrequestsperchild value = 0
to
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 8
MinSpareServers 10
MaxSpareServers 15
StartServers 10
MaxClients 250
AND Maxrequestsperchild value = 5000
restart apache server
4. 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
run that for a few days and repost output for
mysqladmin -u root -p proc ext stat
could be related to your hacks too... dctorre_maindb3 is your forum database ?
if you remove torents, arcade hacks what happens as this is the query holding up all other queries causing them to lock
Copying to tmp table select a.attachmentid, t.title bt_name, a.bt_size, a.bt_tracker, f.forumid, f.title, p.postid,
x
we don't officially provide support for hacked forums, so you need to either ask author of the hack for support or try vbulletin.org as well
did you 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
unfortunately there is no paid support for hacks
Thanks for helping me, i have made the changes, i will update this post after a week or so.
Good to hear... be careful of the hacks you install.. and best to always remove hacks to troubleshoot load issues too.. you can do that by copying the directory of files to backup location in case you want to revert back to the hacked state
hmm hard to pinpoint the issue really more something for your web host to diagnose
how large is your databases ?
enough free disk space what does command df -h output in ssh telnet ?
apache error log ?
output of
mysqladmin -u root -p proc ext stat
did the restarts only start after the new my.cnf ?
Hi,
Mysql seems to be restarting automatically..
the fourm is becoming slow at times..and stops working..
follwoing is what i found in mysqld.log file...
root@merc [/var/log]# tail -25 mysqld.log
060124 11:08:04 [ERROR] /usr/sbin/mysqld: Can't find file: 'datastore.MYI' (errno: 2)
060124 11:29:01 [ERROR] /usr/sbin/mysqld: Can't find file: 'datastore.MYI' (errno: 2)
060124 11:29:01 [ERROR] /usr/sbin/mysqld: Can't find file: 'datastore.MYI' (errno: 2)
060124 11:29:07 [ERROR] /usr/sbin/mysqld: Can't find file: 'datastore.MYI' (errno: 2)
060124 11:29:07 [ERROR] /usr/sbin/mysqld: Can't find file: 'datastore.MYI' (errno: 2)
060125 3:02:58 [Note] /usr/sbin/mysqld: Normal shutdown
060125 3:02:59 [Note] /usr/sbin/mysqld: Shutdown complete
060125 03:02:59 mysqld ended
060125 03:27:18 mysqld started
060125 3:27:19 [Warning] Asked for 196608 thread stack, but got 126976
/usr/sbin/mysqld: ready for connections.
Version: '4.1.14-standard' socket: '/var/lib/mysql/mysql.sock' port: 0 MySQL Community Edition - Standard (GPL)
060128 13:04:53 [Note] /usr/sbin/mysqld: Normal shutdown
060128 13:04:54 [Note] /usr/sbin/mysqld: Shutdown complete
060128 13:04:55 mysqld ended
060128 13:04:56 mysqld started
060128 13:04:57 [Warning] Asked for 196608 thread stack, but got 126976
/usr/sbin/mysqld: ready for connections.
Version: '4.1.14-standard' socket: '/var/lib/mysql/mysql.sock' port: 0 MySQL Community Edition - Standard (GPL)
any pointers?
Thanks
Yes maindb3 is the db we are using for forums...
I have disabled the options you have mentioned...
I guess I will have to post in vbulletin.org or does vbulletin provide paid support for this?
Nikunj
Hi,
I am sorry for replying late.
Currently mysql got stuck again because of this problem...following are the details...
DB size is around 185 MB
Apache error log output...
nothing much in it basically...
[Thu Feb 2 23:05:38 2006] [error] [client 65.110.30.93] File does not exist: /home/dctorre/public_html/404.shtml
[Thu Feb 2 23:05:39 2006] [error] [client 218.111.5.99] File does not exist: /home/dctorre/public_html/customavatars/avatar12_15.gif
[Thu Feb 2 23:05:39 2006] [error] [client 218.111.5.99] File does not exist: /home/dctorre/public_html/404.shtml
[Thu Feb 2 23:05:39 2006] [error] [client 65.110.30.93] File does not exist: /home/dctorre/public_html/customavatars/avatar4371_28.gif
[Thu Feb 2 23:05:39 2006] [error] [client 65.110.30.93] File does not exist: /home/dctorre/public_html/404.shtml
[Thu Feb 2 23:05:41 2006] [error] [client 69.192.8.10] File does not exist: /home/dctorre/public_html/customavatars/avatar3_25.gif
[Thu Feb 2 23:05:41 2006] [error] [client 69.192.8.10] File does not exist: /home/dctorre/public_html/404.shtml
[Thu Feb 2 23:05:41 2006] [error] [client 69.192.8.10] File does not exist: /home/dctorre/public_html/favicon.ico
[Thu Feb 2 23:05:41 2006] [error] [client 69.192.8.10] File does not exist: /home/dctorre/public_html/404.shtml
[Thu Feb 2 23:05:41 2006] [error] [client 24.181.224.81] File does not exist: /home/dctorre/public_html/customavatars/avatar12_15.gif
[Thu Feb 2 23:05:41 2006] [error] [client 24.181.224.81] File does not exist: /home/dctorre/public_html/404.shtml
[Thu Feb 2 23:05:42 2006] [error] [client 24.181.224.81] File does not exist: /home/dctorre/public_html/customavatars/avatar3_25.gif
[Thu Feb 2 23:05:42 2006] [error] [client 24.181.224.81] File does not exist: /home/dctorre/public_html/404.shtml
[Thu Feb 2 23:05:42 2006] [error] [client 24.181.224.81] File does not exist: /home/dclub/public_html/images/icons/icon14.gif
[Thu Feb 2 23:05:42 2006] [error] [client 24.181.224.81] File does not exist: /home/dclub/public_html/404.shtml
[Thu Feb 2 23:05:47 2006] [error] [client 69.192.8.10] File does not exist: /home/dctorre/public_html/favicon.ico
[Thu Feb 2 23:05:47 2006] [error] [client 69.192.8.10] File does not exist: /home/dctorre/public_html/404.shtml
[Thu Feb 2 23:05:47 2006] [error] [client 69.192.8.10] File does not exist: /home/dctorre/public_html/favicon.ico
[Thu Feb 2 23:05:47 2006] [error] [client 69.192.8.10] File does not exist: /home/dctorre/public_html/404.shtml
[Thu Feb 2 23:07:35 2006] [error] [client 67.187.26.244] File does not exist: /home/dctorre/public_html/favicon.ico
[Thu Feb 2 23:07:35 2006] [error] [client 67.187.26.244] File does not exist: /home/dctorre/public_html/404.shtml
the problem is with mysql...too many queries are getting locked at the same time and then mysql stops responding and after sometime it starts to restart itself...
following is the output of mysqladmin -u root -p proc ext stat
http://www. .com/mysql_output.txt
too many queries are getting locked...
and this was happening earlier too i.e. before putting your values in my.cnf...
Any ideas about this problem?
Thanks
Nikunj
Edit: The server load at that time is very normal i.e. less than 1
 Where was the last debate on wednesday with Mccain and Obama?
 INSTANCE / WAITING FOR SPRING
|
Need help with server optimization (high load issues)