Mysql Causing High Iowait

Apr 12, 2008

Server is Xeon 3060, 3GB Ram, 250GB SATA-II HDD. I followed the recommendations made by mysqlprimer tuner script.

top - 11:54:33 up 237 days, 15:01, 1 user, load average: 6.06, 2.80, 1.65
Tasks: 133 total, 2 running, 131 sleeping, 0 stopped, 0 zombie
Cpu(s): 13.0% us, 4.9% sy, 0.0% ni, 24.3% id, 57.8% wa, 0.0% hi, 0.0% si
Mem: 3114144k total, 3096468k used, 17676k free, 30220k buffers
Swap: 2096440k total, 32348k used, 2064092k free, 2515412k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
8610 nobody 16 0 28 1:37.73 0.7 78908 20m 16m S lsphp4
10520 nobody 16 0 3 0:01.97 0.4 78788 10m 6884 S lsphp4
26474 nobody 15 0 2 15:04.07 0.4 14196 11m 3216 S lshttpd
28275 mysql 22 6 1 131:28.83 7.4 733m 224m 2700 S /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pi

Code:
[mysqld]
max_connections=500
max_user_connections=300
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1
interactive_timeout=60
wait_timeout=60
connect_timeout=6
thread_cache_size=128
key_buffer_size=640M
max_allowed_packet=16M
table_cache=2048
join_buffer=1M
sort_buffer=2M
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
safe-show-database
max_connect_errors=10
thread_concurrency=2
myisam_sort_buffer_size=64M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#log_slow_queries=/var/log/mysql/mysql_slow-queries.log
long_query_time=3
tmp_table_size=32M
max_heap_table_size=32M
#log-queries-not-using-indexes=/var/log/mysql/mysql_queries_no-indexes.log
join_buffer_size=2M
low_priority_updates=1
read_rnd_buffer_size=2M

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

[safe_mysqld]
err-log=/var/log/mysqld.log
var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout
Whenever there are a lot of members online on my forum, the iowait shoot up, >30% and often times hovering at 60%. It'll eventually drop down to normal levels. However, during the high iowait, there are over 2.5GB of free memory according to the command, free -m

is it due to mysql not optimized or other processes? I don't think the bottleneck is on the cpu or ram.

View 12 Replies


ADVERTISEMENT

Iowait Causing High Load

Jul 14, 2009

In my server iowait is causing high load, how do we find out which are all users taking memory that could cause my server to use swap memory.

server : linux centos/Cpanel

I have tried the below commands, but how do we locate user specific,

ps aux --sort -vsz | head -25
vmstat 1

View 7 Replies View Related

High Load From High Iowait

Jul 2, 2008

I am running in a High load problem lately. I have one of those cheap 1and1 servers which was running fine until 2 weeks ago. Once I rebooted accidentaly, it did not come back with some unrepairable kernel errors and I had to re-image it.

I chose to reimage the server with CentOS 5, for better support. The new re-image worked fine for some days, at least so I thought and now I am having high loads. The server crashes if not monitored every moment as the load is unpredictable.

Just a restart of the Apache will bring the server back to normality, but I am not sure if it is apache or some other script to be blamed.
I have beeing monitoring through apache server-status, but I cannot organize something unusual in the high load moments.

At this moment for example:

top - 09:00:36 up 15:43, 3 users, load average: 3.57, 4.60, 5.32
Tasks: 144 total, 2 running, 142 sleeping, 0 stopped, 0 zombie
Cpu(s): 10.6%us, 6.5%sy, 0.0%ni, 18.7%id, 63.5%wa, 0.2%hi, 0.5%si, 0.0%st

