[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] 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) Filter: (gender_v AND (gender = 'm'::character varying) AND count ry_v AND (country = 'br'::character varying) AND
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 table) 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.
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: (gender_v AND (gender = 'm'::character varying) AND count ry_v AND (country = 'br'::character varying) AND (dob_v = true)
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.
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] 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.
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
