Re: [PERFORM] Some question
Scott Marlowe wrote: 2010/3/31 Ľubomír Varga lu...@plaintext.sk: Hi, stright to my problem: If I try to select constant 1 from table with two rows, it will be something like this: explain SELECT * FROM t_route WHERE t_route.route_type_fk = (SELECT id FROM t_route_type WHERE type = 2) limit 4; Limit (cost=1.02..1.91 rows=4 width=2640) InitPlan - Seq Scan on t_route_type (cost=0.00..1.02 rows=1 width=8) Filter: (type = 2) - Seq Scan on t_route (cost=0.00..118115.25 rows=535090 width=2640) Filter: (route_type_fk = $0) Looking at this it looks like you're using prepared queries, which can't make as good of a decision as regular queries because the values are opaque to the planner. Can you provide us with the output of explain analyze of that query? ISTM that the initplan 'outputs' id as $0, so it is not a prepared query. Maybe EXPLAIN VERBOSE ANALYZE of the query reveals that better. But both plans show seqscans of the large table, so it is surprising that the performance is different, if the filter expression uses the same values. Are you sure the output SELECT id FROM t_route_type WHERE type = 2 is equal to 1? regards, Yeb Havinga -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
Guys, Thanks for trying and opening your mind. If you want to know how Oracle addressed this issue, here it is: index on two columns. I remember that they told me in the training postgres has no this kind of index, can someone clarify? Thanks much! Xuefeng Zhu (Sherry) Crown Consulting Inc. -- Oracle DBA AIM Lab Data Team (703) 925-3192 Joel Jacobson j...@gluefinance.com 04/06/2010 06:30 PM To Sherry CTR Zhu/AWA/CNTR/f...@faa, pgsql-performance@postgresql.org cc Robert Haas robertmh...@gmail.com Subject Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index Actually, swapping the order of the conditions did in fact make some difference, strange. I ran the query a couple of times for each variation to see if the difference in speed was just a coincidence or a pattern. Looks like the speed really is different. EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID = 108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1; QUERY PLAN -- Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1384.401..1384.402 rows=1 loops=1) - Index Scan using transactions_pkey on transactions (cost=0.00..1260254.03 rows=10862 width=4) (actual time=1384.399..1384.399 rows=1 loops=1) Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar)) Total runtime: 1384.431 ms (4 rows) EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency = 'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1; QUERY PLAN -- Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1710.166..1710.167 rows=1 loops=1) - Index Scan using transactions_pkey on transactions (cost=0.00..1260254.03 rows=10862 width=4) (actual time=1710.164..1710.164 rows=1 loops=1) Filter: ((currency = 'SEK'::bpchar) AND (accountid = 108)) Total runtime: 1710.200 ms (4 rows) EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID = 108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1; QUERY PLAN -- Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1366.526..1366.527 rows=1 loops=1) - Index Scan using transactions_pkey on transactions (cost=0.00..1260254.03 rows=10862 width=4) (actual time=1366.525..1366.525 rows=1 loops=1) Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar)) Total runtime: 1366.552 ms (4 rows) EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency = 'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1; QUERY PLAN -- Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1685.395..1685.396 rows=1 loops=1) - Index Scan using transactions_pkey on transactions (cost=0.00..1260254.03 rows=10862 width=4) (actual time=1685.394..1685.394 rows=1 loops=1) Filter: ((currency = 'SEK'::bpchar) AND (accountid = 108)) Total runtime: 1685.423 ms (4 rows) EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID = 108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1; QUERY PLAN -- Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1403.904..1403.905 rows=1 loops=1) - Index Scan using transactions_pkey on transactions (cost=0.00..1260254.03 rows=10862 width=4) (actual time=1403.903..1403.903 rows=1 loops=1) Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar)) Total runtime: 1403.931 ms (4 rows) EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency = 'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1;
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
On Wed, Apr 7, 2010 at 1:20 PM, sherry.ctr@faa.gov wrote: Guys, Thanks for trying and opening your mind. If you want to know how Oracle addressed this issue, here it is: index on two columns. I remember that they told me in the training postgres has no this kind of index, can someone clarify? lies. postgresql allows you indices on multiple columns. What it does not have, is index on multiple tables. -- GJ
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
Do you mean one index on two columns? something like this: create index idx1 on tb1(col1, col2); Thanks much! Xuefeng Zhu (Sherry) Crown Consulting Inc. -- Oracle DBA AIM Lab Data Team (703) 925-3192 Grzegorz Jaśkiewicz gryz...@gmail.com 04/07/2010 08:51 AM To Sherry CTR Zhu/AWA/CNTR/f...@faa cc Joel Jacobson j...@gluefinance.com, pgsql-performance@postgresql.org, Robert Haas robertmh...@gmail.com Subject Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index On Wed, Apr 7, 2010 at 1:20 PM, sherry.ctr@faa.gov wrote: Guys, Thanks for trying and opening your mind. If you want to know how Oracle addressed this issue, here it is: index on two columns. I remember that they told me in the training postgres has no this kind of index, can someone clarify? lies. postgresql allows you indices on multiple columns. What it does not have, is index on multiple tables. -- GJ
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
2010/4/7 sherry.ctr@faa.gov Do you mean one index on two columns? something like this: create index idx1 on tb1(col1, col2); yup :) It would be quite useless without that feature. Don't listen to oracle folks, they obviously know not much about products others than oracle db(s). -- GJ
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
Please just let me know if Postgres can do this kind of index or not. create index idx1 on tb1(col1, col2) Then later we can find it is useful or useless. Thanks much! Xuefeng Zhu (Sherry) Crown Consulting Inc. -- Oracle DBA AIM Lab Data Team (703) 925-3192 Grzegorz Jaśkiewicz gryz...@gmail.com Sent by: pgsql-performance-ow...@postgresql.org 04/07/2010 09:12 AM To Sherry CTR Zhu/AWA/CNTR/f...@faa cc Joel Jacobson j...@gluefinance.com, pgsql-performance@postgresql.org, Robert Haas robertmh...@gmail.com Subject Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index 2010/4/7 sherry.ctr@faa.gov Do you mean one index on two columns? something like this: create index idx1 on tb1(col1, col2); yup :) It would be quite useless without that feature. Don't listen to oracle folks, they obviously know not much about products others than oracle db(s). -- GJ
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
On Wed, 7 Apr 2010, sherry.ctr@faa.gov wrote: Please just let me know if Postgres can do this kind of index or not. create index idx1 on tb1(col1, col2) Then later we can find it is useful or useless. Have you tried it? Grzegorz Jaśkiewicz gryz...@gmail.com wrote: something like this: create index idx1 on tb1(col1, col2); yup :) For those of you who are not native English speakers, Yup is a synonym for Yes. Matthew -- Richards' Laws of Data Security: 1. Don't buy a computer. 2. If you must buy a computer, don't turn it on. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] query slow; strace output worrisome
On Tue, Apr 6, 2010 at 10:32 PM, Craig Ringer cr...@postnewspapers.com.au wrote: On 7/04/2010 12:24 AM, Brian Cox wrote: On 04/06/2010 01:18 AM, Craig Ringer [cr...@postnewspapers.com.au] wrote: I'm wondering if the issue is with strace rather than Pg. That is to say, that strace is trying to print: Thanks, Craig: I do think that this is a strace issue. As for what Pg is doing: creat() returns -1 on error and a file descriptor otherwise, so the return value appears to indicate success. I'm kind of wondering what the Pg backend is actually _doing_ though - if it was using sort temp files you'd expect open()/write()/read()/close() not just creat() calls. My quesiton exactly: what is the backend doing calling creat() over and over again? Note that this query does complete -- in 30-40 mins. I'd assume it was tempfile creation, but for the fact that there's nothing but creat() calls. However, we can't trust strace. There may be more going on that is being hidden from strace via limitations on the ptrace syscall imposed by SELinux / AppArmor / whatever. I suggest turning on the logging options in Pg that report use of tempfiles and disk-spilled sorts, then have a look and see if Pg is in fact using on-disk temp files for sorts or materialized data sets. If it is, you might find that increasing work_mem helps your query out. Yeah, I'd start with EXPLAIN and then, if you can wait long enough, EXPLAIN ANALYZE. You'll probably find it's doing a big sort or a big hash join. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] indexes in partitioned tables - again
On Tue, Apr 6, 2010 at 5:37 PM, Samuel Gendler sgend...@ideasculptor.com wrote: In part, I'm surprised that the index scan takes as long as it does, since I'd think an index would be able to return the set of keys relatively quickly. But that's a secondary issue. We don't actually have a facility built into the index-scan machinery to scan for distinct keys. It's doing a full scan of the index and then unique-ifying the results afterward. It produces the right answers, but it's definitely not as fast as it could be. The planner is not as smart about partitioned tables as it could be, either. A scan of the partitioned tables is implemented as an append node with one member per partition; and the planner isn't very good at pushing things down through append nodes. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Occasional giant spikes in CPU load
Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh). I managed to capture one such event using top(1) with the batch option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike. For some reason, every postgres backend suddenly decides (is told?) to do something. When this happens, the system become unusable for anywhere from ten seconds to a minute or so, depending on how much web traffic stacks up behind this event. We have two servers, one offline and one public, and they both do this, so it's not caused by actual web traffic (and the Apache logs don't show any HTTP activity correlated with the spikes). I thought based on other posts that this might be a background-writer problem, but it's not I/O, it's all CPU as far as I can tell. Any ideas where I can look to find what's triggering this? 8 CPUs, 8 GB memory 8-disk RAID10 (10k SATA) Postgres 8.3.0 Fedora 8, kernel is 2.6.24.4-64.fc8 Diffs from original postgres.conf: max_connections = 1000 shared_buffers = 2000MB work_mem = 256MB max_fsm_pages = 1600 max_fsm_relations = 625000 synchronous_commit = off wal_buffers = 256kB checkpoint_segments = 30 effective_cache_size = 4GB escape_string_warning = off Thanks, Craig top - 11:24:59 up 81 days, 20:27, 4 users, load average: 0.98, 0.83, 0.92 Tasks: 366 total, 20 running, 346 sleeping, 0 stopped, 0 zombie Cpu(s): 30.6%us, 1.5%sy, 0.0%ni, 66.3%id, 1.5%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 8194800k total, 8118688k used,76112k free, 36k buffers Swap: 2031608k total, 169348k used, 1862260k free, 7313232k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 18972 postgres 20 0 2514m 11m 8752 R 11 0.1 0:00.35 postmaster 10618 postgres 20 0 2514m 12m 9456 R9 0.2 0:00.54 postmaster 10636 postgres 20 0 2514m 11m 9192 R9 0.1 0:00.45 postmaster 25903 postgres 20 0 2514m 11m 8784 R9 0.1 0:00.21 postmaster 10626 postgres 20 0 2514m 11m 8716 R6 0.1 0:00.45 postmaster 10645 postgres 20 0 2514m 12m 9352 R6 0.2 0:00.42 postmaster 10647 postgres 20 0 2514m 11m 9172 R6 0.1 0:00.51 postmaster 18502 postgres 20 0 2514m 11m 9016 R6 0.1 0:00.23 postmaster 10641 postgres 20 0 2514m 12m 9296 R5 0.2 0:00.36 postmaster 10051 postgres 20 0 2514m 13m 10m R4 0.2 0:00.70 postmaster 10622 postgres 20 0 2514m 12m 9216 R4 0.2 0:00.39 postmaster 10640 postgres 20 0 2514m 11m 8592 R4 0.1 0:00.52 postmaster 18497 postgres 20 0 2514m 11m 8804 R4 0.1 0:00.25 postmaster 18498 postgres 20 0 2514m 11m 8804 R4 0.1 0:00.22 postmaster 10341 postgres 20 0 2514m 13m 9m R2 0.2 0:00.57 postmaster 10619 postgres 20 0 2514m 12m 9336 R1 0.2 0:00.38 postmaster 15687 postgres 20 0 2321m 35m 35m R0 0.4 8:36.12 postmaster -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
On Wed, 2010-04-07 at 14:37 -0700, Craig James wrote: Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh). I managed to capture one such event using top(1) with the batch option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike. What does iostat 5 say during the jump? Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
Craig James wrote: I managed to capture one such event using top(1) with the batch option as a background process. See output below You should add -c to your batch top capture, then you'll be able to see what the individual postmaster processes are actually doing when things get stuck. max_connections = 1000 shared_buffers = 2000MB work_mem = 256MB Mem: 8194800k total, 8118688k used,76112k free, 36k buffers Swap: 2031608k total, 169348k used, 1862260k free, 7313232k cached These settings appear way too high for a server with 8GB of RAM. I'm not sure if max_connections is too large, or if it's work_mem that's too big, but one or both of them may need to be tuned way down from where they are now to get your memory usage under control. Your server might running out of RAM during the periods where it becomes unresponsive--that could be the system paging stuff out to swap, which isn't necessarily a high user of I/O but it will block things. Not having any memory used for buffers is never a good sign. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
Craig James craig_ja...@emolecules.com writes: Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh). I managed to capture one such event using top(1) with the batch option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike. Any ideas where I can look to find what's triggering this? Postgres 8.3.0 ^ If it's really 8.3.0, try updating to 8.3.something-recent. We've fixed a whole lot of bugs since then. I have a suspicion that this might be an sinval overrun scenario, in which case you'd need to update to 8.4 to get a real fix. But updating in the 8.3 branch would be cheap and easy. If it is sinval overrun, it would presumably be triggered by a whole lot of catalog changes being made at approximately the same time. Can you correlate the spikes with anything like that? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
On Wed, 2010-04-07 at 14:45 -0700, Craig James wrote: On 4/7/10 2:40 PM, Joshua D. Drake wrote: On Wed, 2010-04-07 at 14:37 -0700, Craig James wrote: Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh). I managed to capture one such event using top(1) with the batch option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike. What does iostat 5 say during the jump? It's very hard to say ... I'll have to start a background job to watch for a day or so. While it's happening, you can't login, and any open windows become unresponsive. I'll probably have to run it at high priority using nice(1) to get any data at all during the event. Do you have sar runing? Say a sar -A ? Would vmstat be informative? Yes. My guess is that it is not CPU, it is IO and your CPU usage is all WAIT on IO. To have your CPUs so flooded that they are the cause of an inability to log in is pretty suspect. Joshua D. Drake Thanks, Craig -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
On Wed, Apr 7, 2010 at 2:37 PM, Craig James craig_ja...@emolecules.com wrote: Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh). You need to find out what all those Postgres processes are doing. You might try enabling update_process_title and then using ps to figure out what each instance is using. Otherwise, you might try enabling logging of commands that take a certain amount of time to run (see log_min_duration_statement). I managed to capture one such event using top(1) with the batch option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike. Looks like it. The system doesn't appear to be overloaded at all at that point. 8 CPUs, 8 GB memory 8-disk RAID10 (10k SATA) Postgres 8.3.0 Should definitely update to the latest 8.3.10 - 8.3 has a LOT of known bugs. Fedora 8, kernel is 2.6.24.4-64.fc8 Wow, that is very old, too. Diffs from original postgres.conf: max_connections = 1000 shared_buffers = 2000MB work_mem = 256MB work_mem is way too high for 1000 connections and 8GB ram. You could simply be starting up too many postgres processes and overwhelming the machine. Either significantly reduce max_connections or work_mem. max_fsm_pages = 1600 max_fsm_relations = 625000 synchronous_commit = off You are playing with fire here. You should never turn this off unless you do not care if your data becomes irrecoverably corrupted. top - 11:24:59 up 81 days, 20:27, 4 users, load average: 0.98, 0.83, 0.92 Tasks: 366 total, 20 running, 346 sleeping, 0 stopped, 0 zombie Cpu(s): 30.6%us, 1.5%sy, 0.0%ni, 66.3%id, 1.5%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 8194800k total, 8118688k used, 76112k free, 36k buffers Swap: 2031608k total, 169348k used, 1862260k free, 7313232k cached System load looks very much OK given that you have 8 CPUs. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 18972 postgres 20 0 2514m 11m 8752 R 11 0.1 0:00.35 postmaster 10618 postgres 20 0 2514m 12m 9456 R 9 0.2 0:00.54 postmaster 10636 postgres 20 0 2514m 11m 9192 R 9 0.1 0:00.45 postmaster 25903 postgres 20 0 2514m 11m 8784 R 9 0.1 0:00.21 postmaster 10626 postgres 20 0 2514m 11m 8716 R 6 0.1 0:00.45 postmaster 10645 postgres 20 0 2514m 12m 9352 R 6 0.2 0:00.42 postmaster 10647 postgres 20 0 2514m 11m 9172 R 6 0.1 0:00.51 postmaster 18502 postgres 20 0 2514m 11m 9016 R 6 0.1 0:00.23 postmaster 10641 postgres 20 0 2514m 12m 9296 R 5 0.2 0:00.36 postmaster 10051 postgres 20 0 2514m 13m 10m R 4 0.2 0:00.70 postmaster 10622 postgres 20 0 2514m 12m 9216 R 4 0.2 0:00.39 postmaster 10640 postgres 20 0 2514m 11m 8592 R 4 0.1 0:00.52 postmaster 18497 postgres 20 0 2514m 11m 8804 R 4 0.1 0:00.25 postmaster 18498 postgres 20 0 2514m 11m 8804 R 4 0.1 0:00.22 postmaster 10341 postgres 20 0 2514m 13m 9m R 2 0.2 0:00.57 postmaster 10619 postgres 20 0 2514m 12m 9336 R 1 0.2 0:00.38 postmaster 15687 postgres 20 0 2321m 35m 35m R 0 0.4 8:36.12 postmaster Judging by the amount of CPU time each postmaster as accumulated, they are all fairly new processes. How many pg proceses of the ~350 currently running are there? -Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
On 4/7/10 3:36 PM, Joshua D. Drake wrote: On Wed, 2010-04-07 at 14:45 -0700, Craig James wrote: On 4/7/10 2:40 PM, Joshua D. Drake wrote: On Wed, 2010-04-07 at 14:37 -0700, Craig James wrote: Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh). I managed to capture one such event using top(1) with the batch option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike. What does iostat 5 say during the jump? It's very hard to say ... I'll have to start a background job to watch for a day or so. While it's happening, you can't login, and any open windows become unresponsive. I'll probably have to run it at high priority using nice(1) to get any data at all during the event. Do you have sar runing? Say a sar -A ? No, I don't have it installed. I'll have a look. At first glance it looks like a combination of what I can get with top -b and vmstat, but with a single program. My guess is that it is not CPU, it is IO and your CPU usage is all WAIT on IO. To have your CPUs so flooded that they are the cause of an inability to log in is pretty suspect. I thought so too, except that I can't login during the flood. If the CPUs were all doing iowaits, logging in should be easy. Greg's suggestion that shared_buffers and work_mem are too big for an 8 GB system fits these symptoms -- if it's having a swap storm, login is effectively impossible. Craig Joshua D. Drake Thanks, Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
On 4/7/10 2:59 PM, Tom Lane wrote: Craig Jamescraig_ja...@emolecules.com writes: Most of the time Postgres runs nicely, but two or three times a day we get a huge spike in the CPU load that lasts just a short time -- it jumps to 10-20 CPU loads. Today it hit 100 CPU loads. Sometimes days go by with no spike events. During these spikes, the system is completely unresponsive (you can't even login via ssh). I managed to capture one such event using top(1) with the batch option as a background process. See output below - it shows 19 active postgress processes, but I think it missed the bulk of the spike. Any ideas where I can look to find what's triggering this? Postgres 8.3.0 ^ If it's really 8.3.0, try updating to 8.3.something-recent. We've fixed a whole lot of bugs since then. Good advice, I've been meaning to do this, maybe this will be a kick in the pants to motivate me. I have a suspicion that this might be an sinval overrun scenario, in which case you'd need to update to 8.4 to get a real fix. But updating in the 8.3 branch would be cheap and easy. If it is sinval overrun, it would presumably be triggered by a whole lot of catalog changes being made at approximately the same time. Can you correlate the spikes with anything like that? Not that I know of. Just regular web traffic. On the backup server these events happen occasionally even when there is little or no web traffic, and nobody logged in doing maintenance. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
...Can you correlate the spikes with anything like that? Not that I know of. Just regular web traffic. On the backup server these events happen occasionally even when there is little or no web traffic, and nobody logged in doing maintenance. What, if anything, are you logging in the PostgreSQL logs? Anything interesting, there? Cheers, Steve -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
On Wed, Apr 7, 2010 at 3:57 PM, Craig James craig_ja...@emolecules.com wrote: On 4/7/10 3:36 PM, Joshua D. Drake wrote: My guess is that it is not CPU, it is IO and your CPU usage is all WAIT on IO. To have your CPUs so flooded that they are the cause of an inability to log in is pretty suspect. I thought so too, except that I can't login during the flood. If the CPUs were all doing iowaits, logging in should be easy. No - logging in with high iowait is very harder to do than high CPU time because of latency of disk access. Greg's suggestion that shared_buffers and work_mem are too big for an 8 GB system fits these symptoms -- if it's having a swap storm, login is effectively impossible. A swap storm effectively puts the machine into very high iowait time. -Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
Craig James craig_ja...@emolecules.com writes: On 4/7/10 3:36 PM, Joshua D. Drake wrote: To have your CPUs so flooded that they are the cause of an inability to log in is pretty suspect. I thought so too, except that I can't login during the flood. If the CPUs were all doing iowaits, logging in should be easy. Greg's suggestion that shared_buffers and work_mem are too big for an 8 GB system fits these symptoms -- if it's having a swap storm, login is effectively impossible. Yeah, but there is also the question of what's causing all the backends to try to run at the same time. Oversubscribed memory could well be the direct cause of the machine getting driven into the ground, but there's something else going on here too IMO. Anyway I concur with the advice to lower shared_buffers, and run fewer backends if possible, to see if that ameliorates the problem. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
On Wed, Apr 7, 2010 at 6:56 PM, David Rees dree...@gmail.com wrote: max_fsm_pages = 1600 max_fsm_relations = 625000 synchronous_commit = off You are playing with fire here. You should never turn this off unless you do not care if your data becomes irrecoverably corrupted. That is not correct. Turning off synchronous_commit is sensible if you don't mind losing the last few transactions on a crash. What will corrupt your database is if you turn off fsync. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
On 4/7/10 5:47 PM, Robert Haas wrote: On Wed, Apr 7, 2010 at 6:56 PM, David Reesdree...@gmail.com wrote: max_fsm_pages = 1600 max_fsm_relations = 625000 synchronous_commit = off You are playing with fire here. You should never turn this off unless you do not care if your data becomes irrecoverably corrupted. That is not correct. Turning off synchronous_commit is sensible if you don't mind losing the last few transactions on a crash. What will corrupt your database is if you turn off fsync. A bit off the original topic, but ... I set it this way because I was advised that with a battery-backed RAID controller, this was a safe setting. Is that not the case? Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
On Wed, Apr 7, 2010 at 7:06 PM, Craig James craig_ja...@emolecules.com wrote: On 4/7/10 5:47 PM, Robert Haas wrote: On Wed, Apr 7, 2010 at 6:56 PM, David Reesdree...@gmail.com wrote: synchronous_commit = off You are playing with fire here. You should never turn this off unless you do not care if your data becomes irrecoverably corrupted. That is not correct. Turning off synchronous_commit is sensible if you don't mind losing the last few transactions on a crash. What will corrupt your database is if you turn off fsync. Whoops, you're right. A bit off the original topic, but ... I set it this way because I was advised that with a battery-backed RAID controller, this was a safe setting. Is that not the case? Robert has it right - with synchronous_commit off, your database will always be consistent, but you may lose transactions in the event of a crash. Doesn't matter if you have a BBU or not - all the BBU does is give the controller the ability to acknowledge a write without the data actually having been written to disk. According to the documentation, with synchronous_commit off and a default wal_writer_delay of 200ms, it's possible to lose up to a maximum of 600ms of data you thought were written to disk. -Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Occasional giant spikes in CPU load
On Wed, Apr 7, 2010 at 10:50 PM, David Rees dree...@gmail.com wrote: On Wed, Apr 7, 2010 at 7:06 PM, Craig James craig_ja...@emolecules.com wrote: On 4/7/10 5:47 PM, Robert Haas wrote: On Wed, Apr 7, 2010 at 6:56 PM, David Reesdree...@gmail.com wrote: synchronous_commit = off You are playing with fire here. You should never turn this off unless you do not care if your data becomes irrecoverably corrupted. That is not correct. Turning off synchronous_commit is sensible if you don't mind losing the last few transactions on a crash. What will corrupt your database is if you turn off fsync. Whoops, you're right. A bit off the original topic, but ... I set it this way because I was advised that with a battery-backed RAID controller, this was a safe setting. Is that not the case? Robert has it right - with synchronous_commit off, your database will always be consistent, but you may lose transactions in the event of a crash. Doesn't matter if you have a BBU or not - all the BBU does is give the controller the ability to acknowledge a write without the data actually having been written to disk. According to the documentation, with synchronous_commit off and a default wal_writer_delay of 200ms, it's possible to lose up to a maximum of 600ms of data you thought were written to disk. So, IOW, if you're running a social networking web site and your database is full of status updates sent by teenagers to other teenagers, you might judge that turning off synchronous_commit is a reasonable thing to do, if you need the performance. If you're running a bank and your database is full of information on wire transfers sent and received, not so much. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 3ware vs. MegaRAID
On Apr 6, 2010, at 9:49 AM, Ireneusz Pluta wrote: Greg Smith pisze: The MegaRAID SAS 84* cards have worked extremely well for me in terms of performance and features for all the systems I've seen them installed in. I'd consider it a modest upgrade from that 3ware card, speed wise. OK, sounds promising. The main issue with the MegaRAID cards is that you will have to write a lot of your own custom scripts to monitor for failures using their painful MegaCLI utility, and under FreeBSD that also requires using their Linux utility via emulation: http://www.freebsdsoftware.org/sysutils/linux-megacli.html And this is what worries me, as I prefer not to play with utilities too much, but put the hardware into production, instead. So I'd like to find more precisely if expected speed boost would pay enough for that pain. Let me ask the following way then, if such a question makes much sense with the data I provide. I already have another box with 3ware 9650SE-16ML. With the array configured as follows: RAID-10, 14 x 500GB Seagate ST3500320NS, stripe size 256K, 16GB RAM, Xeon X5355, write caching enabled, BBU, FreeBSD 7.2, ufs, when testing with bonnie++ on idle machine, I got sequential block read/write around 320MB/290MB and random seeks around 660. Would that result be substantially better with LSI MegaRAID? My experiences with the 3ware 9650 on linux are similar -- horribly slow for some reason with raid 10 on larger arrays. Others have claimed this card performs well on FreeBSD, but the above looks just as bad as Linux. 660 iops is slow for 14 spindles of any type, although the raid 10 on might limit it to an effective 7 spindles on reading in which case its OK -- but should still top 100 iops per effective disk on 7200rpm drives unless the effective concurrency of the benchmark is low. My experience with the 9650 was that iops was OK, but sequential performance for raid 10 was very poor. On linux, I was able to get better sequential read performance like this: * set it up as 3 raid 10 blocks, each 4 drives (2 others spare or for xlog or something). Software RAID-0 these RAID 10 chunks together in the OS. * Change the linux 'readahead' block device parameter to at least 4MB (8192, see blockdev --setra) -- I don't know if there is a FreeBSD equivalent. A better raid card you should hit at minimum 800, if not 1000, MB/sec + depending on whether you bottleneck on your PCIe or SATA ports or not. I switched to two adaptec 5xx5 series cards (each with half the disks, software raid-0 between them) to get about 1200MB/sec max throughput and 2000iops from two sets of 10 Seagate STxxxNS 1TB drives. That is still not as good as it should be, but much better. FWIW, one set of 8 drives in raid 10 on the adaptec did about 750MB/sec sequential and ~950 iops read. It required XFS to do this, ext3 was 20% slower in throughput. A PERC 6 card (LSI MegaRaid clone) performed somewhere between the two. I don't like bonnie++ much, its OK at single drive tests but not as good at larger arrays. If you have time try fio, and create some custom profiles. Lastly, for these sorts of tests partition your array in smaller chunks so that you can reliably test the front or back of the drive. Sequential speed at the front of a typical 3.5 drive is about 2x as fast as at the end of the drive. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 3ware vs. MegaRAID
For a card level RAID controller, I am a big fan of the LSI , which is available in a PCIe riser form factor for blade / 1U servers, and comes with 0.5GB of battery backed cache. Full Linux support including mainline kernel drivers and command line config tools. Was using these with SAS expanders and 48x 1TB SATA-300 spindles per card, and it was pretty (adjective) quick for a card-based system ... comparable with a small FC-AL EMC Clariion CX3 series in fact, just without the redundancy. My only gripe is that as of 18 months ago, it did not support triples (RAID-10 with 3 drives per set instead of 2) ... I had a little knowledge is a dangerous thing client who was stars-in-the-eyes sold on RAID-6 and so wanted double drive failure protection for everything (and didn't get my explanation about how archive logs on other LUNs make this OK, or why RAID-5/6 sucks for a database, or really listen to anything I said :-) ... It would do RAID-10 quads however (weird...). Also decent in the Dell OEM'ed version (don't know the Dell PERC model number) though they tend to be a bit behind on firmware. MegaCLI isn't the slickest tool, but you can find Nagios scripts for it online ... what's the problem? The Clariion will send you (and EMC support) an email if it loses a drive, but I'm not sure that's worth the 1500% price difference ;-) Cheers Dave On Wed, Apr 7, 2010 at 10:29 PM, Scott Carey sc...@richrelevance.comwrote: On Apr 6, 2010, at 9:49 AM, Ireneusz Pluta wrote: Greg Smith pisze: The MegaRAID SAS 84* cards have worked extremely well for me in terms of performance and features for all the systems I've seen them installed in. I'd consider it a modest upgrade from that 3ware card, speed wise. OK, sounds promising. The main issue with the MegaRAID cards is that you will have to write a lot of your own custom scripts to monitor for failures using their painful MegaCLI utility, and under FreeBSD that also requires using their Linux utility via emulation: http://www.freebsdsoftware.org/sysutils/linux-megacli.html And this is what worries me, as I prefer not to play with utilities too much, but put the hardware into production, instead. So I'd like to find more precisely if expected speed boost would pay enough for that pain. Let me ask the following way then, if such a question makes much sense with the data I provide. I already have another box with 3ware 9650SE-16ML. With the array configured as follows: RAID-10, 14 x 500GB Seagate ST3500320NS, stripe size 256K, 16GB RAM, Xeon X5355, write caching enabled, BBU, FreeBSD 7.2, ufs, when testing with bonnie++ on idle machine, I got sequential block read/write around 320MB/290MB and random seeks around 660. Would that result be substantially better with LSI MegaRAID? My experiences with the 3ware 9650 on linux are similar -- horribly slow for some reason with raid 10 on larger arrays. Others have claimed this card performs well on FreeBSD, but the above looks just as bad as Linux. 660 iops is slow for 14 spindles of any type, although the raid 10 on might limit it to an effective 7 spindles on reading in which case its OK -- but should still top 100 iops per effective disk on 7200rpm drives unless the effective concurrency of the benchmark is low. My experience with the 9650 was that iops was OK, but sequential performance for raid 10 was very poor. On linux, I was able to get better sequential read performance like this: * set it up as 3 raid 10 blocks, each 4 drives (2 others spare or for xlog or something). Software RAID-0 these RAID 10 chunks together in the OS. * Change the linux 'readahead' block device parameter to at least 4MB (8192, see blockdev --setra) -- I don't know if there is a FreeBSD equivalent. A better raid card you should hit at minimum 800, if not 1000, MB/sec + depending on whether you bottleneck on your PCIe or SATA ports or not. I switched to two adaptec 5xx5 series cards (each with half the disks, software raid-0 between them) to get about 1200MB/sec max throughput and 2000iops from two sets of 10 Seagate STxxxNS 1TB drives. That is still not as good as it should be, but much better. FWIW, one set of 8 drives in raid 10 on the adaptec did about 750MB/sec sequential and ~950 iops read. It required XFS to do this, ext3 was 20% slower in throughput. A PERC 6 card (LSI MegaRaid clone) performed somewhere between the two. I don't like bonnie++ much, its OK at single drive tests but not as good at larger arrays. If you have time try fio, and create some custom profiles. Lastly, for these sorts of tests partition your array in smaller chunks so that you can reliably test the front or back of the drive. Sequential speed at the front of a typical 3.5 drive is about 2x as fast as at the end of the drive. -- Sent via pgsql-performance mailing list ( pgsql-performance@postgresql.org) To make changes to your subscription:
