I have a table about 50 million rows. There are a few writers to pump
data into the table at the rate of 40000 row/hours. Most the time, the
SELECT is less than 100 ms. However sometime it is very slow, from 30
seconds to 500 seconds. The database is vacuum analyze regularly.
One months ago, this type of slow query happened about a few time per
day. But recently, the slow query happens more frequent at the rate of
once every 10 minutes or less. There seesm not relation to the
database loading or the type of query. If I manually execute these
query, it is returns in less than 1 seconds.
I just wonder where should I start to look?
Thanks
Shawn.