[PERFORM] Slow query with planner row strange estimation
Hello, I try to make a query run quicker but I don't really know how to give hints to the planner. We are using postgresql 8.4.3 64bit on ubuntu 9.10 server. The hardware is a 10 SAS drive (15k) on a single RAID 10 array with 8Go RAM. Queries come from J2EE application (OLAP cube), but running them in pg_admin perform the same way. I made a short example that shows what I think is the problem. The real query is much longer but with only one join it already cause problems. Here is the short example : select rfoadv_8.rfoadvsup as c8, sum(dwhinv.dwhinvqte) as m0 from dwhinv as dwhinv, rfoadv as rfoadv_8 where (dwhinv.dwhinv___rforefide = 'HPLUS' and (dwhinv.dwhinv___rfodomide = 'PMSI' and dwhinv.dwhinv___rfoindrvs = '1' and dwhinv.dwhinv___rfoindide='recN3_BB_reel') ) and dwhinv.dwhinv_p2rfodstide = rfoadv_8.rfoadvinf and rfoadv_8.rfoadvsup = 'ACTI' group by rfoadv_8.rfoadvsup dwhinv is a table with almost 6.000.000 records rfoadv is a view with 800.000 records rfoadv is based on rfoade which is 50.000 records Here is the explain analyse : GroupAggregate (cost=0.00..16.56 rows=1 width=13) (actual time=2028.452..2028.453 rows=1 loops=1) - Nested Loop (cost=0.00..16.54 rows=1 width=13) (actual time=0.391..1947.432 rows=42664 loops=1) Join Filter: (((ade2.rfoadegch)::text = (ade1.rfoadegch)::text) AND ((ade2.rfoadedrt)::text = (ade1.rfoadedrt)::text)) - Nested Loop (cost=0.00..12.54 rows=1 width=214) (actual time=0.304..533.281 rows=114350 loops=1) - Index Scan using dwhinv_rdi_idx on dwhinv (cost=0.00..4.87 rows=1 width=12) (actual time=0.227..16.827 rows=6360 loops=1) Index Cond: (((dwhinv___rforefide)::text = 'HPLUS'::text) AND ((dwhinv___rfodomide)::text = 'PMSI'::text) AND ((dwhinv___rfoindide)::text = 'recN3_BB_reel'::text) AND (dwhinv___rfoindrvs = 1)) - Index Scan using rfoade_dsi_idx on rfoade ade2 (cost=0.00..7.63 rows=3 width=213) (actual time=0.007..0.037 rows=18 loops=6360) Index Cond: ((ade2.rfoade_i_rfodstide)::text = (dwhinv.dwhinv_p2rfodstide)::text) - Index Scan using rfoade_pk on rfoade ade1 (cost=0.00..3.98 rows=1 width=213) (actual time=0.008..0.009 rows=0 loops=114350) Index Cond: (((ade1.rfoade___rforefide)::text = (ade2.rfoade___rforefide)::text) AND ((ade1.rfoade_i_rfodstide)::text = 'ACTI'::text) AND ((ade1.rfoade___rfovdeide)::text = (ade2.rfoade___rfovdeide)::text) AND (ade1.rfoadervs = ade2.rfoadervs)) We can see that the planner think that accessing dwhinv with the dwhinv_rdi_idx index will return 1 row, but in fact there are 6360. So the nested loop is not done with 1 loop but 6360. With only one Join, the query runs in about 1.5 sec which is not really long, but with 8 join, the same mistake is repeated 8 times, the query runs in 30-60 sec. I try to disable nested loop, hash join and merge join are done instead of nested loops, example query runs in 0.2 - 0.5 sec, and the real query no more that 1 sec ! Which is great. Here is the execution plan with nested loop off: GroupAggregate (cost=12.56..2453.94 rows=1 width=13) (actual time=817.306..817.307 rows=1 loops=1) - Hash Join (cost=12.56..2453.93 rows=1 width=13) (actual time=42.583..720.746 rows=42664 loops=1) Hash Cond: (((ade2.rfoade___rforefide)::text = (ade1.rfoade___rforefide)::text) AND ((ade2.rfoade___rfovdeide)::text = (ade1.rfoade___rfovdeide)::text) AND (ade2.rfoadervs = ade1.rfoadervs)) Join Filter: (((ade2.rfoadegch)::text = (ade1.rfoadegch)::text) AND ((ade2.rfoadedrt)::text = (ade1.rfoadedrt)::text)) - Hash Join (cost=4.88..2446.21 rows=1 width=214) (actual time=42.168..411.962 rows=114350 loops=1) Hash Cond: ((ade2.rfoade_i_rfodstide)::text = (dwhinv.dwhinv_p2rfodstide)::text) - Seq Scan on rfoade ade2 (cost=0.00..2262.05 rows=47805 width=213) (actual time=0.057..78.988 rows=47805 loops=1) - Hash (cost=4.87..4.87 rows=1 width=12) (actual time=41.632..41.632 rows=6360 loops=1) - Index Scan using dwhinv_rdi_idx on dwhinv (cost=0.00..4.87 rows=1 width=12) (actual time=0.232..28.199 rows=6360 loops=1) Index Cond: (((dwhinv___rforefide)::text = 'HPLUS'::text) AND ((dwhinv___rfodomide)::text = 'PMSI'::text) AND ((dwhinv___rfoindide)::text = 'recN3_BB_reel'::text) AND (dwhinv___rfoindrvs = 1)) - Hash (cost=7.63..7.63 rows=3 width=213) (actual time=0.347..0.347 rows=11 loops=1) - Index Scan using rfoade_dsi_idx on rfoade ade1 (cost=0.00..7.63 rows=3 width=213) (actual time=0.095..0.307 rows=11 loops=1) Index Cond: ((rfoade_i_rfodstide)::text = 'ACTI'::text) Even if dwhinv row estimation is wrong, the query is quicker So after looking at dwhinv_rdi_idx statistics, I found that dwhinv___rfoindide related stats wasn't good, so I try ALTER TABLE dwhinv ALTER dwhinv_p2rfodstide
Re: [PERFORM] Highly Efficient Custom Sorting
On Thu, Jul 1, 2010 at 8:46 PM, Eliot Gable egable+pgsql-performa...@gmail.com wrote: I have a long stored procedure (over 3,000 lines). Originally, it would take about 44ms to run the whole query. After lots and lots of tweaking, Postgres now runs the entire thing and gathers my results in just 15.2ms, which is very impressive given the hardware this is running on. Now, I used to return the results unsorted to my C++ backend and then sort them there using my custom sort order which provides prioritized, weighted random ordering with 4 different priority fields and 3 different weighting fields within 3 of those 4 priority fields. Needless to say, the sorting is quite complex. I wanted to cut down on the amount of data being delivered to my C++ backend, so I am using the stored procedure to insert a summary of my results directly into the database, which is far more efficient than dumping it all to the C++ backend (including stuff that isn't really needed there) and then dumping it all back to Postgres via INSERTS later in the execution path. The problem is that I want the results sorted in this custom order before they are stored in the database. (By sorted, I mean I want to include a field that lists a numerical order for the set of results.) Thus, I used to dump everything to the C++ program, perform the sorting, then INSERT back to Postgres. This was obviously not all that efficient. Now, the sorting in C++ took 1ms to accomplish. When I re-wrote the sorting in pl/pgsql using a couple of additional stored procedures, I discovered it is taking 15.2ms to perform the sort of the records within Postgres. This almost cancels out all of the prior optimizations I previously performed: T:20100702001841+0903010 TID:0x43945940 INFO:NOTICE: Sorting group ... snip ... /snip what are you sorting and how are you sorting it? merlin -- 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] Highly Efficient Custom Sorting
Yes, I have two pl/pgsql functions. They take a prepared set of data (just the row id of the original results, plus the particular priority and weight fields) and they return the same set of data with an extra field called order which contains a numerical order to apply when sorting the rows. One function uses the priority information to break everything into priority groups, then calls the other function for each priority group. Each time it gets results back from the inner function, it returns that set of results. When it has looped through all priority groups, then it returns the full built-up set of results back to the calling function. The pl/pgsql functions implementing the sort are as optimized as they are likely to get. I don't want to waste my time trying to further optimize pl/pgsql functions that are never going to be as fast and efficient as I need. I would rather spend that time re-writing it in C and get sorting back to 1ms. I guess the real question is, is a generic C sorting function my only real alternative? Is there anything else that would allow me to sort things faster than pl/pgsql functions? For example, if I used pl/perl, would I be able to expect considerably better performance for sorting than using pl/pgsql? What about other supported languages? If I can get close to 1ms sorting performance without resorting to C, it would save me much time and frustration. On Fri, Jul 2, 2010 at 12:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: Craig Ringer cr...@postnewspapers.com.au writes: On 02/07/10 08:46, Eliot Gable wrote: So, the bottom line is, I need a faster way to do this sorting. You haven't showed us how you're doing it at the moment, so it's awfully hard to comment usefully on possible approaches. I'm guessing from tea leaves, but the impression I got from Eliot's description is that he's using plpgsql functions as sort comparators. It's not surprising that that sucks performance-wise compared to having the equivalent logic in C/C++ functions used as comparators on the client side. plpgsql is no speed demon. Best fix might be to code the comparators as C functions on the server side. regards, tom lane -- Eliot Gable We do not inherit the Earth from our ancestors: we borrow it from our children. ~David Brower I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime. ~David Brower Esse oportet ut vivas, non vivere ut edas. (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero
[PERFORM] using dbt2 postgresql 8.4 - rampup time issue
Hi,
We are using dbt2 to check performance of postgresql 8.4 on Linux64 machine.
When we increase TERMINALS PER WAREHOUSE TPM value increase rapidly but
rampup time increase too , dbt2 estimated rampup time calculation do not work
properly that’s why it run the test for wrong duration i.e.
1.
Settings :
DATABASE CONNECTIONS: 50
TERMINALS PER WAREHOUSE: 10
SCALE FACTOR (WAREHOUSES): 200
DURATION OF TEST (in sec): 7200
Result :
Response Time (s)
Transaction %Average :90th %TotalRollbacks
%
- - --- ---
-
Delivery 3.96 0.285 : 0.023268830
0.00
New Order 45.26 0.360 : 0.010 307335 3082
1.01
Order Status 3.98 0.238 : 0.003270590
0.00
Payment 42.82 0.233 : 0.003 2908020
0.00
Stock Level 3.97 0.245 : 0.002269700
0.00
- - --- ---
-
2508.36 new-order transactions per minute (NOTPM)
120.1 minute duration
0 total unknown errors
2000 second(s) ramping up
2.
Settings :
DATABASE CONNECTIONS: 50
TERMINALS PER WAREHOUSE: 40
SCALE FACTOR (WAREHOUSES): 200
DURATION OF TEST (in sec): 7200
Result :
Response Time (s)
Transaction %Average :90th %TotalRollbacks
%
- - --- ---
-
Delivery 3.95 8.123 : 4.605436720
0.00
New Order 45.19 12.205 : 2.563 499356 4933
1.00
Order Status 4.00 7.385 : 3.314441750
0.00
Payment 42.89 7.221 : 1.920 4739120
0.00
Stock Level 3.97 7.093 : 1.887438680
0.00
- - --- ---
-
7009.40 new-order transactions per minute (NOTPM)
69.8 minute duration
0 total unknown errors
8016 second(s) ramping up
3.
Settings :
DATABASE CONNECTIONS: 50
TERMINALS PER WAREHOUSE: 40
SCALE FACTOR (WAREHOUSES): 200
DURATION OF TEST (in sec): 7200
Result :
Response Time (s)
Transaction %Average :90th %TotalRollbacks
%
- - --- ---
-
Delivery 3.98 9.095 :16.103152340
0.00
New Order 45.33 7.896 :14.794 173539 1661
0.97
Order Status 3.96 8.165 :13.989151560
0.00
Payment 42.76 7.295 :12.470 1637260
0.00
Stock Level 3.97 7.198 :12.520151980
0.00
- - --- ---
-
10432.09 new-order transactions per minute (NOTPM)
16.3 minute duration
0 total unknown errors
11227 second(s) ramping up
These results show that dbt2 test actually did not run for 2 hours but it start
varying with the increase of TERMINALS PER WAREHOUSE value i.e. 1st Run (
120.1 minute duration ), 2nd Run (69.8 minute duration) and 3rd Run (16.3
minute duration).
To fix and sync with the rampup time, I have made a minor change in the
dbt2-run-workload script i.e.
--- dbt2-run-workload 2010-07-02 08:18:06.0 -0400
+++ dbt2-run-workload 2010-07-02 08:20:11.0 -0400
@@ -625,7 +625,11 @@
done
echo -n estimated rampup time:
-do_sleep $SLEEP_RAMPUP
+#do_sleep $SLEEP_RAMPUP
+while ! grep START ${DRIVER_OUTPUT_DIR}/*/mix.log ; do
+ sleep 1
+done
+date
echo estimated rampup time has elapsed
# Clear the readprofile data after the driver ramps up.
What is rempup time ? And what do you think about the patch?. Can you please
guide me?. Thanks.
Best Regards,
Asif Naeem
_
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
https://signup.live.com/signup.aspx?id=60969
Re: [PERFORM] Highly Efficient Custom Sorting
On Fri, Jul 2, 2010 at 12:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm guessing from tea leaves, but the impression I got from Eliot's description is that he's using plpgsql functions as sort comparators. It's not surprising that that sucks performance-wise compared to having the equivalent logic in C/C++ functions used as comparators on the client side. plpgsql is no speed demon. Best fix might be to code the comparators as C functions on the server side. On Fri, 2 Jul 2010, Eliot Gable wrote: I guess the real question is, is a generic C sorting function my only real alternative? Sounds to me like you are not really listening. You don't need to code an entire sorting algorithm in C, as Postgres already has a pretty good one of those. All you need to do is implement a comparator of some kind. Inserting C functions into Postgres is pretty easy, especially on the level of comparators. Matthew -- For those of you who are into writing programs that are as obscure and complicated as possible, there are opportunities for... real fun here -- Computer Science Lecturer -- 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] Highly Efficient Custom Sorting
On Fri, Jul 2, 2010 at 10:50 AM, Matthew Wakeling matt...@flymine.org wrote: On Fri, Jul 2, 2010 at 12:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm guessing from tea leaves, but the impression I got from Eliot's description is that he's using plpgsql functions as sort comparators. It's not surprising that that sucks performance-wise compared to having the equivalent logic in C/C++ functions used as comparators on the client side. plpgsql is no speed demon. Best fix might be to code the comparators as C functions on the server side. On Fri, 2 Jul 2010, Eliot Gable wrote: I guess the real question is, is a generic C sorting function my only real alternative? Sounds to me like you are not really listening. You don't need to code an entire sorting algorithm in C, as Postgres already has a pretty good one of those. All you need to do is implement a comparator of some kind. Inserting C functions into Postgres is pretty easy, especially on the level of comparators. in recent versions of postgres you rarely if ever even have to do that -- row types are comparable w/o any extra work, as are arrays. If Eliot would just give a little more deal of WHAT he is trying to sort and HOW he is currently doing it, i suspect his problem will be trivially solved :-). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Question about partitioned query behavior
Hi, My question is regarding ORDER BY / LIMIT query behavior when using partitioning. I have a large table (about 100 columns, several million rows) partitioned by a column called day (which is the date stored as mmdd - say 20100502 for May 2nd 2010 etc.). Say the main table is called FACT_TABLE and each child table is called FACT_TABLE_mmdd (e.g. FACT_TABLE_20100502, FACT_TABLE_20100503 etc.) and has an appropriate CHECK constraint created on it to CHECK (day = mmdd). Postgres Version: PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 64-bit The query pattern I am looking at is (I have tried to simplify the column names for readability): SELECT F1 from FACT_TABLE where day = 20100502 and day = 20100507 # selecting for a week ORDER BY F2 desc LIMIT 100 This is what is happening: When I query from the specific day's (child) table, I get what I expect - a descending Index scan and good performance. # explain select F1 from FACT_TABLE_20100502 where day = 20100502 order by F2 desc limit 100; QUERY PLAN -- Limit (cost=0.00..4.81 rows=100 width=41) - Index Scan Backward using F2_20100502 on FACT_TABLE_20100502 (cost=0.00..90355.89 rows=1876985 width=41 ) Filter: (day = 20100502) BUT: When I do the same query against the parent table it is much slower - two things seem to happen - one is that the descending scan of the index is not done and secondly there seems to be a separate sort/limit at the end - i.e. all data from all partitions is retrieved and then sorted and limited - This seems to be much less efficient than doing a descending scan on each partition and limiting the results and then combining and reapplying the limit at the end. explain select F1 from FACT_TABLE where day = 20100502 order by F2 desc limit 100; QUERY PLAN --- Limit (cost=2084948.01..2084948.01 rows=100 width=41) - Sort (cost=2084948.01..2084994.93 rows=1876986 width=41) Sort Key: public.FACT_TABLE.F2 - Result (cost=100.00..2084230.64 rows=1876986 width=41) - Append (cost=100.00..2084230.64 rows=1876986 width=41) - Seq Scan on FACT_TABLE (cost=100.00..110.02 rows=1 width=186) Filter: (day = 20100502) - Seq Scan on FACT_TABLE_20100502 FACT_TABLE (cost=100.00..1084220.62 rows=1876985 width=4 1) Filter: (day = 20100502) (9 rows) Could anyone please explain why this is happening and what I can do to get the query to perform well even when querying from the parent table? Thanks, Ranga _ Hotmail is redefining busy with tools for the New Busy. Get more from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2
Re: [PERFORM] Highly Efficient Custom Sorting
On 7/2/10 6:59 AM, Eliot Gable wrote: Yes, I have two pl/pgsql functions. They take a prepared set of data (just the row id of the original results, plus the particular priority and weight fields) and they return the same set of data with an extra field called order which contains a numerical order to apply when sorting the rows. One function uses the priority information to break everything into priority groups, then calls the other function for each priority group. Each time it gets results back from the inner function, it returns that set of results. When it has looped through all priority groups, then it returns the full built-up set of results back to the calling function. The pl/pgsql functions implementing the sort are as optimized as they are likely to get. I don't want to waste my time trying to further optimize pl/pgsql functions that are never going to be as fast and efficient as I need. I would rather spend that time re-writing it in C and get sorting back to 1ms. I guess the real question is, is a generic C sorting function my only real alternative? Is there anything else that would allow me to sort things faster than pl/pgsql functions? For example, if I used pl/perl, would I be able to expect considerably better performance for sorting than using pl/pgsql? What about other supported languages? If I can get close to 1ms sorting performance without resorting to C, it would save me much time and frustration. Try coding it in perl on the server. It is MUCH easier to code, and you don't have to link anything or learn the esoteric details of the Postgres/C API. Perl itself is written in C, and some of it's operations are extremely fast. Depending on the size and complexity of your data structures, Perl code may be just as fast as code you could write in C. Even if it turns out to be slower than you like, it will give you a way to package up your sort functionality into a function call, so if you later find you need to replace the Perl function with a C function, the rest of your application won't change. 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] Question about partitioned query behavior
In postgresql.conf, what are your settings for constraint_exclusion? There are 3 settings - on, off, or partition. Mine are set as follows: constraint_exclusion = on# on, off, or partition Under 8.4.4 I had it set to partition, but the behavior was not what I expected so I set it back to on and only the applicable partitions get processed. From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Ranga Gopalan Sent: Friday, July 02, 2010 9:29 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Question about partitioned query behavior Hi, My question is regarding ORDER BY / LIMIT query behavior when using partitioning. I have a large table (about 100 columns, several million rows) partitioned by a column called day (which is the date stored as mmdd - say 20100502 for May 2nd 2010 etc.). Say the main table is called FACT_TABLE and each child table is called FACT_TABLE_mmdd (e.g. FACT_TABLE_20100502, FACT_TABLE_20100503 etc.) and has an appropriate CHECK constraint created on it to CHECK (day = mmdd). Postgres Version: PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 64-bit The query pattern I am looking at is (I have tried to simplify the column names for readability): SELECT F1 from FACT_TABLE where day = 20100502 and day = 20100507 # selecting for a week ORDER BY F2 desc LIMIT 100 This is what is happening: When I query from the specific day's (child) table, I get what I expect - a descending Index scan and good performance. # explain select F1 from FACT_TABLE_20100502 where day = 20100502 order by F2 desc limit 100; QUERY PLAN -- Limit (cost=0.00..4.81 rows=100 width=41) - Index Scan Backward using F2_20100502 on FACT_TABLE_20100502 (cost=0.00..90355.89 rows=1876985 width=41 ) Filter: (day = 20100502) BUT: When I do the same query against the parent table it is much slower - two things seem to happen - one is that the descending scan of the index is not done and secondly there seems to be a separate sort/limit at the end - i.e. all data from all partitions is retrieved and then sorted and limited - This seems to be much less efficient than doing a descending scan on each partition and limiting the results and then combining and reapplying the limit at the end. explain select F1 from FACT_TABLE where day = 20100502 order by F2 desc limit 100; QUERY PLAN --- Limit (cost=2084948.01..2084948.01 rows=100 width=41) - Sort (cost=2084948.01..2084994.93 rows=1876986 width=41) Sort Key: public.FACT_TABLE.F2 - Result (cost=100.00..2084230.64 rows=1876986 width=41) - Append (cost=100.00..2084230.64 rows=1876986 width=41) - Seq Scan on FACT_TABLE (cost=100.00..110.02 rows=1 width=186) Filter: (day = 20100502) - Seq Scan on FACT_TABLE_20100502 FACT_TABLE (cost=100.00..1084220.62 rows=1876985 width=4 1) Filter: (day = 20100502) (9 rows) Could anyone please explain why this is happening and what I can do to get the query to perform well even when querying from the parent table? Thanks, Ranga Hotmail is redefining busy with tools for the New Busy. Get more from your inbox. See how. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL :ON:WL:en-US:WM_HMP:042010_2
Re: [PERFORM] Low perfomance SUM and Group by large databse
On Tue, Jun 29, 2010 at 7:59 AM, Sergio Charpinel Jr. sergiocharpi...@gmail.com wrote: One more question about two specifics query behavior: If I add AND (ip_dst = x.x.x.x), it uses another plan and take a much more time. In both of them, I'm using WHERE clause. Why this behavior? With either query, the planner is choosing to scan backward through the acct_2010_26_pk index to get the rows in descending order by the bytes column. It keeps scanning until it finds 50 rows that match the WHERE clause. With just the critieria on stamp_inserted, matches are pretty common, so it doesn't have to scan very far before finding 50 suitable rows. But when you add the ip_dst = 'x.x.x.x' criterion, suddenly a much smaller percentage of the rows match and so it has to read much further into the index before it finds 50 that do. A second index on just the ip_dst column might help a lot - then it could consider index-scanning for the matching rows and sorting them afterwards. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] how to (temporarily) disable/minimize benefits of disk block cache or postgresql shared buffer
Dear Criag, Thanks for thinking about it.I do not understand why u feel OpenVz is weird. at the most its not very popular. But lets not get into that debate as its not the proper forum. From your reply i understand that there is not a easy and clean way of doing it. Since performance related profiling requires multiple iterations it is not feasible to reboot the machine. I think i will try to profile my code using new and unique input parameters in each iteration, this shall roughly serve my purpose. On Fri, Jul 2, 2010 at 8:30 AM, Craig Ringer cr...@postnewspapers.com.auwrote: On 02/07/10 01:59, Rajesh Kumar Mallah wrote: I had set it to 128kb it does not really work , i even tried your next suggestion. I am in virtualized environment particularly OpenVz. where echo 3 /proc/sys/vm/drop_caches does not work inside the virtual container, i did it in the hardware node but still does not give desired result. Yeah, if you're in a weird virtualized environment like that you're likely to have problems, because caching can be done at multiple levels. In the case of OpenVZ, it's hard to know what the guest and what the host even is sometimes, and I wouldn't trust it to respect things like the Linux VM cache management. You might have to fall back on the classic method: a program that tries to allocate as much RAM as it can. On Linux this is EXTREMELY unsafe unless you ensure you have vm overcommit disabled (see the postgresql docs) because by default Linux systems will never fail a memory allocation - instead they'll go looking for a big process to kill to free some memory. In theory this should be your memory gobbler program, but in reality the OOM killer isn't so predictable. So: try turning vm overcommit off, then writing (or finding) a simple program that keeps on malloc()ing memory until an allocation call fails. That should force any caches out, freeing you for another cold run. Note that this method won't just force out the obvious caches like postgresql data files. It also forces out things like caches of running binaries. Things will grind to an absolute crawl for a minute or two before resuming normal speed, because *everything* has to come back from disk at once. The same is true of using /proc/sys/vm/drop_caches to drop all caches. I guess, in the end, nothing really subtitutes for a good reboot. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/
Re: [PERFORM] Extremely high CPU usage when building tables
Dear Deb, i feel three distinct steps are present 1. CREATE TABLE 2. LOAding of data in tables 3. Creation of indexes It is still not clear when you are seeing your system becoming unresponsive to other application. Is it during loading of data or creation of indexes? 1. can you give any idea about how much data you are loading ? rows count or GB data etc 2. how many indexes are you creation ? regds Rajesh Kumar Mallah.
Re: [PERFORM] No hash join across partitioned tables?
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: I would have liked to get to this for 9.0 but I feel it's a bit late now. What do we want to do about the above issue? TODO item. Added to TODO: Have autoanalyze of parent tables occur when child tables are modified * http://archives.postgresql.org/message-id/aanlktinx8lltekwcyeq1rxvz6wmjvknezfxw5tknn...@mail.gmail.com I am surprised there is no documentation update requirement for this. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- 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] No hash join across partitioned tables?
Bruce Momjian br...@momjian.us writes: I am surprised there is no documentation update requirement for this. Somebody put something about it in the docs a few days ago, IIRC. 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] No hash join across partitioned tables?
On Fri, Jul 2, 2010 at 4:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: I am surprised there is no documentation update requirement for this. Somebody put something about it in the docs a few days ago, IIRC. That was me. http://archives.postgresql.org/pgsql-committers/2010-06/msg00144.php -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] No hash join across partitioned tables?
Robert Haas wrote: On Fri, Jul 2, 2010 at 4:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: I am surprised there is no documentation update requirement for this. Somebody put something about it in the docs a few days ago, IIRC. That was me. http://archives.postgresql.org/pgsql-committers/2010-06/msg00144.php Oh, thanks, I missed that. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- 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] using dbt2 postgresql 8.4 - rampup time issue
On Fri, Jul 2, 2010 at 7:38 AM, MUHAMMAD ASIF anaeem...@hotmail.com wrote:
Hi,
We are using dbt2 to check performance of postgresql 8.4 on Linux64 machine.
When we increase TERMINALS PER WAREHOUSE TPM value increase rapidly but
rampup time increase too , dbt2 estimated rampup time calculation do not
work properly that’s why it run the test for wrong duration i.e.
A clarification of terms may help to start. The terminals per
warehouse in the scripts correlates to the number terminals emulated.
An emulated terminal is tied to a warehouse's district. In other
words, the number of terminals translates to the number of districts
in a warehouse across the entire database. To increase the terminals
per warehouse implies you have scaled the database differently, which
I'm assuming is not the case here.
1.
Settings :
DATABASE CONNECTIONS: 50
TERMINALS PER WAREHOUSE: 10
SCALE FACTOR (WAREHOUSES): 200
DURATION OF TEST (in sec): 7200
Result :
Response Time (s)
Transaction % Average : 90th % Total
Rollbacks %
- - ---
--- -
Delivery 3.96 0.285 : 0.023 26883
0 0.00
New Order 45.26 0.360 : 0.010 307335
3082 1.01
Order Status 3.98 0.238 : 0.003 27059
0 0.00
Payment 42.82 0.233 : 0.003 290802
0 0.00
Stock Level 3.97 0.245 : 0.002 26970
0 0.00
- - ---
--- -
2508.36 new-order transactions per minute (NOTPM)
120.1 minute duration
0 total unknown errors
2000 second(s) ramping up
2.
Settings :
DATABASE CONNECTIONS: 50
TERMINALS PER WAREHOUSE: 40
SCALE FACTOR (WAREHOUSES): 200
DURATION OF TEST (in sec): 7200
Result :
Response Time (s)
Transaction % Average : 90th % Total
Rollbacks %
- - ---
--- -
Delivery 3.95 8.123 : 4.605 43672
0 0.00
New Order 45.19 12.205 : 2.563 499356
4933 1.00
Order Status 4.00 7.385 : 3.314 44175
0 0.00
Payment 42.89 7.221 : 1.920 473912
0 0.00
Stock Level 3.97 7.093 : 1.887 43868
0 0.00
- - ---
--- -
7009.40 new-order transactions per minute (NOTPM)
69.8 minute duration
0 total unknown errors
8016 second(s) ramping up
3.
Settings :
DATABASE CONNECTIONS: 50
TERMINALS PER WAREHOUSE: 40
SCALE FACTOR (WAREHOUSES): 200
DURATION OF TEST (in sec): 7200
Result :
Response Time (s)
Transaction % Average : 90th % Total
Rollbacks %
- - ---
--- -
Delivery 3.98 9.095 : 16.103 15234
0 0.00
New Order 45.33 7.896 : 14.794 173539
1661 0.97
Order Status 3.96 8.165 : 13.989 15156
0 0.00
Payment 42.76 7.295 : 12.470 163726
0 0.00
Stock Level 3.97 7.198 : 12.520 15198
0 0.00
- - ---
--- -
10432.09 new-order transactions per minute (NOTPM)
16.3 minute duration
0 total unknown errors
11227 second(s) ramping up
These results show that dbt2 test actually did not run for 2 hours but it
start varying with the increase of TERMINALS PER WAREHOUSE value i.e. 1st
Run ( 120.1 minute duration ), 2nd Run (69.8 minute duration) and 3rd Run
(16.3 minute duration).
The ramp up times are actually as expected (explained below). What
you are witnessing is more likely that the driver is crashing because
the values are out of range from the scale of the database. You have
effectively told the driver that there are more than 10 districts per
warehouse, and have likely not built the database that way. I'm
actually surprised the driver actually ramped up completely.
To fix and sync with the rampup time, I have made a minor change in the
dbt2-run-workload script i.e.
--- dbt2-run-workload 2010-07-02 08:18:06.0 -0400
+++ dbt2-run-workload 2010-07-02 08:20:11.0 -0400
@@ -625,7 +625,11 @@
done
echo -n estimated rampup time:
-do_sleep $SLEEP_RAMPUP
+#do_sleep $SLEEP_RAMPUP
+while ! grep START ${DRIVER_OUTPUT_DIR}/*/mix.log ; do
+ sleep 1
+done
+date
echo estimated rampup time has elapsed
# Clear the readprofile data after the driver ramps up.
What is rempup time ? And what do you think about the
Re: [PERFORM] Highly Efficient Custom Sorting
On 03/07/10 00:36, Craig James wrote: Perl itself is written in C, and some of it's operations are extremely fast. The same is true of PL/PgSQL, though ;-) The main advantage of PL/Perl is that it doesn't rely on the SPI to do everything. It's interpreted not compiled, but it has a much faster approach to interpretation than PL/PgSQL. Really, the right choice depends on exactly what the OP is doing and how, which they're not saying. Where's the code? -- Craig Ringer -- 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] Highly Efficient Custom Sorting
Well, I re-wrote the algorithm in Perl. However, it did not solve the speed
issue. Running time now is a whopping 240+ ms instead of the 31.8ms I was
getting before (15.2 of which is sorting). Here is the Perl code on the
sorting. I won't post the pl/pgsql code, because this is far more clear (in
my opinion) on what the algorithm does:
DROP TYPE IF EXISTS glbtype CASCADE;
CREATE TYPE glbtype AS (
id INTEGER,
group TEXT,
priority INTEGER,
weight INTEGER
);
DROP TYPE IF EXISTS glbtype_result CASCADE;
CREATE TYPE glbtype_result AS (
id INTEGER,
priority INTEGER,
weight INTEGER,
order BIGINT
);
CREATE OR REPLACE FUNCTION GroupedRandomWeightedLB(glbtype[]) RETURNS SETOF
glbtype_result AS
$BODY$
# Input is an array of a composite type
my ($input) = @_;
my %groups;
$input =~ s/^{|}$//g;
$input =~ s/[)(]//g;
my @rows;
my $count = 0;
while ($input $count 1) {
my ($id, $group, $prio, $weight, @rest) = split(/,/, $input);
push(@rows, {id = $id, group = $group, priority = $prio, weight =
$weight});
$count++;
$input = join(',', @rest);
}
if(scalar @rows 1) {
elog(NOTICE, ' No rows sent for sorting.');
return undef;
} else {
elog(NOTICE, ' '.(scalar @rows).' rows sent for sorting.');
}
foreach $rw (@rows) {
if($rw-{group} $rw-{priority} $rw-{weight}) {
push( @{ $groups{$rw-{group}}{$rw-{priority}} }, $rw);
elog(NOTICE, ' Pushing '.$rw-{group}.' with prio ('.$rw-{priority}.'),
weight ('.$rw-{weight}.') onto array.');
} else {
elog(NOTICE, ' Invalid sort row: Group ('.$rw-{group}.'), Prio
('.$rw-{priority}.'), Weight ('.$rw-{weight}.')');
}
}
foreach $group (keys %groups) {
elog(NOTICE, ' Sorting group '.$group.'...');
foreach $prio (keys %{$groups{$group}}) {
my @rows = @{ $groups{$group}{$prio} };
elog(NOTICE, 'Sorting '.(scalar @rows).' rows in priority
'.$prio.'...');
my @zeros;
my @nonzeros;
my $total_weight = 0;
my $row_order = 1;
for($row_id = 0; $row_id scalar @rows; $row_id++) {
my $row = $rows[$row_id];
$total_weight += $row-{weight};
elog(NOTICE, 'Total Weight ('.$total_weight.')');
if($row-{weight} == 0) {
push(@zeros, $row);
} else {
push(@nonzeros, $row);
}
}
my @first_order = (@zeros, @nonzeros);
undef(@zeros);
undef(@nonzeros);
while(scalar @first_order) {
elog(NOTICE, ' '.(scalar @first_order).' items remaining ...');
my $rand = int(rand($total_weight));
elog(NOTICE, ' Random weight ('.$rand.')');
my $running_weight = 0;
for($row_id = 0; $row_id scalar @first_order; $row_id++) {
my $row = $first_order[$row_id];
$running_weight += $row-{weight};
elog(NOTICE, ' Running weight ('.$running_weight.') Current Weight
('.$row-{weight}.')');
if($running_weight = $rand) {
elog(NOTICE, ': Priority ('.($row-{priority}).') Weight
('.($row-{weight}).')');
return_next(
{ id = int($row-{id}),
priority = int($row-{priority}),
weight = int($row-{weight}),
order = int($row_order) }
);
$row_order++;
splice(@first_order, $row_id, 1);
# Recalculate total weight
$total_weight = 0;
foreach $row (@first_order) {
$total_weight += $row-{weight};
}
elog(NOTICE, ': Remaining Weight ('.$total_weight.')');
break;
}
}
}
}
}
return undef;
$BODY$
LANGUAGE plperl VOLATILE;
5 rows sent for sorting.
Pushing GROUP_7 with prio (1), weight (0) onto array.
Pushing GROUP_7 with prio (1), weight (5) onto array.
Pushing GROUP_8 with prio (1), weight (1) onto array.
Pushing GROUP_8 with prio (1), weight (5) onto array.
Pushing GROUP_8 with prio (1), weight (5) onto array.
Sorting group GROUP_7...
Sorting 2 rows in priority 1...
Total Weight (0)
Total Weight (5)
2 items remaining ...
Random weight (0)
Running weight (0) Current Weight (0)
: Priority (1) Weight (0)
: Remaining Weight (5)
1 items remaining ...
Random weight (0)
Running weight (5) Current Weight (5)
: Priority (1) Weight (5)
: Remaining Weight (0)
Sorting group GROUP_8...
Sorting 3 rows in priority 1...
Total Weight (1)
Total Weight (6)
Total Weight (11)
3 items remaining ...
Random weight (8)
Running weight (1) Current Weight (1)
Running weight (6) Current Weight (5)
Running weight (11) Current Weight (5)
: Priority (1) Weight (5)
: Remaining Weight (6)
2 items remaining ...
Random weight (2)
Running weight (1) Current Weight (1)
Running weight (6) Current Weight (5)
: Priority (1) Weight (5)
: Remaining Weight (1)
1 items remaining ...
Random weight (0)
Running weight (1) Current Weight (1)
: Priority (1) Weight (1)
: Remaining Weight (0)
2 rows sent for sorting.
Pushing GROUP_1 with prio (1), weight (0) onto array.
Pushing GROUP_1 with prio (2), weight (4) onto array.
Sorting group GROUP_1...
Sorting 1 rows in priority 1...
Total Weight (0)
1 items remaining ...
Random weight (0)
Running weight (0) Current Weight (0)
: Priority (1) Weight (0)
: Remaining Weight (0)
Sorting 1 rows in priority 2...
Total Weight (4)
1 items remaining ...
Random weight (2)
Running weight (4) Current Weight (4)
: Priority (2) Weight (4)
: Remaining Weight (0)
Total runtime: 244.101 ms
On Fri, Jul 2, 2010 at 9:44 PM, Craig Ringer
