mysqlsqloptimizationdatabase-administrationdatabase-tuning

Speed and tuning for mySQL (1billion rows)


My company has a mySQL server used by a team of analysts (usually 3-4 at a time). Lately the queries have slowed down, with some of them taking even days, for a database with tables up to 1 billion rows (10^9 records).

We know nothing of fine tuning, so any tool/rule of thumb to find out what is causing the trouble or at least to narrow it down, would be welcome.

Going to Workbench studio>Table inspector I found these key values for the DB that we use the most:

Ideally I would like to fine tune the server (better), the DB (worse), or both (in the future), in the simplest possible way, to speed it up.

My questions:

  1. Are these values (500, 80, 230 GB) normal and manageable for a medium size server?
  2. Is it normal to have indexes of this size -230Gb-, way larger than the table itself?
  3. What parameters/strategy can be tweaked to fix this? I'm thinking memory logs, or buying server RAM, but happy to investigate any sensible answers.

Many thanks.


Solution

  • If you're managing a MySQL instance of this scale, it would be worth your time to read High Performance MySQL which is the best book on MySQL tuning. I strongly recommend you get this book and read it.

    Your InnoDB buffer pool is probably still at its default size, not taking advantage of the RAM on your Linux system. It doesn't matter how much RAM you have if you haven't configured MySQL to use it!

    There are other important tuning parameters too. MySQL 5.7 Performance Tuning Immediately After Installation is a great introduction to the most important tuning options.

    Indexes can be larger than the table itself. The factor of nearly 4 to 1 is unusual, but not necessarily bad. It depends on what indexes you need, and there's no way to know that unless you consider the queries you need to run against this data.

    I did a presentation How to Design Indexes, Really a few years ago (it's just as relevant to current versions of MySQL). Here's the video: https://www.youtube.com/watch?v=ELR7-RdU9XU