First, I think you may need to migrate off of OSX for your server if you are
under heavy load. I love OSX and have setup many
servers with it, but not for system with high contention. Many reviews show
that MySQL under OSX doesn't scale, most likely because
of the lack of fine grained locking in the kernel. We'll see what Jobs reveals
tomorrow.
InnoDB has always done well under heavy load, up until the data gets to a
certain size. Usually when the size of the data (i.e.
indexes) exceeds RAM. MyISAM scales better for size, InnoDB scales better for
load/contention. If you find your SELECTs are causing
the table to lock and slowing down INSERTs, UPDATEs and DELETEs, then you
should be considering InnoDB. Or if you need transaction
control. Otherwise I would stick with MyISAM.
Also, Innodb tables never get smaller, so if you are deleting records, keep that in mind. Your table only grows. In my experience,
once InnoDB hits a certain size, performance drops. As I mentioned, the size is typically dependent on amount of memory.
I'm not sure what your data patterns are like. Perhaps you don't do deletes or you don't need real time access to data, meaning a
few seconds delay would be fine. I've typically used merge tables (only available for MyISAM based tables) for large datasets.
Typically because one of the requirements was to periodically "archive" data out of the current data set.
I've done hybrid where there was a "feeder" table that was InnoDB and the main data was in MyISAM. Real time queries would need to
do a UNION to get the latest information.
You can also play around with replication, where insert, updates and deletes occur on one system and queries point to the
replication system. Any interesting table type for something like this is the BLACKHOLE table type. The replication system then
handles creating a kind of queue.
----- Original Message -----
From: "Michael Dykman" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, January 07, 2007 12:14 PM
Subject: high load, tons of data
I am leading a project which has us (among other things) migrating
around 4T of data from Mysql 4.0.18 to 5.x.. we are currently
targeting 5.0.27 which seems to be holding up fairly well, although we
still having some issues in a couple of places.
Having put our migration suite through it's many paces (5 distinct
target hosts, multiple databases on each) we have found a few issues
which we have generally been able to address by refactoring our code,
reducing the load of any given query or upgrading to the latest
publicly available patch.
It has been suggested by our systems staff that InnoDB table type is
inherently unstable under MySql 5.x.and MyIsam presents the only
stable route.
Who else out there is dealing with very large amounts of data under
high load and InnoDB who would care to share some heart-warming
stories about how well InnoDB is working out under those
circumstances? Horror stories are also invited... please embellish
with version numbers and any interesting platform notes you might
have.
Our particular albatross is that we are trying to do this on a
combination of of PPC and Intel Macs under OSX 10.4.4
Any input, comments or sympathy cards accepted.
--
- michael dykman
- [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
