Re: [PERFORM] Sun vs a P2. Interesting results.
On Tue, Aug 26, 2003 at 03:05:12PM -0400, Jeff wrote: > On Tue, 26 Aug 2003, Darcy Buskermolen wrote: > > I'm still seeing differences in the planner estimates, have you run a VACUUM > > ANALYZE prior to running these tests? > > > I did. I shall retry that.. but the numbers (the cost estimates) are > pretty close on both. the actual times are very different. I don't see why you need to bother, the query plans & cost estimates are similar enough I doubt that's the problem. > As I said in my first email IO isn't the problem here - the data set is > small enough that it is all cached (~10MB). iostat reports 0 activity on > the disks on both the sun and p2. Would it be possible to get a profile (e.g. gprof output) for a postgres backend executing the query on the Sun machine? -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Sun vs a P2. Interesting results.
On Tue, 26 Aug 2003, Darcy Buskermolen wrote: > I'm still seeing differences in the planner estimates, have you run a VACUUM > ANALYZE prior to running these tests? > I did. I shall retry that.. but the numbers (the cost estimates) are pretty close on both. the actual times are very different. > Also, are the disk subsystems in these 2 systems the same? You may be seeing > some discrepancies in things spindle speed, U160 vs U320, throughput on > specific RAID controlers, different blocksize, ect. > As I said in my first email IO isn't the problem here - the data set is small enough that it is all cached (~10MB). iostat reports 0 activity on the disks on both the sun and p2. and I just ran teh test again with 40 clients: 730s for hte p2, 1100 for the sun. (0% idle on both of them, no IO). I think the next I may try is recompiling with a newer gcc. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Sun vs a P2. Interesting results.
I'm still seeing differences in the planner estimates, have you run a VACUUM ANALYZE prior to running these tests? Also, are the disk subsystems in these 2 systems the same? You may be seeing some discrepancies in things spindle speed, U160 vs U320, throughput on specific RAID controlers, different blocksize, ect. On Tuesday 26 August 2003 11:41, Jeff wrote: > On Tue, 26 Aug 2003, Darcy Buskermolen wrote: > > Also, after having taken another look at this, you aren't preforming the > > same query on both datasets, so you can't expect them to generate the > > same results, or the same query plans, or even comparable times. Please > > retry your tests with identical queries , specify the dates, don;t use a > > function like now() to retrieve them. > > Given what you said in the previous email and this one here's some new > information. I redid the query to use a static starting time and I ran > 19 beaters in parallel. After I send this mail out I'll try it with 40. > > New Query: > > select userkey, dob, email, gender, country from userprofile > where gender_v and gender='m' > and country_v and country = 'br' > and dob_v > and dob >= '2003-08-26'::timestamptz - '29 > years'::interval > and dob <= '2003-08-26'::timestamptz - '18 years'::interval > order by dob asc > limit 20 > offset 100 > > Explain Analyze's: (basically the same) > Sun: > Limit (cost=2390.05..2390.10 rows=20 width=67) (actual > time=1098.34..1098.39 rows=20 loops=1) >-> Sort (cost=2389.80..2390.24 rows=178 width=67) (actual > time=1098.16..1098.28 rows=121 loops=1) > Sort Key: dob > -> Seq Scan on imuserprofile (cost=0.00..2383.14 rows=178 > width=67) (actual time=0.38..1068.94 rows=1783 loops=1) >Filter: (gender_v AND (gender = 'm'::character varying) AND > country_v AND (country = 'br'::character varying) AND dob_v AND (dob >= > '1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <= > '1985-08-26 00:00:00-04'::timestamp with time zone)) > Total runtime: 1099.93 msec > (6 rows) > > > p2 > > Limit (cost=2353.38..2353.43 rows=20 width=67) (actual > time=371.75..371.83 rows=20 loops=1) >-> Sort (cost=2353.13..2353.60 rows=186 width=67) (actual > time=371.46..371.63 rows=121 loops=1) > Sort Key: dob > -> Seq Scan on imuserprofile (cost=0.00..2346.14 rows=186 > width=67) (actual time=0.17..345.53 rows=1783 loops=1) >Filter: (gender_v AND (gender = 'm'::character varying) AND > country_v AND (country = 'br'::character varying) AND dob_v AND (dob >= > '1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <= > '1985-08-26 00:00:00-04'::timestamp with time zone)) > Total runtime: 372.63 msec > (6 rows) > > > I ran this query 100 times per beater (no prepared queries) and ran > 19 beaters in parellel. > > P2 Machine: 345sec avg > Sun:565sec avg > > > > I know solaris/sun isn't the preferred pg platform, and we have plenty of > capicty even with these numbers, I just find it a little suprising the > speed difference. -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Sun vs a P2. Interesting results.
On Tue, 26 Aug 2003, Darcy Buskermolen wrote: > Also, after having taken another look at this, you aren't preforming the same > query on both datasets, so you can't expect them to generate the same > results, or the same query plans, or even comparable times. Please retry your > tests with identical queries , specify the dates, don;t use a function like > now() to retrieve them. > Given what you said in the previous email and this one here's some new information. I redid the query to use a static starting time and I ran 19 beaters in parallel. After I send this mail out I'll try it with 40. New Query: select userkey, dob, email, gender, country from userprofile where gender_v and gender='m' and country_v and country = 'br' and dob_v and dob >= '2003-08-26'::timestamptz - '29 years'::interval and dob <= '2003-08-26'::timestamptz - '18 years'::interval order by dob asc limit 20 offset 100 Explain Analyze's: (basically the same) Sun: Limit (cost=2390.05..2390.10 rows=20 width=67) (actual time=1098.34..1098.39 rows=20 loops=1) -> Sort (cost=2389.80..2390.24 rows=178 width=67) (actual time=1098.16..1098.28 rows=121 loops=1) Sort Key: dob -> Seq Scan on imuserprofile (cost=0.00..2383.14 rows=178 width=67) (actual time=0.38..1068.94 rows=1783 loops=1) Filter: (gender_v AND (gender = 'm'::character varying) AND country_v AND (country = 'br'::character varying) AND dob_v AND (dob >= '1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <= '1985-08-26 00:00:00-04'::timestamp with time zone)) Total runtime: 1099.93 msec (6 rows) p2 Limit (cost=2353.38..2353.43 rows=20 width=67) (actual time=371.75..371.83 rows=20 loops=1) -> Sort (cost=2353.13..2353.60 rows=186 width=67) (actual time=371.46..371.63 rows=121 loops=1) Sort Key: dob -> Seq Scan on imuserprofile (cost=0.00..2346.14 rows=186 width=67) (actual time=0.17..345.53 rows=1783 loops=1) Filter: (gender_v AND (gender = 'm'::character varying) AND country_v AND (country = 'br'::character varying) AND dob_v AND (dob >= '1974-08-26 00:00:00-04'::timestamp with time zone) AND (dob <= '1985-08-26 00:00:00-04'::timestamp with time zone)) Total runtime: 372.63 msec (6 rows) I ran this query 100 times per beater (no prepared queries) and ran 19 beaters in parellel. P2 Machine: 345sec avg Sun:565sec avg I know solaris/sun isn't the preferred pg platform, and we have plenty of capicty even with these numbers, I just find it a little suprising the speed difference. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] What is the fastest way to get a resultset
On Tue, Aug 26, 2003 at 02:18:23AM -0700, Bupp Phillips wrote: > It there a way to get server side cursors with Postgresql like SQLServer has > or is this a limitation that it has? http://www.postgresql.org/docs/7.3/static/sql-declare.html http://www.postgresql.org/docs/7.3/static/sql-fetch.html -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Sun vs a P2. Interesting results.
Also, after having taken another look at this, you aren't preforming the same query on both datasets, so you can't expect them to generate the same results, or the same query plans, or even comparable times. Please retry your tests with identical queries , specify the dates, don;t use a function like now() to retrieve them. On Tuesday 26 August 2003 05:34, Jeff wrote: > Here's an interesting situation, and I think it may be just that Sun > stinks. > > I was recently given the go ahead to switch from Informix to Postgres on > one of our properties. (I had dozens of performance comparisons showing > how slow Informix was compared to it and my boss seeing me struggle trying > to make it run fast while Postgres, nearly out of the box, was simply > spanking it.). > > > Well, in order to facilitate things we were going to run pg on a 4 cpu > (ultrasparc ii 400Mhz) sun with 4gb of memory (also the current informix > box. It isn't very loaded). Now I know FreeBSD/Linux is preferred (and > where I do a lot of development and testing). But check this out for > interesting results. > > The Hardware: > Machine A: 4 CPU Sun Ultrasparc II 400Mhz, 4GB mem, 20GB RAID5, Solaris 8 > (32 bit mode) > > Machine B: 2 CPU Pentium II, 450Mhz, 512MB mem, 18GB RAID0 (2 old scsi > disks) Linux 2.4.18 (Stock redhat 8.0) > > The software: PG 7.3.4 compiled myself. (Reading specs from > /opt/sfw/lib/gcc-lib/sparc-sun-solaris2.9/2.95.3/specs gcc version 2.95.3 > 20010315 (release) (The solaris 8 box has no compilers, could this be the > issue?) and (Reading specs from > /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs > Configured with: ../configure --prefix=/usr --mandir=/usr/share/man > --infodir=/u > sr/share/info --enable-shared --enable-threads=posix --disable-checking > --host=i > 386-redhat-linux --with-system-zlib --enable-__cxa_atexit > Thread model: posix > gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)) > > Ok. Maybe the compiler (I'll try installing a newer gcc for sun later > today). > > The PG.conf: > shared_buffers = 2000 > sort_mem = 8192 > effective_cache_size = 32000 > tcpip_sockets = true > > The Schema: > > userprofile: > > userkey | character varying(128) | > displayname | character varying(128) | > displayname_v | boolean | default 'f' > name | character varying(128) | > name_v| boolean | default 'f' > email | character varying(128) | > email_v | boolean | default 'f' > gender| character varying(1) | > gender_v | boolean | default 'f' > country | character varying(64)| > country_v | boolean | default 'f' > zip | character varying(10)| > zip_v | boolean | default 'f' > city | character varying(128) | > city_v| boolean | default 'f' > state | character varying(10)| > state_v | boolean | default 'f' > lang | character varying(2) | > lang_v| boolean | default 'f' > url | character varying(255) | > url_v | boolean | default 'f' > phone | character varying(64)| > phone_v | boolean | default 'f' > phonemobile | character varying(64)| > phonemobile_v | boolean | default 'f' > phonefax | character varying(64)| > phonefax_v| boolean | default 'f' > dob | timestamp with time zone | > dob_v | boolean | default 'f' > interests_v | boolean | default 'f' > description | character varying(255) | > description2 | character varying(255) | > description_v | boolean | default 'f' > > (Yes, I kknow it isn't good - a lot of it is because it is the same schema > I had to use on informix. Convienantly you cannot do much with a textblob > on infomrix, so I have to use big varchar's, but that is a fiffernt > story). > > The magic query: > > select userkey, dob, email, gender, country from imuserprofile > where gender_v and gender='m' > and country_v and country = 'br' > and dob_v = 't' > and dob >= 'now'::timestamptz - '29 years'::interval > and dob <= 'now'::timestamptz - '18 years'::interval > order by dob asc > limit 20 > offset 100 > > (Page 5 of male brazillians, 18-29) > > Now the P2 runs this in about 0.3 seconds, and hte sun box runs it in 1 > second. > Here's the explain analyze's on each: > > P2: > Limit (cost=2484.52..2484.57 rows=20 width=67) (actual > time=377.32..377.41 row > s=20 loops=1) >-> Sort (cost=2484.27..2484.74 rows=186 width=67) (actual > time=377.02..377. > 21 rows=121 loops=1) > Sort Key: dob > -> Seq Scan on userprofile (cost=0.00..2477.28 rows=186 > width=67) ( > actual time=0.15..350.93 rows=1783 loops=1) >Filter: (gen
Re: [PERFORM] What is the fastest way to get a resultset
Is this something that can be done thru a SQL statement, or are you saying that I need to develop logic to handle this because the database won't hold the resultset on the server, but instead sends it all to the client? It there a way to get server side cursors with Postgresql like SQLServer has or is this a limitation that it has? Thanks "Jeff" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Mon, 25 Aug 2003, Bupp Phillips wrote: > > > > > I have a table that has 103,000 records in it (record size is about 953 > > bytes) and when I do a select all (select * from ) it takes a > > whopping 30 secs for the data to return!! > > > > SQLServer on the other hand takes 6 secs, but you can also use what is > > called a firehose cursor, which will return the data in < 1 sec. > > > You probably want a cursor. > Typically what happens is postgres sends _all_ the data to the client - > which can be rather substantial. A cursor allows you to say "get me the > first 1000 records. now the next 1000" - it should get you the speed you > want. > > > -- > Jeff Trout <[EMAIL PROTECTED]> > http://www.jefftrout.com/ > http://www.stuarthamm.net/ > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Sun vs a P2. Interesting results.
I spoke with my SUN admin, and this is what he had to say about what you are seeing. Sun gear is known to show a lower than Intel performance on light loads, rerun your test with 100 concurrent users (queries) and see what happens. Also he recommends installing a 64bit version of Solaris, the 32bit robs a lot of performance as well. On Tuesday 26 August 2003 05:34, Jeff wrote: > Here's an interesting situation, and I think it may be just that Sun > stinks. > > I was recently given the go ahead to switch from Informix to Postgres on > one of our properties. (I had dozens of performance comparisons showing > how slow Informix was compared to it and my boss seeing me struggle trying > to make it run fast while Postgres, nearly out of the box, was simply > spanking it.). > > > Well, in order to facilitate things we were going to run pg on a 4 cpu > (ultrasparc ii 400Mhz) sun with 4gb of memory (also the current informix > box. It isn't very loaded). Now I know FreeBSD/Linux is preferred (and > where I do a lot of development and testing). But check this out for > interesting results. > > The Hardware: > Machine A: 4 CPU Sun Ultrasparc II 400Mhz, 4GB mem, 20GB RAID5, Solaris 8 > (32 bit mode) > > Machine B: 2 CPU Pentium II, 450Mhz, 512MB mem, 18GB RAID0 (2 old scsi > disks) Linux 2.4.18 (Stock redhat 8.0) > > The software: PG 7.3.4 compiled myself. (Reading specs from > /opt/sfw/lib/gcc-lib/sparc-sun-solaris2.9/2.95.3/specs gcc version 2.95.3 > 20010315 (release) (The solaris 8 box has no compilers, could this be the > issue?) and (Reading specs from > /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs > Configured with: ../configure --prefix=/usr --mandir=/usr/share/man > --infodir=/u > sr/share/info --enable-shared --enable-threads=posix --disable-checking > --host=i > 386-redhat-linux --with-system-zlib --enable-__cxa_atexit > Thread model: posix > gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)) > > Ok. Maybe the compiler (I'll try installing a newer gcc for sun later > today). > > The PG.conf: > shared_buffers = 2000 > sort_mem = 8192 > effective_cache_size = 32000 > tcpip_sockets = true > > The Schema: > > userprofile: > > userkey | character varying(128) | > displayname | character varying(128) | > displayname_v | boolean | default 'f' > name | character varying(128) | > name_v| boolean | default 'f' > email | character varying(128) | > email_v | boolean | default 'f' > gender| character varying(1) | > gender_v | boolean | default 'f' > country | character varying(64)| > country_v | boolean | default 'f' > zip | character varying(10)| > zip_v | boolean | default 'f' > city | character varying(128) | > city_v| boolean | default 'f' > state | character varying(10)| > state_v | boolean | default 'f' > lang | character varying(2) | > lang_v| boolean | default 'f' > url | character varying(255) | > url_v | boolean | default 'f' > phone | character varying(64)| > phone_v | boolean | default 'f' > phonemobile | character varying(64)| > phonemobile_v | boolean | default 'f' > phonefax | character varying(64)| > phonefax_v| boolean | default 'f' > dob | timestamp with time zone | > dob_v | boolean | default 'f' > interests_v | boolean | default 'f' > description | character varying(255) | > description2 | character varying(255) | > description_v | boolean | default 'f' > > (Yes, I kknow it isn't good - a lot of it is because it is the same schema > I had to use on informix. Convienantly you cannot do much with a textblob > on infomrix, so I have to use big varchar's, but that is a fiffernt > story). > > The magic query: > > select userkey, dob, email, gender, country from imuserprofile > where gender_v and gender='m' > and country_v and country = 'br' > and dob_v = 't' > and dob >= 'now'::timestamptz - '29 years'::interval > and dob <= 'now'::timestamptz - '18 years'::interval > order by dob asc > limit 20 > offset 100 > > (Page 5 of male brazillians, 18-29) > > Now the P2 runs this in about 0.3 seconds, and hte sun box runs it in 1 > second. > Here's the explain analyze's on each: > > P2: > Limit (cost=2484.52..2484.57 rows=20 width=67) (actual > time=377.32..377.41 row > s=20 loops=1) >-> Sort (cost=2484.27..2484.74 rows=186 width=67) (actual > time=377.02..377. > 21 rows=121 loops=1) > Sort Key: dob > -> Seq Scan on userprofile (cost=0.00..2477.28 rows=186 > width=67) ( > actual time=0.15..350.93 rows=1783 loops=1) >F
Re: [PERFORM] Best tweak for fast results.. ?
On Tuesday 26 August 2003 14:42, JM wrote: > need input on parameter values on confs... > > our database is getting 1000 transactions/sec on peak periods.. > > sitting on RH 7.3 > 2.4.7-10smp > RAM: 1028400 > SWAP: 2040244 > > queries are just simple select statements based on timestamps, varchars... > less on joins... on a 300K rows.. Assuming you're getting good query plans (check the output of EXPLAIN ANALYSE)... Start by checking the output of vmstat/iostat during busy periods - this will tell you whether CPU/IO/RAM is the bottleneck. There is a good starter for tuning PG at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php Assuming your rows aren't too wide, they're probably mostly cached by Linux, so you probably don't want to overdo the shared buffers/sort memory and make sure the effective cache size is accurate. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Sun vs a P2. Interesting results.
Here's an interesting situation, and I think it may be just that Sun stinks. I was recently given the go ahead to switch from Informix to Postgres on one of our properties. (I had dozens of performance comparisons showing how slow Informix was compared to it and my boss seeing me struggle trying to make it run fast while Postgres, nearly out of the box, was simply spanking it.). Well, in order to facilitate things we were going to run pg on a 4 cpu (ultrasparc ii 400Mhz) sun with 4gb of memory (also the current informix box. It isn't very loaded). Now I know FreeBSD/Linux is preferred (and where I do a lot of development and testing). But check this out for interesting results. The Hardware: Machine A: 4 CPU Sun Ultrasparc II 400Mhz, 4GB mem, 20GB RAID5, Solaris 8 (32 bit mode) Machine B: 2 CPU Pentium II, 450Mhz, 512MB mem, 18GB RAID0 (2 old scsi disks) Linux 2.4.18 (Stock redhat 8.0) The software: PG 7.3.4 compiled myself. (Reading specs from /opt/sfw/lib/gcc-lib/sparc-sun-solaris2.9/2.95.3/specs gcc version 2.95.3 20010315 (release) (The solaris 8 box has no compilers, could this be the issue?) and (Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/u sr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i 386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)) Ok. Maybe the compiler (I'll try installing a newer gcc for sun later today). The PG.conf: shared_buffers = 2000 sort_mem = 8192 effective_cache_size = 32000 tcpip_sockets = true The Schema: userprofile: userkey | character varying(128) | displayname | character varying(128) | displayname_v | boolean | default 'f' name | character varying(128) | name_v| boolean | default 'f' email | character varying(128) | email_v | boolean | default 'f' gender| character varying(1) | gender_v | boolean | default 'f' country | character varying(64)| country_v | boolean | default 'f' zip | character varying(10)| zip_v | boolean | default 'f' city | character varying(128) | city_v| boolean | default 'f' state | character varying(10)| state_v | boolean | default 'f' lang | character varying(2) | lang_v| boolean | default 'f' url | character varying(255) | url_v | boolean | default 'f' phone | character varying(64)| phone_v | boolean | default 'f' phonemobile | character varying(64)| phonemobile_v | boolean | default 'f' phonefax | character varying(64)| phonefax_v| boolean | default 'f' dob | timestamp with time zone | dob_v | boolean | default 'f' interests_v | boolean | default 'f' description | character varying(255) | description2 | character varying(255) | description_v | boolean | default 'f' (Yes, I kknow it isn't good - a lot of it is because it is the same schema I had to use on informix. Convienantly you cannot do much with a textblob on infomrix, so I have to use big varchar's, but that is a fiffernt story). The magic query: select userkey, dob, email, gender, country from imuserprofile where gender_v and gender='m' and country_v and country = 'br' and dob_v = 't' and dob >= 'now'::timestamptz - '29 years'::interval and dob <= 'now'::timestamptz - '18 years'::interval order by dob asc limit 20 offset 100 (Page 5 of male brazillians, 18-29) Now the P2 runs this in about 0.3 seconds, and hte sun box runs it in 1 second. Here's the explain analyze's on each: P2: Limit (cost=2484.52..2484.57 rows=20 width=67) (actual time=377.32..377.41 row s=20 loops=1) -> Sort (cost=2484.27..2484.74 rows=186 width=67) (actual time=377.02..377. 21 rows=121 loops=1) Sort Key: dob -> Seq Scan on userprofile (cost=0.00..2477.28 rows=186 width=67) ( actual time=0.15..350.93 rows=1783 loops=1) Filter: (gender_v AND (gender = 'm'::character varying) AND count ry_v AND (country = 'br'::character varying) AND (dob_v = true) AND (dob >= '197 4-08-26 07:13:15.903437-04'::timestamp with time zone) AND (dob <= '1985-08-26 0 7:13:15.903437-04'::timestamp with time zone)) Total runtime: 378.21 msec (6 rows) Sun: Limit (cost=2521.19..2521.24 rows=20 width=67) (actual time=1041.14..1041.20 r ows=20 loops=1) -> Sort (cost=2520.94..2521.39 rows=178 width=67) (actual time=1040.96..104 1.08 rows=121 loops=1) Sort Key: dob -> Seq Scan on userprofile (cost=0.00..2514.28 rows=178
[PERFORM] Best tweak for fast results.. ?
need input on parameter values on confs... our database is getting 1000 transactions/sec on peak periods.. sitting on RH 7.3 2.4.7-10smp RAM: 1028400 SWAP: 2040244 queries are just simple select statements based on timestamps, varchars... less on joins... on a 300K rows.. TIA ---(end of broadcast)--- TIP 3: 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
Re: [PERFORM] Query too slow
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Tue, 26 Aug 2003, Ang Chin Han wrote: >> Veering aside a bit, since we usually pinpoint performance problems by >> looking at EXPLAIN ANALYZE's differences between the planner's >> estimation and actual execution's stats, what's involved in parsing the >> EXPLAIN ANALYZE results, and highlighting the places where they are way >> different? Bold, underline, or put some asterisks in front of those steps. > The hardest part is determining where it matters I think. You can use the > row counts as the base for that, but going from 1 row to 50 is not > necessarily going to be an issue, but it might be if a nested loop is > chosen. We've been chatting about this idea among the Red Hat group. The RHDB Visual Explain tool (get it at http://sources.redhat.com/rhdb/) already computes the percent of total runtime represented by each plan node. It seems like we could highlight nodes based on a large difference between estimated and actual percentage, or just highlight the nodes that are more than X percent of the runtime. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Query too slow
On Tue, 26 Aug 2003, Ang Chin Han wrote: > Stephan Szabo wrote: > > > Looking at the explain: > > Veering aside a bit, since we usually pinpoint performance problems by > looking at EXPLAIN ANALYZE's differences between the planner's > estimation and actual execution's stats, what's involved in parsing the > EXPLAIN ANALYZE results, and highlighting the places where they are way > different? Bold, underline, or put some asterisks in front of those steps. The hardest part is determining where it matters I think. You can use the row counts as the base for that, but going from 1 row to 50 is not necessarily going to be an issue, but it might be if a nested loop is chosen. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Query too slow
Stephan Szabo wrote: Looking at the explain: Veering aside a bit, since we usually pinpoint performance problems by looking at EXPLAIN ANALYZE's differences between the planner's estimation and actual execution's stats, what's involved in parsing the EXPLAIN ANALYZE results, and highlighting the places where they are way different? Bold, underline, or put some asterisks in front of those steps. Makes looking at big EXPLAIN ANALYZE trees much easier. -- Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux 2:30pm up 243 days, 5:48, 8 users, load average: 5.52, 5.29, 5.10 pgp0.pgp Description: PGP signature
Re: [PERFORM] What is the fastest way to get a resultset
Bupp Phillips <[EMAIL PROTECTED]> wrote: > I'm very new to Postgresql, so don't beat me up to bad if you see a > problem, just inform me what I've done wrong. > > I'm use Postgresql 7.2 (PeerDirect's Windows port) on Win2000 384MB > RAM 10GB of Free space 800 Mhz, using the ODBC driver 7.03.01.00. > > I have a table that has 103,000 records in it (record size is about > 953 bytes) and when I do a select all (select * from ) it > takes a whopping 30 secs for the data to return!! > > SQLServer on the other hand takes 6 secs, but you can also use what is > called a firehose cursor, which will return the data in < 1 sec. > > I have done everything that I know how to speed this up, does anyone > have any advise? > Probably you need to fetch more than one row at a time. I made that misstake once myself :) Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] What is the fastest way to get a resultset
On Mon, 25 Aug 2003, Bupp Phillips wrote: > > I have a table that has 103,000 records in it (record size is about 953 > bytes) and when I do a select all (select * from ) it takes a > whopping 30 secs for the data to return!! > > SQLServer on the other hand takes 6 secs, but you can also use what is > called a firehose cursor, which will return the data in < 1 sec. > You probably want a cursor. Typically what happens is postgres sends _all_ the data to the client - which can be rather substantial. A cursor allows you to say "get me the first 1000 records. now the next 1000" - it should get you the speed you want. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Replication Ideas
A long time ago, in a galaxy far, far away, "Bupp Phillips" <[EMAIL PROTECTED]> wrote:
>I have a table that has 103,000 records in it (record size is about
>953 bytes) and when I do a select all (select * from ) it takes
>a whopping 30 secs for the data to return!!
>SQLServer on the other hand takes 6 secs, but you can also use what is
>called a firehose cursor, which will return the data in < 1 sec.
>I have done everything that I know how to speed this up, does anyone
>have any advise?
Have you VACUUMed the table? 30 seconds to start getting data back
from such a query _seems_ a liittle high.
It would be quite a reasonable idea to open up a CURSOR and request
the data in more byte-sized pieces so that the result set wouldn't
forcibly bloat in any one spot.
You start by submitting the cursor definition, inside a transaction:
begin transaction;
declare cursor my_fire_hose for select * from ;
You then iterate over the following, which fetches 1000 rows at a time:
fetch forward 1000 in my_fire_hose;
That should cut down the time it takes to start getting records to
near enough to zero...
--
output = reverse("gro.gultn" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/lisp.html
"Microsoft is sort of a mixture between the Borg and the
Ferengi. Combine the Borg marketing with Ferengi networking..."
-- Andre Beck in dcouln
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
