•  

Announcement

Announcement Module
Collapse
No announcement yet.

log table with many insertions and exponential growth

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • log table with many insertions and exponential growth

    #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
    Tags: None

  • #2
    Take a look at INSERT DELAYED.

    Comment


    • #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.

      Comment


      • #4
        iberkner wrote on Wed, 03 November 2010 04:11

        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.

        Comment


        • #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.

          Comment


          • #6
            iberkner wrote on Thu, 04 November 2010 03:15

            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.

            A low frequency of reads are not a problem.
            But if you once in a blue moon delete a lot of rows (like some maintenance scheme) then you should also optimize the table afterward.
            MyISAM has a insert optimization feature that if the table is optimized it knows that there are no "holes" in the table and subsequently it doesn't have to lock the entire table at the period of writing and instead just writes the new data at the end of the table. Can speed up things a lot of you still are running selects against the table, the downside is of course that an optimization of the table can take a long time.

            In fact so long time that you could possibly consider thinking up some scheme where you instead of deleting rows right away wait with the delete until you have a very large chunk to delete and then optimize the table afterwards. But that is if you need the extra performance, if you don't need it then it complicates things more than it benefits and should be left out.

            Comment

            Working...
            X