Create Table Like Scalability Issues
When we wanted to quickly process intermediate data by using temporary or heap tables; then its normal tendency to create a table like its source table; so people will simply opt for CREATE TEMPORARY TABLE temp_table LIKE source_table.
This is an easy and convenient way. But the problem is if you have a simple stored procedure or code module that gets executed frequently, then you might indirectly experience a slow-down in the performance if your source_table is large enough and highly contended. Things will be really worst if concurrent threads starts using the source_table or in combination of source_table and CREATE LIKE.. statements and can lead to disaster scalability issues. The main reason for the post is; I noticed from SHOW PROCESSLIST from random servers where X threads getting into WAITING for TABLE lock state for simple CREATE TABLE LIKE statements and lot of threads associated with source table is opening and closing the tables.
The main source of issue is, In MySQL 5.0 or lower versions; CREATE LIKE uses exclusive named lock (lock_and_wait_for_table_name) on the source_table; which forces all threads to close the table which are currently in use and gets the named lock and no other thread can read/write from source_table until destination table is created. If source_table is large enough and few long running selects are running; then CREATE TABLE will wait until it gets the lock. To make it simple; you can even simulate this with a simple read lock on a table in one session and try to create the table like the locked table in another session; and notice that create table will wait until session one unlocks the tables.
Session 1:
localhost:test> create table source_table (id int)Engine=InnoDB; Query OK, 0 rows affected (0.00 sec) localhost:test> lock table source_table read; Query OK, 0 rows affected (0.00 sec) |
Session 2:
localhost:test> create temporary table temp_table like source_table; |
Thats a simple case, but lets simulate a real example with stored procedure and mysqlslap with multiple threads where few threads will be using the source_table and few threads starts creating the temporary tables.
First, create the the source_table and populate the table with 10K rows..
localhost:test> use test; Query OK, 0 rows affected (0.02 sec) localhost:test> DROP TABLE IF EXISTS source_table; Query OK, 0 rows affected, 1 warning (0.00 sec) localhost:test> CREATE TABLE source_table(id int, name VARCHAR(20))Engine=InnoDB; Query OK, 0 rows affected (0.00 sec) localhost:test> DROP PROCEDURE IF EXISTS load_table_proc; Query OK, 0 rows affected (0.00 sec) localhost:test> DROP PROCEDURE IF EXISTS temp_scale_proc; Query OK, 0 rows affected (0.00 sec) localhost:test> DELIMITER GO localhost:test> localhost:test> CREATE PROCEDURE load_table_proc(count INT) -> BEGIN -> DECLARE _i INT; -> SET _i = 1; -> WHILE _i < = count DO -> INSERT INTO source_table SELECT _i, CONCAT('testing', _i); -> SET _i = _i + 1; -> END WHILE; -> END -> GO Query OK, 0 rows affected (0.00 sec) localhost:test> localhost:test> DELIMITER ; localhost:test> CALL load_table_proc(10000); Query OK, 1 row affected (1.54 sec) |
CREATE another stored procedure which can use the source_table to create the temp table and searches for a given number
localhost:test> DELIMITER GO localhost:test> CREATE PROCEDURE temp_scale_proc(cnt INT) -> BEGIN -> DROP TABLE IF EXISTS temp_tab; -> CREATE TEMPORARY TABLE temp_tab LIKE source_table; -> INSERT INTO temp_tab SELECT * FROM source_table; -> SELECT * FROM temp_tab WHERE id=cnt; -> END -> GO Query OK, 0 rows affected (0.00 sec) localhost:test> DELIMITER ; localhost:test> |
Now execute the stored procedure temp_scale_proc in parallel using 10 threads through mysqlslap; and start watching the processlist and observe the thread states.
mysqlslap --create-schema="test" --concurrency=10 --iteration=100 --delimiter=";" --query="call test.temp_scale_proc(1+(RAND() * 10000))" |
The above calls the stored procedure by passing a random number between 1 to 10000.
Some thread states from SHOW PROCESSLIST showing locking and scalability issues…
2117 root localhost NULL Sleep 0 NULL 2118 root localhost test Query 0 Waiting for table INSERT INTO temp_tab SELECT * FROM source_table 2119 root localhost test Query 0 Sending data INSERT INTO temp_tab SELECT * FROM source_table 2120 root localhost test Query 0 Waiting for table CREATE TEMPORARY TABLE temp_tab LIKE source_table 2121 root localhost test Query 0 Waiting for table CREATE TEMPORARY TABLE temp_tab LIKE source_table 2122 root localhost test Query 0 Waiting for table CREATE TEMPORARY TABLE temp_tab LIKE source_table 2123 root localhost test Query 0 Waiting for table CREATE TEMPORARY TABLE temp_tab LIKE source_table 2124 root localhost test Query 0 Waiting for table CREATE TEMPORARY TABLE temp_tab LIKE source_table 2125 root localhost test Query 0 Waiting for table INSERT INTO temp_tab SELECT * FROM source_table 2126 root localhost test Query 0 Waiting for table CREATE TEMPORARY TABLE temp_tab LIKE source_table 2127 root localhost test Query 0 Waiting for table CREATE TEMPORARY TABLE temp_tab LIKE source_table -- Total open tables: 8 and Name Locks: 7 (output of SHOW OPEN TABLES) |
Not just the locking, the statement also indirectly causes table to be closed and opened again and again by other sessions…
2117 root localhost NULL Sleep 15 NULL 3032 root localhost test Query 0 closing tables SELECT * FROM temp_tab WHERE id= NAME_CONST('cnt',9319) 3033 root localhost test Query 0 Opening tables INSERT INTO temp_tab SELECT * FROM source_table 3034 root localhost test Query 0 Waiting for table CREATE TEMPORARY TABLE temp_tab LIKE source_table 3035 root localhost test Query 0 Waiting for table INSERT INTO temp_tab SELECT * FROM source_table 3036 root localhost test Query 0 checking permissions CREATE TEMPORARY TABLE temp_tab LIKE source_table 3037 root localhost test Query 0 closing tables CREATE TEMPORARY TABLE temp_tab LIKE source_table 3038 root localhost test Query 0 Opening tables INSERT INTO temp_tab SELECT * FROM source_table 3039 root localhost test Query 0 checking permissions CREATE TEMPORARY TABLE temp_tab LIKE source_table 3040 root localhost test Query 0 checking permissions CREATE TEMPORARY TABLE temp_tab LIKE source_table 3041 root localhost test Query 0 Waiting for table CREATE TEMPORARY TABLE temp_tab LIKE source_table -- Total open tables: 1 and Name Locks: 1 (output of SHOW OPEN TABLES) |
So, the best bet is not to use CREATE TABLE LIKE on a highly contended source table or use direct table schema to create the table or if you upgrade to MySQL 5.1 and above; where locking issue is addressed as it opens the source_table instead of getting the named lock that prevents any updates to FRM file.
Related posts:
1 Comment
Good recommendation. It can be very unpleasant if you have a query that is running for hundreds or thousands of seconds and have started CREATE TABLE LIKE. The create will start waiting for a lock and every other attempt to use the table will wait until the slow query finishes because they are blocked by the create lock.
Comment :: January 19, 2010 @ 3:38 am