12:00:29 AM CPU %user %nice %system %iowait %steal %idle
12:10:01 AM all 9.14 0.00 5.52 44.66 0.00 40.68
12:20:14 AM all 6.83 0.00 3.98 27.88 0.00 61.32
12:30:10 AM all 6.44 0.00 4.20 81.25 0.00 8.11
12:40:09 AM all 5.25 0.00 4.09 81.93 0.00 8.73
12:50:15 AM all 5.11 0.00 3.79 90.74 0.00 0.36
01:00:07 AM all 7.22 0.00 4.52 57.11 0.00 31.15
01:10:13 AM all 6.89 0.00 4.01 55.38 0.00 33.71
01:20:14 AM all 4.37 0.00 3.27 41.88 0.00 50.48
01:30:25 AM all 4.26 0.00 3.29 63.42 0.00 29.03
01:40:06 AM all 27.18 0.00 4.75 58.27 0.00 9.80
01:50:03 AM all 29.64 0.00 6.61 51.50 0.00 12.25
02:00:07 AM all 27.00 0.00 8.48 55.49 0.00 9.03
02:10:10 AM all 19.29 0.00 4.97 73.80 0.00 1.94
02:20:04 AM all 37.85 0.00 6.78 40.70 0.00 14.67
02:30:05 AM all 15.65 0.00 4.80 68.47 0.00 11.08
02:40:08 AM all 9.06 0.00 5.60 37.49 0.00 47.86
02:50:07 AM all 5.36 0.00 3.62 42.29 0.00 48.73
03:00:02 AM all 6.05 0.00 4.08 47.27 0.00 42.60
03:10:02 AM all 4.22 0.00 3.68 38.17 0.00 53.93
03:20:02 AM all 4.06 0.00 3.75 41.37 0.00 50.82
03:30:22 AM all 4.42 0.00 3.93 45.25 0.00 46.41
03:40:11 AM all 4.34 0.00 3.95 39.58 0.00 52.13
03:50:02 AM all 4.67 0.00 4.01 32.53 0.00 58.80
04:00:08 AM all 3.72 0.00 3.87 28.40 0.00 64.02
04:10:02 AM all 13.49 0.00 6.58 20.82 0.00 59.10
04:20:01 AM all 6.70 0.00 4.63 6.06 0.00 82.61
04:30:02 AM all 1.44 0.00 1.21 4.75 0.00 92.59
04:40:01 AM all 12.42 0.00 8.12 7.65 0.00 71.81
04:50:02 AM all 1.43 0.00 1.07 4.02 0.00 93.47
05:00:02 AM all 1.60 0.00 1.40 8.62 0.00 88.38
05:10:10 AM all 3.80 0.00 3.02 17.86 0.00 75.32
05:20:06 AM all 5.10 0.00 4.22 23.34 0.00 67.34
05:30:02 AM all 1.54 0.00 1.40 11.22 0.00 85.85
05:40:05 AM all 1.75 0.00 1.89 13.12 0.00 83.23
05:50:12 AM all 2.15 0.00 2.22 18.92 0.00 76.72
06:00:02 AM all 1.92 0.00 2.01 12.87 0.00 83.20
06:10:02 AM all 2.27 0.00 2.16 11.53 0.00 84.04
06:20:03 AM all 3.56 0.00 3.02 25.26 0.00 68.16
06:30:10 AM all 2.66 0.00 2.05 18.13 0.00 77.16
06:40:02 AM all 2.58 0.00 2.25 22.87 0.00 72.30
06:50:02 AM all 2.68 0.00 1.92 15.77 0.00 79.63
07:00:03 AM all 3.06 0.00 2.48 26.01 0.00 68.46
07:10:03 AM all 3.65 0.00 3.20 36.54 0.00 56.61

07:10:03 AM CPU %user %nice %system %iowait %steal %idle
07:20:03 AM all 4.40 0.00 3.28 43.86 0.00 48.46
07:30:02 AM all 4.10 0.00 3.17 31.30 0.00 61.43
07:40:06 AM all 7.67 0.00 3.95 50.79 0.00 37.59
07:50:02 AM all 4.72 0.00 3.11 44.30 0.00 47.86
08:00:03 AM all 5.57 0.00 3.72 47.15 0.00 43.56
08:10:07 AM all 10.66 0.00 3.59 71.62 0.00 14.13
08:20:17 AM all 5.67 0.00 3.42 58.81 0.00 32.10
08:30:10 AM all 11.12 0.00 3.49 76.71 0.00 8.67
08:40:03 AM all 7.00 0.00 3.36 47.94 0.00 41.71
Average: all 7.53 0.00 3.76 38.90 0.00 49.81
Some configurations:
The reimage partittioning looks like this:

