1

I'm looking or willing to do a script to benchmark mysql config variations for a specific system.

A script that would change certain mysql config values and do a sysbench, save results, change config and restart service, and test again.

max_connections = A
table_open_cache = B
query_cache_type = C
innodb_buffer_pool_size = D
innodb_buffer_pool_instances = E
innodb_adaptive_hash_index_partitions = F

...


And the script does a sysbench looping through all possibilities that make sense:

  • on A takes values of 50 100 200 250
  • on B takes values of ....

I know it depends on system, but it would be great to have a recommended "set" of values / ranges to test. (Even check the current free RAM and do a small CPU bench and with this values create the set of values to test)

Is there anything outside that can acomplish this?

Would it be useful for you or it's not worth doing it?

3 Answers 3

3

Such a tool would be almost completely worthless in practical use.

max_connections has nothing to do with performance. It's a safety mechanism to prevent a runaway application from making so many connections that the server becomes overwhelmed. The value makes absolutely no difference unless you are using that many connections. It is also a value that you should never reach under normal operating conditions.

table_open_cache I had hoped by now that everybody knew just to leave this one alone. It tends to scale negatively -- the more "sensible" the value seems to be, the more wrong you are. The worst performing servers in my entire network were the ones where someone, at some time in the past, had felt a need to mess with this.

query_cache_type just set it to 1, with a reasonable size, something in the 32 to 128 MiB range, and walk away. You will be hard-pressed to develop a meaningful benchmark here, and a small query cache definitely has its value for many workloads. But keep it reasonably small, or turn it off. Above a certain size, it will scale negatively and degrade performance. The more processor cores you have, the more potential exists for the query cache mutex to become a bottleneck. For workloads where there is virtually no chance of a cache hit, it's a waste of cpu cycles.

innodb_buffer_pool_size has only one correct answer: How much memory can you spare? Set that here. Lower will always be slower, and higher will also be slower if the server goes into swap, and of course infinitely slower if it crashes because the system ran out of memory entirely. Correlated to this, if you're running anything else on your database server, you are doing it wrong.

innodb_buffer_pool_instances As long as each pool is at least 1GB, there's not a lot of variation available or needed here on a decent sized server.

innodb_adaptive_hash_index_partitions is probably a poster child for an argument that applies in some sense to all of the other values above, and some not mentioned: even if you tweaked this to find a sweet spot with sysbench... who cares? How does that help you? It doesn't.

Your specific workload is the only thing really relevant to the ideal values for the adaptive hash index, the query cache, and so many of the other tweakable parameters on MySQL Server.

Given two servers with "identical everything" -- storage, memory, CPUs, background cosmic radiation levels -- identical everything, that is, except the workload (meaning the schemata, datafill, and the actual queries being run) the "optimum" configuration parameters may still be wildly divergent.

Benchmarking with anything other than a real live workload will not give you anything of significant value, and tweaking a server is not usually the ticket to performance.

Well-written queries against well-structured schemata and appropriately indexed tables, and use of read replicas, will take you much further than tweaking.

0
0

Im not sure about a script but you can build your own based on MySQL recommendations and good practices shared by experts here:

Make sure to adapt your script by taking into consideration the previous links.

2
  • I'm been working for years on such a tool; it is still a "work in progress". Meanwhile, I accept RAM size + SHOW VARIABLES + SHOW GLOBAL STATUS for analysis. Unfortunately, I still have to tweak the automated output to get rid of the 'bad' advice that shows up in quirky situations.
    – Rick James
    May 1, 2016 at 21:38
  • And I threw my 2-cents onto most of those links.
    – Rick James
    May 1, 2016 at 21:39
-2

I have been using mysqltuner.pl from mysqltuner.com for years. It's not automatic, it analyses and makes recommendations. Good for part time dba's like me.

1
  • mysqltuner's big mistake is to always encourage you to optimize your tables. I have repeatedly ranted against that.
    – Rick James
    May 1, 2016 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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