2

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).

  • Server main features: Linux OS-64 GB of memory- 3 Terabytes of hard drive.

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:

  • DB size: ~500 Gbytes
  • Largest table size: ~80 Gbytes
  • Index length (for largest table): ~230 Gbytes. This index relies on 6 fields.
  • Almost no MyISAM tables, all InnoDB

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
user3310782
  • 811
  • 2
  • 10
  • 18
  • 2
    DB tuning is a complex task, not only the server, but the query affect the performance. Right now you haven't tell us much beside the db size so isn't much we can do here. Tell us what is your query, what is the plan generated. Performance questions should include `EXPLAIN ANALYZE` and some information about table size, index, current time performance, desire time, etc. `Slow` is a relative term and we need a real value to compare. [**MySQL**](http://dba.stackexchange.com/questions/15371/how-do-i-get-the-execution-plan-for-a-view) – Juan Carlos Oropeza Jul 18 '17 at 16:14
  • Agreed DB tuning is complex, but the questions are pretty concrete I think. I don't include EXPLAIN_ANALYZE because I don't even know what that is. Regarding slowering: I'm talking of queries that read/join tables (one being up to 1 billion records), as explained taking *days* (several days) to run (used to be a few hours when the DB was smaller). – user3310782 Jul 18 '17 at 16:20
  • 2
    To those who would close this: This is probably NOT a server tuning issue and is very much about learning to use databases correctly. It should be left open. – Andy Lester Jul 18 '17 at 17:26
  • I already post the link for the `EXPLAIN PLAN` and if you havent use it is your problem is probably related to the index, so check those plan first. Also because your problem get worst when the db size increase also point to your queries doing full table scans *(again bad index)*. Also you can use partitioning to improve access to big tables https://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html – Juan Carlos Oropeza Jul 18 '17 at 18:37

4 Answers4

7

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
3

Here's the order you want to check things:

1) Tune your indexes. Pick a commonly-used slow query and analyze it. Learn about EXPLAIN ANALYZE so that you can tell if your query is using indexes properly. It is entirely possible that your tables are not indexed correctly, and your days-long queries might run in minutes. Literally. Without proper indexes, your queries will be doing full table scans in order to do joins, and with billions of rows, that's going to be very, very slow.

A good introduction to indexes is at http://use-the-index-luke.com/ but there are zillions of books and articles on the topic.

1a) Repeat #1 with other slow queries. See if you can improve them. If you've worked on a number of slow queries and you're not able to speed them up, then proceed to server tuning.

2) Tune your server. Bill Karwin's links will be helpful there.

3) Look at increasing hardware/RAM. This should only be last resort.

Spend time with #1. It is likely to return the best bang for the buck. There is much you can do to improve things without spending a dime. You'll also learn how to write better queries and create better indexes and prevent these problems in the future.

Also: Listen to Bill Karwin and his knowledge. He is an Expert with a capital E.

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
3

In a survey of 600 rather random tables (a few were much bigger than yours), your 230GB:80GB ratio would be at about the 99th percentile. Please provide SHOW CREATE TABLE so we can discuss whether you are "doing something wrong", or it is simply an extreme situation. (Rarely is a 6-column index advisable. And if it is a single index adding up to 230GB, something is 'wrong'.)

I've seen bigger tables run fine in smaller machines. If you are doing mostly "point queries", there is virtually no size limitation. If you are using UUIDs, you are screwed. That is, it really depends on the data, the queries, the schema, the phase of the moon, your karma, etc.

A cross-join can easily get to a trillion things to do. A join with eq_ref is often not much slower than a query with no joins.

"You can't tune your way out of a performance problem." "Throwing hardware at a performance problem either wastes money, or delays the inevitable." Instead, let's see the "queries that are slowing down", together with EXPLAIN SELECT ... and SHOW CREATE TABLE.

Is this a Data Warehouse application? Do you have Summary Tables?

Here is my Cookbook on creating indexes . But it might be faster if you show us your code.

And I can provide another Tuning Analysis .

EXPLAIN SELECT ..... is a critical part of information needed to investigate your request for assistance.

SHOW CREATE TABLE for each table involved would also be helpful.

At this point in time, neither are visible in the data available from user......

Wilson Hauck
  • 2,094
  • 1
  • 11
  • 19
Rick James
  • 135,179
  • 13
  • 127
  • 222
1

I will try to answer your question but keep in mind that I am no MySQL expert.

1) It is quite large DB with large table, but nothing fairly sized server couldn't handle. But it really depends on the workload you have.

2) The index size greater than table itself is interesting but it will probably be size of all indexes on that table. In that case it is completely normal.

3) 64 GB of RAM in your server means that there will be probably lot of disk operations going on and it will definitely slow you down. So adding some memory will surely help. Maybe check how the server behaves when the query is running with iotop. And compare it with information from top to see if the server is waiting on disks.

Marek Vitek
  • 1,573
  • 9
  • 20