Search Postgresql Archives

Queries take long long(10 sec.) time running

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:

[code]
top - 09:24:13 up 49 days, 22:44,  3 users,  load average: 0.15, 0.12, 0.13
Tasks: 123 total,   1 running, 122 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni, 96.6%id,  3.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:    500452k total,   490576k used,     9876k free,      496k buffers
Swap:   524284k total,   343664k used,   180620k free,    32428k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  SWAP
COMMAND
 1881 mysql     20   0  929m 2024    0 S  0.0  0.4 142:11.73 927m
mysqld
11093 root      20   0  275m   56    0 S  0.0  0.0   0:03.46 275m
httpd
19462 apache    20   0  280m 6704 1796 S  0.0  1.3   0:02.00 273m
httpd
11103 apache    20   0  280m 7076 1740 S  0.0  1.4   0:07.80 273m
httpd
11095 apache    20   0  280m 7544 1788 S  0.0  1.5   0:08.70 273m
httpd
11260 apache    20   0  280m 7548 1548 S  0.0  1.5   0:07.94 273m
httpd
11096 apache    20   0  280m 7728 1588 S  0.0  1.5   0:08.45 272m
httpd
12668 apache    20   0  280m 7660 1804 S  0.0  1.5   0:07.30 272m
httpd
11293 apache    20   0  280m 7908 1800 S  0.0  1.6   0:08.59 272m
httpd
12669 apache    20   0  280m 8052 1860 S  0.0  1.6   0:07.69 272m
httpd
11099 apache    20   0  277m 4940 1720 S  0.0  1.0   0:08.64 272m
httpd
11297 apache    20   0  280m 8100 1884 S  0.0  1.6   0:09.00 272m
httpd
11102 apache    20   0  280m 8148 1920 S  0.0  1.6   0:09.34 272m
httpd
12672 apache    20   0  280m 8172 1804 S  0.0  1.6   0:07.62 272m
httpd
12113 apache    20   0  280m 8220 1804 S  0.0  1.6   0:07.87 272m
httpd
11100 apache    20   0  280m 8348 1940 S  0.0  1.7   0:08.70 272m
httpd
12663 apache    20   0  278m 7188 1940 S  0.0  1.4   0:07.66 271m
httpd
19350 apache    20   0  277m 6148 1936 S  0.0  1.2   0:07.23 271m
httpd
11105 apache    20   0  280m 8928 2412 S  0.0  1.8   0:08.08 271m
httpd
 1960 apache    20   0  279m 7912 2108 S  0.0  1.6   0:05.63 271m
httpd
11287 apache    20   0  276m 5176 1936 S  0.0  1.0   0:08.66 271m
httpd
14813 apache    20   0  280m 9336 2424 S  0.0  1.9   0:03.00 271m
httpd
 1729 root      20   0  242m  396  252 S  0.0  0.1   1:14.37 242m
rsyslogd
11304 postgres  20   0  218m  15m  15m S  0.0  3.2   1:34.18 203m
postmaster
11323 postgres  20   0  218m  20m  16m S  0.0  4.2   1:58.70 198m
postmaster
20149 postgres  20   0  218m  22m  17m S  0.0  4.7   0:07.35 195m
postmaster
11360 postgres  20   0  218m  23m  17m S  0.0  4.8   1:14.27 194m
postmaster
11604 postgres  20   0  218m  23m  17m S  0.0  4.9   1:43.92 194m
postmaster
11531 postgres  20   0  218m  24m  17m S  0.0  4.9   2:29.91 194m
postmaster
11628 postgres  20   0  218m  24m  17m S  0.0  5.0   2:46.56 194m
postmaster
11437 postgres  20   0  218m  24m  17m S  0.0  4.9   1:38.11 194m
postmaster
28295 postgres  20   0  188m 3364 2920 S  0.0  0.7   0:00.09 184m
postmaster
13465 postgres  20   0  184m  140   96 S  0.0  0.0  15:07.25 183m
postmaster
13466 postgres  20   0  184m  596  300 S  0.0  0.1   6:14.63 183m
postmaster
13460 postgres  20   0  184m  340  220 S  0.0  0.1   5:14.39 183m
postmaster
13555 postgres  20   0  218m  35m  17m S  0.0  7.3   1:28.89 183m
postmaster
13464 postgres  20   0  184m 3332 3168 S  0.3  0.7  16:38.80 180m
postmaster
11761 postgres  20   0  218m  37m  17m S  0.0  7.7   1:41.28 180m
postmaster
11560 postgres  20   0  218m  38m  17m S  0.0  7.8   1:37.13 180m
postmaster
12914 postgres  20   0  218m  39m  17m S  0.0  8.1   1:49.34 179m
postmaster
11305 postgres  20   0  202m  24m  17m S  0.0  5.0   1:31.30 178m
postmaster
29837 postgres  20   0  188m  10m 8332 S  0.0  2.1   0:01.42 178m
postmaster
12666 postgres  20   0  218m  40m  17m S  0.0  8.4   0:59.64 177m
postmaster
19639 postgres  20   0  216m  41m  17m S  0.0  8.5   1:38.68 175m
postmaster
11373 postgres  20   0  218m  44m  18m S  0.0  9.2   1:39.24 173m
postmaster
12196 postgres  20   0  195m  23m  16m S  0.0  4.8   1:15.28 172m
postmaster
 2313 postgres  20   0  202m  31m  18m S  0.0  6.4   1:09.09 171m
postmaster
14947 postgres  20   0  218m  47m  18m S  0.0  9.7   0:30.29 170m
postmaster
[/code]

This "top" result is ordered by swap.

This is enough information for some clues on how to increase the
response time in queries?


Best Regards,

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux