Queries take long long(10 sec.) time running - Mailing list pgsql-general
| From | Andre Lopes |
|---|---|
| Subject | Queries take long long(10 sec.) time running |
| Date | |
| Msg-id | CAGFRAbN7MqF+JRxVzvPDyaHOYgrMcZA1NoO8gLBDBk-Zz-cLQw@mail.gmail.com Whole thread Raw |
| Responses |
Re: Queries take long long(10 sec.) time running
Re: Queries take long long(10 sec.) time running |
| List | pgsql-general |
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,
pgsql-general by date: