log table with many insertions and exponential growth

MySQL & MariaDB Other MySQL® Questions
1

Hi All,

We have a new table in our system:

CREATE TABLE details (
id int(11) NOT NULL AUTO_INCREMENT,
log_id int(11) NOT NULL,
w_id int(11) NOT NULL,
s varchar(40) DEFAULT NULL,
PRIMARY KEY (id),
KEY logid (log_id)
) ENGINE=MyISAM AUTO_INCREMENT=860 DEFAULT CHARSET=latin1

This is a details table which maps to a log entry (log_id). For each log entry, on average, there will 15 detail records. I expect to have 2,000 to 5,000 log entries per day which means anywhere from 30,000 to 75,000 log detail entries per day. We selected MyISAM as we need it to be super fast for insertions with “concurrent_insert=2”. Selects will be done much less frequently.

I was wondering if the table structure and engine selection look good? or maybe we could do something better?

Thanks

2

Take a look at INSERT DELAYED.

3

I looked at that option and I’m not that happy with it, but will continue to consider it after we’ve had a few days of monitoring and observe behavior and look if there are issues with our current configuration.

4
[B]iberkner wrote on Wed, 03 November 2010 04:11[/B]
Hi I was wondering if the table structure and engine selection look good? or maybe we could do something better?
I can't say anything directly about your table structure since I don't know your application but the choice of engine is good for a "write often/read seldom/never delete" log table.
5

There are going to be reads on the table, but their frequency will be less than the writes. There will also be some deletes, but not a high frequency.