mysql update brought major performance problems

LidoShuffle · 03-25-2008, 02:14 AM

I just updated mysql to 5.0.44 and now the moderate amount of traffic the server gets is bringing it to its knees. Sometimes when I run "top" the mysqld process it using over 50% of the cpu. What's going on? It never used to be like that before, for years and years. I think the server was usually around 90% idle all the time regardless of traffic.

trickykid · 03-25-2008, 08:09 AM

Well a change to mysql either discovered a bug in your code using it or vice versa. Or something is seriously wrong with MySQL in where you have to do some actual tweaking. Any other information you can provide?

What version were you using before? Did you actually test this before upgrading? Always test, it's beneficial and won't impact your production data. What can you tell when you show processes within MySQL? What type of application is using it or connecting to it, PHP, etc?

More details please. How you asked is like a person taking their car to a mechanic. "Well, yesterday it was running fine and I had no problems since I've had it for over 4 years now! What's the problem mechanic? It just won't start!"

LidoShuffle · 03-25-2008, 12:17 PM

Thanks. Yes, it's two php sites. We've been using 5 for a while without issue, but recently (about two weeks ago) we updated to 5.0.44 and that sort of correlates to when the problems started. Here's top:

Code:

top - 09:45:40 up 3 days, 10:49,  2 users,  load average: 0.65, 0.59, 0.47
Tasks:  79 total,   2 running,  77 sleeping,   0 stopped,   0 zombie
Cpu(s): 31.4%us,  4.7%sy,  0.0%ni, 61.5%id,  0.3%wa,  0.7%hi,  1.3%si,  0.0%st
Mem:    967624k total,   950516k used,    17108k free,   173340k buffers
Swap:  2007992k total,      188k used,  2007804k free,   585732k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                             
23048 mysql     15   0  149m  41m 4632 S 26.6  4.4 109:30.48 mysqld                                              
  959 root      10  -5     0    0    0 S  3.3  0.0  58:31.74 md3_raid1                                           
27423 apache    15   0 23264  10m 3332 S  1.7  1.1   0:01.08 apache2                                             
    5 root      10  -5     0    0    0 S  0.7  0.0   0:40.79 events/0                                            
  956 root      10  -5     0    0    0 S  0.7  0.0  29:55.62 md4_raid1                                           
28171 revhq     15   0  2244 1104  848 R  0.3  0.1   0:00.05 top                                                 
    1 root      15   0  1592  492  432 S  0.0  0.1   0:09.40 init

You'll notice the mysqld line and its time. I don't know if that's normal. There's nothing crazy showing in processes in mysql:

Code:

mysql> show processlist;
+-------+------+-----------+------+---------+------+-------+------------------+
| Id    | User | Host      | db   | Command | Time | State | Info             |
+-------+------+-----------+------+---------+------+-------+------------------+
| 39195 | root | localhost | NULL | Query   |    0 | NULL  | show processlist | 
+-------+------+-----------+------+---------+------+-------+------------------+

LidoShuffle · 03-27-2008, 12:26 PM

I think it might be something to do with the memory or some other config with mysql. Anyone?

Code:

=====================================
080327  9:50:55 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 7 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2, signal count 2
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 4, OS waits 2; RW-excl spins 0, OS waits 0
------------
TRANSACTIONS
------------
Trx id counter 0 1792
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 5082, OS thread id 2950278032
MySQL thread id 35330, query id 969462 localhost root
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
25 OS file reads, 3 OS file writes, 3 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 69257, used cells 0, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 43665
Log flushed up to   0 43665
Last checkpoint at  0 43665
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 34467384; in additional pool allocated 1388544
Buffer pool size   1024
Free buffers       989
Database pages     35
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 35, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 5082, id 2989108112, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT

forrestt · 03-27-2008, 12:43 PM

When you upgraded, did you dump and restore your databases? If so, did you make sure any indexes were also restored?

Forrest

LidoShuffle · 03-27-2008, 03:05 PM

Yes, the db's were dumped and restored. I thought the indexes were built automatically. I did some looking and couldn't find any info on restoring the indexes. The other odd thing I've noticed is that with older versions of mysql (e.g. 5.0.26) there were several mysqld processes running at all times, now there's only ever one mysqld process. Is that ok?