Hello,
I saw many configuration, benchmark and tunning related articles or posts, but I have a question none of them has answered nor the documentation of MySQL, Percona or MariaDB, I don’t know if this is because the question itself is illogic or nonsesne, but I need to ask.
Is needed that the server has enough RAM to fit all the data stored in the database tables? What is the maximum size of data can be stored in the database in relation to ram installed in the server: half of the ram, same of the ram, double of ram o no matter the amount of ram installed if the server is properly tuned
Regards
Hello @Dario_Q_T
Absolutely not. We have customers with multi TB of data on a single MySQL server. You can’t put that amount of memory in most servers.
There is not a maximum. You can have a 4TB database with only 2GB of RAM. If you only run 100 read-only queries per second, that will work just fine. But if you want to run 1000s of queries and connections, you’ll need more memory.
The bottom line is “you need as much memory as your active dataset”. If you have 4TB of data, you will never be accessing all 4TB at the same time. Typically, you’d see about 10-20% of the total dataset as “active” (meaning reads/writes). That’s the amount of RAM you’d need.
For a more concrete answer, you’d need to graph the state of InnoDB’s buffer pool. If the hit rate is 95% or higher, then you have enough memory. If below 80%, you might want to add more memory.
Thanks for your quick answer.
How I can measure the ‘active’ dataset?
In this moment I have a innodb_buffer_pool of 15Gb (32Gb of installed RAM).
There are some mysqltunner results 8 days after importing the last db:
[–] Reads / Writes: 97% / 3%
[–] Binary logging is disabled
[–] Physical Memory : 31.3G
[–] Max MySQL memory : 21.6G
[–] Other process memory: 0B
[–] Total buffers: 15.2G global + 42.7M per thread (151 max threads)
[–] Performance_schema Max memory usage: 104M
[–] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 16.9G (54.15% of installed RAM)
[OK] Maximum possible memory usage: 21.6G (69.08% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (15K/1B)
[OK] Highest usage of available connections: 25% (39/151)
[OK] Aborted connections: 0.04% (457/1134094)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (183K temp sorts / 272M sorts)
[!!] Joins performed without indexes: 3913175
[OK] Temporary tables created on disk: 2% (4M on disk / 182M total)
[OK] Thread cache hit rate: 99% (269 created / 1M connections)
[OK] Table cache hit rate: 99% (2B hits / 2B requests)
[!!] table_definition_cache (3500) is less than number of tables (3844)
[OK] Open file limit used: 0% (14/256K)
[OK] Table locks acquired immediately: 100% (97K immediate / 97K locks)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[–] InnoDB is enabled.
[–] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 15.0G ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 15.0G / 17.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (66.6666666666667%): 5.0G * 2 / 15.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 12
[–] Number of InnoDB Buffer Pool Chunk: 60 for 12 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (375743838302 hits / 375745385510 total)
[OK] InnoDB Write Log efficiency: 93.34% (350785524 hits / 375822605 total)
[OK] InnoDB log waits: 0.00% (0 waits / 25037081 writes)
I do suggest you switch from mysqltuner to something like PMM. tuner cannot give you information based on trends over time. it’s just a 1-shot tool.
That said, the last 3 lines of the output show you are fine. You have 100% hit rate on reads and your db is 97% reads.
[!!] Joins performed without indexes: 3913175
This is far more concerning than your buffer pool size. You need top optimize your queries to use indexes. No amount of ram will fix a bad query. Take each query in your application and run EXPLAIN on it. Any queries that show ‘type: ALL’ are full table scans and need indexes.
Thank you matthewb,
I’m not a programmer, I know only enough of programming to barely know what some code is doing, I’m only the sysadmin… So the server’s problems, blame on me whether or not it’s really my fault.
That sentence about the joins (or something very similar) was the first words I said to the web developers that are using the server, and their answer was literally “reduce the join requests is impossible and modifing that is out of any negotiation, you (for me) as the ‘server maintainer’ are entirely responsible for the proper and smooth working of the server”
Is there any way I can prove or tell to them the importance of optimizing this kind of queries? Also is there any way i can log some of that queries?
As far I can see in the server are a few prestashops, one wordpress and one custom webpage, maybe they aren’t even making the queries by themselves because they are using that kind of platforms.
If that were the case can the queries be optimized in that kind of webpages?
Let’s clarify that in my previous comment, I did not recommend reducing joins. What I said was “optimize your queries to use indexes”. JOINs are perfectly normal, and in fact, you should rewrite all subqueries to use JOINs whenever possible.
The problem your app team faces is their JOINs are not using indexes. That’s what the stat shows.
SELECT * FROM foo LEFT JOIN bar WHERE foo.id = bar.baz;
In this example query, id is the PK of foo, but baz is just a secondary column of bar. If baz does not have an index, MySQL will have to perform a FULL TABLE SCAN on bar to find all matching values of baz.
This is what your app team needs to fix and this is 100% their responsibility.
as the ‘server maintainer’ are entirely responsible for the proper and smooth working of the serve
This is correct. You as the sysadmin are responsible for keeping the server online and handling configuration, HOWEVER there is no configuration parameter to magically fix bad queries.
Your app team is still completely responsible for the application queries and their performance. They need to learn how to use EXPLAIN in MySQL to determine if queries are performing full table scans, or using proper indexes.