Re: Queries take long long(10 sec.) time running

On 7 Prosinec 2011, 10:27, Andre Lopes wrote:
> Hi,
>
> I have a "vertical" database schema running with an webapp. I query
> this database in the webapp rotating the vertical schema with a
> view(something like a pivot view)
>
> I know that this type of operation is very expensive, but I have the
> webapp running with acceptable response time for 45 days(machine
> uptime). Now the queries started to take much much time to execute. I
> use a VPS with 512MB memory, and I have Apache2, PostgreSQL 8.4, MySQL
> 5. The webapp don't have high traffic, have about 700 visits per
> day...
>
> I've run "top" and I see that the server is using a lot of swap. I
> have ordered the "top" to give me the used swap, the result is this:

The swap is probably the reason why it's so slow. Anyway top output is
rather useless in this case, especially when ordered by SWAP. We have no
clue which of the processes is the one running the query

Post vmstat output, e.g. 10 lines of "vmstat 1" when the query is running.

Get rid of the swap and read this:
http://wiki.postgresql.org/wiki/Slow_Query_Questions

You should post at least EXPLAIN ANALYZE of the query and info about the
settings of the database (shared buffers etc.).

Tomas