# 2 minutes to kill server
**URL:** https://forums.percona.com/t/2-minutes-to-kill-server/302
**Category:** Other MySQL® Questions
**Created:** [April 25, 2007, 4:23pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302 "2007-04-25T16:23:05Z")
**Posts on this page:** 18
**Page:** 1
### Author:  [@Thorton](https://forums.percona.com/u/Thorton)
#### Post date: [April 25, 2007, 4:23pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/1 "2007-04-25T16:23:05Z")
Hello,
I have extreme problems with mysql on 2xdual Opteron + 2GB RAM server which is more than enough to handle many requests, I believe. I don’t know if my queries are not optimized (but I think they are ok). When I start mysql, it takes 2 minutes to kill server because and sites become not accessible. While Apache is processing about 100-200 requests at the same time, there are hundreds of mysql proccesses for unknown reasons. I’m attaching screenshot how everything looks. Screenshot also displays what queries are used, may be queries are wrong?
Any help is more than welcome, because all my sites are down about 22-23 hours per day due to heavy sql load and I can’t do anything (
---
### Author:  [@sterin](https://forums.percona.com/u/sterin)
#### Post date: [April 26, 2007, 8:25am UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/2 "2007-04-26T08:25:22Z")
How big is your DB in Mb?
What are your my.cnf settings?
Can you also run a:
SHOW GLOBAL STATUS;
and post the output here.
As it looks like is that MySQL is spending a lot of time trying to open tables.
This means that something is either locking the tables or that you have a very low setting of table\_cache compared to the amount of connections that you have.
---
### Author:  [@Thorton](https://forums.percona.com/u/Thorton)
#### Post date: [April 26, 2007, 1:31pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/3 "2007-04-26T13:31:52Z")
Current my.cnf:
[mysqld]
skip-locking
skip-innodb
query\_cache\_limit=1M
query\_cache\_size=4M
query\_cache\_type=1
max\_connections=900
wait\_timeout=10
interactive\_timeout=90
connect\_timeout=7
thread\_cache\_size=100
key\_buffer\_size=24M
join\_buffer=1M
max\_allowed\_packet=16M
table\_cache=768
sort\_buffer\_size=1M
read\_buffer\_size=1M
read\_rnd\_buffer\_size=768K
max\_connect\_errors=10
thread\_concurrency=8
myisam\_sort\_buffer\_size=64M
tmp\_table\_size=768M
low\_priority\_updates=1
#log-bin
server-id=1
log-slow-queries
long\_query\_time = 1
max\_user\_connections=20
One database is about 100-200 MB of size (about 20.000-25.000 rows per database). This is output of SHOW GLOBAL STATUS;
mysql\> SHOW GLOBAL STATUS;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘STATUS’ at line 1
If I run SHOW GLOBAL STATUS (without ; at the end), nothing is displayed then.
P.S. 200 apache requests and 500 active mysql proccesses (with SELECT commands) at the moment (
---
### Author:  [@sterin](https://forums.percona.com/u/sterin)
#### Post date: [April 26, 2007, 6:05pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/4 "2007-04-26T18:05:39Z")
Sorry, previous MySQL version 5 the status was always global so you should just write this:
SHOW STATUS;
Do you have any queries in the slow query log?
If you do, try to find one that seems to take a long time and occurs often and run an EXPLAIN on tha query.
Also post the output from:
SHOW CREATE TABLE keywords;
Because a lot of the queries in your processlist is accessing keywords.
---
### Author:  [@Thorton](https://forums.percona.com/u/Thorton)
#### Post date: [April 27, 2007, 12:36am UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/5 "2007-04-27T00:36:58Z")
Hi, this is output from show status:
| Aborted\_clients | 124 |
| Aborted\_connects | 8256 |
| Binlog\_cache\_disk\_use | 0 |
| Binlog\_cache\_use | 0 |
| Bytes\_received | 86361549 |
| Bytes\_sent | 4207237726 |
| Com\_admin\_commands | 9 |
| Com\_alter\_db | 0 |
| Com\_alter\_table | 6 |
| Com\_analyze | 0 |
| Com\_backup\_table | 0 |
| Com\_begin | 0 |
| Com\_change\_db | 70376 |
| 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 | 37 |
| Com\_dealloc\_sql | 0 |
| Com\_delete | 5321 |
| Com\_delete\_multi | 0 |
| Com\_do | 0 |
| Com\_drop\_db | 13 |
| Com\_drop\_function | 0 |
| Com\_drop\_index | 0 |
| Com\_drop\_table | 1 |
| Com\_drop\_user | 0 |
| Com\_execute\_sql | 0 |
| Com\_flush | 657 |
| Com\_grant | 6 |
| Com\_ha\_close | 0 |
| Com\_ha\_open | 0 |
| Com\_ha\_read | 0 |
| Com\_help | 0 |
| Com\_insert | 66914 |
| Com\_insert\_select | 8 |
| Com\_kill | 0 |
| Com\_load | 0 |
| Com\_load\_master\_data | 0 |
| Com\_load\_master\_table | 0 |
| Com\_lock\_tables | 1096 |
| Com\_optimize | 0 |
| 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 | 164 |
| 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 | 210045 |
| Com\_set\_option | 7051 |
| 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 |
| Com\_show\_create\_table | 2863 |
| Com\_show\_databases | 339 |
| Com\_show\_errors | 0 |
| Com\_show\_fields | 3186 |
| Com\_show\_grants | 605 |
| Com\_show\_innodb\_status | 0 |
| Com\_show\_keys | 0 |
| Com\_show\_logs | 0 |
| Com\_show\_master\_status | 0 |
| Com\_show\_ndb\_status | 0 |
| Com\_show\_new\_master | 0 |
| Com\_show\_open\_tables | 0 |
| Com\_show\_privileges | 0 |
| Com\_show\_processlist | 95 |
| Com\_show\_slave\_hosts | 0 |
| Com\_show\_slave\_status | 0 |
| Com\_show\_status | 1 |
| Com\_show\_storage\_engines | 0 |
| Com\_show\_tables | 5194 |
| Com\_show\_variables | 332 |
| Com\_show\_warnings | 0 |
| Com\_slave\_start | 0 |
| Com\_slave\_stop | 0 |
| Com\_stmt\_close | 0 |
| Com\_stmt\_execute | 0 |
| Com\_stmt\_prepare | 0 |
| Com\_stmt\_reset | 0 |
| Com\_stmt\_send\_long\_data | 0 |
| Com\_truncate | 14 |
| Com\_unlock\_tables | 1206 |
| Com\_update | 104639 |
| Com\_update\_multi | 471 |
| Connections | 51398 |
| Created\_tmp\_disk\_tables | 408 |
| Created\_tmp\_files | 776 |
| Created\_tmp\_tables | 3098 |
| Delayed\_errors | 0 |
| Delayed\_insert\_threads | 0 |
| Delayed\_writes | 0 |
| Flush\_commands | 1 |
| Handler\_commit | 0 |
| Handler\_delete | 7219 |
| Handler\_discover | 0 |
| Handler\_read\_first | 47937 |
| Handler\_read\_key | 8668209 |
| Handler\_read\_next | 142388566 |
| Handler\_read\_prev | 46443293 |
| Handler\_read\_rnd | 139251 |
| Handler\_read\_rnd\_next | 299901178 |
| Handler\_rollback | 0 |
| Handler\_update | 5115859 |
| Handler\_write | 103648 |
| Key\_blocks\_not\_flushed | 0 |
| Key\_blocks\_unused | 0 |
| Key\_blocks\_used | 21806 |
| Key\_read\_requests | 17744600 |
| Key\_reads | 89178 |
| Key\_write\_requests | 179800 |
| Key\_writes | 117077 |
| Max\_used\_connections | 105 |
| Not\_flushed\_delayed\_rows | 0 |
| Open\_files | 1446 |
| Open\_streams | 0 |
| Open\_tables | 768 |
| Opened\_tables | 6424 |
| Qcache\_free\_blocks | 141 |
| Qcache\_free\_memory | 595552 |
| Qcache\_hits | 157206 |
| Qcache\_inserts | 198905 |
| Qcache\_lowmem\_prunes | 126811 |
| Qcache\_not\_cached | 6667 |
| Qcache\_queries\_in\_cache | 423 |
| Qcache\_total\_blocks | 1426 |
| Questions | 683011 |
| Rpl\_status | NULL |
| Select\_full\_join | 194 |
| Select\_full\_range\_join | 0 |
| Select\_range | 9985 |
| Select\_range\_check | 0 |
| Select\_scan | 90984 |
| Slave\_open\_temp\_tables | 0 |
| Slave\_retried\_transactions | 0 |
| Slave\_running | OFF |
| Slow\_launch\_threads | 0 |
| Slow\_queries | 3003 |
| Sort\_merge\_passes | 388 |
| Sort\_range | 18907 |
| Sort\_rows | 345227 |
| Sort\_scan | 6553 |
| Table\_locks\_immediate | 410541 |
| Table\_locks\_waited | 135 |
| Threads\_cached | 47 |
| Threads\_connected | 58 |
| Threads\_created | 105 |
| Threads\_running | 1 |
| Uptime | 28298 |
Output from SHOW CREATE TABLE keywords;
keywords | CREATE TABLE `keywords` (
`id` int(11) NOT NULL auto\_increment,
`keyword` varchar(255) NOT NULL default ‘’,
`yahoo_body` text NOT NULL,
`markov` text NOT NULL,
`whois` tinyint(4) NOT NULL default ‘0’,
`meta_keywords` varchar(255) NOT NULL default ‘’,
`meta_description` varchar(255) NOT NULL default ‘’,
`meta_title` varchar(255) NOT NULL default ‘’,
`featured` tinyint(4) NOT NULL default ‘0’,
`featured_body` text NOT NULL,
`published` tinyint(4) NOT NULL default ‘0’,
`tb_direct` tinyint(4) NOT NULL default ‘0’,
`tb_direct_google` tinyint(4) NOT NULL default ‘0’,
`coppermine` tinyint(4) NOT NULL default ‘0’,
`social` tinyint(4) NOT NULL default ‘0’,
PRIMARY KEY (`id`),
KEY `keyword` (`keyword`)
) ENGINE=MyISAM AUTO\_INCREMENT=22894 DEFAULT CHARSET=latin1
Thanks for any suggestions.
---
### Author:  [@sterin](https://forums.percona.com/u/sterin)
#### Post date: [April 27, 2007, 2:59am UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/6 "2007-04-27T02:59:01Z")
Looking at you table you basically don’t have any indexes.
Only two of the columns id (primary key) and keyword is indexed.
But if you look at the queries in your processlist a lot of them are of the kind:
SELECT … FROM keywords WHERE social = 0 LIMIT 1;… FROM keywords WHERE published = 1 ORDER BY id DESC LIMIT 5;
And:
SELECT … FROM trackback\_direct WHERE posted = 0;
So here are some suggestions:
1. Create some indexes that target the queries above:
ALTER TABLE keywords ADD INDEX kw\_ix\_published\_id (published, id);ALTER TABLE keywords ADD INDEX kw\_ix\_social (social);ALTER TABLE trackback\_direct ADD INDEX tb\_ix\_posted(posted);
1.
Increase sort\_buffer\_size 1MB is very small (it’s actually smaller than the default of 2MB which is a bit odd since you seem to have a pretty hefty server). Set it to about 5MB instead.
Try increasing the query cache variables:
query\_cache\_size=40MB
query\_cache\_limit=2MB
Try those to begin with and we shall see what happens.
---
### Author:  [@Thorton](https://forums.percona.com/u/Thorton)
#### Post date: [April 27, 2007, 3:27am UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/7 "2007-04-27T03:27:45Z")
Thanks, I tweaked my.cnf now and will try to add indexes to these columns.
Actually, I had index on “published” column previously, but after running EXPLAIN, I decided to remove index. Just can’t remember what was wrong with it )
---
### Author:  [@Thorton](https://forums.percona.com/u/Thorton)
#### Post date: [April 30, 2007, 3:01pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/8 "2007-04-30T15:01:29Z")
Ok, I just added index to published column, and attached image with EXPLAIN output (1st query is without index, and 2nd query is with published index added). As you may see, 2nd query displays “Using where; Using filesort” and I read somewhere that “Using filesort” indicates slower query?
---
### Author:  [@sterin](https://forums.percona.com/u/sterin)
#### Post date: [April 30, 2007, 3:54pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/9 "2007-04-30T15:54:32Z")
You didn’t run the ALTER TABLE statement that I gave you. Shame on you! 😉
If you look at it I am creating a combined index with the columns (published, id).
And it is only this combined index that makes it possible to both retrieve the appropriate records and in the right order without a filesort.
Your first explain does not have a filesort on it because mysql choose to make an index scan, which means that it goes thru all rows in the primary index trying to find matching rows.
In your second explain it finds the matching rows by using the index but then has to sort them to deliver them in the right order.
You must not stare yourself blind on the last part of the explain. It is all of it that is interresting.
For instance if you have a query where you only have 4 rows left before the sorting it will still be faster than having to perform a table scan.
For example in your case your first explain reports that mysql has to examin 14804 rows while in you second explain it only has 1243 rows left after using the index.
So you see the index does make a difference.
But to speed it up even more, drop the index “published” that you created and create my combined index instead.
---
### Author:  [@Thorton](https://forums.percona.com/u/Thorton)
#### Post date: [April 30, 2007, 4:01pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/10 "2007-04-30T16:01:08Z")
Oh, I really forgot about your queries. Shame on me, you are absolutely right about this 😉
Going to run your queries on all databases, will let you know how it’s going then
---
### Author:  [@Thorton](https://forums.percona.com/u/Thorton)
#### Post date: [May 1, 2007, 5:52am UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/11 "2007-05-01T05:52:44Z")
Added all these indexes on all websites hosted on server, but still no luck… Tons of same queries in active mysql proccesses list. I have no more ideas what causes it )
---
### Author:  [@sterin](https://forums.percona.com/u/sterin)
#### Post date: [May 1, 2007, 4:40pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/12 "2007-05-01T16:40:58Z")
Some more opinions:
1.
Some general important questions:
What is actually your server doing during this high load?
Is it high CPU load or disk load?
How much of the RAM memory is used?
What OS are you running on?
Is the server a strict mysql DB server or are you running Apache or any other software on the same server?
Are the DB files located on a locally connected harddrive?
What kind of disk is it?
1.
I think some of your my.cnf options are very odd. And unless you know why they are set as they are then I have some suggestions:
Start by increasing this value:
key\_buffer\_size=256M
Your setting of 24M seems _very_ low.
Decrease this:
tmp\_table\_size=5M
Your 768M looks rediculously big and that memory can be used much better.
Then you have this setting:
max\_connections=900
that looks awfully large compared to this:
table\_cache=768
Since mysql is multithreaded and each thread that wants to read data from a MyISAM table needs a separate file handler the table\_cache should be nr of connections times tables part of the query. But the strange part in that is that your status variables didn’t indicate that you had many opened tables. Which contradict this.
---
### Author:  [@Thorton](https://forums.percona.com/u/Thorton)
#### Post date: [May 2, 2007, 4:11pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/13 "2007-05-02T16:11:13Z")
All my settings are configured according to mysql tuning primer ( [URL=“http://[MySQL](http://forge.mysql.com/projects/view.php?id=44)”][MySQL](http://forge.mysql.com/projects/view.php?id=44%5B/URL%5D) ) - launched it multiple times and software recommended these values to be used on server.
It’s high CPU load. Memory usage is normal, about 20-40% of memory is used total. I’m running CentOS 4.4, and it’s standard webserver hosting many different sites (it’s not dedicated mysql server). Server has SATA disk drives and server just starts making tons of mysql proccesses every X minutes. But the traffic is the same all the time, so it looks strange for me - same number of visitors, but sometims load goes high, and sometimes it doesn’t.
I’ll tweak my.cnf according to your recommendations now.
UPDATE: Server is still crazy. I was looking at server for about 1 hour and it was ok. Many apache requests, load was normal. Suddenly server started making hundreds of mysql proccesses (like you saw in my previous attachment) and was overloaded, while number of apache requests is the same.
---
### Author:  [@linuxrunner](https://forums.percona.com/u/linuxrunner)
#### Post date: [May 3, 2007, 7:01pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/14 "2007-05-03T19:01:23Z")
Turn on your slow query log and find the problem ones.
---
### Author:  [@Thorton](https://forums.percona.com/u/Thorton)
#### Post date: [May 4, 2007, 2:43pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/15 "2007-05-04T14:43:24Z")
Tried already without success. Log file displays all these queries (displayed in 1st screenshot). Of course, it happens not all the time, only every X minutes (when load is skyrocketed) all queries are marked as slow.
---
### Author:  [@sterin](https://forums.percona.com/u/sterin)
#### Post date: [May 4, 2007, 2:56pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/16 "2007-05-04T14:56:37Z")
How regular is the “only every X minutes”?
Do you have any cron jobs or does the application has any re-indexing jobs that it runs regularily?
And when you get it the next time, run the SHOW PROCESSLIST command and read thru _all_ the rows. Or better yet post them her also in an attachment. And please make it a text file since it is a much easier format to filter.
Because you have something that is taking a lot of CPU and it isn’t the amount of processes since most of them was waiting for opening a table, I think that it is just a secondary effect since they can’t open the table they want to and then the mysql threads are delayed and that is when they start to pile up.
---
### Author:  [@Thorton](https://forums.percona.com/u/Thorton)
#### Post date: [May 4, 2007, 3:13pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/17 "2007-05-04T15:13:56Z")
Yes, there are some cronjobs, but they just insert some rows (up to 100 rows per run, usually about 20-50 rows) to database and exit. I’ll check these cronjobs, but they are really simple - a few lines of code to insert several rows of data.
---
### Author:  [@linuxrunner](https://forums.percona.com/u/linuxrunner)
#### Post date: [May 4, 2007, 5:36pm UTC](https://forums.percona.com/t/2-minutes-to-kill-server/302/18 "2007-05-04T17:36:01Z")
This sounds like an IO bottleneck, are you running off of SAN disks?.. nm just saw you posted its SATA drives…