How to increase database performance if there is 0.1M traffic

Question

I am developing a site and I'm concerned about the performance.

In the current system there are transactions like adding 10,000 rows to a single table. It doesn't matter it took around 0.6 seconds to insert.

But I am worrying about what happens if there are 100,000 concurrent users and 1000 of the users want to add 10,000 rows to a single table at once.

How could this impact the performance compared to a single user? How can I improve these transactions if there is a large amount of traffic like in this situation?

Answer 1

If you are trying to scale for inserting millions of rows per second, you have bigger problems. That could add up to trillions of rows per month. That's hundreds of terabytes before the end of the month. Do you have a big enough disk farm for that? Can you afford enough SSDs for that.

Another thing. With a trillion rows, it is quite challenging to have any indexes other than a simple auto_increment. Without any indexes, how do you plan on accessing the data? A table scan of a trillion rows will take day(s).

Also, you said 100,000 users; you implied that they are connected simultaneously? That, too, is a challenge.

What are the users doing to generate 10K rows all at once? What about the network bandwidth?

Etc. Etc.

If you really have a task like this, Sharding is probably the only solution. And that is in addition to SSDs, RAID, IOPs, etc, etc.

Answer 2

Few stuff that you must consider both from software and hardware point.

Things must consider :

1. Go for SSD drive to have better IO.
2. Good to have 10GB of network, if you have that huge traffic.
3. Use mysql 5.6 or above, they made good improvement on performance over previous version.
4. Use bulk inserts, instead of sequential one, and even better if you can store all data in a file and use load_data_infile. This would be 20 times faster then regular insert.

Mysql provide multiple ways to scaleout. Its depend upon on your product requirement which way you want to go.

Answer 3

When write speed is mandatory, the way we tackle it is getting quicker hard drives. You mentioned transactions, that means you need your data durable (D of ACID). This requirement rules out MyISAM storage engine or any type of NoSQL so I'll focus the answer towards what goes on with relational databases.

The way it works is this: you get a set number of Input Output Operations per Second or IOPS per hard drive. Hard drives also have a metric called bandwith. The metric you are interested in is write speed. Some crude calculation here would be this - Number of MB per second divided by number of IOPS = how much data you can squeeze per IOPS.

For mechanical drives, this magic IOPS number is anywhere between 150 and 300 - quite low. Given their bandwith of about 100 MB/sec, you get a real small number of writes and bandwith per write. This is where Solid State Drives kick in - their IOPS number starts at about 5 000 (some even go to 80 000) which is awesome for databases.

Connecting these drives in RAID gives you a super quick storage solution. If you are able to squeeze 10 000 inserts into one transaction, the disk will try to squeeze all 10k inserts through 1 IOPS.

Another strategy is partitioning your table and having multiple drives where MySQL stores the data.

This is as far as you can go with a single MySQL installation. There are strategies for distributing data to multiple MySQL nodes etc. but I assume that's out of scope of your question.

TL;DR: you need quicker disks.