Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 0:33
    
I would think it should. Adding an index shouldn't be a big deal - give it a try. –  BJT Dec 21 '13 at 0:34
    
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 2:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.