After upgrading Mysql 5.5 to Percona 5.7 application move very slow. I am unable to optimize the query because are compiled with the application The only thing I can do is to optimize the MySQL server (or the server config)
I need to mention that I see some query that returns lots of data (70M of result) Data dir is on SSD. I have 32G RAM, but I allocate 20 for Mysql (60% because we have also other small services)
I notice a small improvement if I activate cache (that will be deprecated)
SHOW GLOBAL STATUS result is here
Do someone have some idea what to improve to MySQL config / or server to improve performance? I put an SQL with explain as an example. It returns 672 rows of data but takes 30 seconds (29 stays in "Sending data").
explain select l.idCodeLocation as idLocation, l.txLocation as name, l.radius as radius, l.amLat as lat, l.amLong as lng, g.isin as isin, g.isout as isout, g.onval as onval, m.isGeofenceIn as isGeofenceIn, m.geofenceInTime as geofenceInTime, m.isGeofenceOn as isGeofenceOn, v.idVeh as idveh, v.idPlateVeh as plate, v.idClient as client from sat_geofence g left join sat_clientLocation as l on (g.idLocation=l.idCodeLocation) join sat_geofence_vehicle_mtm as m on (g.idLocation=m.idLocation) join sat_vehicle as v on (m.idVeh=v.idVeh);
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+--------+----------+----------------------------------------------------+
| 1 | SIMPLE | g | NULL | ALL | idx | NULL | NULL | NULL | 38 | 100.00 | NULL |
| 1 | SIMPLE | m | NULL | ref | idx | idx | 768 | Stdb.g.idLocation | 5 | 100.00 | Using index condition |
| 1 | SIMPLE | v | NULL | eq_ref | PRIMARY | PRIMARY | 3 | Stdb.m.idVeh | 1 | 100.00 | Using where |
| 1 | SIMPLE | l | NULL | ALL | NULL | NULL | NULL | NULL | 116952 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+--------+----------+----------------------------------------------------+
the tables structure I put here: https://gist.github.com/costyoancea/deee560f9c94aa2463162d7ce0556392
Thanks, Costi
You are MISSING an index on m.idVeh. m references sat_geofence_vehicle_mtm. Make additional simple index with this in the First position, please. For tuning assistance view my profile, Network profile for contact info, please. And check out my reviews on the Shop page.