In the last episode (May 17), Brad Teale said:
> We are warehousing real-time data. The data is received at up to T1 speeds,
> and is broken up and stored into the database in approximately 25 different
> tables. Currently MySQL is doing terrific, we are using MyISAM tables and
> are storing 24 hours worth of data but we don't have any users and we need
> to store 72 hours worth of data.
> Our concern is that when we start letting our users (up to 200 simultaneous)
> hit the database, we won't be able to keep up with ingesting and serving
> data with the MyISAM locking scheme.
You probably don't want to be doing your archiving and reporting on the
same database. Set up replication, and do your queries on the slave
while the master gets the updates.
Moving to InnoDB tables might help with table contention, since it's
got row-level locking. If there are only updates and few changes, then
even moving to MyISAM will get you a big win, since appending a row
doesn't lock the table.
> We have tested Oracle and PostgreSQL which fell behind on the ingest.
> The current production system uses regular ISAM files, but we need to
> make a certification which requires a relational database. Also, the
> current production system doesn't have the feature list the new
> system has.
> Is there a better database solution or do you think MySQL can handle
> it? If MySQL can handle it, would we be better off using InnoDB or
> MyISAM tables?
ISAM is deprecated and only included for compatibility with
old files. You should switch to MyISAM at minimum.