Results 1 to 16 of 16
-
10-18-2004, 02:23 PM #1Junior Guru Wannabe
- Join Date
- Oct 2003
- Posts
- 89
Unbearable Load, How to Deal With?
Today my site has been linked to from a big site and the load on my db server has shot up to about 10 (doing top). I checked vmstat and free and it's not swapping. So how can I lower the load? Are there any quick fixes I can do to lower it? It's a dual xeon 2G ram, using MySQL 4x. The web server is P4 and is doing fine. Thanks.
-
10-18-2004, 02:29 PM #2Junior Guru Wannabe
- Join Date
- Oct 2004
- Location
- Tampa, Florida
- Posts
- 80
You can try the basics
1. shutdown any unecessary services that are running
2. kill any cron jobs that might be unecessary
3. tweak your my.cnf (MySQL configuration file) to tune MySQL for better performanceeWebtricity
Hosting | Web Design | Server Administration
http://www.ewebtricity.net | sales@ewebtricity.net
http://www.1and1faq.com 1and1 Customer Support
-
10-18-2004, 02:33 PM #3Junior Guru Wannabe
- Join Date
- Oct 2004
- Location
- Tampa, Florida
- Posts
- 80
I use the following my.cnf on one our larger MySQL db servers
(turn of logging for MySQL if your using it, at least until the load dies down)
# The following options will be passed to all MySQL clients
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port = 3306
server-id=1
skip-locking
set-variable = max_connections=1000
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = max_allowed_packet=16M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=512
set-variable = wait_timeout=3600
set-variable = connect_timeout=10
set-variable = net_buffer_length=8K
set-variable = max_connect_errors=10
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
[myisamchk]
set-variable = key_buffer=96M
set-variable = sort_buffer=96M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
[mysqlhotcopy]
interactive-timeout
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files=8192eWebtricity
Hosting | Web Design | Server Administration
http://www.ewebtricity.net | sales@ewebtricity.net
http://www.1and1faq.com 1and1 Customer Support
-
10-18-2004, 02:34 PM #4WHT Addict
- Join Date
- Oct 2004
- Posts
- 100
Have you looked at your query plans to see if they are sane?
-
10-18-2004, 02:48 PM #5Web Hosting Master
- Join Date
- Dec 2002
- Location
- The Shadows
- Posts
- 2,925
Dual Xeon.... Hyperthreading? Do you run 2.4.x or 2.6.x for your kernel?
Also, what is the "top" process by memory usage and CPU usage?
On second though, post the top results for both CPU usage and Memory usage(The full top output please)Dan Sheppard ~ Freelance whatever
-
10-18-2004, 02:48 PM #6Newbie
- Join Date
- Jul 2003
- Posts
- 29
spamming open relay ? do you have pop-before-smtp?
-
10-18-2004, 04:11 PM #7Junior Guru Wannabe
- Join Date
- Oct 2003
- Posts
- 89
It's dual xeon no Hyperthreading. 2.4 kernel.
These are results from top. It's died down a bit but still too slow.
16:07:21 up 103 days, 2:58, 1 user, load average: 3.36, 4.88, 5.56
67 processes: 60 sleeping, 7 running, 0 zombie, 0 stopped
CPU0 states: 90.3% user 7.1% system 0.0% nice 0.0% iowait 2.0% idle
CPU1 states: 69.5% user 5.2% system 0.0% nice 0.0% iowait 24.3% idle
CPU2 states: 59.3% user 15.2% system 0.0% nice 0.0% iowait 24.4% idle
CPU3 states: 92.5% user 6.1% system 0.0% nice 0.0% iowait 0.4% idle
Mem: 2067912k av, 1756684k used, 311228k free, 0k shrd, 135036k buff
411344k active, 1215308k inactive
Swap: 2096472k av, 724k used, 2095748k free 1220692k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
20785 mysql 9 0 272M 272M 2232 S 99.9 13.4 2:58 1 mysqld
2815 myuser 12 0 1180 1180 880 R 5.7 0.0 0:01 2 top
1 root 9 0 456 428 412 S 0.0 0.0 1:06 1 init
2 root 8 0 0 0 0 SW 0.0 0.0 0:00 0 keventd
3 root 19 19 0 0 0 SWN 0.0 0.0 0:16 0 ksoftirqd_CPU
4 root 19 19 0 0 0 SWN 0.0 0.0 0:00 1 ksoftirqd_CPU
5 root 19 19 0 0 0 SWN 0.0 0.0 0:00 2 ksoftirqd_CPU
6 root 19 19 0 0 0 SWN 0.0 0.0 0:00 3 ksoftirqd_CPU
7 root 9 0 0 0 0 SW 0.0 0.0 1:38 3 kswapd
8 root 9 0 0 0 0 SW 0.0 0.0 0:00 2 bdflush
9 root 9 0 0 0 0 SW 0.0 0.0 2:35 0 kupdated
-
10-18-2004, 08:39 PM #8Web Hosting Master
- Join Date
- Dec 2002
- Location
- The Shadows
- Posts
- 2,925
You have high, high CPU usage for some reason. Could you posts your my.cnf?
Dan Sheppard ~ Freelance whatever
-
10-19-2004, 06:41 AM #9Web Hosting Master
- Join Date
- Jan 2003
- Posts
- 1,715
It looks like both disk and CPU are issues. I would guess you are running some queries that don't use an index, thus performing full table scans.
This will list current requests:
mysqladmin processlist
It may lead you to the culprit. It this big site a PHP Nuke site?Game Servers are the next hot market!
Slim margins, heavy support, fickle customers, and moronic suppliers!
Start your own today!
-
10-19-2004, 04:10 PM #10Junior Guru Wannabe
- Join Date
- Oct 2003
- Posts
- 89
It's made by me and all queries use indices, although when I looked at the processes I noticed a few 'copy to tmp table' statuses. I have query_cache turned on, but maybe there is other caching I can do? Would using PHP accelator help with db load? In 2 days that it has been up I notice that Slow queries is already over 9000.
-
10-19-2004, 04:14 PM #11Junior Guru Wannabe
- Join Date
- Oct 2003
- Posts
- 89
my.cnf
--------
port = 3306
socket = /tmp/mysql.sock
skip-locking
open-files-limit = 8192
max_connections = 375
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 1536
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
datadir=/home/mysql/data
# Replication Master Server (default)
# binary logging is required for replication
log-bin
max_binlog_size = 200M
-
10-19-2004, 07:47 PM #12Web Hosting Master
- Join Date
- Jan 2003
- Posts
- 1,715
Have you used EXPLAIN SELECT... to verify the queries are actually using the indices you've set up? How big are the queries (a SELECT * FROM... to get the row count was always a PHP Nuke favorite)?
Game Servers are the next hot market!
Slim margins, heavy support, fickle customers, and moronic suppliers!
Start your own today!
-
01-31-2005, 05:52 AM #13Junior Guru Wannabe
- Join Date
- Dec 2003
- Posts
- 87
do kernel 2.4 or 2.6 have different performace for barton 3000?
thanks
-
01-31-2005, 06:48 AM #14Web Hosting Master
- Join Date
- Nov 2000
- Location
- localhost
- Posts
- 3,771
Kernel 2.6 performs much better IMHO, I realise the Barton's won't benefit from the 2.6.x hyperthreading improvements, but there are a host of other interal improvements including the schedulers.
MattF - Since the start..
-
01-31-2005, 07:49 AM #15Web Hosting Guru
- Join Date
- Jul 2002
- Location
- Kolding, Denmark
- Posts
- 292
I'd say that your setup and configuration looks OK - are you 100% sure that the slowdown is caused by the database server and not Apache running out of connections? A load of less than 5 on that server shouldn't create any real problems and 9000 slow queries isn't necessarily a sign of real trouble - you'll have to hold that number against the total number of queries during the same period and the time limit for slow queries.
The "copy to tmp table" could indicate that you are doing joins that causes temporary tables to be larger than memory allocation allows - that would (IIRC) cause the temporary table to be written to disk instead of a HEAP-table in memory.
Since you have available memory to spare you should probably consider raising tmp_table_size to see if that could help. Have a look at http://dev.mysql.com/doc/mysql/en/memory-use.html for a pretty good explanation on how MySQL uses memeory.Best regards,
Anders C. Madsen
Golden Planet Support - http://www.goldenplanet.com
-
01-31-2005, 11:34 AM #16Web Hosting Master
- Join Date
- Dec 2002
- Location
- The Shadows
- Posts
- 2,925
The 2.6 kernels(latest) also feature different IO Schedulers which let you tune the way your system does alot of operations.
Very good idea, IMODan Sheppard ~ Freelance whatever