2

I am going to do some performance tests on MySQL server as I want to evaluate how MySQL behaves in some situations. MySQL is the only (additional) software installed on my server (Windows Server 2012), so no other process is going to disturb it.

Currently, I have default settings on my MySQL instance - the only setting I've changed is a mode when you install it - "standalone" mode, if I remember correctly.

Which settings should I change? I am mostly interested in giving MySQL as much resources as it can take - I really want to give it everything possible.

Can you point me to some settings, manuals, etc.?

I will be testing on multiple hardware configurations (from commodity hardware (starting with i7-based laptop) to Gen9 HP Servers with "top-notch" disks (througput > 1GB/s)).

I will be testing on MySQL 5.7 and InnoDB engine.

I am going to test some SQL statements on a fairly big dataset (2-3 15+ million rows tables). I will be testing (BULK) INSERT, SELECT, UPDATE and DELETE statements.

Improve this question

1 Answer 1

Reset to default
1

The answer to this question varies depending on what database engine backend you're using.

If you're wildly unfamiliar with all of them, I really recommend that you use TokuDB, because it's entirely self-configuring with no modifications necessary out of the box.

I use MariaDB (MySQL clone) with Aria tables. My configuration would be totally, and completely different from yours.

If you use InnoDB, set innodb_buffer_pool_size to 80% of what you find appropriate for a database to be using, up to a maximum of 10% larger than your data size.

If you use TokuDB, use stock config.

If you use MyISAM, may Loki have mercy on your soul.

Improve this answer
2
  • >If you use InnoDB, set innodb_buffer_pool_size to 80% of what you find appropriate for a database to be using, up to a maximum of 10% larger than your data size. I, in fact, use InnoDB. Why do you recommend "maximum of 10% larger than your data size"? What would happen if I set more? When I can afford it, of course (so other processes still get enough memory)? What are other useful settings for InnoDB? Thanks!
    – user3652540
    Commented Feb 13, 2016 at 17:57
  • 10% greater than your data size allows for growth. If you set more, you are just needlessly wasting ram.
    – Moscato
    Commented Feb 13, 2016 at 20:10

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