MySQL Queries taking too long to execute

Question

I have a database table, with 300,000 rows and 113.7 MB in size. I have my database running on Ubuntu 13.10 with 8 Cores and 8GB of RAM. As things are now, the MySQL server uses up an average of 750% CPU. and 6.5 %MEM (results obtained by running top in the CLI). Also to note, it runs on the same server as Apache2 Web Server.

Here's what I get on the Mem line: Mem: 8141292k total, 6938244k used, 1203048k free, 211396k buffers

When I run: show processlist; I get something like this in return:

2098812 | admin | localhost       | phpb | Query   |   12 | Sending data | SELECT * FROM items WHERE thumb = 'Halloween 2013 Horns/thumbs/Halloween 2013 Horns (Original).png'
2098813 | admin | localhost       | phpb | Query   |   12 | Sending data | SELECT * FROM items WHERE thumb = 'Halloween 2013 Witch Hat/thumbs/Halloween 2013 Witch Hat (Origina
2098814 | admin | localhost       | phpb | Query   |   12 | Sending data | SELECT * FROM items WHERE thumb = 'Halloween 2013 Blouse/thumbs/Halloween 2013 Blouse (Original).png
2098818 | admin | localhost       | phpb | Query   |   11 | Sending data | SELECT * FROM items WHERE parent = 210162 OR auto = 210162

Some queries are taking an excess of 10 seconds to execute, this is not the top of the list, but somewhere in the middle just to give kind of a perspective of how many queries are stacking up in this list. I feel that it may have something to do with my Query Cash configurations. Here are the configurations show from running the SHOW STATUS LIKE 'Qc%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 434      |
| Qcache_free_memory      | 2037880  |
| Qcache_hits             | 62580686 |
| Qcache_inserts          | 10865474 |
| Qcache_lowmem_prunes    | 4157011  |
| Qcache_not_cached       | 3140518  |
| Qcache_queries_in_cache | 1260     |
| Qcache_total_blocks     | 4440     |
+-------------------------+----------+

I noticed that the Qcache_lowmem_prunes seem a bit high, is this normal?

I've been searching around StackOverflow, but I couldn't find anything that would solve my problem. Any help with this would be greatly appreciated, thank you!

score 1 · Accepted Answer · answered Dec 21 '13 at 00:24

1

Do you have indexes on your thumb, parent, or auto columns? As a rule of ahem thumb, you should have an index on each column or group of columns in where clauses.

answered Dec 21 '13 at 00:24

BJT

358
2
10

Only the Auto column has an index, will it reduce the time it takes to run the query if all or most columns were indexes? – Devontrae Marqualius Walls Dec 21 '13 at 00:33
I would think it should. Adding an index shouldn't be a big deal - give it a try. – BJT Dec 21 '13 at 00:34
1

Thank you, this actually worked for me. Sped everything up very quickly, and had absolutely nothing to do with Qcache! – Devontrae Marqualius Walls Dec 21 '13 at 02:56

MySQL Queries taking too long to execute

1 Answers1