We are using MySql.
My situation
-
I have a large number of tables with millions of rows each. Those tables are updated every second and are used both for adding info and retrieving info. Each table can be 5GB or 10GB or even more.
-
I have one table that I keep sums of information (something like a summary table of the information I need) but this is starting to get big in size as well.
My limitations
-
at the moment I cannot change database due to various reasons (mainly no knowledge, time and budget)
-
all the extra power that we add to the server goes to other resources needed so I cannot run very heavy queries
Temporary ways I have thought for scaling
Having these things in mind I am trying to think of ways to scale with what I have:
-
For the tables with millions of rows I have thought to keep to separate databases (could make my life easier for backups / exports / changes). Keep my main data in 1 database and all peripherals (huge tables) to other databases. Let’s say have a different database for a different need.
-
For the problem with the table that I really need regularly and is growing fast I was thinking into splitting it into XX tables. Could be 1 table per user (which might be too much) or 1 table per XXX users.
Are these ideas totally crazy and really bad DB design?
If yes….. any suggestions other than changing everything at once?
Best Answer
PARTITIONing
does not save space. Each partition has 4M-7M of "free" space. That can add up. Partitioning can be used for removing data from the server (or moving to another server) "old" data. More discussion: http://mysql.rjweb.org/doc.php/partitionmaintNor is any ad hoc partitioning likely to help. That is splitting tables up, moving between databases (I'm talking about
CREATE DATABASE
, not server) will not change performance, nor space. On the other hand, "easier for backups / exports" may be a valid reason. Please provide details so we can discuss."Sharding" is complex, but it is the way to split data across multiple servers. (Note: I am using "server" to mean a separate physical machine.)
A small fix is to shrink the datatypes in tables. Are you using
BIGINT
(8 bytes) whenMEDIUMINT
(3 bytes) would suffice? Etc, etc.Summary tables are an excellent way to save space and speed up "reports". But perhaps they are not as small as they could be? Let's see
SHOW CREATE TABLE
."growing fast I was thinking into splitting it into XX tables" -- NO, NO, NO! The only exceptions is when "sharding".
How do you populate the Summary Table(s)? Some tips: http://mysql.rjweb.org/doc.php/summarytables I assume your summary tables are updated either in realtime, or nightly? And you update them only incrementally, not by starting over.
You say 1 insert per second? That is a very low rate. Perhaps it is faster than that?
When the data is properly indexed and/or summarized, the size of the table does not matter (for performance). Are you concerned about disk space or speed?