Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : Mysql reigns CPU! :)

[Angelo]

One of my boxes is being pushed hardly by MySQL (or i think it is mysql) Checking the process list, i can see only a 15-20 queries doing normally. But the load goes on crazy. Averagely 10, and sometimes 35-40. It's also using from swap..

I have tried alternatively optimizing for both mysql and httpd from my logic and also from their official sites, but that did not work out. So i guess something is not going well, maybe a corruption or attack (can not last for that long? netstat seems ok)

Do you have something coming to your mind that i can not think, just share with me, my brain stopped working )

Box is celeron 1.0 ghz, 512 mb RAM.


From "top"

---
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
1829 mysql 9 0 18036 17M 1992 S 78.4 3.5 1:00 0 mysqld
1036 root 10 0 12716 12M 12476 S 18.7 2.4 1:43 0 httpd
---


From my.cnf

---
key_buffer = 256M
wait_timeout = 9
max_connections = 79
connect_timeout = 15
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 4M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_type=1
query_cache_limit=1M
query_cache_size= 1M
thread_concurrency = 8
---


From httpd.conf

---
Timeout 300
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 8
MaxClients 150
MaxRequestsPerChild 100
---

[Haze]

1. Change your thread_concurrency = 8 down to 2
2. Get more RAM.

I'd also suggest a more powerfull CPU.

[Angelo]

Thanks for the reply Haze.
Yeah i also thought that upgrading the system, just curious if its some error with the software because this server only contains one site. Yeah it is big enough but i was thinking that 1.0 ghz with 512 mb ram can hold one big site.

[EXOWorks]

Well, If you can afford some performance decrease ( speed wise ) then you should change the following values in my.cnf

key_buffer = 16M
table_cache = 128
sort_buffer_size = 512k
read_buffer_size = 512k

Can you post your mysql Status ?? and the output from "top" command.

[Angelo]

Seems to be better ha, at least load is not 10 ) Slower though.

TOP:

16:11:13 up 4:41, 1 user, load average: 2.71, 3.62, 6.31
172 processes: 163 sleeping, 9 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 31.7% 0.0% 17.4% 0.0% 0.0% 0.0% 50.8%
Mem: 511528k av, 405484k used, 106044k free, 0k shrd, 7088k buff
91176k active, 283592k inactive
Swap: 1052216k av, 18308k used, 1033908k free 190868k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
17601 mysql 9 0 13788 13M 2004 S 66.8 2.6 1:39 0 mysqld
4806 root 9 0 4060 1196 1152 S 20.4 0.2 1:31 0 httpd


MySQL Status:

Uptime: 272 Threads: 6 Questions: 8740 Slow queries: 0 Opens: 31 Flush tables: 1 Open tables: 25 Queries per second avg: 32.132

[hiryuu]

That's quite a few queries for a box that low, but you should check your queries to make sure they're properly indexed.

Post a 'mysqladmin extended-status'. It should give a decent idea of how often you're doing table scans.

[Steven]

You are kind of killing the reason of query cache in your config.

[Angelo]

It was like this before the query_cache limits are different, even do not exist. I see that this box can not hold this site maybe The lowest load i can see is averagly 4.00 after all my efforts on optimization )

[wheimeng]

Celeron is pretty bad in handling MySQL, get a P4