/dev/sda1 950M 138M 765M 16% /
/dev/sda5 4.7G 1.9G 2.9G 40% /usr
/dev/sda7 63G 58G 5.0G 93% /var
/dev/sda6 4.7G 23M 4.7G 1% /home
none 1.0G 500K 1.0G 1% /tmp

KeepAlive On
MaxKeepAliveRequests 300
KeepAliveTimeout 15

<IfModule prefork.c>
StartServers 10
MinSpareServers 20
MaxSpareServers 50
ServerLimit 256
MaxClients 180
MaxRequestsPerChild 2000
</IfModule>

<IfModule worker.c>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

skip-locking
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=250
interactive_timeout=100
wait_timeout=20
connect_timeout=10
thread_cache_size=80
key_buffer=64M
join_buffer=1M
max_allowed_packet=16M
table_cache=512
record_buffer=1M
sort_buffer_size=1M
read_buffer_size=1M
max_connect_errors=10
thread_concurrency=2
read_rnd_buffer_size=1M
myisam_sort_buffer_size=64M
server-id=1

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 3
model name : Intel(R) Pentium(R) 4 CPU 2.80GHz
stepping : 4
cpu MHz : 2793.324
cache size : 1024 KB

View 10 Replies View Related

High Iowait By Named

Oct 27, 2008

Recently I've noticed named causing high iowait.

If I stop named, iowait reduces to around 40%.

Code:
10:05:55 up 1:59, 2 users, load average: 5.09, 6.73, 6.23
192 processes: 185 sleeping, 7 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 4.3% 0.0% 5.0% 0.3% 0.0% 89.2% 0.9%
cpu00 4.8% 0.0% 4.8% 0.0% 0.1% 89.3% 0.8%
cpu01 3.9% 0.0% 5.2% 0.7% 0.0% 89.0% 1.0%
Mem: 1023624k av, 966688k used, 56936k free, 0k shrd, 119500k buff
658244k actv, 130464k in_d, 16084k in_c
Swap: 1052248k av, 72096k used, 980152k free 496268k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
15 root 15 0 0 0 0 SW 0.8 0.0 2:23 1 kjournald
9182 named 15 0 3504 3504 2184 S 0.6 0.3 0:01 0 named
8301 apache 15 0 14564 11M 6832 S 0.5 1.1 0:00 0 httpd
1043 qmails 24 0 1196 1064 384 R 0.4 0.1 1:10 0 qmail-send
1047 qmailr 25 0 372 364 280 S 0.3 0.0 0:35 1 qmail-rspawn
30825 root 15 0 1140 1088 744 R 0.2 0.1 0:09 1 top
7025 apache 15 0 13536 10M 6820 S 0.2 1.0 0:00 0 httpd
7076 apache 15 0 13868 11M 6820 S 0.2 1.1 0:00 1 httpd
759 root 15 0 568 548 488 R 0.1 0.0 1:30 1 syslogd

I believe this isn't normal.

View 2 Replies View Related

High Iowait : Write Access Request

Nov 22, 2008

diagnosing my server which currently experiencing high iowait for weeks, I'm still figuring the root cause.

total used free shared buffers cached
Mem: 4051 3653 398 0 341 2847
-/+ buffers/cache: 464 3587
Swap: 8191 0 8191
The server spec as follow :
HP DL 380 G5
4G RAM
2.6.18-53.el5PAE

the load is around 1 or 2 , but iowait have been on the high side even the server currently is under utilized

I'm running LVM and hardware RAID 1 on the server.

View 5 Replies View Related

Which Command To See What Is Causing High Server Load.

May 18, 2007

Which command do we type to see what is causing a high server load?

I've restarted the VPS numerous of times, and I wish to found out the problem

View 7 Replies View Related

Suspecting Server Is Causing High Bandwidth Usage

Jun 21, 2009

