MySQL maximum number of rows before performance seriously degrades

user_4698543 0
Viewed 1462

I'm looking to understand at what point our log table will become unusable.

The log table has been growing since the inception of the table. We're currently at 1.2 billion rows. It has 3 indexes that allow us to query it quickly provided we're timeboxing the amount of data we're requesting.

We don't plan on altering the schema, using any join queries that touch this table, or anything besides our querying on account activity based on a timeframe which are columns included in our index.

I've dug around the MySQL documentation regarding InnoDB table limits (https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html) and determined that the upper limit of 64TB is not currently a concern.

The plan is ultimately to offload logging to another tool and archive old logs that are not pertinent.

Does anyone have any experience or documentation that would help me determine how long we have until we have a severe performance problem?

Things that I'm currently concerned about are:

  • At what point will we have an issue with inserts becoming long running actions
  • Is there any scenario that the size of the index becomes too large that will cause serious performance issues
  • Are there any other red flag issues that I should be worried about?
innodb mysql
2 Answers

When the commonly used parts of the index(es) can no longer reside in the innodb buffer pool, queries will start to use a lot more IO.

A discussion on innodb tree length gives an indication as to how many read pages are required to be read for a single lookup but as you can see the B+ tree is quite efficient. Obviously keeping the commonly non-leaf nodes in the buffer pool tool is ideal.

So in general watch the Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads ratio on the status variables and when this starts to fall, consider more RAM.

user_101951530

Likely ways to help:

  • Avoid queries that need to touch lots of rows. Consider using "Summary Tables" to keep daily (or hourly or whatever) subtotals of whatever.
  • 3 indexes is part of the problem; summary table(s) may help eliminate some of them. But keep the PRIMARY KEY. Different indexes may help.
  • Shrink datatypes to decrease I/O, hence be slower.
  • If fields are frequently repeated, normalize and use JOINs; it is likely to help significantly.

Likely ways not to help:

  • Partitioning won't help unless you need to purge 'old' data after some time.

How long before trouble?

  • Depends on columns
  • Depends on RAM size
  • Depends on complexity of queries
  • Depends on other things.
  • INSERTs are not likely to be troublesome unless you use UUIDs.
  • But -- Summary tables can usually postpone disaster for a long time -- perhaps 10 times as long.

Details. Without more details, I can't help you more.

  • SHOW CREATE TABLE
  • Some statistics on rate of ingestion, etc
  • The typical queries
  • Etc.

A Rule of Thumb... The typical InnoDB BTree (data or index) has a fan-out of 100. That is each node has 100 'rows' under it. Hence, your table will (probably) be about 5 levels deep. Ditto for the indexes. Usually the depth of a BTree is not critical to any performance discussion.

A Rule of Thumb... Set innodb_buffer_pool_size to about 70% of RAM.

user_17668310
Related