How to pull a large amount of data from mariadb in a fast manner when workload is IO bound

user_17358811 0
Viewed 50

Important to know information:

We have a database table 'cdrs'

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | bigint(12)    | NO   | PRI | NULL    | auto_increment |
| server_id       | tinyint(2)    | NO   |     | 0       |                |
| cdr_id          | bigint(13)    | NO   | MUL | 0       |                |
| user_id         | int(11)       | NO   | MUL | 0       |                |
| transaction_id  | int(11)       | YES  | MUL | NULL    |                |
| sip_id          | int(11)       | NO   | MUL | 0       |                |
| call_type       | tinyint(2)    | YES  | MUL | NULL    |                |
| did_from        | char(24)      | YES  |     | NULL    |                |
| did_from_alias  | bigint(18)    | YES  | MUL | 0       |                |
| did_to          | char(24)      | YES  |     | NULL    |                |
| did_to_alias    | bigint(18)    | YES  | MUL | 0       |                |
| call_status     | char(12)      | YES  | MUL | NULL    |                |
| start_time      | int(11)       | NO   | PRI | 0       |                |
| duration        | decimal(13,3) | YES  |     | NULL    |                |
| billed_duration | decimal(13,3) | NO   |     | 0.000   |                |
| rate            | decimal(10,4) | YES  |     | NULL    |                |
| amount          | decimal(10,4) | YES  |     | NULL    |                |
| usf             | decimal(10,4) | YES  |     | NULL    |                |
| total           | decimal(10,4) | YES  | MUL | NULL    |                |
| country         | varchar(96)   | YES  |     | NULL    |                |
| country_id      | int(8)        | NO   |     | 0       |                |
| code            | varchar(8)    | NO   |     |         |                |
+-----------------+---------------+------+-----+---------+----------------+

With the following indexes

+---------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| cdrs |          0 | PRIMARY          |            1 | id             | A         |   573346816 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          0 | PRIMARY          |            2 | start_time     | A         |   573346816 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_cdr_id         |            1 | cdr_id         | A         |   573346816 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_user_id        |            1 | user_id        | A         |      158909 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_call_type      |            1 | call_type      | A         |       19887 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_transaction_id |            1 | transaction_id | A         |   143336704 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_total          |            1 | total          | A         |      163953 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_start_time     |            1 | start_time     | A         |    24928122 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_call_status    |            1 | call_status    | A         |        9877 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_sip_id         |            1 | sip_id         | A         |      353481 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| cdrs |          1 | i_did_to         |            1 | did_to_alias   | A         |   286673408 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| cdrs |          1 | i_did_from       |            1 | did_from_alias | A         |    57334681 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+---------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

The Problem:

Currently in production this table has more rows then the max value of int(hence why the id is bigint)

This table grows in size everyday by GB and as the company expects to grow those GB will turn into 10s of GB and eventually hundreds of GB

Right now the database is running on raid 10 SSD's

Taking the following scenario

Its the first of the month and a customer wants to pull his records for last month so he can bill his customer.

This query will be directly IO bound because none of the data will exist in the Innodb buffer because this is the first time it is queried.

So lets say our query looks similar to

SELECT * FROM cdrs WHERE user_id='<SOME_USER_ID' AND start_time>=1654099200 AND start_time<1656691200

So we are grabbing all the call records for this user from the beginning of the month to the end of the month

In a test this resulted in 52,627,431 rows for one client and took 50 seconds to just run the query

SELECT Count(*) FROM cdrs WHERE user_id='<SOME_USER_ID>' AND start_time>=1654099200 AND start_time<1656691200;

I have looked around for different databases and I just don't know what direction I need to go in but we need the ability to pull these records in a much faster fashion currently just pulling an hour of records takes 30mins for some customers with higher volumes

Possible solutions that we have tried

Use a summary table

I know one suggestion will be using a summary table which we already use one but it does not help in this instance because customers need the actual data contained in this table

Partition the table

We partition the table by the month and the results are still to slow

I have tried to use a columnstore engine in mariadb and this actually gave us significant performance improvements in some areas but not specifically for getting the CDR's to our customers

I wish I could use Redis for this because Redis is lightning fast but tables take up 1.1T of data currently and will only continue to grow

Eager and open for a solution

mariadb mysql
1 Answers

PRIMARY KEY

WHERE user_id='<SOME_USER_ID>'
  AND start_time>=1654099200
  AND start_time< 1656691200;

is best handled by

PRIMARY KEY(user_id, start_time) -- in this order.

If a "user" can have two rows with exactly the same start_time, then

PRIMARY KEY(user_id, start_time, id) -- in this order.
INDEX(id)

Otherwise, consider getting rid of id.

Having the PK start with user_id puts all the rows for a given user clustered together, making that (and similar) queries faster.

Having the second part of the PK be start_time significantly helps with that BETWEEN.

PARTITION -- There are many "wrong" ways to use PARTITIONing. To discuss that, please provide more details. In general, Partitioning does not provide any speedup.

The main reason I see for using PARTITIONing is if you need to delete "old" data; DROP PARTITION is immensely faster than DELETEing millions of rows.

Data size

Many of the comments I will make here relate to how much disk space the table is/will take.

By "clustering" the data properly, I/O is decreased when the table is much bigger than RAM. What is the setting of innodb_buffer_pool_size? How much RAM do you have? How many GB will the table have before you start purging it?

Clustering may give you 10x speedup.

CHAR -- This is a fixed length datatype; it should be used only if the data is 'always' the length specified. Otherwise, it wastes space.

INDEXes -- Don't blindly add indexes for lots of columns. Only have the ones you need. "Need" comes from the queries you will be running. Consider using "composite" indexes where appropriate.

It seems like most SELECTs would include WHERE user_id = ...; if that is correct, I would expect many of the indexes to be "composite", starting with user_id.

Country -- Don't include a 4-byte INT and a multibyte string; simply have a CHAR(2) for the standard "country_code" and, if needed, a lookup table to map to strings.

Midnight -- When building a daily or monthly report, what should happen to a call that spills across midnight?

Summary tables -- A summary table should have worked quite well for your data. Please explain what data the report needs; I'll help redesign the summary table.

If the summary is all details for all calls for the month, then that is not a "summary"; the clustered PK gives you a 10x improvement.

If "summary" means daily subtotals, then let's see the details.

Numeric types -- INT takes 4 bytes; BIGINT takes 8 bytes. The "13" in bigint(13) has no meaning. If the value is really limited to 13 digits, then consider DECIMAL(13,0), which takes 6 bytes. See also MEDIUMINT and SMALLINT. DECIMAL(13,3) takes 7 bytes.

ENGINE -- Do use InnoDB.

user_17668310
Related