For the past few days, one of the server is causing a bandwidth utilization surge for the entire rack on almost daily basis. It happen for a few minutes and it went off then it will be the same thing again the next day. When the surge is happening, most of the servers on the same segment will be inaccessible for the few minutes.

The bandwidth utilization graph for my rack is recording an abnormal surge from (6mbps --> 90mbps) for the few minutes. My MRTG is showing 2 of the Plesk servers giving the problem but the NOC guys said it the Cpanel that causing the problem.

I tried logging in to both servers but could not find what's the cause for this.

View 2 Replies View Related

Any Software That Detects Exactly What Is Causing High CPU Load On Your Server

Jul 17, 2008

It's been a while since I've posted in this area of the forum, but was just wondering if by now someone or some company has developed a script/software that you can install on your server that will tell you the exact area where the high CPU load is coming from, such as from someone sending email, a certain users account, and etc?

Most times, you have to be mointoring your server at the time of the high load to be exactly sure of what is causing the high load, so, I'm talking about something that will email you right on the spot of the high load in case you are away and will know what caused it.

Most I've seen only tell you that you have a high load, but don't tell you what exactly caused it.

View 2 Replies View Related

How To Find What Perl Script Is Causing High CPU Usage

Nov 13, 2008

How to find what Perl script is causing high CPU usage?

I logged into my CentOS box tonight and notice after viewing the output of the "top" command that there are the following lines of output that have me concerned. What command can I use to find the source of the Perl script that is causing the CPU spike?

It seems it's been running for some time too.
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
30120 apache 25 0 6908 4544 1216 R 99.8 0.2 13416:07 perl
30654 apache 25 0 8028 4508 1212 R 99.4 0.2 13417:22 perl
21273 apache 25 0 7584 4528 1200 R 98.1 0.2 2225:12 perl
20516 apache 25 0 6760 4540 1232 R 96.8 0.2 2221:30 perl

View 8 Replies View Related

Bastille Firewall Causing Mysql Connection Errors

Jan 27, 2008

The main function of my dedicated server is the vBulletin forum that we run.

Ever since I had bastille firewall installed and configured on my server we have been getting regular vBulletin errors.

Everything runs pretty smoothly most of the time, we can carry lots of connections without any issues, server has plenty of free RAM and CPU is never overloaded even during peak hours. I have been told bastille firewall (iptables) is configured correctly, all ports that need to be open are open.

But about twice per day we have a MySQL "disconnect" that lasts about 30-60 seconds per incident. For example we will start getting the following message via e-mail, and it will amount to 20-30 e-mails like this during the 30-60 second incident. After that, the e-mails and problems will go away, until next time.

vBulletin Database Error!
mysql_connect(): Can't connect to MySQL server on 'sql.datacolony.com' (4)
/hsphere/local/home/site/site.com/forum/includes/class_core.php on line 274
MySQL Error :
Error Number :
Date : Saturday, January 26th 2008 @ 07:53:05 PM
Script : [url]
Referrer : [url]
IP Address : 92.3.190.54
Username :
Classname : vb_database

The server is running CentOS 4.6, PHP 4.4.7, MySQL 5.0.45. The vBulletin software has been patched to the latest version. The vBulletin people are not sure why this could be happening.

Well we never believed that the firewall was causing the issue BUT it only started happening on the day the new firewall was installed. So we tried stopping the firewall for a full week and the MySQL database errors stopped happening, completely. So I'm convinced the problem is the firewall blocking MySQL connections but I can't figure out why?

There are no entries in the MySQL .err logs pertaining to this. Can't figure this out, any help or ideas is appreciated since the people who installed the firewall are not sure either.

View 3 Replies View Related

MySQL ERROR, Hosting Or My Script Causing The Troubles

Jun 5, 2008

I hosted one aardvark topsite @ PluginSpace.com which consist of high traffics (3500+ unique visitors/day). I'm facing max_users_connection for quite number of times and I also have complain to my hosting but I still get no luck, they fix my problem for a short period and the problem appear again after some time.

