[PERFORM] Performance difference between Slon master and slave
Hello all, I hope someone can help me with this. Postgres 9.4.4 Slon 2.2.4 Linux I am using slony-i to replicate a production database which is in the order of 70GB. I have a reasonably complex select query that runs in 40 seconds on the master but takes in the region of 30-40 minutes on the slave. The postgres configurations are identical and the machines are a similar specifications (12 core hyper threaded HP server and the slave has slightly less RAM: 132GB vs 148GB) The server running the slave database has a higher load than the one running the master though the load average on the slave machine was low (1-2) when running the test and the postgres process on the slave machine runs at 100% of a CPU with very little iowait on the server. Inspecting the execution plan shows that there are some differences, for example, the slave is using a HashAggregate when the master is simply grouping. There also seems to be a difference with the ordering of the sub plans. Armed with this knowledge I have set enable_hashagg to off and run the query again and it now takes 53 seconds on the slave which is a more acceptable difference and the execution plans now look very similar (one difference being that there is another HashAggregate in the master which is now missing on the slave and may account for the 13 seconds). I have isolated a much simpler query which I have detailed below with their execution plans which shows the difference on line 4. I would rather not disable hash aggregation on the slave as this might have other consequences so this raises a number of questions. Firstly Is there anything that I can do to stop this feature? Why is the slave behaving differently to the master? Thanks in advance for any help. Cheers Matthew explain with my_view_booking_pax_breakdown as ( SELECT bev.booking_id, ( SELECT count(*) AS count FROM passenger_version WHERE passenger_version.current_version = 'T'::bpchar AND passenger_version.deleted = 'F'::bpchar AND passenger_version.indicative_pax_type = 'A'::bpchar AND passenger_version.booking_id = bev.booking_id) AS adult_count, ( SELECT count(*) AS count FROM passenger_version WHERE passenger_version.current_version = 'T'::bpchar AND passenger_version.deleted = 'F'::bpchar AND passenger_version.indicative_pax_type = 'C'::bpchar AND passenger_version.booking_id = bev.booking_id) AS child_count, ( SELECT count(*) AS count FROM passenger_version WHERE passenger_version.current_version = 'T'::bpchar AND passenger_version.deleted = 'F'::bpchar AND passenger_version.indicative_pax_type = 'I'::bpchar AND passenger_version.booking_id = bev.booking_id) AS infant_count FROM booking_expanded_version bev GROUP BY bev.booking_id ) select * from "my_view_booking_pax_breakdown" "view_booking_pax_breakdown" INNER JOIN "booking"."booking_expanded_version" "booking_expanded_version" ON "view_booking_pax_breakdown"."booking_id"="booking_expanded_version"."booking_id" Master "Merge Join (cost=5569138.32..6158794.12 rows=2461265 width=1375)" " Merge Cond: (booking_expanded_version.booking_id = view_booking_pax_breakdown.booking_id)" " CTE my_view_booking_pax_breakdown" *"-> Group (cost=0.43..5545692.19 rows=215891 width=4)"* " Group Key: bev.booking_id" " -> Index Only Scan using booking_expanded_version_booking_idx on booking_expanded_version bev (cost=0.43..64607.40 rows=2461265 width=4)" " SubPlan 1" "-> Aggregate (cost=8.57..8.58 rows=1 width=0)" " -> Index Scan using passenger_version_idx_4 on passenger_version (cost=0.43..8.55 rows=5 width=0)" "Index Cond: (booking_id = bev.booking_id)" " SubPlan 2" "-> Aggregate (cost=8.45..8.46 rows=1 width=0)" " -> Index Scan using passenger_version_idx_3 on passenger_version passenger_version_1 (cost=0.42..8.45 rows=1 width=0)" "Index Cond: (booking_id = bev.booking_id)" " SubPlan 3" "-> Aggregate (cost=8.31..8.32 rows=1 width=0)" " -> Index Scan using passenger_version_idx_2 on passenger_version passenger_version_2 (cost=0.29..8.31 rows=1 width=0)" "Index Cond: (booking_id = bev.booking_id)" " -> Index Scan using booking_expanded_version_booking_idx on booking_expanded_version (cost=0.43..546584.09 rows=2461265 width=1347)" " -> Sort (cost=23445.70..23985.43 rows=215891 width=28)" "Sort Key: view_booking_pax_breakdown.booking_id" "-> CTE Scan on my_view_booking_pax_breakdown view_booking_pax_breakdown (cost=0.00..4317.82 rows=215891 width=28)" Slave "Merge Join (cost=6168518.91..6764756.86 rows=2505042 width=1299)" " Merge Cond: (booking_expanded_version.booking_id = view_booking_pax_breakdown.booking_id)" " CTE my_view_booking_pax_breakdown" *"->
Re: [PERFORM] Performance problems inside a stored procedure.
Thanks for your help Андрей your English is easily understandable and much better than my ... (Russian?). I managed to get the results of an analyze and this showed that an index was not being used correctly. It seems that I was passing in a varchar and not casting it to an int and this stopped the index from being used. I suppose this is a change in the implicit casting rules between version 7.4.7 and 8.x. Once I added the explicit cast the function now uses the correct plan and returns in about 3 ms which I suppose is the performance hit that a function call has. Anyway thanks very much for your time. Regards Matthew Андрей Репко wrote: Hello Matthew, Monday, January 28, 2008, 2:02:26 PM, Вы писали: ML I have a query which runs pretty quick ( 0.82ms) but when I put it ML inside a stored procedure it takes 10 times as long (11.229ms). Is ML this what you would expect and is there any way that I can get around ML this time delay? ML postgres.conf changes. ML shared_buffers = 500MB ML work_mem = 10MB ML maintenance_work_mem = 100MB ML effective_cache_size = 2048MB ML default_statistics_target = 1000 ML Thanks for any help. When you run it outside stored procedure optimizer know about your parameters, and know what rows (estimate count) will be selected, so it can create fine plan. When you put it into SP optimizer don't know nothing about value of your parameters, but MUST create plan for it. If table is frequently updateable plan, what was created for SP became bad, and need replaning. It's sample for obtaining plan (LeXa NalBat): create function f1 ( integer, integer ) returns void language plpgsql as $body$ declare _rec record; begin for _rec in explain -- put your query here select count(*) from t1 where id between $1 and $2 loop raise info '%', _rec.QUERY PLAN; end loop; return; end; $body$; Sorry for bad English. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] JDBC/Stored procedure performance issue
Hi Tom, Is there any way to work out what plan the query is using in side the function? I think I have a similar problem with a query taking much longer from inside a function than it does as a select statement. Regards Matthew Tom Lane wrote: Claire McLister [EMAIL PROTECTED] writes: When I do an EXPLAIN ANALYZE on one query that returns 3261 rows, it executes in a reasonable 159ms: ... If I issue the same query over JDBC or use a PSQL stored procedure, it takes over 3000 ms, which, of course is unacceptable! I suspect that the problem is with groupid = $1 instead of groupid = 57925. The planner is probably avoiding an indexscan in the parameterized case because it's guessing the actual value will match so many rows as to make a seqscan faster. Is the distribution of groupid highly skewed? You might get better results if you increase the statistics target for that column. Switching to something newer than 7.4.x might help too. 8.1 and up support bitmap indexscans which work much better for large numbers of hits, and correspondingly the planner will use one in cases where it wouldn't use a plain indexscan. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly _ This e-mail has been scanned for viruses by Verizon Business Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.verizonbusiness.com/uk
Re: [PERFORM] Performance problems inside a stored procedure.
Thanks Euler, I made the change to STABLE but it didn't seem to make any difference. On closer inspection it seems to have been a casting problem, I was passing a varchar into the function and then testing this for equality with an integer. The planner seems to have been unable to use this to access the index and so was returning too many rows and then filtering them. It looks like I still have to take a hit of 2ms or so to call the function but I guess that is not unreasonable. Thanks for your help and to everyone who answered this thread. Regards Matthew. Euler Taveira de Oliveira wrote: Matthew Lunnon wrote: Ahh, sorry, I have been too aggressive with my cutting, I am running 8.2.6 and the function is below. snip $BODY$ LANGUAGE 'sql' VOLATILE; ^^ I suspect that it's because you're using VOLATILE (so no good optimizations is done); did you try STABLE? Could you show us the EXPLAIN ANALYZE of query and function? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Performance issues migrating from 743 to 826
Hi
I am investigating migrating from postgres 743 to postgres 826 but
although the performance in postgres 826 seems to be generally better
there are some instances where it seems to be markedly worse, a factor
of up to 10. The problem seems to occur when I join to more than 4
tables. Has anyone else experienced anything similar or got any
suggestions as to what I might do? I am running on an intel box with two
hyper threaded cores and 4GB of RAM. I have tweaked the postgres.conf
files with these values and the query and explain output are below. In
this case the query takes 6.037 ms to run on 862 and 2.332 to run on 743.
Thanks in advance for any help.
Regards
Matthew
8.2.6
shared_buffers = 500MB
work_mem = 10MB
maintenance_work_mem = 100MB
effective_cache_size = 2048MB
default_statistics_target = 1000
7.4.3
shared_buffers = 51200
sort_mem = 10240
vacuum_mem = 81920
effective_cache_size = 102400
explain analyze
SELECT *
FROM market mrkt
JOIN market_group_relation mgr USING (market_id)
JOIN market_group mg USING (market_group_id)
JOIN market_group_price_relation mgpr USING (market_group_id)
JOIN accommodation_price_panel app ON
app.accommodation_price_panel_id = mgpr.price_panel_id
JOIN daily_rates dr USING (accommodation_price_panel_id)
WHERE mrkt.live 'X'::bpchar AND mg.live 'X'::bpchar AND app.live
'X'::bpchar
AND dr.min_group_size = 0
AND MARKET_ID = 10039
AND CODE = 'LONHRL'
AND CODE_TYPE = 'IS'
AND ROOM_TYPE = 'Zk'
AND BOARD_TYPE = 'BB'
AND CONTRACT_ID = '16077'
AND ( START_DATE BETWEEN '2008-05-22' AND '2008-05-31' OR '2008-05-22'
BETWEEN START_DATE AND END_DATE )
Nested Loop (cost=37.27..48.34 rows=1 width=458) (actual
time=1.474..2.138 rows=14 loops=1)
- Nested Loop (cost=37.27..42.34 rows=1 width=282) (actual
time=1.428..1.640 rows=2 loops=1)
- Hash Join (cost=37.27..40.68 rows=1 width=199) (actual
time=1.367..1.516 rows=2 loops=1)
Hash Cond: (outer.market_group_id =
inner.market_group_id)
- Seq Scan on market_group mg (cost=0.00..3.01 rows=78
width=81) (actual time=0.004..0.105 rows=80 loops=1)
Filter: (live 'X'::bpchar)
- Hash (cost=37.27..37.27 rows=1 width=126) (actual
time=1.325..1.325 rows=0 loops=1)
- Hash Join (cost=12.66..37.27 rows=1 width=126)
(actual time=1.051..1.321 rows=2 loops=1)
Hash Cond: (outer.market_group_id =
inner.market_group_id)
- Seq Scan on market_group_relation mgr
(cost=0.00..24.46 rows=27 width=31) (actual time=0.165..0.641 rows=30
loops=1)
Filter: (10039 = market_id)
- Hash (cost=12.66..12.66 rows=2 width=95)
(actual time=0.641..0.641 rows=0 loops=1)
- Nested Loop (cost=0.00..12.66
rows=2 width=95) (actual time=0.056..0.593 rows=27 loops=1)
- Index Scan using
accommodation_price_panel_idx1 on accommodation_price_panel app
(cost=0.00..6.02 rows=1 width=60) (actual time=0.037..0.200 rows=27
loops=1)
Index Cond: ((contract_id =
16077) AND ((code)::text = 'LONHRL'::text) AND (code_type = 'IS'::bpchar))
Filter: (live 'X'::bpchar)
- Index Scan using
market_group_price_relation_pkey on market_group_price_relation mgpr
(cost=0.00..6.62 rows=1 width=35) (actual time=0.007..0.008 rows=1
loops=27)
Index Cond:
(outer.accommodation_price_panel_id = mgpr.price_panel_id)
- Seq Scan on market mrkt (cost=0.00..1.65 rows=1 width=87)
(actual time=0.045..0.046 rows=1 loops=2)
Filter: ((live 'X'::bpchar) AND (market_id = 10039))
- Index Scan using daily_rates_pkey on daily_rates dr
(cost=0.00..5.99 rows=1 width=180) (actual time=0.022..0.113 rows=7
loops=2)
Index Cond: ((dr.accommodation_price_panel_id =
outer.price_panel_id) AND (dr.room_type = 'Zk'::bpchar))
Filter: ((min_group_size = 0) AND (board_type = 'BB'::bpchar)
AND (('2008-05-22'::date = start_date) OR (start_date =
'2008-05-22'::date)) AND (('2008-05-22'::date = end_date) OR
(start_date = '2008-05-22'::date)) AND (('2008-05-22'::date = st (..)
Total runtime: 2.332 ms
Nested Loop (cost=0.00..30.39 rows=1 width=458) (actual
time=0.123..5.841 rows=14 loops=1)
- Nested Loop (cost=0.00..29.70 rows=1 width=439) (actual
time=0.099..4.590 rows=189 loops=1)
- Nested Loop (cost=0.00..29.40 rows=1 width=358) (actual
time=0.091..3.243 rows=189 loops=1)
- Nested Loop (cost=0.00..21.07 rows=1 width=327)
(actual time=0.081..1.571 rows=189 loops=1)
- Nested Loop (cost=0.00..10.40 rows=1
width=147) (actual time=0.053..0.134 rows=27 loops=1)
- Seq Scan on market mrkt (cost=0.00..2.08
Re: [PERFORM] Performance problems inside a stored procedure.
Ahh, sorry, I have been too aggressive with my cutting, I am running 8.2.6 and the function is below. Thanks. Matthew CREATE OR REPLACE FUNCTION sp_get_price_panel_id(int4, varchar, varchar, varchar, bpchar) RETURNS SETOF t_market_price_panel AS $BODY$ SELECT * FROM market mrkt JOIN market_group_relation mgr USING (market_id) JOIN market_group mg USING (market_group_id) JOIN market_group_price_relation mgpr USING (market_group_id) JOIN accommodation_price_panel app ON app.accommodation_price_panel_id = mgpr.price_panel_id WHERE mrkt.live 'X'::bpchar AND mg.live 'X'::bpchar AND app.live 'X'::bpchar AND MARKET_ID = $1 AND CODE = $2 AND CODE_TYPE = $3::CHAR(2) AND CONTRACT_ID = $4 AND ( PRICE_PANEL_TYPE = 'B' OR PRICE_PANEL_TYPE = $5 ); $BODY$ LANGUAGE 'sql' VOLATILE; Heikki Linnakangas wrote: Matthew Lunnon wrote: I have a query which runs pretty quick ( 0.82ms) but when I put it inside a stored procedure it takes 10 times as long (11.229ms). Is this what you would expect and is there any way that I can get around this time delay? It depends. You'll need to show us the function. Also, what version of Postgres are you running? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Performance problems inside a stored procedure.
Hi ms I have a query which runs pretty quick ( 0.82ms) but when I put it inside a stored procedure it takes 10 times as long (11.229ms). Is this what you would expect and is there any way that I can get around this time delay? postgres.conf changes. shared_buffers = 500MB work_mem = 10MB maintenance_work_mem = 100MB effective_cache_size = 2048MB default_statistics_target = 1000 Thanks for any help. Regards Matthew. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance issues migrating from 743 to 826
Scott Marlowe wrote: Whatever email agent you're using seems to be quoting in a way that doesn't get along well with gmail, so I'm just gonna chop most of it rather than have it quoted confusingly... Heck, I woulda chopped a lot anyway to keep it small. :) Thanks again for your time. I'm using Thunderbird, maybe I need to upgrade. On Jan 28, 2008 9:27 AM, Matthew Lunnon [EMAIL PROTECTED] wrote: Scott Marlowe wrote: On Jan 28, 2008 5:41 AM, Matthew Lunnon [EMAIL PROTECTED] wrote: default_statistics_target = 1000 That's very high for the default. Planning times will be increased noticeably I had originally left the default_statistics_target at its default and then increased it to 100, but this did not seem to make much difference. I will reduce this down to something more normal again. You do know that if you create a column when the default is 10, then increase the default, it won't change the column's stats target, right? So, assuming the table was first created, then you changed the default, you'll now need to do: alter table xyz alter column abc set statistics 100; analyze xyz; for it to make any difference. Thanks I haven't looked into this yet, I'll look. When I changed the default_stats_target it did take a very long time to do its analyze so I assumed it was doing something. The queries were on exactly the same data. My interpretation of what is going on here is that 8.2.6 seems to be leaving the filtering of market_id to the very last point, which is why it ends up with 189 rows at this point instead of the 2 that 743 has. 743 seems to do that filtering much earlier and so reduce the number of rows at a much earlier point in the execution of the query. I guess that this is something to do with the planner which is why I tried increasing the default_statistics_target. Ahh, I'm guessing it's something that your 7.4 database CAN use an index on and your 8.2 data base can't use an index on. Like text in a non-C locale. Or something... Table def? Thanks, I'll take a look at that, is there any documentation on what 8.2.6. can't use in an index? It didn't seem to have complained about any of my indexes when I generated the database. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster _ This e-mail has been scanned for viruses by Verizon Business Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.verizonbusiness.com/uk -- Matthew Lunnon Technical Consultant RWA Ltd. [EMAIL PROTECTED] Tel: +44 (0)29 2081 5056 www.rwa-net.co.uk --
Re: [PERFORM] Performance issues migrating from 743 to 826
Hi Scott, Thanks for your time Regards Matthew Scott Marlowe wrote: On Jan 28, 2008 5:41 AM, Matthew Lunnon [EMAIL PROTECTED] wrote: Hi I am investigating migrating from postgres 743 to postgres 826 but although the performance in postgres 826 seems to be generally better there are some instances where it seems to be markedly worse, a factor of up to 10. The problem seems to occur when I join to more than 4 tables. Has anyone else experienced anything similar or got any suggestions as to what I might do? I am running on an intel box with two hyper threaded cores and 4GB of RAM. I have tweaked the postgres.conf files with these values and the query and explain output are below. In this case the query takes 6.037 ms to run on 862 and 2.332 to run on 743. It looks like the data are not the same in these two environments. 8.2.6 shared_buffers = 500MB work_mem = 10MB maintenance_work_mem = 100MB effective_cache_size = 2048MB default_statistics_target = 1000 That's very high for the default. Planning times will be increased noticeably I had originally left the default_statistics_target at its default and then increased it to 100, but this did not seem to make much difference. I will reduce this down to something more normal again. Plan for 7.4: Nested Loop (cost=37.27..48.34 rows=1 width=458) (actual time=1.474..2.138 rows=14 loops=1) - Nested Loop (cost=37.27..42.34 rows=1 width=282) (actual time=1.428..1.640 rows=2 loops=1) This is processing 2 rows... Total runtime: 2.332 ms While this is processing 189 rows: Nested Loop (cost=0.00..30.39 rows=1 width=458) (actual time=0.123..5.841 rows=14 loops=1) - Nested Loop (cost=0.00..29.70 rows=1 width=439) (actual time=0.099..4.590 rows=189 loops=1) Hardly seems a fair comparison. The queries were on exactly the same data. My interpretation of what is going on here is that 8.2.6 seems to be leaving the filtering of market_id to the very last point, which is why it ends up with 189 rows at this point instead of the 2 that 743 has. 743 seems to do that filtering much earlier and so reduce the number of rows at a much earlier point in the execution of the query. I guess that this is something to do with the planner which is why I tried increasing the default_statistics_target. _ This e-mail has been scanned for viruses by Verizon Business Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.verizonbusiness.com/uk
Re: [PERFORM] Performance issues migrating from 743 to 826
Hi Gregory/All,
Thanks for your time.
Yes the difference is pretty small but does seem to be consistent, the
problem that I have is that this is just part of the query, I have tried
to break things down so that I can see where the time is being spent. I
set the default_statistics_target to 1000 after going via 100 but it
seemed to make no difference.
I have a confession to make though, this is not like for like. I did in
fact have to add a couple of indexes to the data as the performance was
so bad with 8.2.6. Very sorry for that, it doesn't help. The actual
difference if from 2ms to 57ms when these indexes are removed which is
much more significant. Here is the like for like comparison with 8.2.6,
the indexes were added to the market_group_relation table since it is
doing a seq scan at the very end.
Nested Loop (cost=0.00..54.03 rows=1 width=458) (actual
time=0.279..57.457 rows=14 loops=1)
Join Filter: (mgr.market_group_id = mgpr.market_group_id)
- Nested Loop (cost=0.00..29.19 rows=1 width=439) (actual
time=0.102..4.867 rows=189 loops=1)
- Nested Loop (cost=0.00..28.91 rows=1 width=358) (actual
time=0.095..3.441 rows=189 loops=1)
- Nested Loop (cost=0.00..20.60 rows=1 width=327)
(actual time=0.082..1.639 rows=189 loops=1)
- Nested Loop (cost=0.00..9.95 rows=1 width=147)
(actual time=0.054..0.138 rows=27 loops=1)
- Seq Scan on market mrkt (cost=0.00..1.65
rows=1 width=87) (actual time=0.020..0.020 rows=1 loops=1)
Filter: ((live 'X'::bpchar) AND
(market_id = 10039))
- Index Scan using
accommodation_price_panel_idx1 on accommodation_price_panel app
(cost=0.00..8.30 rows=1 width=60) (actual time=0.029..0.079 rows=27
loops=1)
Index Cond: ((contract_id = 16077) AND
((code)::text = 'LONHRL'::text) AND (code_type = 'IS'::bpchar))
Filter: (live 'X'::bpchar)
- Index Scan using daily_rates_pkey on
daily_rates dr (cost=0.00..10.63 rows=1 width=180) (actual
time=0.021..0.041 rows=7 loops=27)
Index Cond:
((app.accommodation_price_panel_id = dr.accommodation_price_panel_id)
AND (dr.room_type = 'Zk'::bpchar) AND (dr.board_type = 'BB'::bpchar) AND
(dr.min_group_size = 0))
Filter: (((start_date = '2008-05-22'::date)
AND (start_date = '2008-05-31'::date)) OR (('2008-05-22'::date =
start_date) AND ('2008-05-22'::date = end_date)))
- Index Scan using market_group_price_relation_pkey on
market_group_price_relation mgpr (cost=0.00..8.30 rows=1 width=35)
(actual time=0.005..0.006 rows=1 loops=189)
Index Cond: (app.accommodation_price_panel_id =
mgpr.price_panel_id)
- Index Scan using market_group_pkey on market_group mg
(cost=0.00..0.27 rows=1 width=81) (actual time=0.003..0.004 rows=1
loops=189)
Index Cond: (mgpr.market_group_id = mg.market_group_id)
Filter: (live 'X'::bpchar)
- Seq Scan on market_group_relation mgr (cost=0.00..24.46 rows=30
width=31) (actual time=0.068..0.259 rows=30 loops=189)
Filter: (10039 = market_id)
Total runtime: 57.648 ms
Gregory Stark wrote:
Matthew Lunnon [EMAIL PROTECTED] writes:
In this case the query takes 6.037 ms to run on 862 and 2.332 to run on 743.
The difference between 2ms and 6ms is pretty negligable. A single context
switch or disk cache miss could throw the results off by that margin in either
direction.
But what plan does 7.4.3 come up with if you set enable_hashjoins = off? I'm
curious whether it comes up with the same nested loops plan as 8.2 and what
cost it says it has.
I'll investigate and let you know.
I think you need to find queries which take longer to have any reliable
performance comparisons. Note that the configuration parameters here aren't
the same at all, it's possible the change of effective_cache_size from 800k to
2GB is what's changing the cost estimation. I seem to recall a change in the
arithmetic for calculatin Nested loop costs too which made it more aggressive
in estimating cache effectiveness.
Incidentally, default_statistics_target=1000 is awfully aggressive. I found in
the past that that caused the statistics table to become much larger and much
slower to access. It may have caused some statistics to be toasted or it may
have just been the sheer volume of data present. It will also make your
ANALYZEs take a lot longer. I would suggest trying 100 first and incrementally
raising it rather than jumping straight to 1000. And preferably only on the
columns which really matter.
--
Matthew Lunnon
Technical Consultant
RWA Ltd.
[EMAIL PROTECTED]
Tel: +44 (0)29 2081 5056
www.rwa-net.co.uk
--
[PERFORM] Limited performance on multi core server
Hi, I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running postgres 7.4.3. This has been recompiled on the server for 64 stored procedure parameters, (I assume this makes postgres 64 bit but are not sure). When the server gets under load from database connections executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to limit at about 30-35% usage with no iowait reported. If I run a simple select at this time it takes 5 seconds, the same query runs in 300 millis when the server is not under load so it seems that the database is not performing well even though there is plenty of spare CPU. There does not appear to be large amounts of disk IO and my database is about 5.5G so this should fit comfortably in RAM. changes to postgresql.sql: max_connections = 500 shared_buffers = 96000 sort_mem = 10240 effective_cache_size = 100 Does anyone have any ideas what my bottle neck might be and what I can do about it? Thanks for any help. Matthew. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Limited performance on multi core server
Limiting the queries was our initial thought but we then hit a problem with connection pooling which didn't implement a fifo algorithm. Looks like I'll have to look deeper into the connection pooling. So you think the problem might be context switching on the server, I'll take a closer look at the this Thanks Matthew Sven Geisler wrote: Hi Matthew, I know exactly what you experience. We had a 4-way DC Opteron and Pg 7.4 too. You should monitor context switches. First suggest upgrade to 8.2.5 because the scale up is much better with 8.2. You need to limit the number of concurrent queries to less than 8 (8 cores) if you need to stay with Pg 7.4. The memory setting is looking good to me. I would increase sort_mem and effective_cache_size, but this would solve your problem. Best regards Sven. Matthew Lunnon schrieb: Hi, I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running postgres 7.4.3. This has been recompiled on the server for 64 stored procedure parameters, (I assume this makes postgres 64 bit but are not sure). When the server gets under load from database connections executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to limit at about 30-35% usage with no iowait reported. If I run a simple select at this time it takes 5 seconds, the same query runs in 300 millis when the server is not under load so it seems that the database is not performing well even though there is plenty of spare CPU. There does not appear to be large amounts of disk IO and my database is about 5.5G so this should fit comfortably in RAM. changes to postgresql.sql: max_connections = 500 shared_buffers = 96000 sort_mem = 10240 effective_cache_size = 100 Does anyone have any ideas what my bottle neck might be and what I can do about it? Thanks for any help. Matthew. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Matthew Lunnon Technical Consultant RWA Ltd. [EMAIL PROTECTED] Tel: +44 (0)29 2081 5056 www.rwa-net.co.uk --
Re: [PERFORM] Limited performance on multi core server
Thanks for the information Claus, Why would reducing the effective cache size help the processor usage? It seems that there is plenty of resources on the box although I can see that 10MB of sort space could mount up if we had 500 connections but at the moment we do not have anything like that number. Thanks Matthew. Claus Guttesen wrote: I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running postgres 7.4.3. This has been recompiled on the server for 64 stored procedure parameters, (I assume this makes postgres 64 bit but are not sure). When the server gets under load from database connections executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to limit at about 30-35% usage with no iowait reported. If I run a simple select at this time it takes 5 seconds, the same query runs in 300 millis when the server is not under load so it seems that the database is not performing well even though there is plenty of spare CPU. There does not appear to be large amounts of disk IO and my database is about 5.5G so this should fit comfortably in RAM. changes to postgresql.sql: max_connections = 500 shared_buffers = 96000 sort_mem = 10240 effective_cache_size = 100 Does anyone have any ideas what my bottle neck might be and what I can do about it? You might want to lower shared_buffers to a lower value. Mine is set at 32768. Is your db performing complex sort? Remember that this value is per connection. Maby 1024. effective_cache_size should also be lowered to something like 32768. As far as I understand shared_buffers and effective_cache_size have to be altered in reverse, ie. when lowering one the other can be raised. HTH. -- Matthew Lunnon Technical Consultant RWA Ltd. [EMAIL PROTECTED] Tel: +44 (0)29 2081 5056 www.rwa-net.co.uk --
Re: [PERFORM] Limited performance on multi core server
Ah I was afraid of that. Maybe I'll have to come out of the dark ages. Matthew Steinar H. Gunderson wrote: On Wed, Dec 12, 2007 at 10:16:43AM +, Matthew Lunnon wrote: Does anyone have any ideas what my bottle neck might be and what I can do about it? Your bottleneck is that you are using a very old version of PostgreSQL. Try 8.2 or (if you can) the 8.3 beta series -- it scales a _lot_ better in this kind of situation. /* Steinar */ -- Matthew Lunnon Technical Consultant RWA Ltd. [EMAIL PROTECTED] Tel: +44 (0)29 2081 5056 www.rwa-net.co.uk --
Re: [PERFORM] Limited performance on multi core server
Hi Sven, yes the patch would be great if you could send it to me, we have already had to compile postgres to up the number of function parameters from 32 to 64. Meanwhile I will try and persuade my colleagues to consider the upgrade option. Thanks Matthew Sven Geisler wrote: Hi Matthew, I remember that I also an issue with AMD Opterons before Pg 8.1 There is a specific Opteron behaviour on shared memory locks which adds a extra penalty during the execution time for Pg code before 8.1. I can you provide my patch for Pg 8.0 which should be adaptable for Pg 7.4 if you can compile PostgreSQL. But if you can upgrade you should upgrade to Pg 8.2.5 64-bit. The scale up for your concurrent queries will be great. Sven. Matthew Lunnon schrieb: Hi, I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running postgres 7.4.3. This has been recompiled on the server for 64 stored procedure parameters, (I assume this makes postgres 64 bit but are not sure). When the server gets under load from database connections executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to limit at about 30-35% usage with no iowait reported. If I run a simple select at this time it takes 5 seconds, the same query runs in 300 millis when the server is not under load so it seems that the database is not performing well even though there is plenty of spare CPU. There does not appear to be large amounts of disk IO and my database is about 5.5G so this should fit comfortably in RAM. changes to postgresql.sql: max_connections = 500 shared_buffers = 96000 sort_mem = 10240 effective_cache_size = 100 Does anyone have any ideas what my bottle neck might be and what I can do about it? Thanks for any help. Matthew. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Matthew Lunnon Technical Consultant RWA Ltd. [EMAIL PROTECTED] Tel: +44 (0)29 2081 5056 www.rwa-net.co.uk --
Re: [PERFORM] Limited performance on multi core server
Hi Sven, Yes I have done a reasonable amount of query tuning. The application is a web service using an apache/resin combination at the front end, we have thought about using resin threads to limit the number of connections but are worried about backing up connections in apache and getting some overflow here. But some kind of limiting of connections is probably required. Thanks Matthew Sven Geisler wrote: Hi Matthew, The context switching isn't the issue. This is an indicator which is useful to identify your problem. What kind of application do you running? Can you limit the database clients? We have a web application based on apache running. We have a limit number of apache processes which are able to connect the database. We use that to reduce the number of concurrent queries. The apache does the rest for us - the apache does queue incoming http request if all workers are busy. The configuration helps us to solve the performance issue with to much concurrent queries. I assume that you already checked you application and each sql query is necessary and tuned as best as you can. Regards Sven. Matthew Lunnon schrieb: Limiting the queries was our initial thought but we then hit a problem with connection pooling which didn't implement a fifo algorithm. Looks like I'll have to look deeper into the connection pooling. So you think the problem might be context switching on the server, I'll take a closer look at the this Thanks Matthew Sven Geisler wrote: Hi Matthew, I know exactly what you experience. We had a 4-way DC Opteron and Pg 7.4 too. You should monitor context switches. First suggest upgrade to 8.2.5 because the scale up is much better with 8.2. You need to limit the number of concurrent queries to less than 8 (8 cores) if you need to stay with Pg 7.4. The memory setting is looking good to me. I would increase sort_mem and effective_cache_size, but this would solve your problem. Best regards Sven. Matthew Lunnon schrieb: Hi, I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running postgres 7.4.3. This has been recompiled on the server for 64 stored procedure parameters, (I assume this makes postgres 64 bit but are not sure). When the server gets under load from database connections executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to limit at about 30-35% usage with no iowait reported. If I run a simple select at this time it takes 5 seconds, the same query runs in 300 millis when the server is not under load so it seems that the database is not performing well even though there is plenty of spare CPU. There does not appear to be large amounts of disk IO and my database is about 5.5G so this should fit comfortably in RAM. changes to postgresql.sql: max_connections = 500 shared_buffers = 96000 sort_mem = 10240 effective_cache_size = 100 Does anyone have any ideas what my bottle neck might be and what I can do about it? Thanks for any help. Matthew. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Matthew Lunnon Technical Consultant RWA Ltd. [EMAIL PROTECTED] Tel: +44 (0)29 2081 5056 www.rwa-net.co.uk -- -- Matthew Lunnon Technical Consultant RWA Ltd. [EMAIL PROTECTED] Tel: +44 (0)29 2081 5056 www.rwa-net.co.uk --
Re: [PERFORM] Limited performance on multi core server
Hi Sven, Does this mean that one option I have is to use a multi core Intel based server instead of an AMD based server? Matthew Sven Geisler wrote: Hi Matthew, I remember that I also an issue with AMD Opterons before Pg 8.1 There is a specific Opteron behaviour on shared memory locks which adds a extra penalty during the execution time for Pg code before 8.1. I can you provide my patch for Pg 8.0 which should be adaptable for Pg 7.4 if you can compile PostgreSQL. But if you can upgrade you should upgrade to Pg 8.2.5 64-bit. The scale up for your concurrent queries will be great. Sven. Matthew Lunnon schrieb: Hi, I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running postgres 7.4.3. This has been recompiled on the server for 64 stored procedure parameters, (I assume this makes postgres 64 bit but are not sure). When the server gets under load from database connections executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to limit at about 30-35% usage with no iowait reported. If I run a simple select at this time it takes 5 seconds, the same query runs in 300 millis when the server is not under load so it seems that the database is not performing well even though there is plenty of spare CPU. There does not appear to be large amounts of disk IO and my database is about 5.5G so this should fit comfortably in RAM. changes to postgresql.sql: max_connections = 500 shared_buffers = 96000 sort_mem = 10240 effective_cache_size = 100 Does anyone have any ideas what my bottle neck might be and what I can do about it? Thanks for any help. Matthew. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Matthew Lunnon Technical Consultant RWA Ltd. [EMAIL PROTECTED] Tel: +44 (0)29 2081 5056 www.rwa-net.co.uk --
