Difference between revisions of "Examine large SQL queries"
(→Export results for query) |
|||
| Line 7: | Line 7: | ||
The full query is important, so lets go ahead and get the full thing. | The full query is important, so lets go ahead and get the full thing. | ||
| − | < | + | |
| + | <source lang='bash'> | ||
mysql -e "show full processlist;" | mysql -e "show full processlist;" | ||
| − | </ | + | </source> |
==Examine the query to see how many records it is accessing== | ==Examine the query to see how many records it is accessing== | ||
Often times slow downs happen outside of the actual sending due to the number of records being accessed and returned. | Often times slow downs happen outside of the actual sending due to the number of records being accessed and returned. | ||
| − | < | + | |
| + | <source lang='bash'> | ||
mysql $database -e "examine $query;" | mysql $database -e "examine $query;" | ||
| − | </ | + | </source> |
==Export results for query== | ==Export results for query== | ||
To be able to look at the query easier you will want to export the query. | To be able to look at the query easier you will want to export the query. | ||
| − | < | + | |
| + | <source lang='bash'> | ||
mysql $datbase -e "$query;" > sqlquery.sql | mysql $datbase -e "$query;" > sqlquery.sql | ||
| − | </ | + | </source> |
Obviously at this point you can look at the size using ls -lah sqlquery.sql and tell how much data is actually being sent over the network. | Obviously at this point you can look at the size using ls -lah sqlquery.sql and tell how much data is actually being sent over the network. | ||
| Line 33: | Line 36: | ||
This step is the one that is dependant on what row is your key value. Generally this is the first row, if not then you may need to tweak. | This step is the one that is dependant on what row is your key value. Generally this is the first row, if not then you may need to tweak. | ||
| − | < | + | |
| + | <source lang='bash'> | ||
for i in $(cat sqlquery.sql); do row=`echo "$i"|awk '{print $1}'`; cnt=`echo "$i" | wc -c`; echo $cnt ":" $row;done | sort -nr | head | for i in $(cat sqlquery.sql); do row=`echo "$i"|awk '{print $1}'`; cnt=`echo "$i" | wc -c`; echo $cnt ":" $row;done | sort -nr | head | ||
| − | </ | + | </source> |
==Export the top record or records== | ==Export the top record or records== | ||
| Line 41: | Line 45: | ||
This step is optional, at this point you should have an idea as to the cause of the large query, but if you want to supply the record you can. | This step is optional, at this point you should have an idea as to the cause of the large query, but if you want to supply the record you can. | ||
If you export the record you can get the actual size of that record. | If you export the record you can get the actual size of that record. | ||
| − | < | + | |
| + | <source lang='bash'> | ||
grep "$record" sqlquery.sql > record.sql | grep "$record" sqlquery.sql > record.sql | ||
| − | </ | + | </source> |
Obviously at this point you can once again to ls -lah record.sql to get the actual size. | Obviously at this point you can once again to ls -lah record.sql to get the actual size. | ||
Latest revision as of 14:25, 25 March 2014
If you are running in to problems with specific SQL queries seeming to take a long time to send (ie "Writing to net") you can examine the query to get an idea as to what is happening.
One thing to note, this process was set up for the wp_options table, it assumes that the first row in the field is your key value, if this is not the case you can change it around as needed.
Contents
Get the full query
The full query is important, so lets go ahead and get the full thing.
mysql -e "show full processlist;"
Examine the query to see how many records it is accessing
Often times slow downs happen outside of the actual sending due to the number of records being accessed and returned.
mysql $database -e "examine $query;"
Export results for query
To be able to look at the query easier you will want to export the query.
mysql $datbase -e "$query;" > sqlquery.sql
Obviously at this point you can look at the size using ls -lah sqlquery.sql and tell how much data is actually being sent over the network.
Sort records returned by characters
Once you know that the query itself is large you can sort the records returned by the number of characters in the record. Note that the head is optional, but if you have hundreds of records being returned you really only want the top ones.
This step is the one that is dependant on what row is your key value. Generally this is the first row, if not then you may need to tweak.
for i in $(cat sqlquery.sql); do row=`echo "$i"|awk '{print $1}'`; cnt=`echo "$i" | wc -c`; echo $cnt ":" $row;done | sort -nr | head
Export the top record or records
This step is optional, at this point you should have an idea as to the cause of the large query, but if you want to supply the record you can. If you export the record you can get the actual size of that record.
grep "$record" sqlquery.sql > record.sql
Obviously at this point you can once again to ls -lah record.sql to get the actual size.