I rent myself a dedicated server with 320GB Space and 3400GB Bandwith and 2GB RAM. Everytime I manage to get a lot of traffics to my topsite, maybe 100+ online users at a time, tracked by freeonlineusers[dot]com. I'll got these errors. Can anyone tell me what can I do? Can anyone tell me more about how much visitors can a normal dedicated server handle? Is it my script problem or my server problem?

(ERROR WILL POST IN NEXT POST)

can the dedicated server stand many visitors or suppose a normal dedicated server can handle so many visitors or my hosting maintenance is poor?

View 13 Replies View Related

Server High Load/high Mysql Usage

Jul 2, 2009

I have a site that is eating up my server resources and need to know what the best solution for this is. I'm thinking of getting another server just for mysql but do not know what specs the server should be to handle the current traffic/database load and have the site run smoothly without slowing down to a snail's pace.

An alternative is to get another server just for the videos being served and leave the database and html on the current server. This is where I'm stuck and don't know what route to take with this.

My current server stats:

Dual Xeon 5130
4GB RAM
250GB
50 Mbit/sec
CentOS 3.9

Website traffic stats:

15,000 visitors/day
150,000 pageviews/day
Serving videos

I've attached screenshots of top and bandwidth usage per day. Hopefully with this information you could tell me if I need another server or if there are any things I can do to the current server to help things move faster.

View 13 Replies View Related

Iowait

Feb 22, 2007

Code:

19:28:57 up 22 min, 2 users, load average: 2.99, 3.48, 3.52
105 processes: 103 sleeping, 1 running, 0 zombie, 1 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 0.0% 0.0% 0.4% 0.0% 0.2% 99.2% 0.0%
cpu00 0.0% 0.0% 0.4% 0.0% 0.4% 99.0% 0.0%
cpu01 0.0% 0.0% 0.4% 0.0% 0.0% 99.5% 0.0%
Mem: 2047220k av, 1020368k used, 1026852k free, 0k shrd, 88516k buff
515212k active, 316448k inactive
Swap: 2040244k av, 0k used, 2040244k free 672648k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
7775 root 15 0 1344 1344 916 S 0.2 0.0 0:02 0 top
14220 root 15 0 1216 1216 920 R 0.2 0.0 0:00 1 top -c

19768 root 25 0 3764 3764 2216 S 0.4 0.1 0:00 0 /usr/local/cpanel/bin/dcpumon
1 root 15 0 544 544 472 S 0.0 0.0 0:03 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 15 0 0 0 0 SW 0.0 0.0 0:00 1 keventd
5 root 34 19 0 0 0 SWN 0.0 0.0 0:00 0 ksoftirqd/0
6 root 34 19 0 0 0 SWN 0.0 0.0 0:00 1 ksoftirqd/1
9 root 25 0 0 0 0 SW 0.0 0.0 0:00 1 bdflush
7 root 15 0 0 0 0 SW 0.0 0.0 0:00 1 kswapd
Was sorted by processor usage.

Code:
root@dual [/var/log]# uname -a
Linux 2.4.21-47.0.1.ELsmp #1 SMP Thu Oct 19 11:33:45 EDT 2006 i686 i686 i386 GNU/Linux

Code:
Processor Information

Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Pentium(R) 4 CPU 3.80GHz
Processor #1 speed: 3790.694 MHz
Processor #1 cache size: 2048 KB

Processor #2 Vendor: GenuineIntel
Processor #2 Name: Intel(R) Pentium(R) 4 CPU 3.80GHz
Processor #2 speed: 3790.694 MHz
Processor #2 cache size: 2048 KB

View 2 Replies View Related

MYSQL High Load!

Aug 23, 2007

I am just a beginner and it is about a month that the mysql loads on my server is so high , I have checked the mysql processlist via cpanel and there is no account that takes high mysql and make it high , and even I have checked for any possible rotten databases. but still the server load is so high and I can not control it , even when I restart it just after a minute it goes up again.

just the spamd command takes a high CPU usage sometimes. what it should be from? the spamd for a special account!

I need to optimize mysql , and need help. please do not tell me to hire an expert , just help me thanks

and another question is that how can I check which account is sending spam and stop it?

