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

Thanks for the replies.

I've done a "vmstat 1", here is the result:

r b swpd free buff cache si so bi bo in cs us sy id wa st
1 1 342728 21384 264 34940 96 0 1428 0 616 275 1
0 92 7 0
0 0 342728 15032 312 40008 0 0 5136 0 821 561 0
0 89 10 0
0 0 342728 15048 312 40072 0 0 0 0 86 51 0
0 100 0 0
0 0 342728 15792 336 40272 0 0 256 0 561 201 0
0 94 5 0
0 0 342728 15808 344 40300 0 0 0 36 92 60 0
0 100 0 0
0 0 342728 15800 344 40304 0 0 0 0 366 171 0
0 100 0 0
0 0 342728 15800 344 40304 0 0 0 0 67 44 0
0 100 0 0
0 0 342728 15800 344 40304 0 0 0 0 351 163 0
0 100 0 0
0 0 342728 15800 344 40304 0 0 0 0 85 52 0
0 100 0 0
0 1 342728 14808 344 40876 0 0 528 40 459 200 0
0 96 4 0
0 1 342728 14596 356 41408 32 0 636 88 217 80 0
0 95 5 0
1 1 340184 10876 356 41428 4144 0 4144 0 1155 715 0
0 82 17 0
0 3 333176 5460 316 38484 10312 28 10336 32 2158 1474 3
1 75 21 0
0 2 329480 6192 148 31452 7688 1764 8000 1812 2140 1311 2
1 74 23 0
1 1 329112 6452 136 30836 4284 2268 5828 2296 1638 799 6
0 75 19 0
0 3 328832 5972 120 30356 3572 2312 5396 2316 1388 744 4
0 72 24 0
1 5 331156 5204 116 30656 764 2988 2136 3036 552 391 0
0 68 32 0
1 0 329708 6524 104 30100 2176 188 3256 192 1586 517 8
0 77 14 0
0 1 330748 6580 104 30840 1244 1764 5112 1764 1270 509 5
0 70 25 0
1 1 330584 5888 124 31204 1160 1180 2936 1184 1110 381 7
0 76 17 0
1 1 332684 5660 200 34564 936 2928 14252 3452 2812 1191 9
1 61 28 0
1 2 332500 5916 220 34216 860 580 2084 1160 1151 542 4
0 66 30 0
0 2 332236 5596 228 33712 1636 1088 2444 1132 1665 475 10
1 64 25 0
1 1 332552 6232 152 32580 1180 1192 4948 1192 1386 504 6
0 61 32 0
1 0 332748 6496 192 34380 32 204 2128 204 1804 363 13
0 78 9 0
0 4 334712 6056 244 34644 356 2072 4836 2168 1752 542 8
1 71 21 0
0 2 335060 5792 260 35132 100 372 1284 372 506 212 0
0 76 24 0
1 0 335492 6572 280 36352 96 480 2516 576 769 325 1
0 76 23 0
0 0 335492 6568 280 36432 0 0 0 0 264 120 0
0 100 0 0

Seems my problem is SWAP/IO... I can only solve this putting more RAM
on the machine or it is possible to put down this values only
adjusting some settings?

Best Regards,

On Wed, Dec 7, 2011 at 9:52 AM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 12/07/11 1:27 AM, Andre Lopes wrote:
>>
>> This is enough information for some clues on how to increase the
>> response time in queries?
>
>
> no.
>
> to optimize queries, you generally need to know what the queries are, what
> the relations they are using look like, and get the output of `explain
> analyze <your query>;`
>
> some random comments in passing...
>
>  * whats mysql got to do with this?
>
>  * a server running efficiently should be using zero swap.   the fact
>   that you have 340MB of swap used implies you need at least twice as
>   much physical memory as you have.
>
>  * VPS?  as in virtual server?  so your disk IO is virtualized too?
>  this is usually bad news for getting decent database performance.
>
>
>
> --
> john r pierce                            N 37, W 122
> santa cruz ca                         mid-left coast
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general