1

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!

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
3
  • 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? Commented Dec 21, 2013 at 0:33
  • I would think it should. Adding an index shouldn't be a big deal - give it a try. Commented Dec 21, 2013 at 0:34
  • 1
    Thank you, this actually worked for me. Sped everything up very quickly, and had absolutely nothing to do with Qcache! Commented Dec 21, 2013 at 2:56

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.