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@hogranch.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general