0

My mysql cluster: Ver 5.6.30-76.3-56 for debian-linux-gnu on x86_64 (Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16)

I've a complicated sql query which inserts for about 36k rows into a table with this syntax:

INSERT INTO `sometable` (SELECT ...);

The select is a bit complicated but not slow (0.0023s) but the insert takes about 40-50s. The table is not in use when I'm inserting the rows.

My questions are:

  • Can I speed it up somehow?
  • The slow insert causes locking problems on the other tables (because of select)
  • This workflow is good or bad practice? Is there any better?

Thanks

UPDATE:

The table schema:

CREATE TABLE `sometable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT NULL,
  `a` varchar(255) DEFAULT NULL,
  `b` smallint(6) unsigned DEFAULT NULL,
  `c` smallint(6) unsigned DEFAULT NULL,
  `d` smallint(6) unsigned DEFAULT NULL,
  `e` smallint(6) unsigned DEFAULT NULL,
  `f` varchar(255) DEFAULT '',
  `country_id` int(10) unsigned DEFAULT NULL,
  `city_id` int(10) unsigned DEFAULT NULL,
  `g` smallint(6) unsigned DEFAULT NULL,
  `h` smallint(6) unsigned DEFAULT NULL,
  `i` smallint(6) unsigned DEFAULT NULL,
  `j` smallint(6) unsigned DEFAULT NULL,
  `k` smallint(6) unsigned DEFAULT NULL,
  `l` varchar(3) DEFAULT NULL,
  `m` varchar(3) DEFAULT NULL,
  `n` text,
  `o` varchar(255) DEFAULT NULL,
  `p` varchar(32) DEFAULT NULL,
  `q` varchar(32) DEFAULT NULL,
  `r` varchar(32) DEFAULT NULL,
  `s` time DEFAULT NULL,
  `t` time DEFAULT NULL,
  `u` text,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `country_id` (`country_id`),
  KEY `city_id` (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

UPDATE2:

When I try to run the query I get an error in some cases:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

MY SOLUTION:

Here is my final solution if somebody interested in: gist

The main problem was that while I fill mytable the other queries are stuck and the cluster had serious performance problems. In this solution I create a temporary table and fill it with data in "dirty read" mode, then I copy these data to mytable in chunks so it takes a bit more time but there is no performance problem and not stuck the queries.

MrRP
  • 822
  • 2
  • 10
  • 25
  • 1
    mind sharing with us the `CREATE TABLE` statement of your table? (Include only the important stuff, like keys, indexed columns, indexes, etc.) – Mike Nakis Jul 27 '17 at 10:24
  • 1
    Please read this http://meta.stackoverflow.com/a/271056/ especially the part on query performance. Then please [edit] your question. – O. Jones Jul 27 '17 at 10:25
  • Are you sure your `SELECT` operation is really that fast? Read this: http://meta.stackoverflow.com/a/271056/ – O. Jones Jul 27 '17 at 10:26
  • @O.Jones I've updated the question with the schema. And yes, I've tried many times, the select is never more then 0.003s. – MrRP Jul 27 '17 at 10:45
  • if this process is manual every time why dont you just `dump` the data and edit it and reinsert it ? it wont take much time when importing – Arun pandian M Jul 27 '17 at 10:52
  • @ArunpandianM It's not manual, I have to do it certain intervals. – MrRP Jul 27 '17 at 10:56
  • Now try the `SELECT` this way: `SELECT SQL_NO_CACHE ...` -- 3ms sounds like reading from the "Query cache". – Rick James Jul 27 '17 at 23:59
  • Where is the `DELETE` of the old data? Is it based on `id`? Read about IODKU. – Rick James Jul 28 '17 at 00:00
  • How far (ping time) apart are the nodes of the Cluster? What, if anything are the _other_ nodes doing with this table at about the same time? – Rick James Jul 28 '17 at 00:03

2 Answers2

1

A SELECT operation that returns a row of the length you describe every 64 nanoseconds is very fast. That's what 36 kilorows in 2.3 milliseconds works out to. It seems likely that your SELECT query timing doesn't account for the transport of the result set to the MySQL client. At any rate, using that performance as a comparison to an INSERT operation sets your expectations unreasonably high.

You might try issuing this command before starting your operation. It will allow your SELECT operation to proceed with fewer contentions with your application's traffic on the source tables for the SELECT. See here https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

You might try a two step process, involving a temporary table. This will have the advantage of not having to update all the indexes in some_table at the same time as the SELECT operation. That operation will look like this.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TEMPORARY TABLE insert_batch AS SELECT ... ;
INSERT INTO some_table SELECT * FROM insert_batch;
DROP TEMPORARY TABLE insert_batch;

You should understand that InnoDB posts your batch of insertions to your table as a single transaction. If you can do this in a way that handles about 500 rows at a time rather than 36K, you'll have more transactions, but they will be smaller. That's generally a way to get higher throughput.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thank you, this helped me really much. Based on this I've solved the problem. It's not so fast but there is no performance issue while running the procedure. – MrRP Jul 28 '17 at 13:34
  • You're welcome. If you have time, can you leave a comment saying what you did and how much it improved performance? – O. Jones Jul 28 '17 at 22:22
  • I've updated the question with my solution. If you have any comment about it I would really appreciate it. – MrRP Aug 01 '17 at 08:32
1

If all else fails, this may be a viable solution. First, see http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

  1. Load your corrections into a temp table (or non-replicated MyISAM table).
  2. Loop through the temp table (using code similar to that link). Pick 100 rows at a time.
  3. Do the INSERT ... SELECT ... of 100 rows in a separate transaction.

This technique may (or may not) take longer than 40-50s, but at least is much less likely to timeout or deadlock.

In general, avoid running any transaction that lasts longer than a few seconds. This link is somewhat generic on how to "chunk" lengthy (and repetitive) operations to avoid long transactions.

Rick James
  • 135,179
  • 13
  • 127
  • 222