6

Hi all of a sudden my server has slowed to a crawl, queries that were 0.10 of a second consistently now taking 20 seconds. somewhat randomly, sometimes it snaps right back. so I have a really sudden and harsh performance problem.

I ran mysqltuner.pl and it reported one thing that might be a problem :

[!!] Table cache hit rate: 0% (400 open / 1M opened)

I am just starting to delve into this performance problem, but does anyone know if this a big immediate red flag?

I can do some DD but am pressed right now. any help much appreciated.

Don

Improve this question
0

3 Answers 3

Reset to default
1

I have seen this problem when a table being frequently table scanned no longer fits in memory. Instead of reading data from cache, the data is read from disk. This will flush data out on a LRU (Least Recently Used) basis. As a result you will be constantly reading the data back in for the next table scan cycle.

If you have it available run sar to determine which disk partition has high IO. This is more useful if you have distributed your database over more than one disk or partition. Also check to see if memory is being paged heavily.

Check your slow queries log and run explain plans on the queries. This should help identify the problem table.

A similar problem can happen if you no longer have enough free memory to keep the database buffers in memory. This will cause part or all of the database's memory to be paged out. It will soon be paged in. This is often called thrashing.

Improve this answer
5
  • Hi Bill thx for the response. I only have one disk partition, which is an SDD.
    – Don Wool
    Commented Jul 4, 2012 at 7:50
  • the SDD looks like this :
    – Don Wool
    Commented Jul 4, 2012 at 7:50
  • /dev/sda1 156030876 16166480 132052556 11% /data
    – Don Wool
    Commented Jul 4, 2012 at 7:51
  • Is there an advantage of spreading a database across multiple disk partitions? disks are so big now, that I thought doing that was obviated. If one were to do so, would you try and put different tables on certain partitions? Note : I would think this becomes harder in a RAID environment. then again, maybe the RAID lets you do this. will investigate sar (not currently installed on system). thx again for the reply.
    – Don Wool
    Commented Jul 4, 2012 at 7:53
  • Spreading databases over multiple disks is done to provide allow higher throughput. Splitting index and data partitions on different disks allows data to be read or written on one disk whie to complete while other disk is performing a seek. RAID can provide similar benefits in slitting I/O across disks. SSDs minimize seek time, so splitting across disks may not be as beneficial.
    – BillThor
    Commented Jul 6, 2012 at 0:43
2

The query cache was only hit 400 out of 1,000,000 times.

That is a query cache hitrate of 0.04 %

This indicates that 99.96% of your queries are unique and are not reusable. That's not unusual if your are storing session data, doing blogging, audit trails, or just plain logging of events.

While you could setup slow query logging, there is a better approach to checking on SQL performance. I recommend using pt-query-digest with --processlist option or --tcpdump option

This will allow you to catch bad queries in the act of being bad.

Improve this answer
2
  • Most of my database access is thru stored procedures. I believe this is the culprit. 'selects' snap back so fast frankly it almost scares me. The problem I have is writing. I wrote a post on it here. dba.stackexchange.com/questions/20372/…
    – Don Wool
    Commented Jul 4, 2012 at 7:56
  • by the way I am on Percona. not sure if that impacts your choice on suggested SQL Performance tools?
    – Don Wool
    Commented Jul 4, 2012 at 7:58
1

There are many things to look at, including:

I'd suggest turning on the "slow-query-log" ASAP:

Also look at these parameters:

Improve this answer
1
  • well, I know which queries are taking a long time as I said "queries that were 0.10 of a second consistently now taking 20 seconds". indicies are being used on all tables in these queries. It just went off a cliff with no warning! thx.
    – Don Wool
    Commented Jul 2, 2012 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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