This question already has an answer here:

Note: this is a hypothetical scenario

Lets say that:

  • I'm running MySQL, InnoDB engine, UTF-8 encoding
  • I have a table with 50+ billions of rows
  • The tables primary key is a BIGINT
  • The table also has 4 other columns, 3 of which are indexed (a BIGINT, TINYINT, and a VARCHAR(20)) ... (the unindexed column is type TEXT)

My questions are:

  1. How fast would the queries be when selecting a row via primary key, or via indexed columns? What kind of hardware/server/s would be needed in order to make these queries execute faster than 0.1s?
  2. Should I be using some other storage engine for this?

marked as duplicate by Max Vernon, Marian, RLF, Mark Storey-Smith, RolandoMySQLDBA mysql Aug 7 '14 at 19:05

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from Apr 1 '14 at 18:58

This question came from our site for professional and enthusiast programmers.

  • why dont u use explain command to check some queries and post the result here. – Abhik Chakraborty Apr 1 '14 at 17:36
  • Normally execution time depends on query estimation and hardware capabilities. if you are adding fully optimized query and yet delay you have to upgrade hardware. still hard to give exact ans without the query. – ambarox Apr 1 '14 at 17:39
  • Do you actually have this much data, or is it theoretical at this point? You may be prematurely optimising, and if so, I wouldn't worry about whatever it is you are worrying about. As Abhik says, if we can see your schema and explain plan, that will help. – halfer Apr 1 '14 at 17:41
  • This is just a hypothetical scenario... Lets say that the table looks something like this: id - BIGINT(20) PRIMARY; text - TEXT; number - TINYINT INDEX; characters - VARCHAR(20) INDEX; bignumber - INT INDEX; no UNIQUE indexes... – user3486101 Apr 1 '14 at 17:49
  • Please come with a real problem, not some hypothetical scenario. – Colin 't Hart Aug 7 '14 at 16:34

Let's do a hypothetical answer to a hypothetical question (all of this are ball-park calculations, I do not intend to be very exact, but to have a big picture):

Your table, at the bare minimum, will have a storage requirements of:

50 000 000 000*(8 + 8 + 1 + 1 + 20*3) / 1024^4 ~ 4TB

I do not have into account the TEXT because, even if it can be inline, if it is too big it goes off-page (so funnily enough, it won't be a problem or a performance penalty unless it is selected or modified).

The secondary indexes (I do not have into account the primary key, as the data is clustered around it, but obviously, it will take even more space than just the data) will take at least:

50 000 000 000 * ( (8 + 8) + (8 + 1) + (8 + 1 + 20 *3) ) ~ 4TB

And that is being too optimistic.

As that cannot be realistically into the main memory (largest deployments of traditional SMP architectures can only integrate around 4TB, I do not consider other architectures with its own problems), a single access though primary key may require ~25 theoretical disk hits (double it for secondary keys), which with fast enough disks (a huge RAID of SSDs) and disabling large read-ahead caching to the buffer pool and other tweaks, in a single-thread read-only load: why not?

The problem is that InnoDB was designed assuming that the buffer pool being able to cache at least the most used/upper parts of the primary key, which -assuming you can select any row of the table- would create an excessive amount of traffic between the main memory and disks. With the more and more popular flash drives, some parameters of InnoDB have been tuned to allow faster and faster disk access.

In fact, in reality your biggest problem would be inserting. You probably won't be able to have such a tall table due to the increasing performance penalty of checking for uniqueness on the PK for every row inserted. That is why InnoDB works very well when the working set is mainly on the buffer pool, but not when it goes beyond that. Additionally, structures like the change buffer, transaction log etc. would be easy overloaded on insertion. You can see a big slowdown on the blue line once a threshold is surpassed:

InnoDB vs. TokuDB insert performance Note: the previous image stops inserting into InnoDB when the table it reaches ~60GB.

In a nutshell, InnoDB is not designed for that kind of operations. Solutions? Splitting the table is usually the number one call- specially because partitions tend not to be access with equal frequency. Compression could help with lower storage requirement, but it may change the bottleneck to the CPU on inserting, and it may make things worse regarding caching.

Probably the best option would be to go for a different engine, which works well for lots of rows that is disk-bound. TokuDB is one of the engines that has been heard a lot lately that tries to cover that market gap, and has some interesting benchmarks.

You have 50+ billion records in a table in MySQL? Not only do I doubt that, but I would also say that if it is true, you probably need to normalize your system.

also a possible duplicate of: see Ash's response about horizontal partitioning. This will help cut down query execution times on that large of a system.

  • 2
    What do you mean with "you probably need to normalize"? Normalization (if the design is not normalized) usually leads to more tables and sometimes some of them having more rows than before. – ypercubeᵀᴹ Apr 1 '14 at 19:24
  • You are correct, sometimes. Always ask yourself, can object A ever have more than one of attribute x. If so, chuck it into another table. In this instance, having 50+ billion records in a single table means that you are either duplicating information or you should be splitting off chunks of that into other partitions. – VikingBlooded Apr 2 '14 at 21:22
  • 1
    "Chuck"? Is that a new normalization technique? – ypercubeᵀᴹ Apr 2 '14 at 22:09
  • no, it's just common vernacular in the U.S. – VikingBlooded Apr 2 '14 at 23:49

Primary key look-up will much faster than indexed columns. For the hardware question if your database fits on memory then searching by primary key only one row then you can achieve 0.1 secs.

Not the answer you're looking for? Browse other questions tagged or ask your own question.