Help for very bad perf for MySQL

[cpghost]

On Mon, 26 Nov 2007 13:03:19 +0100
Albert Shih <Albert.Shih at obspm.fr> wrote:

> > > I've a bi-proc single core Xeon 3.2ghz with FreeBSD 6.2, I'm 
> > > running Mysql 5.X on this server and the performance of MySQL 
> > > is very bad. For some complexe select I've got ~6secondes (on 
> > > some basic Linux it's take 0.6 sec). And I think this is 
> > > nothing about thead (that's mean I don't think FreeBSD 7.0 
> > > can solve my problem) because it's just for one select.
>
> I don't really known it's some scientifical data. But the problem is
> on a basic linux pc (with SATA disk) the time is 0.6 sec with same
> request and same data. And it's for web applications. At 6 sec for
> one request it's become very long for the visitor because the
> application make many requests.

It may also be a simple database administration issue:

If selects are taking so long, I'd strongly suspect that an
INDEX table is either missing or damaged. Are you 100% sure
that the database schema is *identical* on the Linux and
FreeBSD machines? Perhaps dropping and rebuilding the index
tables could speed things up?

You could also try to listen to the disks while that slow
select is performed: if the disks are thrashing, AND the
swap activity is not really higher than else (vmstat -s,
or top), it's a dead giveaway that mysqld is doing more
disk i/o than necessary, i.e. check the index tables. If
on the contrary the disks are quiet while the select runs,
check if mysqld is accumulating CPU time (with top): if it
is NOT, I'd guess it is some issue with the threading library,
i.e. some threads are deadlocked and waiting.

-cpghost.

-- 
Cordula's Web. http://www.cordula.ws/