Mysql – InnoDB MySQL table with a trigger that inserts into a MyISAM table

innodblockingmyisamMySQLtrigger

Background

I have a Java web application that is used by 300 – 500 users at any given time. On a separate Linux server we have a 5.7.19 MySQL Community Database Server that handles all the transactions for this application. We have a very active InnoDB table called "Requests". This table is involved in many queries. 70% are select statements, 20% are updates, 10% are inserts.

On any CRUD operation against Requests, there is a trigger that does an insert into a different table called Audit. That audit table is MyISAM. We have having these very weird performance issues once or twice a day that are caused by Lock Wait Timeouts (at least that is what I see in the log file for Apache Tomcat). So I wrote a shell script that dumps all the processes in MySQL whenever I see that entry in the Tomcat log file. What I see is that there is a full table lock Requests.

Question

Can a InnoDB table with a trigger that does a insert into a MyISAM table cause a table level lock on the InnoDB table?

Best Answer

Inserting into a MyISAM table may take a table lock on that table. Since that INSERT is coming from a Trigger on a InnoDB table, it is inside a transaction. So, if the table lock takes a long time, the transaction takes a long time, hence "lock wait timeout".

The exception on the table lock is if there have been no updates or deletes from the MyISAM table and the PK is AUTO_INCREMENT. (Or something like that. Dig in the docs for the specifics.)

Is it possible to add to audit outside the InnoDB transaction?

You say "full table lock requests" -- which table? What is the specific text?

Another thing that can mess up inserting a MyISAM table -- a big SELECT. Perhaps you do a big 'report' twice a day?

Rethink whether to use MyISAM for audit.

REASON #1

You only have 150MiB of MyISAM tables (70MiB Data, 80 MiB Indexes)

REASON #2

InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.

REASON #3

InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:

With innodb_file_per_table disabled, the system tablespace file ibdata1 would steadily grow with 0% chance of reclaiming of disk space. You would have to convert InnoDB storage engine to use innodb_file_per_table.
With innodb_file_per_table enabled, you could go to the individual InnoDB and run of the the following to physically shrink the InnoDB table mydb.mytable:
- OPTIMIZE TABLE mydb.mytable;
- ALTER TABLE mydb.mytable ENGINE=InnoDB;

All this can be avoid with MyISAM

REASON #4

InnoDB protects individual rows by performing MVCC for transaction control. The overhead generated for reads in a day would probably be greater than 150 MiB.

I can probably name 2 or 3 more reasons, but let's cut to the chase: Is there anything that can improve performance for MyISAM in your case? Why, yes there is.

Your said the following

The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints

If you have many varchars, there is something you can do to increase read/write performance. For any MyISAM table mydb.mytable: run this command:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

What will this do, this will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. In your case, let's assume it doubles your 150 MiB MyISAM table to 300 MiB. Where is the benefit? Your MyISAM table can now be read/written anywhere from 20% - 30% faster without changing anything else I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

Which is faster, InnoDB or MyISAM? (May 03, 2012)
Which mysql storage engine to choose? (May 02, 2012)
Best of MyISAM and InnoDB (Sep 20, 2011)
Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)

MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)

Here is something to really think about

MyISAM only caches indexes, so there is always disk I/O. InnoDB caches data and indexes. If you could guarantee that every query you call against this one table can only be fetch necessary columns and all columns can be fetched from the index only (called a covering index)

Here are some nice links on Covering Indexes:

MyISAM can do shared reads. So can InnoDB.

Please remember that MyISAM inserts, updates, and deletes perform full tables locks each time (that can be the critical path for disk I/O in making InnoDB wait its turn in certain circumstances). If the table is ready-heavy, you may want to use a dedicated MyISAM key cache.

Heavy INSERTs from LOAD DATA INFILE will lock the MyISAM table. InnoDB can tolerate it better but check the behavior of LOAD DATA INFILE to see if it locks the table regardless of Storage Engine.

The tradeoff depends upon how often you write to the MyISAM table.

PLEASE DO NOT JOIN THE MyISAM TABLE TO ANY INNODB TABLE !!! The locking is clumsy and MyISAM changes cannot rollback.