View 14 Replies View Related

High Load From Mysql

Mar 11, 2008

I have a dual xeon server that host around 15 small website, but these days I see that the load generated from mysql is very high as you can see below is consumes 32.6 CPU

mysql 32.60 1.20 0.0

how I can know what is the cause of this load?

View 11 Replies View Related

High MySQL Load

Feb 15, 2007

MySQL.

Quote:

top - 18:43:24 up 18 days, 18:05, 1 user, load average: 145.13, 92.77, 40.84
Tasks: 145 total, 10 running, 134 sleeping, 0 stopped, 1 zombie
Cpu(s): 28.7% us, 70.6% sy, 0.0% ni, 0.0% id, 0.7% wa, 0.1% hi, 0.0% si
Mem: 1034060k total, 605968k used, 428092k free, 21400k buffers
Swap: 1020088k total, 165076k used, 855012k free, 119448k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6102 mysql 16 0 343m 146m 2868 S 364 14.5 1441:52 mysqld

My server almost went dead... until I rebooted MySQL.

View 7 Replies View Related

MySql Die With High Load

Oct 17, 2007

MySql die with high load problem?

My server running: Server Load124.40 (2 cpus)[url] Memory Used70 %[url]
Server info:

Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Xeon(R) CPU 3060 @ 2.40GHz
Processor #1 speed: 2394.840 MHz
Processor #1 cache size: 4096 KB

Processor #2 Vendor: GenuineIntel
Processor #2 Name: Intel(R) Xeon(R) CPU 3060 @ 2.40GHz
Processor #2 speed: 2394.840 MHz
Processor #2 cache size: 4096 KB

Memory Information Memory: 4148800k/4718592k available (1883k kernel code, 43336k reserved, 761k data, 188k init, 3275648k highmem)

View 9 Replies View Related

MySQL High CPU Usage Put On RAM

Jan 30, 2007

I need some help my CPU (Intel Core 2 Duo 1.7 Ghz) always gets high usage from mysql sometimes its using 95% cpu etc... How can I modify my.cnf to use less CPU and more ram since I have 3gb ram installed?

here is my.cnf

Code:
[mysqld]
back_log = 5
skip-innodb
max_connections = 400
key_buffer = 190M
myisam_sort_buffer_size = 156M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 5M
table_cache = 1024
record_buffer = 1024
thread_concurrency = 2
long_query_time = 1
thread_cache = 1024
thread_cache_size = 992025
wait_timeout = 13
connect_timeout = 5
tmp_table_size = 64M
max_heap_table_size = 16M
max_allowed_packet = 16M
max_connect_errors = 500
read_rnd_buffer_size = 1024
bulk_insert_buffer_size = 32M
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
low_priority_updates=1
default-storage-engine = MyISAM

[mysqld_safe]
nice = -2
open_files_limit = 4096

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 32M
write_buffer = 32M

View 1 Replies View Related

High Loads Due To MYSQL

May 11, 2007

Recently, we've been getting a lot of high loads (every 30 mins or so..) This is what shows in the warning email that we receive:

---------------------------------
12.0 04:31:35 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/swh.sellwebhost.com.pid --skip-external-locking
----------------------------------

We are using whm.. Any idea what might be causing this?

View 12 Replies View Related

Bottlenecks (iowait Or CPU)

Oct 24, 2007

I have a server with two cPanel accounts, each running gallery2. Load has been high:

[url]
[url]
[url]

Sar output (done at a less busy time):

[url]

Hardware:
1 Intel 2.4 GHz 1066FSB - Conroe Xeon 3060 (Dual Core)
2 Generic 1024 MB DDR2 667 ECC
1 Maxtor 250GB SATA 7200RPM MaxLine Plus II
1 Unknown Onboard SATA
1 Dell Single socket 1067FSB - Quad Core Capable PowerEdge 840

Howmuch %iowait is too much?

Since it's using less than 1GB Swap, there's enough RAM?

How do I get more specific than httpd as the cause of the load. The gallery script is the only thing running so is the execution time of PHP the bottleneck? If PHP and apache are single threaded would a quad core reduce load? Does lighttpd run PHP faster?

