{"id":417,"date":"2014-10-08T09:48:54","date_gmt":"2014-10-08T07:48:54","guid":{"rendered":"http:\/\/dbahire.com\/?p=417"},"modified":"2019-08-27T12:05:19","modified_gmt":"2019-08-27T10:05:19","slug":"testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results","status":"publish","type":"post","link":"https:\/\/jynus.com\/dbahire\/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results\/","title":{"rendered":"Testing the Fastest Way to Import a Table into MySQL (and some interesting 5.7 performance results)"},"content":{"rendered":"

As I mentioned on my last post, where I compared the default configurations options in 5.6 and 5.7<\/a>, I have been doing some testing for a particular load in several versions of MySQL. What I have been checking is different ways to load a CSV file (the same file I used for testing the compression tools<\/a>) into MySQL. For those seasoned MySQL DBAs and programmers, you probably know the answer, so you can jump over to my 5.6 versus 5.7 results. However, the first part of this post is dedicated for developers and MySQL beginners that want to know the answer to the title question, in a step-by-step fashion. I must say I also learned something, as I under- and over-estimated some of the effects of certain configuration options for this workload.<\/p>\n

Disclaimers: I do not intend to do proper benchmarks, most of the results obtained here were produced in a couple of runs, and many of them with a default configuration. This is intended, as I want to show “bad practices”<\/strong> for people that is just starting to work with MySQL, and what they should avoid doing. It is only the 5.6 to 5.7 comparison that have left me wondering. Additional disclaimer<\/strong>: I do not call myself a programmer, and much less a python programmer, so I apologize in advance for my code- after all, this is about MySQL<\/em>. The download link for the scripts is at the bottom.<\/p>\n

The Rules<\/h3>\n

I start with a CSV file<\/a> (remember that it is actually a tab-separated values file) that is 3,700,635,579 bytes in size, has 46,741,126 rows and looks like this:<\/p>\n

171773  38.6048402      -0.0489871      4       2012-08-25 00:37:46     12850816        472193  rubensd\n171774  38.6061981      -0.0496867      2       2008-01-19 10:23:21     666916  9250    j3m\n171775  38.6067166      -0.0498342      2       2008-01-19 10:23:21     666916  9250    j3m\n171776  38.6028122      -0.0497136      5       2012-08-25 00:26:40     12850816        472193  rubensd\n171933  40.4200658      -3.7016652      6       2011-11-29 05:37:42     9984625 33103   sergionaranja \n<\/pre>\n
I want to load it into a table with the following structure:<\/p>\n
CREATE TABLE `nodes` (\n  `id` bigint PRIMARY KEY,\n  `lat` decimal(9,7),\n  `lon` decimal(10,7),\n  `version` int,\n  `timestamp` timestamp,\n  `changeset` bigint,\n  `uid` bigint,\n  `user` varchar(255)\n);\n<\/pre>\n
The import finish time will be defined as the moment the table is crash safe (even if there is some pending IO). That means that for InnoDB, the last COMMIT<\/code> has to be successful and flush_log_at_trx_commit<\/code> must be equal to 1, meaning that even if there is pending IO to be made, it is fully durable on disk (it is crash-resistant). For MyISAM, that means that I force a FLUSH TABLES<\/code> before finishing the test. Those are, of course, not equivalent but it is at least a way to make sure that everything is more or less disk-synced. This is the ending part of all my scripts:<\/p>\n
# finishing\nif (options.engine == 'MyISAM'):\n    cursor.execute('FLUSH TABLES')\nelse:\n    db.commit()\n\ncursor.close()\ndb.close()\n<\/pre>\n
For the hardware and OS, check the specs on this previous post<\/a>– I used the same environment as the one mentioned there, with the exception of using CentOS7 instead of 6.5.<\/p>\n
The naive method<\/h3>\n
Let’s say I am a developer being tasked with loading a file regularly into MySQL- how would I do that? I would probably be tempted to use a CSV parsing library, the mysql connector and link them together in a loop. That would work, wouldn’t it? The main parts of the code would look like this (load_data_01.py<\/code>):<\/p>\n
# import\ninsert_node = \"INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)\"\n\nwith open(CSV_FILE, 'rb') as csv_file:\n    csv_reader = csv.reader(csv_file, delimiter='\\t')\n    for node in csv_reader:\n        cursor.execute(insert_node, node)\n<\/pre>\n
As I am playing the role of a developer without MySQL experience, I would also use the default configuration. Let’s see what we get (again, that is why I call these “tests”, and not benchmarks). Lower is better:
\n\"load_data_01\"<\/a><\/p>\n\n\n\n\n\n
MySQL Version<\/em><\/td>\n5.1.72<\/td>\n5.5.39<\/td>\n5.6.20<\/td>\n5.7.4<\/td>\n<\/tr>\n<\/thead>\n
Load time (seconds)<\/em><\/td>\n4708.594<\/td>\n6274.304<\/td>\n6499.033<\/td>\n6939.722<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Wow, is that 5.1 being a 50% faster than the rest of versions? Absolutely not<\/strong>, remember that 5.5 was the first version to introduce InnoDB as the default engine, and InnoDB has additional transactional overhead and usually not good default configuration (unlike MyISAM, which is so simple that the default options can work in many cases). Let’s normalize our results by engine:
\n\"load_data_01_by_engine\"<\/a><\/p>\n\n\n\n\n\n\n
MySQL Version<\/em><\/td>\n5.1.72<\/td>\n5.5.39<\/td>\n5.6.20<\/td>\n5.7.4<\/td>\n<\/tr>\n<\/thead>\n
MyISAM<\/em><\/td>\n4708.594<\/td>\n5010.655<\/td>\n5149.791<\/td>\n5365.005<\/td>\n<\/tr>\n
InnoDB<\/em><\/td>\n6238.503<\/td>\n6274.304<\/td>\n6499.033<\/td>\n6939.722<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
This seems more reasonable, doesn’t it? However, in this case, it seems that there is a slight regression in single-thread performance as the versions go up, specially on MySQL 5.7. Of course, it is early to draw conclusions, because this method of importing a CSV file, row by row, is one of the slowest ones, and we are using very poor configuration options (the defaults), which vary from version to version and should not be taken into account to draw conclusions<\/strong>.<\/p>\n
What we can say is that MyISAM seems to work better by default for this very particular scenario for the reasons I mentioned before, but it still takes 1-2 hours to load such a simple file.<\/p>\n
The even more naive method<\/h3>\n
The next question is not: can we do it better, but, can we do it even slower? A particular text draw my attention when looking at the MySQL connector documentation:<\/p>\n
Since by default Connector\/Python does not autocommit, it is important to call this method after every transaction that modifies data for tables that use transactional storage engines.<\/p><\/blockquote>\n
-from the connector\/python documentation<\/a>
\nI though to myself- oh, so maybe we can speedup the import process by committing every single row to the database, one by one, don’t we? After all, we are inserting the table on a single huge transaction. Certainly, a huge number of small transactons will be better! \ud83d\ude42 This is the slightly modified code (load_data_02.py<\/code>):<\/p>\n
insert_node = \"INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)\"\n\nwith open('\/tmp\/nodes.csv', 'rb') as csv_file:\n    csv_reader = csv.reader(csv_file, delimiter='\\t')\n    for node in csv_reader:\n        cursor.execute(insert_node, node)\n        db.commit()\n<\/pre>\n
And I do not even have a fancy graphic to show you because after 2 hours, 19 minutes and 39.405 seconds, I canceled the import because only 111533 nodes had been inserted<\/strong> in MySQL 5.1.72 for InnoDB with the default configuration (innodb_flush_log_at_trx_commit = 1<\/code>). Obviously, millions of fsyinc<\/code>s will not make our load faster, consider this a leason learned.<\/p>\n
Going forward: multi-inserts<\/h3>\n
The next step I wanted to test is how effective grouping queries was in a multi-insert statement. This method is used by mysqldump<\/code>, and supposedly minimizes the SQL overhead of handling every single query (parsing, permission checking, query planning, etc.). This is the main code (load_data_03.py<\/code>):<\/p>\n
concurrent_insertions = 100\n[...]\nwith open(CSV_FILE, 'rb') as csv_file:\n    csv_reader = csv.reader(csv_file, delimiter='\\t')\n    i = 0\n    node_list = []\n    for node in csv_reader:\n        i += 1\n\tnode_list += node\n        if (i == concurrent_insertions):\n            cursor.execute(insert_node, node_list)\n            i = 0\n            node_list = []\n    csv_file.close()\n\n# insert the reminder nodes\nif i > 0:\n    insert_node = \"INSERT INTO nodes (id, lat, lon, version, timestamp, changeset, uid, user) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)\"\n    for j in xrange(i - 1):\n        insert_node += ', (%s, %s, %s, %s, %s, %s, %s, %s)'\n    cursor.execute(insert_node, node_list)\n<\/pre>\n
We tested it with a sample of 100 rows inserted with every query. What are the results? Lower is better:
\n\"load_data_03.py<\/a><\/p>\n\n\n\n\n\n\n
MySQL Version<\/em><\/td>\n5.1.72<\/td>\n5.5.39<\/td>\n5.6.20<\/td>\n5.7.4<\/td>\n<\/tr>\n<\/thead>\n
MyISAM<\/em><\/td>\n1794.693<\/td>\n1822.081<\/td>\n1861.341<\/td>\n1888.283<\/td>\n<\/tr>\n
InnoDB<\/em><\/td>\n3645.454<\/td>\n3455.800<\/td>\n2849.299<\/td>\n3032.496<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
With this method we observe an improvement of the import time of 262-284% from the original time for MyISAM and of 171-229% from the original time for InnoDB<\/strong>. Remember that this method will not scale indefinitely, as we will encounter the package size limit if we try to insert too many rows at the same time. However, it is a clear improvement over the one-row-at-a-time approach.<\/p>\n
MyISAM times are essentially the same between versions while InnoDB shows an improvement over time (which may be due to code and optimization changes, but also to the defaults like the transaction log size changing, too), except again between 5.6 and 5.7.<\/p>\n
The right method for importing data: Load Data<\/h3>\n
If you have a minimum of experience with MySQL, you know that there is a specialized keyword for data imports, and that is LOAD DATA<\/code>. Let’s see how the code would end up looking like by using this option (load_data_04.py<\/code>):<\/p>\n
# data import\nload_data = \"LOAD DATA INFILE '\" + CSV_FILE + \"' INTO TABLE nodes\"\n\ncursor.execute(load_data)\n<\/pre>\n
Simple, isn’t it? With this we are minimizing the SQL overhead, and executing the loop in the compiled C MySQL code. Let’s have a look at the results (lower is better):
\n\"load_data_04.py<\/a><\/p>\n\n\n\n\n\n\n\n
MySQL Version<\/em><\/td>\n5.1.72<\/td>\n5.5.39<\/td>\n5.6.20<\/td>\n5.7.4<\/td>\n<\/tr>\n<\/thead>\n
MyISAM<\/em><\/td>\n141.414<\/td>\n149.612<\/td>\n155.181<\/td>\n166.836<\/td>\n<\/tr>\n<\/tbody>\n
InnoDB<\/em><\/td>\n2091.617<\/td>\n1890.972<\/td>\n920.615<\/td>\n1041.702<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
In this case, MyISAM has a very dramatic improvement – LOAD DATA<\/code> speeds up to 12x times the import. InnoDB, again still each one with the default parameters can improve the speed up to 3x times, and more significantly in the newer versions (5.6, 5.7) than the older ones (5.1, 5.5).<\/strong> I predict that this has to do much more with the different configuration of log files than with the code changes.<\/p>\n
Trying to improve Load Data for MyISAM<\/h3>\n
Can we improve the load times for MyISAM? There are 2 things that I tried to do -augmenting the key_cache_size and disabling the Performance Schema for 5.6 and 5.7. I set up the key_cache_size<\/code> to 600M<\/code> (trying to fit the primary key on memory) and I set the performance_schema = 0<\/code>, and I tested the 3 remaining combinations. Lower is better:
\n\"load_data_04.py<\/a><\/p>\n\n\n\n\n\n\n\n\n
MySQL Version<\/em><\/td>\n5.1.72<\/td>\n5.5.39<\/td>\n5.6.20<\/td>\n5.7.4<\/td>\n<\/tr>\n<\/thead>\n
default<\/em><\/td>\n141.414<\/td>\n149.612<\/td>\n155.181<\/td>\n166.836<\/td>\n<\/tr>\n
key_buffer_size=600M<\/em><\/td>\n136.649<\/td>\n170.622<\/td>\n182.698<\/td>\n191.228<\/td>\n<\/tr>\n
key_buffer_size=600M, P_S = OFF<\/em><\/td>\n133.967<\/td>\n170.677<\/td>\n177.724<\/td>\n186.171<\/td>\n<\/tr>\n
P_S = OFF<\/em><\/td>\n142.592<\/td>\n145.679<\/td>\n150.684<\/td>\n159.702<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
There are certain things to notice here:<\/p>\n