We started seeing performance issues in our master database after its
host server was accidentally rebooted. Although the OS was rebooted
gracefully, we have a concern that mysql was not able to stop in time
and was killed by the OS.

The machine came back and we started mysql. Performance began to
suffer after some time. One table was marked as crashed, but it stores
temporary data so we dropped it and re-created. There are otherwise no
errors in any logs to indicate a problem.

A yum update on the machine broke SSHD. We have been meaning to
upgrade to MySQL 5, but this forced us to do it immediately. The
database was migrated to a new machine (source of the data was one of
the slaves) and the problem persists. We have used profiling and
innotop to attempt to determine the problem.

When the database is restarted, performance is excellent for about 30
minutes until the issues reappear.

Basic inserts and updates are very fast, but selects and any other
queries involving a join or read operation are very slow. Queries on
our slaves are fast.

The master has twin quad core processors, 32 GB RAM. The MySQL
partition is on a four-disk RAID 5 array. The old server had a
four-disk RAID 10 array. Controller is set to use write-back with no
readahead.

Can anyone offer some suggestions? These performance issues are
choking our operations.

ampersand wrote:

Other possible is that disk caching or RAM is limited somewhere.

On Fri, 24 Sep 2010 16:03:38 +0100, The Natural Philosopher wrote:

No, we have not lost indices.

Can you be more specific about disk caching? According to the OS, RAM is
completely utilized with about 4 GB going to caching.

On 9/24/2010 10:36 AM, ampersand wrote:
Well, first of all, using equivalent hardware, RAID 5 will generally
have worse performance than RAID 10 (of course, like everything else,
there are exceptions). And how is the physical disk speed vs. the old
disks?

The first thing to look at would be your configuration on the new system
vs. the old one, especially buffer sizes.

Finally, since this occurred after a crash, I would consider a bad index
somewhere. Maybe you have one (or more) which has bad data (but MySQL
thinks it's OK), causing MySQL to use a less efficient access. Not very
likely, but possible. I would suggest you EXPLAIN some of the slow
queries on the main system (while it's running slowly), then do the same
on one of the working slaves. If the databases and queries are
identical, you should get the same results. If you don't, look into why
it failed. Alternatively, you could drop all the indexes and recreate
them, but I wouldn't - especially on a large database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

ampersand wrote:
Well its normally automatic, so unless something has changed markedly,
or its a question of refilling an SQL query cache or something, I would
be surprised if in the absence of howling errors, it was a problem.

I would start by running various queries using the command line tool and
timing them.

On Fri, 24 Sep 2010 12:11:29 -0400, ampersand wrote:
System cacheing or mysql caching? And was that the case before when the
thing was running well?

(IMHO, mysql works much better using its own caches than it does useing
the OS's caching of disk, and if you can make mysql demand enough RAM to
cache whole busy tables in memory, you'll get far better performance
than having mysql constrained and the leftover memory in the system
going to caching the same tables.)

--
77. If I have a fit of temporary insanity and decide to give the hero
the chance to reject a job as my trusted lieutentant, I will retain
enough sanity to wait until my current trusted lieutenant is out of
earshot before making the offer. --Anspach's Evil Overlord List

On Sep 24, 3:51*pm, The Natural Philosopher <t... (AT) invalid (DOT) invalid>
wrote:
I work with ampersand and very simple queries takes 25-30 to return
data. For example table x has 700 rows in it ( a very small table with
5 columns in it), select * from x takes 25 seconds to return data. The
same query on one of the slaves take less than 1 second to return
data. One thing I noticed is that if I use a limit I can get the
results back faster when the limit is smaller.

select * from x limit 1 - .3 sec
select * from x limit 5 - 1 sec
select * from x limit 20 - 5 sec
select * from x limit 200 - 10 sec

Jim wrote:
weird. Do the disks check out OK? No disk errors?
does top show a lot of CPU or RAM usage when you run a query?

I'd expect that to be a 10ms query, not 10 seconds

On 24-09-10 23:22, Jim wrote:
are there any differences in the output of:
show variables like '%cache%';
when comparing master or slaves(s) ?
and what is the output of above variables ?

i suspect one of the cache parameters is set wrong....
but its only a guess.... ;-)

--
Luuk

ampersand <user (AT) domain (DOT) invalid> wrote:
That last thing you can check in the MySQL error log. If there is a
"shutdown complete" message then no problem. A crashed table however
indicates some problem with unclean shutdown. But: if a MyISAM table
was damaged by the shutdown, then there will be messages in the error
log. And you can configure MySQL to automatically repair such tables.
InnoDB will recover automatically with no permanent issues.

For the overall problem of "worse performance after reboot" - there
are two points:

- cache warming; caches inside and outside MySQL will be cold after
reboot and need to warm up. This should however be finished after
at most 30 minutes of normal load.

- unsaved config changes; maybe the OS kernel was booted with some
special config, or some dynamic MySQL variables had been changed
on the fly without setting them in my.cnf too.


XL