Database design for heavy timed data logging

Question

I have an application where I receive each data 40.000 rows. I have 5 million rows to handle (500 Mb MySQL 5.0 database).

Actually, those rows are stored in the same table => slow to update, hard to backup, etc.

Which kind of scheme is used in such application to allow long term accessibility to the data without problems with too big tables, easy backup, fast read/write ?

Is postgresql better than mysql for such purpose ?

Answer 1

1 - 40000 rows / day is not that big

2 - Partition your data against the insert date : you can easily delete old data this way.

3 - Don't hesitate to go through a datamart step. (compute often asked metrics in intermediary tables)

FYI, I have used PostgreSQL with tables containing several GB of data without any problem (and without partitioning). INSERT/UPDATE time was constant

Answer 2

We're having log tables of 100-200million rows now, and it is quite painful.

• backup is impossible, requires several days of down time.

• purging old data is becoming too painful - it usually ties down the database for several hours

So far we've only seen these solutions:

• backup , set up a MySQL slave. Backing up the slave doesn't impact the main db. (We havn't done this yet - as the logs we load and transform are from flat files - we back up these files and can regenerate the db in case of failures)

• Purging old data, only painless way we've found is to introduce a new integer column that identifies the current date, and partition the tables(requires mysql 5.1) on that key, per day. Dropping old data is a matter of dropping a partition, which is fast.

If in addition you need to do continuously transactions on these tables(as opposed to just load data every now and then and mostly query that data), you probably need to look into InnoDB and not the default MyISAM tables.