Going for a LARGE Table: Any Tips? - MySQL

[Good Man]

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 tion 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.

[Giuseppe Maxia]

Good Man wrote:

> 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 tion 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.
>

Have a look at the ARCHIVE engine. It may be useful to you.
[url]http://dev.mysql.com/tech-resources/articles/storage-engine.html[/url]

As for the advice you're asking.
The dump can be long. If your data is really huge, you may think about
copying the data directory somewhere (after stopping the server), and then
storing it away.

Think about tion only if :
- you need to balance you access load;
or
- you need to perform a file-copy backup and can't stop the master for any reason.
In this case you can just stop the slave, carry out the backup, and restart it,
without affecting the master.

But don't think that tion is a replacement for a backup. If you make a mistake
in the master, the slave is automatically affected.

About backup, have a look at this recent presentation:
[url]http://lenz.homelinux.org/mysql/[/url]

About data storage, there is some unusual ideas here:
[url]http://datacharmer.blogspot.com/2005/11/data-on-demand-for-data-warehouses.html[/url]

ciao
gmax

--
_ _ _ _
(_|| | |(_|><
_|
[url]http://gmax.oltrelinux.com[/url]