mysql table with 550M rows with 128MB memory

Question

I would appreciate if someone could explain how is it possible MySQL is not churning with a large table on default config.

note: I don't need advice how to increase the memory, improve the performance or migrate etc. I want to understand why it is working and performing well.

I have the following table:

CREATE TABLE `daily_reads` (
  `a` varchar(32) NOT NULL DEFAULT '',
  `b` varchar(50) NOT NULL DEFAULT '',
  `c` varchar(20) NOT NULL DEFAULT '',
  `d` varchar(20) NOT NULL DEFAULT '',
  `e` varchar(20) NOT NULL DEFAULT '',
  `f` varchar(10) NOT NULL DEFAULT 'Wh',
  `g` datetime NOT NULL,
  `PERIOD_START` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `i` decimal(16,3) NOT NULL,
  `j` decimal(16,3) NOT NULL DEFAULT '0.000',
  `k` decimal(16,2) NOT NULL DEFAULT '0.00',
  `l` varchar(1) NOT NULL DEFAULT 'N',
  `m` varchar(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (`a`,`b`,`c`,`PERIOD_START`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It is running on a VM with 1 CPU Core, 6GB RAM, CentOS 7 (have very limited access to that VM).

It is running on a default MySQL config with 128MB buffer pool (SELECT @@innodb_buffer_pool_size/1024/1024)

DB size is ~96GB, ~560M rows in the 'reads' table, ~710M rows with other tables.

select database_name, table_name, index_name, stat_value*@@innodb_page_size
from mysql.innodb_index_stats where stat_name='size';

PRIMARY: 83,213,500,416 (no other indexes)

I get like ~500K reads/month and writes are done only as part of an ETL process directly from Informatica to the DB (~ 75M writes/month).

The read queries are called only via stored procedure:

CALL sp_get_meter_data('678912345678', '1234567765432', '2017-01-13 00:00:00', '2017-05-20 00:00:00');

// striped out the not important bits:
...
SET daily_from_date = DATE_FORMAT(FROM_DATE_TIME, '%Y-%m-%d 00:00:00');
SET daily_to_date = DATE_FORMAT(TO_DATE_TIME, '%Y-%m-%d 23:59:59');
...
SELECT
    *
FROM
    daily_reads
WHERE
    A = FRIST_NUMBER
    AND
    B = SECOND_NUMBER
    AND
    daily_from_date <= PERIOD_START
    AND
    daily_to_date >= PERIOD_START
ORDER BY
    PERIOD_START ASC;

My understanding of InnoDB is quite limited, but I thought I need to fit all indexes into memory to do fast queries. The read procedure takes only a few milliseconds. I thought it is not technically possible to query 500M+ tables fast enough on a default MySQL config...?

What am I missing?

note: I don't need advice how to increase the memory, improve the performance or migrate etc. I want to understand why it is working and performing well.

Answer 1

Long answer: Your primary key is a composite of several columns starting with a and b.

Your WHERE clause says this.

 WHERE a = FRIST_NUMBER
   AND b = SECOND_NUMBER
   AND etc etc.

This WHERE clauses exploits the index associated with your primary key very efficiently indeed. It random-accesses the index to precisely the first row it needs, and then scans it sequentially. So it doesn't actually have to page in much of your index or your table to satisfy your query.

Short answer: When queries exploit indexes, MySQL is fast and cheap.

If you wanted an index that was perfect for this query, it would be a composite index on (a, b, daily_from_date). This would use equality matching to hit the first matching row in the index, then range scan the index for your chosen date range. But the performance you have now is pretty good.

You asked whether the index must fit entirely in memory. No. The entire purpose of DBMS software is to handle volumes of data that can't possibly fit in memory at once. Good DBMS implementations do a good job of maintaining memory caches, and refreshing those caches from mass storage, when needed. The innodb buffer pool is one such cache. Keep in mind that any insertions or updates to a table require both the table data and the index data to be written to mass storage eventually.

Answer 2

The performances can be improved with some index.

In your specific case, you are filtering on 3 columns: A, B, and PERIOD_START. To speed up the query you can use index on this columns.

Add an index over PERIOD_START can be inefficient because this type stores TIME information, so you have a lot of differnt values in the same day.

You can add a new column to store the DATE part of PERIOD_START in the correct type (DATE) (something like PERIOD_START_DATE) and add an index on this column.

This makes a more effective indexing and this can improve the computation performance because you are using a look up table (key -> values).

If you do not want to change your client code, you can use a "Generated stored column". See MySql manual

Best regards

Answer 3

its possible your index is getting used (probably not given the leading edge doesnt match the columns in your query) but even if it isn't, you'd only ever read through the table once because the query doesn't have any joins and the subsequent runs would pick the cached results.

Since You're using informatica to load the data (its a swiss army knife of data loading) it may be doing a lot more than you realise e.g. assuming the data load is all inserts then it may drop and recreate indexes and run in bulk mode to load the data really quickly. It may even prerun the query to prime your cache with the first post load run.

Answer 4

Doesn't the index have to fit in memory?

No, the entire index does not have to fit in memory. Only the part of the index that needs to be examined during the query execution.

Since you have conditions on the left-most columns of your primary key (which is you clustered index), the query only examines rows that match the values you search for. The rest of the table is not examined at all.

You can try using EXPLAIN with your query and see an estimate of the number of rows examined. This is only a rough estimate calculated by the optimizer, but it should show that your query only needs to examine a small subset of the 550 million rows.

The InnoDB buffer pool keeps copies of frequently-used pages in RAM. The more frequently a page is used, the more likely it is to stay in the buffer pool and not get kicked out. Over time, as you run queries, your buffer pool gradually stabilizes with the set of pages that is most worth keeping in RAM.

If your query workload were to really scan your entire table frequently, then the small buffer pool would churn a lot more. But it's likely that your queries request the same small subset of the table repeatedly. A phenomenon called the Pareto Principle applies in many real-world applications: the majority of the requests are satisfied by a small minority of data.

This principle tends to fail when we run complex analytical queries, because those queries are more likely to scan the entire table.