2

I have a large table with about 100 million rows.

I have done indexing and partitioning in the table, but still queries sometimes take >100 or even >200 seconds to run.

So I was thinking, what is the next step after indexing and partitioning to improvise the MySQL performance.

  1. Changing the code logic is one option (but that is reducing the data, I am concerned about how to tune MySQL to work with this data only)
  2. Upgrading the hardware and MySQL versions in another.(I am already using SSD, 8 core CPU and 32GBs RAM, and resources are available most of the time. )

And this question is not only about this table, but about general MySQL practices. Like doing indexing is one of the basic things, and after indexing second thing is partitioning. But what after that? My question can be little vague, but I think this will be helpful to many people searching for similar ansers.

To give you an idea, here is an overview of my table large_table :

+------+------------+---------------+---------------------+
| user | mobile     | is_first_time | time_send           |
+------+------------+---------------+---------------------+
| a    | xxxxxxxxxx | 0             | 2018-03-12 00:00:00 |
+------+------------+---------------+---------------------+
| b    | xxxxxxxxxx | 1             | 2018-04-02 07:08:09 |
+------+------------+---------------+---------------------+
| c    | xxxxxxxxxx | 0             | 2018-01-03 01:02:03 |
+------+------------+---------------+---------------------+

Following is the output of SHOW INDEXES FOR large_table

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| large_table |          0 | PRIMARY  |            1 | user        | A         |         493 |     NULL | NULL   |      | BTREE      |         |               |
| large_table |          0 | PRIMARY  |            2 | mobile      | A         |   105682194 |     NULL | NULL   |      | BTREE      |         |               |
| large_table |          1 | userid   |            1 | user        | A         |      188718 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

The four type of queries that generally run on this table :

INSERT IGNORE INTO large_table (user,mobile) VALUES ('1234','9876543210')

