Sorry for the long post!
I have a database containing ~30 tables (InnoDB engine). Only two of these tables, namely, "transaction" and "shift" are quite large (the first one have 1.5 million rows and shift has 23k rows). Now everything works fine and I don't have problem with the current database size.
However, we will have a similar database (same datatypes, design ,..) but much larger, e.g., the "transaction" table will have about 1 billion records (about 2,3 million transaction per day) and we are thinking about how we should deal with such volume of data in MySQL? (it is both read and write intensive). I read a lot of related posts to see if Mysql (and more specifically InnoDB engine) can perform well with billions of records, but still I have some questions. Some of those related posts that I've read are in the following:
- Can MySQL reasonably perform queries on billions of rows?
- Is InnoDB (MySQL 5.5.8) the right choice for multi-billion rows?
- Best data store for billions of rows
- How big can a MySQL database get before performance starts to degrade
- Why MySQL could be slow with large tables?
- Can Mysql handle tables which will hold about 300 million records?
What I've understood so far to improve the performance for very large tables:
- (for innoDB tables which is my case) increasing the
innodb_buffer_pool_size(e.g., up to 80% of RAM). Also, I found some other MySQL performance tunning settings here in percona blog
- having proper indexes on the table (using EXPLAN on queries)
- partitioning the table
- MySQL Sharding or clustering
Here are my questions/confusions:
About partitioning, I have some doubts whether we should use it or not. On one hand many people suggested it to improve performance when table is very large. On the other hand, I've read many posts saying it does not improve query performance and it does not make queries run faster (e.g., here and here). Also, I read in MySQL Reference Manual that InnoDB foreign keys and MySQL partitioning are not compatible (we have foreign keys).
Regarding indexes, right now they perform well, but as far as I understood, for very large tables indexing is more restrictive (as Kevin Bedell mentioned in his answer here). Also, indexes speed up reads while slow down write (insert/update). So, for the new similar project that we will have this large DB, should we first insert/load all the data and then create indexes? (to speed up the insert)
If we cannot use partitioning for our big table ("transaction" table), what is an alternative option to improve the performance? (except MySQl variable settings such as
innodb_buffer_pool_size). Should we use Mysql clusters? (we have also lots of joins)
Thanks for your time,