MySQL maximum number of rows before performance seriously degrades

Question

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?

Answer 1

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.