Once in a while I actually get to remember enough to help someone - with something I’m actually interested in - not often, but just sometimes. 🙂 Generally I do as much learning as helping along the way.
Someone asked about a slow MySQL query on #sage-au tonight. They had been asked to look into a small e-commerce site that’s been having issues for a few years, slow page loads and the like.
It’s always fun working blind (no access to schema or the server), but if there’s anything K-T has taught me it’s that nothing is impossible to solve by asking questions.
The thing with SQL queries is that the system can do weird and wonderful things, especially when you’re doing subqueries and the like.
The query was as follows:
DELETE FROM vendor_shipping WHERE vid NOT IN ( SELECT distinct(vendorid) FROM products WHERE pid IN ( SELECT pid FROM orders_content WHERE oid='663') ) and oid='663';
Fairly simple, delete something if it’s not in a list of vendor id’s which are related to product orders (ish?)
The first things suggested were to throw more hardware at it, which had most definitely been done - for a small online store it had 8GB of ram and a quad core processor of some sort.
Running the selects by themselves were fast (alone or in the sub-query state), and nothing major seemed out of the ordinary as shown by the output of explain.
For ~9k orders, there’s ~9k rows, that makes sense.
There were triggers which could cause other horrible things to happen with deletes. Profiling wasn’t turned on (yet) so that wasn’t slowing it down. No stored procedures either.
The fun part came when profiling was turned on. Long story short, it was chugging through the database thousands of times over:
SlowDB: 2910567 rows in set (12.87 sec) before i ctrl+c
Well, that’s no right! ~9k orders, ~170 vendors. 3 million rows queried? Silly MySQL. Ok, silly schema, but you get the idea.
Something I should probably have asked about were indexes. A lack of indexing’s a sure-fire way to slow down any queries, especially if there’s subselects and joins and all sorts of other horrible things going on.
It seems there wasn’t an index on the oid column in _vendorshipping. This meant that every time it filtered the query (with a where) for something in the _vendorshipping table it would have to scan through it row by row. The subselects only made this exponentially worse!
I’ll leave it to the code to show the difference - remember that 17 seconds was the normal time for this query to run.
We double checked our assumptions and confirmed that index creation load shouldn’t be too high. 167 rows, a single INT column, and it was already slow - should be OK. (This is something you have to think about when messing with FULLTEXT indexes.)
Here the index on the table is created:
mysql> create index oid_index on vendor_shipping (oid); Query OK, 167 rows affected (0.02 sec) Records: 167 Duplicates: 0 Warnings: 0
That was quick!
And now, to test running the DELETE command:
mysql> DELETE FROM vendor_shipping WHERE vid NOT IN(SELECT distinct(vendorid) FROM products WHERE pid IN (select pid from orders_content WHERE oid='663')) AND oid='663'; Query OK, 0 rows affected (0.00 sec)
Suffice to say, the issue was closed. 🙂
The original chat log is below, mainly so I can remember how it went. Other people’s names have been changed, but the content’s the same. 🙂
SlowDB: So I've got a slow SQL query which is out of my control to fix. Is there any tweaks to mysql to help these? otherguy: SlowDB: does more RAM count as a tweak? SlowDB: in this case yes.. SlowDB: i've bumped the values in mysql SlowDB: http://pastebin.com/A5kRw09B SlowDB: I've asked to check if the crappy shopping cart DB will support innodb as it is currently myisam SlowDB: this is the query that I've found to be slow SlowDB: DELETE FROM vendor_shipping WHERE vid NOT IN(SELECT distinct(vendorid) FROM products where pid in(select pid from orders_content WHERE oid='663')) and oid='663'; SlowDB: the nested SQLs all return quick. SlowDB: but run as a whole it takes 17secs yaleman: SlowDB: have you run mysqltuner? SlowDB: yes SlowDB: it's a shitty DB schema, so I know I am limited in what I can actually do to help yaleman: hrm, so how big are the returns from the nested queries? SlowDB: 1 field for each which return in 0.00sec yaleman: more cpu/ram in the box, if you can't change the query SlowDB: 8GB of ram, quad core. Think I'm stuck then hey. yaleman: probably not a terrible idea, if you can rewrite the query move the oid check before the other? 😛 yaleman: geez, how big is vendor_shipping SlowDB: 167 rows, not big 😐 yaleman: and it's taking 17 seconds SlowDB: indeed - i can't understand it yaleman: explain? (as in run explain on the query) SlowDB: i've never done that before SlowDB: http://www.sql.org/sql-database/postgresql/manual/sql-explain.html ? yaleman: should be able to go into mysql, then just do "explain [your query]" yaleman: http://dev.mysql.com/doc/refman/5.0/en/explain.html yaleman: 8gb, quad core, somehow it's taking 17 seconds to run across 170 rows... there's something seriously wrong there yaleman: hell, I process hundreds of thousands of rows in similarly horrible ways in less than that with much less resources yaleman: it's a delete, unless the indexes are horribly corrupt (in some truly f***ed way) it shouldn't matter on the row size yaleman: oh, check for on delete/on x procedures maybe? :S yaleman: but explain should tell you otherguy: are any of the other tables big? maybe a full table scan going on. its beyond my skillz tho. SlowDB: yes, product table has over 10K rows SlowDB: which is nothing still for a box of this size. SlowDB: It's russian software for joomla..... SlowDB: explain seems only for SELECT statements from the mysql page otherguy: are both 'oid' integers? SlowDB: yes yaleman: do an explain on the selects, as the are to see if there's something odd (as in just strip the outer delete off) SlowDB: yer, just did it. SlowDB: nothing stands out SlowDB: http://pastebin.com/HagpNGXX otherguy: any triggers, or auditing enabled? SlowDB: Unsure about triggers or auditing sorry. yaleman: do a "show triggers in [database]" SlowDB: empty yaleman: O_o SlowDB: mysql> show triggers in [database]; SlowDB: Empty set (0.00 sec) yaleman: http://dev.mysql.com/doc/refman/5.1/en/show-profile.html yaleman: try profiling the query SlowDB: ok SlowDB: wow, lots of output. yaleman: yeah, it'll do that SlowDB: still scrolling through show profile; yaleman: ah, I'd be reading that whole page before doing profiling 🙂 SlowDB: i show profile 4, and it scrolls through a bunch of | Sending data | 0.000013 | 0.000000 | 0.000000 | SlowDB: 2910567 rows in set (12.87 sec) before i ctrl+c SlowDB: indexing it comes down to is my guess.... yaleman: show index? yaleman: if there's no index on vid that'll be less fun for it SlowDB: there is three: id, uid and status SlowDB: so you are correct. yaleman: create index on [table] oid using hash yaleman: actually ignore the using hash bit SlowDB: ok SlowDB: there is no hazard in doing that command? otherguy: good luck with i!. yaleman: "create index oid_index on [table] oid" yaleman: is what I can make out of my fuzzy memory of creating indexes and referring to [http://dev.mysql.com/doc/refman/5.0/en/create-index.html](http://dev.mysql.com/doc/refman/5.0/en/create-index.html) otherguy: do you have disk space and io headroom while it creates? yaleman: what version of mysql are you running? 🙂 SlowDB: will, yes SlowDB: yaleman: 5.5 otherguy: does mysql support hints? yaleman: well that's unlikely to be it 🙂 SlowDB: the index u mean yaleman ? yaleman: wait, what's the full version code SlowDB: Server version: 5.5.27-cll MySQL Community Server (GPL) yaleman: k SlowDB: index still? yaleman: saw some weirdass issues with a recent ubuntu one yaleman: if you're not terribly worried about the possible performance hit I'd go for it yaleman: there's only 10k rows, and oid's an int isn't it? SlowDB: yes yaleman: then it's just doing a "select oid from [table]" and creating an index on it yaleman: did you remember to turn profiling back off SlowDB: yup! yaleman: 🙂 yaleman: ip accounting's on our edge routers at work atm yaleman: I giggle to myself when I see it otherguy: at 17 secs, i dont think one index will fix, but lets see if it goes part way yaleman: how much traffic's on this server SlowDB: not too much SlowDB: it is a [snip] shop SlowDB: mysql> create index oid_index on vendor_shipping (oid); SlowDB: Query OK, 167 rows affected (0.02 sec) SlowDB: Records: 167 Duplicates: 0 Warnings: 0 otherguy: cant see that helping at all yaleman: 8gb of ram and a quad core for a [snip] shop SlowDB: it fixed it SlowDB: mysql> DELETE FROM vendor_shipping WHERE vid NOT IN(SELECT distinct(vendorid) FROM products where pid in(select pid from orders_content WHERE oid='663')) and oid='663'; SlowDB: Query OK, 0 rows affected (0.00 sec) yaleman: 😀