Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. 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
    
This is almost certainly not related to the query cache, but it would be good to include the result from SHOW GLOBAL VARIABLES LIKE 'query_cache_size'; in your question. The answer, though, is likely to be found by posting a couple of examples of the output from EXPLAIN SELECT ... I would suggest posting these for the first and last queries in your original question. The storage engine you are using (typically MyISAM or InnoDB) is important information, as well as the output from SELECT @@VERSION;. –  Michael - sqlbot Dec 21 '13 at 4:31
    
You should check for over-indexing in this situation and as you are sharing the server with the apache you might be running into resource issues if there is any load to speak of. Adding the information suggested by @Michael-sqlbot is a very good starting point. –  Ian Lewis Dec 23 '13 at 17:04

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.