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

Don Wool
  • 339
  • 2
  • 4
  • 10

3 Answers3

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.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
1

There are many things to look at, including:

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

Also look at these parameters:

paulsm4
  • 151
  • 3
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.

BillThor
  • 4,518
  • 20
  • 12