1

I'm suffering a hard time because of a university project. The goal is to retrieve measurement values from a field test and to store them in a db. Upon those stored values calculations shall be executed...

The current DB scheme has one table for incoming data values, called data:

+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | bigint(20) | NO   | PRI | NULL    | auto_increment |
| data_node_id | bigint(20) | NO   | MUL | NULL    |                |
| timestamp    | datetime   | NO   | MUL | NULL    |                |
| value        | float      | YES  |     | NULL    |                |
+--------------+------------+------+-----+---------+----------------+

This table holds about 1.7 billion records so far and is ~165GB in size.

Now I'm trying to do some simple queries to extract data for research like:
SELECT timestamp, value FROM iren2.data WHERE data.timestamp >= now() - INTERVAL 7 DAY AND data_node_id = 114
Those queries take more than 10 minutes to finish :( Is there any way to speed up things? Did I miss something in the scheme design?

By the way, the db is a MySQL 5.7 Community Edition running on a Ubuntu 16.04 HyperV machine with 10 vCores (Intel® Xeon® Processor E5-2698 v3) and 256GB Memory.

Thanks for your help! :)

EDIT
Some more information on config:

innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 216G
innodb_thread_concurrency = 0
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048
  • Is this table partitioned? if not please do partitioning using partition key timestamp. In case you always get one week's data, you create weekly partitions and the composite index on the columns with the order (data_node_id, timestamp). Also rather than having open filter of > now() - INTERVAL 7 DAY, change your query to use between clause as between (now() - INTERVAL 7 DAY) and now(). This will reduce the partitions to be scanned. Hope this helps. – SQL.RK Jan 18 '17 at 12:40
  • 1
    Please provide SHOW CREATE TABLE; it is more descriptive than DESCRIBE. – Rick James Jan 18 '17 at 20:19
1

You may need to add proper indexes to your data... My guess is that you need a multi-column index on (data_node_id, timestamp).

Check the mySQL documentation on Multiple-Column Indexes

--

Side note:

I suspect that a data_node cannot have two readings at the same time. That would make the (data_node_id, timestamp) pair always unique (and not null). If this were the case, I would use this as the primary key, and take out of the table the id column (and the corresponding index). Taking out the surrogate id key will save some data that doesn't need to be read... if you're looking for the last bit of performance, it also helps.

As mySQL + innoDB keeps your data clustered by primary index, having (data_node_id, timestamp) as your PK would clearly be an advantage for retrieving information. Like always, it comes with a price: INSERTS will cause fragmentation (and thus, be more expensive), because your PK will not be an increasing number.

  • Adding the composite index did the trick! Thank you! But dropping the id column does not work: mysql> ALTER TABLE iren2.data DROP column id, DROP primary key, ADD PRIMARY KEY(data_node_id ASC, timestamp ASC); ERROR 1062 (23000): Duplicate entry '307-2016-05-19 18:58:03' for key 'PRIMARY' – Fmeuer Jan 18 '17 at 14:05
  • That means my assumption about (data_node_id, timestamp) being unique is then false... pity. – joanolo Jan 18 '17 at 15:06
  • The "fragmentation" mentioned will not be bad enough to worry about. There will be 3000 blocks being inserted into; those will easily be cached, so no problem there. The blocks will probably be less than full, so the disk footprint will be bigger; perhaps 40% bigger. – Rick James Jan 23 '17 at 17:55
1

Further improvement...

How many different data_node_ids are there? Probably less than 4 billion. BIGINT takes 8 bytes; INT UNSIGNED takes 4. MEDIUMINT UNSIGNED - 3 bytes 16M values. Etc. Shrinking id will save twice: once in the PK&data and once in the secondary index; this will help as the table grows.

The following will "cluster" the query's rows together:

PRIMARY KEY(data_node_id, timestamp, id) -- unique and consecutive
INDEX(id)  -- to keep AUTO_INCREMENT happy

If you are not using id for anything else, the secondary index will not need to be cached, except for the 'last' block. That is, with this formulation of indexes, you could probably work adequately efficiently in a much smaller RAM.

What do the queries do? Are you doing SUM and other aggregates? If so, I suggest looking into "Summary Tables". Such a table might have (hour, node_id, avg_value, max_value, etc). It might make your queries run another 10x faster. After initially building the table(s), you would augment them hourly. See: http://mysql.rjweb.org/doc.php/summarytables

PARTITIONing is not warranted for this query. Indexing does a better job.

  • Hi Rick, you are totally right. There are only about 3000 data_nodes. Until now I didn't do aggregates on queries, because they were so slow. Filtering and aggregation is done in application code. – Fmeuer Jan 23 '17 at 8: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.