UPDATE large_table SET is_first_time = 0 WHERE (user,mobile) IN(('xxxx','XXXXXXXXXX'),('zzzz','ZZZZZZZZZZ'),('yyyy','YYYYYYYYYY'),('aaaa','AAAAAAAAAA')

SELECT mobile FROM large_table WHERE user = 168807

SELECT count(mobile) FROM large_table WHERE user =xxxx

Thanks in advance (this is more of a knowledge seeking question rather than problem's solution)

DDL of table : https://gist.github.com/kadambkaluskar/4e2ff8d5be6f625c123b58bc10cb32b0

Edit 1: Couldn't run EXPLAIN on UPDATE query, as I am using MySQL 5.5

Edit 2:

Wherenever there is a query like UPDATE large_table SET is_first_time = 0 WHERE (user,mobile) IN, it goes into updating state, and all other queries start to line-up after that.

Following is the output of SHOW PROCESSLIST

+------------+---------+------------------+--------+---------+------+------------+------------------------------------------------------------------------------------------------------+
| Id         | User    | Host             | db     | Command | Time | State      | Info                                                                                                 |
+------------+----------------------+------------------+-----------------+---------+------+------------------+--------------------------------------------------------------------------+
| 1307948736 | my_user | 10.0.0.48:29134  | my_db  | Query   |   91 | Updating   | UPDATE large_table SET is_first_time = 0 WHERE (`user`,`mobile`) IN(('21xx67','919xxxxxx002'),('2177 |
| 1308045382 | my_user | 10.0.0.62:42912  | my_db  | Query   |   50 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('9xx75','917xxxxxx805')                          |
| 1308064919 | my_user | 10.0.0.48:56362  | my_db  | Query   |   42 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('105xx7','9189xxxxxx59')                         |
| 1308066190 | my_user | 10.0.0.21:63342  | my_db  | Query   |   41 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('91xx0','9196xxxxxx60')                          |
| 1308069898 | my_user | 10.0.0.62:48648  | my_db  | Query   |   39 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('95xx5','9192xxxxxx14')                          |
| 1308073279 | my_user | 10.0.0.88:30996  | my_db  | Query   |   38 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('57xx4','9170xxxxxx43')                          |
| 1308073424 | my_user | 10.0.0.24:4738   | my_db  | Query   |   38 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('91xx0','919xxxxxx494')                          |
| 1308073776 | my_user | 10.0.0.88:31118  | my_db  | Query   |   38 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('11xxx3','919xxxxxx224')                         |
| 1308076906 | my_user | 10.0.0.72:26482  | my_db  | Query   |   37 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('1xx17','9197xxxxxx62')                          |
| 1308077527 | my_user | 10.0.0.9:28928   | my_db  | Query   |   37 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('76xx0','9198xxxxxx66')                          |
| 1308082112 | my_user | 10.0.0.113:4230  | my_db  | Query   |   34 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('10xxx8','9197xxxxxx60')                         |
| 1308083588 | my_user | 10.0.0.72:27516  | my_db  | Query   |   34 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('11xxx4','919xxxxxx557')                         |
| 1308088011 | my_user | 10.0.0.101:60448 | my_db  | Query   |   32 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('12xxx0','9184xxxxxx48')                         |
| 1308090557 | my_user | 10.0.0.9:32118   | my_db  | Query   |   31 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('11xxx0','9193xxxxxx59')                         |
| 1308093171 | my_user | 10.0.0.72:29535  | my_db  | Query   |   30 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('11xx84','9193xxxxxx07')                         |
| 1308095347 | my_user | 10.0.0.113:5454  | my_db  | Query   |   29 | Updating   | UPDATE large_table SET is_first_time = 0 WHERE `user`=12xxx1 AND `mobile` = '917xxxxxx482'           |
| 1308096238 | my_user | 10.0.0.62:56668  | my_db  | Query   |   29 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('10xxx8','9190xxxxxx03')                         |
| 1308096342 | my_user | 10.0.0.78:16782  | my_db  | Query   |   29 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('1xxx6','9193xxxxxx54')                          |
| 1308106957 | my_user | 10.0.0.100:17930 | my_db  | Query   |   25 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('7xxx8','9189xxxxxx33')                          |
| 1308109743 | my_user | 10.0.0.107:29496 | my_db  | Query   |   23 | update     | INSERT IGNORE INTO large_table(user,mobile) VALUES ('1xxx29','9197xxxxxx75')                         |
+------------+---------+------------------+-----------------+---------+------+------------------+---------------------------------------------------------------------------------------+

Userwise mobile distribution :

+-------------+----------------+
| no.of.users | mobile.no.count|
+-------------+----------------+
|      1      |   1.5milion    |
|      2      |   1 milion     |
|      10     |   500-1000k    |
|     147     |   100k-500k    |
|     173     |   50k-100k     |
|    1336     |    10k-50k     |
|    2610     |     5k-10k     |
|    6500     |     1k-5k      |
|     12k     |     100-1k     |
|     12k     |     100-1k     |
|     10k     |     10-100     |
|     23k     |      1-10      |
+-------------+----------------+
  • 2
    DDL of large_table - where is it? We need to look on additional fields, partition info, etc. – Akina May 31 '18 at 10:58
  • Also, which query needs 100+ seconds? Add the EXPLAIN output for that. – ypercubeᵀᴹ May 31 '18 at 11:30
  • 1
    @Lennart : nope, there is no particular reason to use int and bigint as strings, and I don't know why they were there in first place. I am on my way to remove those quotes. – kadamb Jun 2 '18 at 10:54
  • 1
    How many rows do you typically put in the where of the update, IN ( ... )? – Lennart Jun 2 '18 at 14:32
2
  • PARTITIONing buys you no performance. Get rid of it. (Also, 400 partitions is so many that that, itself, slows things down. More discussion.)

  • You already have this; keep it:

PRIMARY KEY(user, mobile)

  • Change the UPDATE. Although "row constructors" as in WHERE(a,b) IN ((1,2),...) is tempting syntax, it is awful at performance. The Optimizer does not (until version 5.7.3) do anything useful with that. It scans the entire table! (Note how thek first UPDATE in the PROCESSLIST is at 91 seconds, and seems to be blocking all the other queries.)

One way to 'fix' the update is to build a table (tmp) with the IN items, then do a multi-table UPDATE with

WHERE large.user   = tmp.user
  AND large.mobile = tmp.mobile

and have PRIMARY KEY(user, mobile) on tmp.

The other solution to the Update is to upgrade to 5.6, then 5.7. Anyway, 5.5 will soon be End-Of-Life'd.

  • To save a little space, and a little speed, consider DECIMAL(11) for mobile. It would take 5 bytes instead of BIGINT, which takes 8.

  • Get rid of the redundant KEY(user). A rule: If you have INDEX(a,b), you don't need INDEX(a). And PRIMARY KEY is Unique and a key`.

  • Don't say count(mobile), simply say COUNT(*). When you specify an expression, it checks that for being NULL, which is not relevant in your case. Although SELECT COUNT(*) FROM lt WHERE user = xx might use INDEX(key), the overhead of that otherwise-unneeded index makes it probably not worth having.

  • Be cautious about MAX(user). 100M is not terribly far from 2 billion. Note, making it INT UNSIGNED would raise the limit to 4 billion, while still taking only 4 bytes.

I hope I have provided you some 'knowledge' as well as multiple solutions toward improving the table's performance.

After all these suggestions, I suspect you will rarely see a Time greater than "1" in PROCESSLIST.

  • thank you for valuable suggestions. most knowledgeable is that partitioning is of no use in this case(and in any case, other than the four you have mentioned in many of your posts). i have started migrating my data to aws-aurora to use 5.7 features. one more question, which is better option in your opinion, aurora for mysql 5.7 or mysql-5.7 on RDS? – kadamb Jun 4 '18 at 18:55
  • @kadamb - Sorry, I do not have enough info on those two to give an opinion. Anyway, the diffs would probably be application-dependent. – Rick James Jun 4 '18 at 20:21
  • just upgrading my system to 5.7 solved all the long-running queries. :) – kadamb Jul 24 '18 at 13:40
  • @kadamb - Glad to hear that 5.7 helped. The rest of my suggestions may help, but to a lesser degree. – Rick James Jul 24 '18 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.