3 ms·
You should be using an APM like NewRelic to find those slow queries. If that's not an option you should have a profiler that tracks how long each query took and
by hparadiz 5y ago
You should be using an APM like NewRelic to find those slow queries. If that's not an option you should have a profiler that tracks how long each query took and have it print out the query to a log. Turn it on for a couple hours during peak time and analyze it for slow queries.
Also use static table design when applicable and avoid running any query that locks the table up. If the query takes any longer than 500 ms you should be running it as a background job instead.
- toast0 5y agoFinding the bad queries is easy --- mysql has a slow query log since forever. The problem is finding where the query was generated. And convincing the people who wrote the bad query to use a better one. Even without that, show process_list or whatever it is tells you how long the query has been running, with mytop you can just look for anything that's been running for more than a couple screen refreshes and start yelling from there. Usually, when I'm brought in, somebody else designed the tables, poorly, too. There's usually not a whole lot I can do about that, because I don't usually have enough authority to demand a data migration.
- hparadiz 5y agoThat's very true and NewRelic has a built in trace route as well as a way to sort and view the slow queries. Even breaks down and does an analysis on the query. Coupled with logging down to the individual thread. It's very powerful.