Bizarre Mysql problem - slower results in more powerful server

Databases
1

Hi,
We have moved the same Mysql database to a MUCH more powerful server. On old server we have:

  • Mysql 5.1
  • 16 GB RAM
  • Red Hat 5.x

On new server we have:
Centos7, Mysql 5.7,
64GB RAM
and faster Hard disk too since it is SSD

Yet some key Mysql SELECTS are running MUCH slower on new server vs Old server, such as:

SELECT country FROM geo.ip_look WHERE ip_start <= 1453274624 AND ip_end >= 1453275135 ORDER BY id DESC LIMIT 1;

And we added Index to ip_start & ip_end
FYI, this table has about 3 Million records

What is going ON! How to fix this.

2

Well as you are probably realizing throwing raw server resources at a problem doesn’t always mean you get better performance. There are a number of other issues at play that can be causing problems including additional software on the server hogging resources, missing indexes, network performance problems and more. Obviously troubleshooting performance issues can be a full time job in itself (aka Database Administrators).

Perhaps take a look at this blog for some ideas…

But also take a look at the MySQL execution plan and perhaps compare it with the old server if you can. Maybe the difference in MySQL version means a different set of optimizations are being done and resulting in downgraded performance. Maybe your indexes are not correct or something similar.

https://dev.mysql.com/doc/refman/5.7/en/execution-plan-information.html

This might take some time to diagnose, just attack the problem pragmatically and do good testing. Usually you will hit one thing that resolves the entire problem. Good luck! :slight_smile:

3

Martyr,
Ok, Thanks

4

Marty,

I read those docx. Applied some changes, but still nothing :frowning:
So this being the SELECT:

SELECT zip_code, city_name, region_name, country_code, country_name FROM geo.iplocal WHERE ‘1759464552’ BETWEEN ip_from AND ip_to LIMIT 1;

and this Table has: 2675765 records
AND we have Index on: ip_from ip_to

It is currently taking 4 Seconds to get results back. Which is bad, since it is in a page where we have to have at least 30 such selects, which means 120 Sec for results to come back!

Do you have any concrete suggestions to improve this?

Thanks

5

Can you please run EXPLAIN SELECT zip_code, city_name, region_name, country_code, country_name FROM geo.iplocal WHERE '1759464552' BETWEEN ip_from AND ip_to LIMIT 1; on the server and show us the result?

1 Like
6

Considering the higher specs, and not mention the newer version of MySQL, I expect that all that there has not been much tuning done on the MySQL settings.

To get good performance of MySQL, it cannot be run with the “default install” settings. This is even more important the more hardware you have available. In some cases, you also need to tune the Linux settings, like how many files it can keep open, memory usage etc.

It is usually best to get this done by someone that really knows what they are doing. Though if you start reading different tutorials online on Tuning MySQL, or books on the subject you should be able to get it running good by try and trial.

7

WOW WOW! After much trying this and that, I on a whim removed the Indexes on all columns
And then the Select would return results in 0.2 Sec which is like 100 times faster than we had the Indexes on. And of course as we well know on many cases Indexes are what makes results 100 times faster.
I guess Use-the-index-luke should changed to Use-the-index-luke-but-not-all-the-time :slight_smile:

8

rpk, FYI as per your request:

+----+-------------+-----------+------------+-------+---------------+--------+---------+------+---------+----------+------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows    | filtered | Extra                  |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+---------+----------+------------------------+
|  1 | SIMPLE      | iplocal | NULL       | range | idx_ip        | idx_ip | 5       | NULL | 1402140 |    11.11 | Using where; Using MRR |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+---------+----------+------------------------+
1 row in set, 1 warning (0.00 sec)

Of course this is after we removed all Indexes but 1.

9

Sound like the planner decided to use a suboptimal index. Now that that index is gone it uses the correct index and that makes it fast again.

The comment from @TheRedDevil above still stands though. Using MySQL out of the box without any tuning will always be sup-optimal, and there will always be improvements possible by tuning.

10

You can be sure we are NOT “Using MySQL out of the box without any tuning”
We have been and are always seeking for ways to fine tune Mysql. Of course this thread was one jab for this purpose. And from this thread it is quite interesting, really astonishing, that we are told all along
“how Indexed can make Mysql SELECTS faster” which is for sure True in most cases, as one can achieve 100 time faster results with Indexes. But on rare occasions, as in this case Index had actually made this SELECT operation run almost 100 times slower! So for sure Mysql fine tuning is a combination of science and art and testing and repeating.

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.