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,
Show quoted text
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