Solving table locking issues
Hello,
I see a lot of discussion about this so it seems like a big topic. The most common answer seems to be to create more tables to get around the issue of table locking, but that doesn't seem like a good solution for me. Among other things, I was always taught that duplicating data across tables isn't a good thing.
I've got an application that has about 80,000 users taking training online, so each time someone launches a course it will create or update a record for that course and user. All training records are kept in one table which has around 300k records currently. In terms of usage, over a day the system did about 1.7m SELECTs and around 5k each INSERTs and UPDATEs.
The ref page about table locking includes this:
Table locking is also disadvantageous under the following scenario:
A client issues a SELECT that takes a long time to run.
Another client then issues an UPDATE on the same table. This client waits until the SELECT is finished.
This is my exact situation. An administrator might run a summary report that takes 3 minutes to complete, so during that time new training records are not able to be created (and might even time out, never getting saved).
In the list of tips on the same ref page, none of them seem to offer a good solution (I'm not sure about SQL_BUFFER_RESULT usage though). The summary tables don't sound like a good solution because of the sheer number of summary tables needed and the work to keep them all synced up, that sounds like I would actually be putting a greater load on the system. The training record table contains 31 fields and the summary report joins that table with 2 other tables, and uses a few subqueries as well. It seems like adding summary tables would only compound the problem, I can't see a way where we could run the summary report without scanning the records table. The report might be limited by date range or other options (certain users, certain pieces of training, etc) where I can't see a good schema that would allow the reports to run much quicker. For example, a report might be a report to see which users completed a certain piece of training during a certain period of time, or which users have never launched a certain piece of training, or maybe a group of courses. The only solution I could see there would be to just create a table for each report, and have a job copy data to that table periodically. This would make the report creation process a much bigger job than it currently is though, and we would still need to lock the tables periodically while the summary tables are updated.
Would it make sense to use InnoDB or BDB instead of MyISAM? It sounds like MyISAM is optimized for large numbers of selects and comparitively few inserts and updates, which sounds exactly like what I've got, but the fact that it locks the entire records table when it only needs read access is a little bit frustrating. This application was ported from a SQL Server database that didn't seem to have the table locking issue when running reports, so it took me by surprise. The first time I saw it happen live I thought the MySQL server had crashed and ended up killing the process running the big query. It doesn't sound like there's a way to run a SELECT statement on a MyISAM table that does not lock the table, and the only priority for a SELECT makes it higher priority instead of lower.
I'm looking into the Enterprise Monitor and some of the other tools available to help analyze what's going on, and maybe help optimize some queries, but what are my other options? Would anyone recommend switching the storage engine? Are other large applications doing this type of thing really using summary tables for everything?
Thanks.