SQL performance improvements: finding the right queries to fix
1–7 of 7 posts
Re: SQL performance improvements: finding the right queries to fix
#2What Ive been really looking for is a way to run SQL queries against MySQL 8.0 Inno DB without them hitting the cache.
Its tricky to validate improvements real time but I guess validating in prod is good too
Re: SQL performance improvements: finding the right queries to fix
#3Are SELECT queries most companies' bottleneck? While we find the occasional SELECT without an index or joining something it doesn't need to, my biggest struggle is optimizing UPDATES.
In our busiest times we need to update up to 100,000 rows, ~50 columns, every ~5 seconds. So ideally the update finishes before the next one starts to avoid deadlocks.
MySQL probably isn't the most ideal DB for our use-case, but it's what the startup had been using for years before I joined.
Re: SQL performance improvements: finding the right queries to fix
#4Are SELECT queries most companies' bottleneck? While we find the occasional SELECT without an index or joining something it doesn't need to, my biggest struggle is optimizing UPDATES. In our busiest times we need to update up to 100,000 rows, ~50 columns, every ~5 seconds. So ideally the update finishes before the next one starts to avoid deadlocks. MySQL probably isn't the most ideal DB for our use-case, but it's wh…
Totally depends on the use case I suppose, we found that in our environment, we perform _a lot_ more SELECT's than we do UPDATE/DELETE/INSERT's.
And with some badly optimized SELECT's, the time MySQL had to spend on sorting results/reading from disk in an inefficient way made all our _write_ queries suffer.
By optimizing our SELECTs first, we freed up some CPU bandwidth (it seems?) that can be spent doing all the other work.
Re: SQL performance improvements: finding the right queries to fix
#5What Ive been really looking for is a way to run SQL queries against MySQL 8.0 Inno DB without them hitting the cache. Its tricky to validate improvements real time but I guess validating in prod is good too
Have you been able to try this with a `SELECT SQL_NO_CACHE * FROM ...` query?
If it's for local testing, you could try to cripple InnoDB as much as possible by just setting some absurdly low values, that would almost certainly mean no InnoDB caching is happening; https://gist.github.com/mattiasgeniar/87cd4a10bfcc788d81b51f...
Re: SQL performance improvements: finding the right queries to fix
#6Are SELECT queries most companies' bottleneck? While we find the occasional SELECT without an index or joining something it doesn't need to, my biggest struggle is optimizing UPDATES. In our busiest times we need to update up to 100,000 rows, ~50 columns, every ~5 seconds. So ideally the update finishes before the next one starts to avoid deadlocks. MySQL probably isn't the most ideal DB for our use-case, but it's wh…
Totally depends on the use case I suppose, we found that in our environment, we perform _a lot_ more SELECT's than we do UPDATE/DELETE/INSERT's. And with some badly optimized SELECT's, the time MySQL had to spend on sorting results/reading from disk in an inefficient way made all our _write_ queries suffer. By optimizing our SELECTs first, we freed up some CPU bandwidth (it seems?) that can be spent doing all the oth…
If you misuse a database you will have a lot more complex SELECTs than UPDATEs perhaps ;-)
Re: SQL performance improvements: finding the right queries to fix
#7Are SELECT queries most companies' bottleneck? While we find the occasional SELECT without an index or joining something it doesn't need to, my biggest struggle is optimizing UPDATES. In our busiest times we need to update up to 100,000 rows, ~50 columns, every ~5 seconds. So ideally the update finishes before the next one starts to avoid deadlocks. MySQL probably isn't the most ideal DB for our use-case, but it's wh…
Totally depends on the use case I suppose, we found that in our environment, we perform _a lot_ more SELECT's than we do UPDATE/DELETE/INSERT's. And with some badly optimized SELECT's, the time MySQL had to spend on sorting results/reading from disk in an inefficient way made all our _write_ queries suffer. By optimizing our SELECTs first, we freed up some CPU bandwidth (it seems?) that can be spent doing all the oth…
That's good to hear. We have found some suspect SELECTs used in our client-facing API recently. Might be good to double-check that those are running efficiently and not hamstringing the writes.