Dne 20.11.2011 13:46, Phoenix Kiula napsal(a):
> On Sun, Nov 20, 2011 at 2:11 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> For a moment there, I unleashed the valve and allowed the INSERT
> functionality. The log was immediately flooded with this:
>
>
> LOG: duration: 6851.054 ms statement: select nextval('maintable_id_seq')
> LOG: duration: 6848.266 ms statement: select nextval('maintable_id_seq')
> LOG: duration: 6846.672 ms statement: select nextval('maintable_id_seq')
> LOG: duration: 6853.451 ms statement: select nextval('maintable_id_seq')
> LOG: duration: 6991.966 ms statement: select nextval('maintable_id_seq')
> LOG: duration: 8244.315 ms statement: select nextval('maintable_id_seq')
> LOG: duration: 6991.071 ms statement: select nextval('maintable_id_seq')
> LOG: duration: 6990.043 ms statement: select nextval('maintable_id_seq')
> LOG: duration: 6988.483 ms statement: select nextval('maintable_id_seq')
> LOG: duration: 6986.793 ms statement: select nextval('maintable_id_seq')
> LOG: duration: 6985.802 ms statement: select nextval('maintable_id_seq')
> ...
>
> I hope it's just because of too much load that even a simple query
> such as this was taking so much time?
Probably, unless you have system with infinite amount of CPU time.
According to the vmstat output you've posted, the CPU is 99% utilized
all the time.
I'm not sure about the I/O, because you haven't posted iostat output
with the INSERTs enabled. But from the information you've provided so
far I guess the main issue is the load and overall system overload.
Another sign of this is rather high number of processes waiting in the
queue. So once again - decrease the number of connections and apache
clients to a reasonable number.
> Other queries taking too much time are also indexed queries!
And? Indexes are not a magical fairy dust - when the system is as
overloaded as yours, even the least expensive operations are going to
take insane amount of time.
And it's rather difficult to help you with queries, unless you provide
us EXPLAIN ANALYZE output - I've already asked you for this twice.
Without that piece of information, we can't tell whether the queries are
slo because of bad query plan or because of the load.
Tomas
