MySQL + LARGE innodb = thrashing HDD

[d***@mfaconsulting.com]

I have the following scenario.

A mysql database running 3 databases. It is version 5.0.27 on Windows
XP Prof.. All innodb databases. The one database is particularly large
(7.8GB of data)...pretty much held in 1 table....there are probabably
30 tables in the rest of the databases....combined they probably take
up 200MB. The machine is pretty well spec'ed AMD X2 4600+, 2GB RAM,
SATA RAID1. Normally the services that use the databases are idle until
our clients come online. Then it gets moderately busy. At the start of
this a single (possibly 2) query will "hang"....it will take 8-10
minutes to complete....it is always a insert or update. During this
time MySQL will write to the error log saying innodb semaphores are
timing out (but that makes sense). After this time the query completes
and the system runs normally.....running the same queries that it got
"stuck" on for a while, but this time taking microseconds to complete.

I've done some profiling using perfmon. During this "hang" the IO
writes byte for the mysql process goes from about <1MB per second to >
12MB per second. I then further ran some dianostics to see where it was
writing data using filemon...and it seems to be writing a ton to
c:\$logfile, which I understand is part of the NTFS transactional
system. I thought it might be the swap file, but I disabled this (set
it to 0MB) and the problem persists.

I have these servers set as masters for replication, so it is using
binary logging.

Can anyone think why this might happen?

Regards

D.

[Michael Austin]

Post by d***@mfaconsulting.com
I have the following scenario.
A mysql database running 3 databases. It is version 5.0.27 on Windows
XP Prof.. All innodb databases. The one database is particularly large
(7.8GB of data)...pretty much held in 1 table....there are probabably
30 tables in the rest of the databases....combined they probably take
up 200MB. The machine is pretty well spec'ed AMD X2 4600+, 2GB RAM,
SATA RAID1. Normally the services that use the databases are idle until
our clients come online. Then it gets moderately busy. At the start of
this a single (possibly 2) query will "hang"....it will take 8-10
minutes to complete....it is always a insert or update. During this
time MySQL will write to the error log saying innodb semaphores are
timing out (but that makes sense). After this time the query completes
and the system runs normally.....running the same queries that it got
"stuck" on for a while, but this time taking microseconds to complete.
I've done some profiling using perfmon. During this "hang" the IO
writes byte for the mysql process goes from about <1MB per second to >
12MB per second. I then further ran some dianostics to see where it was
writing data using filemon...and it seems to be writing a ton to
c:\$logfile, which I understand is part of the NTFS transactional
system. I thought it might be the swap file, but I disabled this (set
it to 0MB) and the problem persists.
I have these servers set as masters for replication, so it is using
binary logging.
Can anyone think why this might happen?
Regards
D.

You said you are running XP Professional on AMDx2 4600+. Is this classed as a
server or desktop. Is it XP Professional Desktop or Server. There is a big
difference in both cost and performance. If you want to us MS crap, us Sql
Server on Windows/<NT/XP/flavor of the month>. If you need a database server to
run MySQL - use Linux as the backend.

But a stab in the dark as to your problem, it sounds like your replication piece
needs to write a whole bunch of logs at the point in time when you start.

And lastly 7.8GB for MySQL or any database is a drop in the ocean - so, in order
to keep from embarassing your self with such comments - "particularly large"
begins at around 500-600GB. Moderately large is ~1-2TB, REALLY large is on the
order of 60-100TB and insanely large is >1PB. :) :) :)

[Jerry Stuckle]

Post by Michael Austin

Post by d***@mfaconsulting.com
I have the following scenario.
A mysql database running 3 databases. It is version 5.0.27 on Windows
XP Prof.. All innodb databases. The one database is particularly large
(7.8GB of data)...pretty much held in 1 table....there are probabably
30 tables in the rest of the databases....combined they probably take
up 200MB. The machine is pretty well spec'ed AMD X2 4600+, 2GB RAM,
SATA RAID1. Normally the services that use the databases are idle until
our clients come online. Then it gets moderately busy. At the start of
this a single (possibly 2) query will "hang"....it will take 8-10
minutes to complete....it is always a insert or update. During this
time MySQL will write to the error log saying innodb semaphores are
timing out (but that makes sense). After this time the query completes
and the system runs normally.....running the same queries that it got
"stuck" on for a while, but this time taking microseconds to complete.
I've done some profiling using perfmon. During this "hang" the IO
writes byte for the mysql process goes from about <1MB per second to >
12MB per second. I then further ran some dianostics to see where it was
writing data using filemon...and it seems to be writing a ton to
c:\$logfile, which I understand is part of the NTFS transactional
system. I thought it might be the swap file, but I disabled this (set
it to 0MB) and the problem persists.
I have these servers set as masters for replication, so it is using
binary logging.
Can anyone think why this might happen?
Regards
D.

You said you are running XP Professional on AMDx2 4600+. Is this classed
as a server or desktop. Is it XP Professional Desktop or Server. There
is a big difference in both cost and performance. If you want to us MS
crap, us Sql Server on Windows/<NT/XP/flavor of the month>. If you need
a database server to run MySQL - use Linux as the backend.

MySQL runs quite well in Windows platforms - although I agree it runs
better on Linux.

Post by Michael Austin
But a stab in the dark as to your problem, it sounds like your
replication piece needs to write a whole bunch of logs at the point in
time when you start.

A good possibility. My recommendation would be to issue a couple of
"dummy" requests when the system starts up as a test. Then watch the
log files to see what changes. Any significant here could account for it.

Post by Michael Austin
And lastly 7.8GB for MySQL or any database is a drop in the ocean - so,
in order to keep from embarassing your self with such comments -
"particularly large" begins at around 500-600GB. Moderately large is
~1-2TB, REALLY large is on the order of 60-100TB and insanely large is

Post by d***@mfaconsulting.com
1PB. :) :) :)

Yep. 8Gb is not a big database.