View 5 Replies View Related

MySQL Server Uage Very High

Jun 29, 2009

Whenever I run the "top" command, I see that some of the user "mysql" records always have very high CPU usage, anywhere from 20-80% on average (I've seen 100%). There are multiple records that show this, which means that the CPU is always above 100%. My cacti monitoring account also shows messed up CPU statistics.

I am not too knowledgeable with servers, but I believe that this is a problem. How much CPU usage is a mysqld command supposed to use on average? Here is a copy of the top part of a sample top command:

=========

top - 06:54:50 up 92 days, 5:32, 1 user, load average: 3.82, 3.89, 4.24
Tasks: 181 total, 4 running, 176 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.2%us, 18.6%sy, 80.9%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Mem: 2075600k total, 1905400k used, 170200k free, 331912k buffers
Swap: 4096564k total, 104k used, 4096460k free, 1187304k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21663 mysql 24 6 63032 30m 3004 R 66 1.5 38:24.06 mysqld
28483 mysql 24 6 63032 30m 3004 R 66 1.5 35:52.86 mysqld
20538 mysql 22 6 63032 30m 3004 S 15 1.5 40:29.74 mysqld
20837 mysql 24 6 63032 30m 3004 R 15 1.5 13:44.15 mysqld
1209 mysql 22 6 63032 30m 3004 S 11 1.5 32:46.26 mysqld
20591 mysql 22 6 63032 30m 3004 S 11 1.5 40:02.76 mysqld
20592 mysql 23 6 63032 30m 3004 S 6 1.5 40:48.29 mysqld
15301 apache 19 4 29928 15m 3608 S 3 0.8 0:00.60 httpd
15317 apache 19 4 28380 14m 3640 S 3 0.7 0:00.58 httpd
15249 apache 19 4 28612 14m 3732 S 1 0.7 0:01.49 httpd
15349 apache 19 4 28876 14m 3324 S 1 0.7 0:00.10 httpd
15319 apache 19 4 28368 14m 3528 S 1 0.7 0:00.34 httpd
15334 apache 19 4 28612 14m 3404 S 1 0.7 0:00.35 httpd
12343 root 15 0 2324 1088 804 R 1 0.1 0:03.31 top
15336 apache 19 4 28876 14m 3500 S 0 0.7 0:00.63 httpd
1 root 15 0 2064 588 508 S 0 0.0 1:01.28 init
2 root RT -5 0 0 0 S 0 0.0 0:09.39 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:01.25 ksoftirqd/0
4 root RT -5 0 0 0 S 0 0.0 0:00.00 watchdog/0
=================================

I told my system manager about the cpu problem, and he said that he installed the cpulimit function on my server. I'm not sure if this is a good solution.

View 2 Replies View Related

Mysql Usage: High CPU %, How Do I Find Out Who's Using What

Aug 3, 2008

just noticed mysql cpu usage has shot up from its usual 5-10% cpu to 30%+. domains are as normal and no extraordinary php processes.

any way i can tell what users are most heavily running mysql? (as in number queries / slow queries etc?) i know mysqladmin processlist but that only gives me a snapshot, whereas i want to see over 24 hours, who used mysql the most...

View 6 Replies View Related

High Mysql Load After Upgrade

Mar 14, 2008

it was very fast until mysql upgraded to 5.0.45 it was 4.. i can’t even turn my forum if it is a busy time cos it is so slow i get page not found after a while but when it is quiet it is not too bad... but it was alot more faster with mysql 4 i don't really want to downgrade please give me some ideas to fix this issue ...........

View 3 Replies View Related

MySQL High Server Load

Dec 18, 2008

MySQL the last few days seems to be constantly the most demanding process in top, which it never was before. As far as I can tell, nothing has substantially changed with regards to traffic to MySQL driven sites on the server. Is there anything that might be wrong with the databases, etc., that might throw MySQL into a tizzy?

View 8 Replies View Related

How To Resolve High MySQL Usage

Apr 20, 2008

on my server i have 104 accounts and the MySQL Usage for th cpus is 25.23
like this pic [url] is that danger? and how can i resolve it?

View 6 Replies View Related

HIGH LOAD Because Of MySQL. [merged]

Oct 24, 2007

my server

Intel(R) Xeon(R) CPU E5310
1g

when i use a software update my site.maybe the sofe use mysql and httpd much.

the load of the server ofen goes high.i can only restart httped or mysql .

some info about the server when load is high.

[root@server ~]# free
total used free shared buffers cached
Mem: 1033676 1018980 14696 0 4352 428756
-/+ buffers/cache: 585872 447804
Swap: 2040244 132660 1907584

[root@server ~]# ps aux | grep mysql | wc -l
3
[root@server ~]# ps aux | grep httpd | wc -l
66

[root@server ~]# mysqladmin -u root -p extended-status
Enter password:
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Aborted_clients | 2 |
| Aborted_connects | 89 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 61741575 |
| Bytes_sent | 11783074 |
| Com_admin_commands | 150 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 33678 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 3327 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 6939 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 96 |
| Com_optimize | 1 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 110 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 122075 |
| Com_set_option | 32609 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |

View 13 Replies View Related

High Load And Mysql Errors

Jun 17, 2007

My server very slow and the is very loaded. How can I fix this problem? My server specialities and loaded softwares which are below. And sometimes there is "mysql connect failed", "Lost connection to MySQL server during query" errors. Thanks for kinds...

---
Speed:
100Mbs

LOAD:
Load avarage : 5-30 +
Cpu usage: %30-95 +
Ram usage: %70-95 +

SYSTEM INFO:
2gb ram, P4 3200 cpu, 2x250 GB SATAII hdd, Plesk 8.1.1, centos 4.5 os, apache 2.0.59, php 4.4.7, mysql 4.1.21(client),
mysql 5.0.41(server), zend optimizer, kernel 2.6.19.1.SM #5 SMP, mod_evasive, plesk firewall, qscanq+clamav,
spamassain and system up-to-date.

SOFTWARE:
phpbb 2.0.22
4images 1.7.4
joomla 1.0.12

ONLINE USER:
100-500 user online
on 2 website

How to optimization?

I installed tuning-primer.sh

but I can’t understand this code:
I don’t know. How to set values, example?
NOTE: Red value

-------------------------
[root@lavinya bin]# tuning-primer.sh

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.41-log i686

Uptime = 3 days 4 hrs 34 min 17 sec
Avg. qps = 92
Total Questions = 25406284
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
.............

SLOW QUERIES
Current long_query_time = 5 sec.
You have 115 out of 25406302 that take longer than 5 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 2
Historic max_used_connections = 110
The number of used connections is 22% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 835 M
Configured Max Per-thread Buffers : 3 G
Configured Max Global Buffers : 46 M
Configured Max Memory Limit : 3 G
Total System Memory : 3.96 G

Max memory limit exceeds 85% of total system memory

KEY BUFFER
Current MyISAM index space = 35 M
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 73658
Key buffer fill ratio = 34.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 4 M
Current query_cache_used = 1 M
Current query_cach_limit = 1 M
Current Query cache fill ratio = 29.83 %
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 1 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 14127 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 2500 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 256 tables
You have a total of 636 tables
You have 256 open tables.
Current table_cache hit rate is 1% , while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 2271787 temp tables, 3% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 28 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio =1 : 112
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrentcy of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

[root@lavinya bin]#
----------------------

if your reply me message I will be happy

View 1 Replies View Related

Raid Card To Help With Iowait

Dec 17, 2007

I was wondering if anyone has had luck with 3ware 9650's in a RAID 10 to help bring down iowait?

I currently have a VPS node that see's spikes in iowait and i'd like to bring it down. I've been considering getting a 9650 in a JBOD setup (so then I don't need to reinstall the whole node) with a battery backup to enable the cache.

My two questions are:

- In JBOD, does the write cache enable at all?
- Would the write cache help bring down the iowait much?

Drive wise we have 4 x 500GB drives in a raid10 (soft for now).

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved