LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices



Reply
 
Search this Thread
Old 03-25-2008, 03:14 AM   #1
LidoShuffle
LQ Newbie
 
Registered: May 2007
Location: LA
Distribution: Gentoo, OpenSolaris and FreeBSD
Posts: 19

Rep: Reputation: 0
mysql update brought major performance problems


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.

Last edited by LidoShuffle; 03-25-2008 at 03:16 AM.
 
Old 03-25-2008, 09:09 AM   #2
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 199Reputation: 199
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!"
 
Old 03-25-2008, 01:17 PM   #3
LidoShuffle
LQ Newbie
 
Registered: May 2007
Location: LA
Distribution: Gentoo, OpenSolaris and FreeBSD
Posts: 19

Original Poster
Rep: Reputation: 0
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 | 
+-------+------+-----------+------+---------+------+-------+------------------+

Last edited by LidoShuffle; 03-25-2008 at 02:49 PM.
 
Old 03-27-2008, 01:26 PM   #4
LidoShuffle
LQ Newbie
 
Registered: May 2007
Location: LA
Distribution: Gentoo, OpenSolaris and FreeBSD
Posts: 19

Original Poster
Rep: Reputation: 0
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
 
Old 03-27-2008, 01:43 PM   #5
forrestt
Senior Member
 
Registered: Mar 2004
Location: Cary, NC, USA
Distribution: Fedora, Kubuntu, RedHat, CentOS, SuSe
Posts: 1,288

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

Forrest
 
Old 03-27-2008, 04:05 PM   #6
LidoShuffle
LQ Newbie
 
Registered: May 2007
Location: LA
Distribution: Gentoo, OpenSolaris and FreeBSD
Posts: 19

Original Poster
Rep: Reputation: 0
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?

Last edited by LidoShuffle; 03-29-2008 at 02:15 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Tried to update MySQL, but then encountered problems unmet dependencies, and others. Spreegem Debian 4 01-10-2007 01:09 PM
LXer: Serbian Linux distro achieves major update LXer Syndicated Linux News 0 12-14-2006 02:54 AM
Major screw up after update. shame Suse/Novell 2 06-02-2006 05:41 AM
LXer: Arm aims major tools update at Linux developers LXer Syndicated Linux News 0 04-16-2006 12:33 AM
Major update in etch (kde 3.5) acanton Debian 24 02-21-2006 03:26 AM


All times are GMT -5. The time now is 05:30 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration