1

I have a system where users are submitting work to my server frequently. With 1500 active at one time, it's about 6000 units of work being submitted per minute. I've been using one large table to summarize the work, and then when a solution gets run, the database clears out old work to keep the size manageable.

My goal is to split the table into two sections: A work counter, and a 15 minute log.

The work counter table would run an UPDATE with each work submission (primary key is worker_id, solution_id), incrementing the third column by 1 each time work is submitted. All the update commands will use the worker_id and solution_id as the WHERE clause, so it will always be hitting a primary key pair.

The log table would be storing 3 columns: worker_id (INT), submit_time (timestamp), valid (ENUM ('Y','N')). Every minute, the table would have a query which removes information older than 15 minutes.

My current plan is using InnoDB for the counter table, and a MEMORY table for the log. Would these be the correct choices of engines for the purpose of each table? The timestamp log is not critical, so if the table got lost due to a restart, it would not be a problem.

1 Answer 1

0

You are right in using InnoDB for the counter table, since it is going to be heavily written to so it would need the row-locking mechanism of InnoDB.,

And since you have no issues with the data being lost on server restarts., then memory engine is a good choice,. but you do have to consider how big your memory table would become,. according to the MySQL manual:

The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. To have larger (or smaller) MEMORY tables, you must change the value of this variable. The value in effect for CREATE TABLE is the value used for the life of the table. (If you use ALTER TABLE or TRUNCATE TABLE, the value in effect at that time becomes the new maximum size for the table. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.) You can set the size for individual tables as described later in this section.

You must log in to answer this question.