Posted by Good Man on 11/11/05 18:36

Hi there

I'm developing a large web application. Part of this web application will
be storing numerical chart data in a MySQL table - these numbers will be
already calculated, and are just being stored for reference.

In this particular table, the stored data will never be deleted or
changed. The only actions performed will be SELECTs and INSERTs. There
will never be any DELETEs or UPDATEs.

The end-result charts themselves have many rows. Let's say about 40-100
rows per chart. I will be storing thousands of charts. I am definitely
looking at the possibility of there being millions (upon millions?) of
rows at some time in the future. I am very aware of relational database
concepts, and one large table truly makes the most sense in this
particular situation. Believe me, there are foreign keys up the wahoo.

A general question to the experts out there (or just people with
experience ;))... what kind of things should I 'look out for', or
prepare for, when designing/using a table that is so large? My previous
MySQL tables in all my years of programming have never really had more
than 40,000 rows. This table will of course be the largest table in a
database that holds many more tables (about 20 or 30).

Here are things I am concerned/wondering about. Any advice on them would
be very much appreciated.

1) Table type: all the tables in the database that depend on data being
inserted/updated to other tables are all InnoDB using transactions. This
table, as mentioned, will strictly be INSERTed to and SELECTed from.
Should I use MyISAM or InnoDB? Just about all columns will be storing
numbers, possibly a varchar column or two, and no blobs/text columns.
However, these columns will need to be 'searched' sometimes.

2) Backing up / Restoring: What should I look out for in terms of
dumping/restoring the table? Anything other than the traditional
mysqldump?

3) If I do go with an InnoDB table... I imagine I will probably have to
change that 'ibdata' setting, or at least get familiar/involved with it,
since this table will be so large. I've seen the term 'multiple
tablespaces' in reference to InnoDB tables. Is this something I should
be investigating for a table of this size? What exactly does it mean?

4) Do I need to think about replication of any sort? Or slave/master
things? Or can I just use the database as is? There would never be more
than 50 or so people using the database at once (if that matters).

The system is a P4 (2.8), running Linux RedHat, MySQL 4.1.7 (though I'm
pretty sure I will have it upgraded to MySQL 5 before putting this into
production or even building it - or do I even need to), 1 GB of RAM....
basically our own box being managed/hosted somewhere across the
continent.

I know this is a lot of advice to ask. I'm self-taught, and have been
re-reading some MySQL books in preperation for this project, which will
be my 'largest' to date, and will be quite complex overall (this is a
small but still critical part of the overall project). The books teach
well, but they don't offer to much advice to specific situations ;)

Thanks.

[Back to original message]