Thread: Queries take long long(10 sec.) time running
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,
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
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
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
On 7 Prosinec 2011, 11:28, Andre Lopes wrote: > Thanks for the replies. > > 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? You can set PostgreSQL, MySQL etc. to use less memory - so that it fits into the RAM. What are the basic config parameters, i.e. shared_buffers, work_mem etc.? The question is why it worked fine for 45 days and then it started to slow down. My bet is that the database grew for some reason (users entering data, bloat ...). This often triggers sudden performance degradation. Tomas