Re: [PERFORM] Query runs too long for indexed tables
Let me guess, you've updated it a lot and aren't familiar with Vacuum? run a vacuum full on your database. schedule a vacuum (plain one) to run every so often (hours or days are a good interval for most folks) If that's NOT your problem, then please, let us know. Scot, thank you. Excellent. If database is created and VACUUM ANALYZE is issued, this query runs fast. However, I need to speed up it during running script. This is a database creation script. Script does the following: 1. CREATE DATABASE foo; 2. START TRANSACTION; 3. Create 145 tables with primary keys. Add data to those tables. 4. Create some additional indexes 5. ANALYZE 6. Clear bad bad foreign keys fields using commands like UPDATE firma1.rid SET toode=NULL WHERE toode IS NOT NULL AND toode NOT IN (SELECT TOODE FROM firma1.TOODE); 7. Create foreign key references 8. COMMIT This script runs about 1 hour in modern server with fsync off. Largest table has 10 records, few other tables have 15000 records and remaining have fewer records. How to speed this up ? Is'nt running ANALYZE sufficient to speed up foreign key clearing ? It seems that ANALYZE does'nt work. Should I isse COMMIT before running ANALYZE or issue more commits? Server has 4 GB RAM postgres.conf file is default from 8.1.3 window zip file except the following settings are added to end: fsync=off shared_buffers = 3 redirect_stderr = on log_min_error_statement = error autovacuum = on ... also 2 stats settings from aurtovacuur max_fsm_pages = 3 Andrus. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Query runs too long for indexed tables
UPDATE firma1.rid SET toode=NULL WHERE toode IS NOT NULL AND toode NOT IN (SELECT TOODE FROM firma1.TOODE); Why not use a LEFT JOIN for this ? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Query runs too long for indexed tables
Why not use a LEFT JOIN for this ? UPDATE firma1.rid SET rid.toode=NULL LEFT join firma1.toode using(toode) WHERE rid.toode IS NOT NULL AND toode.toode IS NULL; Causes: ERROR: syntax error at or near LEFT at character 41 outer joins are not supported in Postgres UPDATE command. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Query runs too long for indexed tables
outer joins are not supported in Postgres UPDATE command. True (and sad). You can try the following script to play with the various options : DROP TABLE one; DROP TABLE two; CREATE TABLE one (a SERIAL PRIMARY KEY, b INT NULL); CREATE TABLE two (b INT NOT NULL PRIMARY KEY); INSERT INTO two (b) SELECT x*2 FROM generate_series( 1, 5 ) AS x; INSERT INTO one (b) SELECT x FROM generate_series( 1, 10 ) AS x; EXPLAIN ANALYZE SELECT count(*) FROM one LEFT JOIN two ON one.b=two.b WHERE two.b IS NULL; --Try with and without... --CREATE INDEX one_b ON one(b); VACUUM ANALYZE one; VACUUM ANALYZE two; EXPLAIN ANALYZE SELECT count(*) FROM one LEFT JOIN two ON one.b=two.b WHERE two.b IS NULL; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=NULL WHERE b NOT IN (SELECT b FROM two ); SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=NULL WHERE b IN (SELECT one.b FROM one LEFT JOIN two ON one.b=two.b WHERE two.b IS NULL); SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=NULL FROM one x LEFT JOIN two ON x.b=two.b WHERE two.b IS NULL AND one.a=x.a; SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=NULL FROM one x LEFT JOIN two ON x.b=two.b WHERE two.b IS NULL AND one.b=x.b; SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=(SELECT two.b FROM two WHERE two.b=one.b); SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; EXPLAIN ANALYZE UPDATE one SET b=NULL WHERE NOT EXISTS (SELECT 1 FROM two WHERE two.b = one.b); SELECT * FROM one ORDER BY a LIMIT 5; ROLLBACK; VACUUM one; BEGIN; CREATE TABLE tmp AS SELECT one.a, two.b FROM one LEFT JOIN two ON one.b=two.b; SELECT * FROM tmp ORDER BY a LIMIT 5; DROP TABLE one; ALTER TABLE tmp RENAME TO one; ROLLBACK; ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Query runs too long for indexed tables
Andrus [EMAIL PROTECTED] writes: UPDATE firma1.rid SET toode=NULL WHERE toode IS NOT NULL AND toode NOT IN (SELECT TOODE FROM firma1.TOODE); How to speed this up ? Increasing work_mem to the point where you get a hashed NOT-IN would help, probably. Have you tried using EXPLAIN to see what the plan is for the UPDATEs? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Greetings - I am testing a Sun Microsystems Sun Fire T2000 demo server at our company. I want to know if anyone here has any experience with this hardware and postgresql 8.1.3. I installed the copy of postgresql 8.1.3 from blastwave.org onto this demo box and loaded our production database into it. This box has a single Ultrasparc T1 cpu with six execution piplelines that can each handle 4 threads. With the Unix top utility the postgresql server appears to bounce around between the available threads on the system. For example I run a single large query and I can see the postgresql server sometimes running on cpu/0, other times on cpu/1, cpu/3,etc up to cpu/23. However, never is the load for the postgres server reported to be higher than 4.16%. I did the math and 4.16% x 24 threads = 98.84% cpu load. So I wonder if the Solaris 10kernel is somehow throttling the processes so that any single virtual processor can do no more than 4.16% load. We got this server last week and I was able to install it in our rack just yesterday. Now I need to see how I can optimize the postgresql server to work on this box. Does anyone have any suggestions? I know the postgresql server is not smp aware but I believe parts of it are. In particular the buffer manager is supposed to scale the performance almost linearly with the number of cpu's (including virtual ones). I don't know however, if I need to recompile the postgresql server myself to get those benefits. I am using the version of postgresql 8.1.3 that is available on blastwave.org. I am also working with the 64 bit version of the database server. This machine has over 8GB of ram so I was thinking of using the64 bit version of the postgresql server so I can access ram beyong the 4gb limit imposed by 32 bit addressing. Any help or recommendations for performance tweaking of postgresql is very much appreciated. Thanks, Juan
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Juan Casero \(FL FLC\) [EMAIL PROTECTED] writes: ... This box has a single Ultrasparc T1 cpu with six execution piplelines that can each handle 4 threads. With the Unix top utility the postgresql server appears to bounce around between the available threads on the system. Try sending it more than one query at a time? If you're testing with just one client connection issuing queries, that's about what I'd expect. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Juan, On 4/5/06 11:12 AM, Juan Casero (FL FLC) [EMAIL PROTECTED] wrote: I know the postgresql server is not smp aware but I believe parts of it are. In particular the buffer manager is supposed to scale the performance almost linearly with the number of cpu's (including virtual ones). I don't know however, if I need to recompile the postgresql server myself to get those benefits. As Tom said, to get the benefits of parallelism on one query, you would need a parallelizing database like Teradata, Oracle Parallel Query option, Netezza, or Bizgres MPP. The announcement about Postgres linear scalability for SMP is only relevant to statement throughput for highly concurrent environments (web sites, OLTP, etc). - Luke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
I am not sure about this. I mean I have postgresql 8.1.3 running on my Windows XP P4 HT laptop that I use for testing my webapps. When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. The usage curve is not identical on both of them that makes me think that parts of the server are multithreaded. Admittedly I am not familiar with the source code fo postgresql so I was hoping maybe one of the developers who is could definitely answer this question. Thanks, Juan -Original Message- From: Luke Lonergan [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 4:43 PM To: Juan Casero (FL FLC); pgsql-performance@postgresql.org Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3 Juan, On 4/5/06 11:12 AM, Juan Casero (FL FLC) [EMAIL PROTECTED] wrote: I know the postgresql server is not smp aware but I believe parts of it are. In particular the buffer manager is supposed to scale the performance almost linearly with the number of cpu's (including virtual ones). I don't know however, if I need to recompile the postgresql server myself to get those benefits. As Tom said, to get the benefits of parallelism on one query, you would need a parallelizing database like Teradata, Oracle Parallel Query option, Netezza, or Bizgres MPP. The announcement about Postgres linear scalability for SMP is only relevant to statement throughput for highly concurrent environments (web sites, OLTP, etc). - Luke ---(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
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Juan, On 4/5/06 1:54 PM, Juan Casero (FL FLC) [EMAIL PROTECTED] wrote: I am not sure about this. I mean I have postgresql 8.1.3 running on my Windows XP P4 HT laptop that I use for testing my webapps. When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. The usage curve is not identical on both of them that makes me think that parts of the server are multithreaded. Admittedly I am not familiar with the source code fo postgresql so I was hoping maybe one of the developers who is could definitely answer this question. There's no part of the Postgres backend that is threaded or multi-processed. A reasonable explanation for your windows experience is that your web server or the psql client may be taking some CPU cycles while the backend is processing your query. Also, depending on how the CPU load is reported, if the OS is doing prefetching of I/O, it might show up as load. - Luke ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] bad performance on Solaris 10
Luke Lonergan wrote: Chris, On 4/5/06 2:31 PM, Chris Mair [EMAIL PROTECTED] wrote: Doing what http://blogs.sun.com/roller/page/jkshah suggests: wal_sync_method = fsync (unchanged) wal_buffers = 128 (was 8) checkpoint_segments = 128 (was 3) bgwriter_all_percent = 0 (was 0.333) bgwriter_all_maxpages = 0 (was 5) and leaving everything else default (solarispackages from pgfoundry) increased performance ~ 7 times! In the recent past, Jignesh Shaw of Sun MDE discovered that changing the bgwriter_* parameters to zero had a dramatic positive impact on performance. This essentially means stopping all bgwriter activity, thereby deferring all I/O until checkpoint. Was this considered? With checkpoint_segments to 128, it wouldn't surprise me that there wasn't any checkpoint executed at all during the whole test ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Juan, On 4/5/06 2:45 PM, Juan Casero (FL FLC) [EMAIL PROTECTED] wrote: Do you think bizgres might be a good choice of database server for the Ultrasparc T1 based T2000? I have downloaded the source code but I was hoping to find out if the potential performance gains were worth the effort to compile and install the code. Bizgres (non-MPP) does not do any multi-CPU parallelism, so it won't use more CPUs in your T2000. It does have the faster sort performance and an on-disk bitmap index, both of which will make it run many times (3-6) faster than 8.1 Postgres depending if your queries use larger data or involve sorts. Bizgres MPP is closed source and unfortunately for your T2000 experiment it doesn't currently support Solaris SPARC CPUs, only Solaris x86. It would use all of your CPUs and I/O channels on one or more machines for every query. Again, it's optimized for queries where that use a lot of data or have a lot of complexity (sorts, aggregations, joins, etc). - Luke ---(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
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Juan, When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. Nope, sorry, you're being decieved. Postgres is strictly one process, one query. You can use Bizgres MPP to achieve multithreading; it's proprietary and you have to pay for it. It does work well, though. More importantly, though, you haven't really explained why you care about multithreading. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(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
Re: [PERFORM] freebsd/softupdates for data dir
On Apr 4, 2006, at 10:41 AM, Vivek Khera wrote: On Apr 3, 2006, at 10:10 PM, Mark Kirkwood wrote: I've always left them on, and never had any issues...(even after unscheduled power loss - which happened here yesterday). As I understand it, the softupdate code reorders *metadata* operations, and does not alter data operations - so the effect of fysnc(2) on a preexisting file is not changed by softupdates being on or off. This is also my understanding, and I also leave softupdates on for the data partition. Even if it doesn't improve performance, it will not reduce it, and otherwise does no harm with respect to postgres' disk usage. More importantly, it allows the system to come up and do fsck in the background. If you've got a large database that's a pretty big benefit. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Josh Berkus josh@agliodbs.com writes: Juan, When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. Nope, sorry, you're being decieved. Postgres is strictly one process, one query. This is not strictly true: we have for instance pushed off some work into a background writer process, and even just having both a client and a server process active allows some small amount of parallelism. But you're certainly not going to see effective use of more than about two CPUs on a single query stream ... at least not without Bizgres or some other add-on. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
josh@agliodbs.com (Josh Berkus) writes: Juan, When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. Nope, sorry, you're being decieved. Postgres is strictly one process, one query. It's not entirely deception; there is indeed independent use of both CPUs, it's just that it isn't from multithreading... -- output = reverse(gro.mca @ enworbbc) http://www.ntlug.org/~cbbrowne/internet.html Don't use C; In my opinion, C is a library programming language not an app programming language. -- Owen Taylor (GTK+ and ORBit developer) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] bad performance on Solaris 10
Doing what http://blogs.sun.com/roller/page/jkshah suggests: wal_sync_method = fsync (unchanged) wal_buffers = 128 (was 8) checkpoint_segments = 128 (was 3) bgwriter_all_percent = 0 (was 0.333) bgwriter_all_maxpages = 0 (was 5) and leaving everything else default (solarispackages from pgfoundry) increased performance ~ 7 times! Ok, so I could quite believe my own benchmarks and I decided to do a fresh initdb and retry everything. At first it looked like I coudn't reproduce the speed up I just saw. Then I realized it was the wal_sync_method = fsync line that makes all the difference! Normally parameters that are commented are default values, but for wal_sync_method it actually says (note the comment): wal_sync_method = fsync # the default is the first option # supported by the operating system: # open_datasync # fdatasync # fsync # fsync_writethrough # open_sync So Im my last mail I drew the wrong conclusion, because i didn't comment wal_sync_method to double check. To the point: the default wal_sync_method choosen on Solaris 10 appears to be a very bad one - for me, picking fsync increases performance ~ times 7, all other parameters unchanged! Would it be a good idea to change this in the default install? Bye, Chris. PS: yes I did a fresh initdb again to double check ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] bad performance on Solaris 10
Chris Mair wrote: Hi, thanks for all replys. I've done a few tests. Remounting the fs where $PGDATA lives with forcedirectio (together with logging, that is default) did not help (if not harm...) performance. Sure - forcedirectio on the entire $PGDATA is a definite loss, you only want it on $PGDATA/pg_xlog. The usual way this is accomplished is by making a separate filsystem for pg_xlog and symlinking from $PGDATA. Did you try the other option of remounting the fs for $PGDATA without logging or forcedirectio? Cheers Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] bad performance on Solaris 10
appears this didn't make it to the list... resending to the list directly... --- Doing what http://blogs.sun.com/roller/page/jkshah suggests: wal_sync_method = fsync (unchanged) wal_buffers = 128 (was 8) checkpoint_segments = 128 (was 3) bgwriter_all_percent = 0 (was 0.333) bgwriter_all_maxpages = 0 (was 5) and leaving everything else default (solarispackages from pgfoundry) increased performance ~ 7 times! Ok, so I could quite believe my own benchmarks and I decided to do a fresh initdb and retry everything. At first it looked like I coudn't reproduce the speed up I just saw. Then I realized it was the wal_sync_method = fsync line that makes all the difference! Normally parameters that are commented are default values, but for wal_sync_method it actually says (note the comment): wal_sync_method = fsync # the default is the first option # supported by the operating system: # open_datasync # fdatasync # fsync # fsync_writethrough # open_sync So Im my last mail I drew the wrong conclusion, because i didn't comment wal_sync_method to double check. To the point: the default wal_sync_method choosen on Solaris 10 appears to be a very bad one - for me, picking fsync increases performance ~ times 7, all other parameters unchanged! Would it be a good idea to change this in the default install? Bye, Chris. PS: yes I did a fresh initdb again to double check ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Juan, I think that AMD Opteron is more flex (OS and Hardware Upgrade) and then, the best solution.¹ What are you think about the Sun Fire X64 X4200 Server? Take a look in this analysis and performance benchmark². Regards, MTada ¹ http://www.anandtech.com/systems/showdoc.aspx?i=2727p=2 ² http://www.anandtech.com/systems/showdoc.aspx?i=2727p=7 Juan Casero (FL FLC) wrote: I am evaluating this SunFire T2000 as a replacement for an Intel P3 1Ghz postgresql server. This intel server runs a retail reporting database on postgresql 8.1.3. I need to realize significant performance gains on T2000 server to justify the expense. So I need to tune the postgresql server as much as I can for it. Right now the operating system (solaris 10) sees each thread as a single cpu and only allows each thread 4.16% of the available cpu resources for processing queries. Since postgresql is not multithreaded and since I cannot apparently break past the operating system imposed limits on a single thread I can't fully realize the performance benefits of the T2000 server unless and until I start getting lots of people hitting the database server with requests. This doesn't happen right now. It may happen later on as I write more applications for the server but I am looking to see if the performance benefit we can get from this server is worth the price tag right now. That is why I am looking for ways to tweak postgres on it. Thanks, Juan -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 6:02 PM To: pgsql-performance@postgresql.org Cc: Juan Casero (FL FLC); Luke Lonergan Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3 Juan, When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. Nope, sorry, you're being decieved. Postgres is strictly one process, one query. You can use Bizgres MPP to achieve multithreading; it's proprietary and you have to pay for it. It does work well, though. More importantly, though, you haven't really explained why you care about multithreading. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Luke (or anyone else who may be following this thread) Do you think bizgres might be a good choice of database server for the Ultrasparc T1 based T2000? I have downloaded the source code but I was hoping to find out if the potential performance gains were worth the effort to compile and install the code. Thanks, Juan -Original Message- From: Luke Lonergan [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 5:37 PM To: Juan Casero (FL FLC); pgsql-performance@postgresql.org Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3 Juan, On 4/5/06 1:54 PM, Juan Casero (FL FLC) [EMAIL PROTECTED] wrote: I am not sure about this. I mean I have postgresql 8.1.3 running on my Windows XP P4 HT laptop that I use for testing my webapps. When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. The usage curve is not identical on both of them that makes me think that parts of the server are multithreaded. Admittedly I am not familiar with the source code fo postgresql so I was hoping maybe one of the developers who is could definitely answer this question. There's no part of the Postgres backend that is threaded or multi-processed. A reasonable explanation for your windows experience is that your web server or the psql client may be taking some CPU cycles while the backend is processing your query. Also, depending on how the CPU load is reported, if the OS is doing prefetching of I/O, it might show up as load. - Luke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Juan Casero (FL FLC) wrote: When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. Intel HT was partly a marketing thing, you don't really get two CPU's / cores etc. The virtual CPU is really that, there is only one cpu doing the actual work with some extra glue to help the hyperthreading. As to how hyper intel's hyperthreading is, OSDL did some testing (I think the dbt2 workload) and I remember HT reducing performance for pgsql by about 15%. Worth looking up, benchmarks are subject to a lot of issues but was interesting. There have been some seriously good recommendations in this newsgroup for nice high powered servers, including good disk subsystems. Most involve some AMD Opertons, lots of spindles with a good raid controller preferred to one or two large disks and a good helping of ram. Be interesting to get some numbers on the sunfire machine. - August ---(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
Re: [PERFORM] bad performance on Solaris 10
Alvaro, On 4/5/06 2:48 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: This essentially means stopping all bgwriter activity, thereby deferring all I/O until checkpoint. Was this considered? With checkpoint_segments to 128, it wouldn't surprise me that there wasn't any checkpoint executed at all during the whole test ... Yes, many things about the Solaris UFS filesystem caused a great deal of pain over the 10 months of experiments we ran with Sun MDE. Ultimately, the conclusion was that ZFS is going to make all of the pain go away. In the meantime, all you can do is tweak up UFS and avoid I/O as much as possible. - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
On Wed, 2006-04-05 at 15:54, Juan Casero (FL FLC) wrote: I am not sure about this. I mean I have postgresql 8.1.3 running on my Windows XP P4 HT laptop that I use for testing my webapps. When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. The usage curve is not identical on both of them that makes me think that parts of the server are multithreaded. Admittedly I am not familiar with the source code fo postgresql so I was hoping maybe one of the developers who is could definitely answer this question. I think that really depends on your workload. Are you going to have a dozen or so transactions running at a time? then regular postgresql is probably ok. If you're gonna be running only one or two big, fat, hairy reporting queries, then you might wanna look at the bizgress mpp version. Note that some queries lend themselves to parallel processing more than others. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] bad performance on Solaris 10
I've done a few tests. Remounting the fs where $PGDATA lives with forcedirectio (together with logging, that is default) did not help (if not harm...) performance. Sure - forcedirectio on the entire $PGDATA is a definite loss, you only want it on $PGDATA/pg_xlog. The usual way this is accomplished is by making a separate filsystem for pg_xlog and symlinking from $PGDATA. Did you try the other option of remounting the fs for $PGDATA without logging or forcedirectio? not yet, I'm not on the final disk set yet. when I get there I'll have two separate filesystems for pg_xlog and base and will try what you suggest. (but note the other mail about wal_sync_method = fsync) bye, chris. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Hi Juan Casero, I've found that serial query clients are best served by PostgreSQL running on fast single or dual core processors, ( such as the Athlon FX60 ) rather than expensive n-way systems. I was orginally using an 8-way Xeon computer for a similar serial throughput problem. and i wasn't supprised to find that at least 6 of the 8 processors were idle. The point is, for this type client, you are better off spending the money on the fastest single or dual core processors than a multiway box. Anthony. Juan Casero (FL FLC) wrote: Greetings - I am testing a Sun Microsystems Sun Fire T2000 demo server at our company. I want to know if anyone here has any experience with this hardware and postgresql 8.1.3. I installed the copy of postgresql 8.1.3 from blastwave.org onto this demo box and loaded our production database into it. This box has a single Ultrasparc T1 cpu with six execution piplelines that can each handle 4 threads. With the Unix top utility the postgresql server appears to bounce around between the available threads on the system. For example I run a single large query and I can see the postgresql server sometimes running on cpu/0, other times on cpu/1, cpu/3,etc up to cpu/23. However, never is the load for the postgres server reported to be higher than 4.16%. I did the math and 4.16% x 24 threads = 98.84% cpu load. So I wonder if the Solaris 10kernel is somehow throttling the processes so that any single virtual processor can do no more than 4.16% load. We got this server last week and I was able to install it in our rack just yesterday. Now I need to see how I can optimize the postgresql server to work on this box. Does anyone have any suggestions? I know the postgresql server is not smp aware but I believe parts of it are. In particular the buffer manager is supposed to scale the performance almost linearly with the number of cpu's (including virtual ones). I don't know however, if I need to recompile the postgresql server myself to get those benefits. I am using the version of postgresql 8.1.3 that is available on blastwave.org. I am also working with the 64 bit version of the database server. This machine has over 8GB of ram so I was thinking of using the64 bit version of the postgresql server so I can access ram beyong the 4gb limit imposed by 32 bit addressing. Any help or recommendations for performance tweaking of postgresql is very much appreciated. Thanks, Juan
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Tom is right. Unless your workload can generate lots of simultaneous queries, you will not reap the full benefit of the Sun Fire T2000 system. I have tested 8.1.3 with an OLTP workload on an 8 cores system. With 1500-2000 client connections, the CPU was only about 30% utilized. The UltraSPARC T1 processor was designed for throughput with many cores running at lower frequency (1-1.2 GHz) to reduce power consumption. To speed up a single big query, you'd be better off with a parallelize DB or an Opteron system with higher clock speed like this one http://www.sun.com/servers/entry/x4200/ Regards, -Robert Tom Lane wrote: Juan Casero \(FL FLC\) [EMAIL PROTECTED] writes: ... This box has a single Ultrasparc T1 cpu with six execution piplelines that can each handle 4 threads. With the Unix top utility the postgresql server appears to bounce around between the available threads on the system. Try sending it more than one query at a time? If you're testing with just one client connection issuing queries, that's about what I'd expect. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
I am evaluating this SunFire T2000 as a replacement for an Intel P3 1Ghz postgresql server. This intel server runs a retail reporting database on postgresql 8.1.3. I need to realize significant performance gains on T2000 server to justify the expense. So I need to tune the postgresql server as much as I can for it. Right now the operating system (solaris 10) sees each thread as a single cpu and only allows each thread 4.16% of the available cpu resources for processing queries. Since postgresql is not multithreaded and since I cannot apparently break past the operating system imposed limits on a single thread I can't fully realize the performance benefits of the T2000 server unless and until I start getting lots of people hitting the database server with requests. This doesn't happen right now. It may happen later on as I write more applications for the server but I am looking to see if the performance benefit we can get from this server is worth the price tag right now. That is why I am looking for ways to tweak postgres on it. Thanks, Juan -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 05, 2006 6:02 PM To: pgsql-performance@postgresql.org Cc: Juan Casero (FL FLC); Luke Lonergan Subject: Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3 Juan, When I hit this pgsql on this laptop with a large query I can see the load spike up really high on both of my virtual processors. Whatever, pgsql is doing it looks like both cpu's are being used indepently. Nope, sorry, you're being decieved. Postgres is strictly one process, one query. You can use Bizgres MPP to achieve multithreading; it's proprietary and you have to pay for it. It does work well, though. More importantly, though, you haven't really explained why you care about multithreading. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Juan, On 4/5/06 5:33 PM, Juan Casero (FL FLC) [EMAIL PROTECTED] wrote: ... but I am looking to see if the performance benefit we can get from this server is worth the price tag right now. While many people here will look forward to performance results on the T2000, I can guarantee that your server money will go much further for a reporting application with an Opteron based system. Buy a Sun Galaxy with a pair of Opteron 275s, run Linux on it, and I predict you will see performance 4-5 times faster than the T2000 running Solaris for handling single queries, and 2-3 times faster when handling multiple queries. - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Luke Lonergan wrote: Juan, On 4/5/06 5:33 PM, Juan Casero (FL FLC) [EMAIL PROTECTED] wrote: ... but I am looking to see if the performance benefit we can get from this server is worth the price tag right now. While many people here will look forward to performance results on the T2000, I can guarantee that your server money will go much further for a reporting application with an Opteron based system. Buy a Sun Galaxy with a pair of Opteron 275s, run Linux on it, and I predict you will see performance 4-5 times faster than the T2000 running Solaris for handling single queries, and 2-3 times faster when handling multiple queries. We've got a Sun Fire V40z and it's quite a nice machine -- 6x 15krpm drives, 4GB RAM, and a pair of Opteron 850s. This gives us more than enough power now for what we need, but it's nice to know that we can shoehorn a lot more RAM, and up it to eight CPU cores if needed. The newer Sun Opteron systems look nice too, but unless you're using external storage, their little 2.5 hard drives may not be ideal. Thanks Leigh - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Leigh, On 4/5/06 9:23 PM, Leigh Dyer [EMAIL PROTECTED] wrote: We've got a Sun Fire V40z and it's quite a nice machine -- 6x 15krpm drives, 4GB RAM, and a pair of Opteron 850s. This gives us more than enough power now for what we need, but it's nice to know that we can shoehorn a lot more RAM, and up it to eight CPU cores if needed. We have one of these too - ours is signed by Scott McNealy. The newer Sun Opteron systems look nice too, but unless you're using external storage, their little 2.5 hard drives may not be ideal. Yes - but they end-of-lifed the V20z and V40z! One big problem with the sun line in general is the tiny internal storage capacity - already too small on the V40z at 5/6 drives, now ridiculous at 4 SAS drives on the galaxy series. - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] bad performance on Solaris 10
Chris Mair wrote: (but note the other mail about wal_sync_method = fsync) Yeah - looks good! (is the default open_datasync still?). Might be worth trying out the fdatasync method too (ISTR this being quite good... again on Solaris 8, so things might have changed)! Cheers Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3
Luke Lonergan wrote: Leigh, On 4/5/06 9:23 PM, Leigh Dyer [EMAIL PROTECTED] wrote: We've got a Sun Fire V40z and it's quite a nice machine -- 6x 15krpm drives, 4GB RAM, and a pair of Opteron 850s. This gives us more than enough power now for what we need, but it's nice to know that we can shoehorn a lot more RAM, and up it to eight CPU cores if needed. We have one of these too - ours is signed by Scott McNealy. Nice :) The newer Sun Opteron systems look nice too, but unless you're using external storage, their little 2.5 hard drives may not be ideal. Yes - but they end-of-lifed the V20z and V40z! That's quite disappointing to hear -- our V40z isn't even six months old! We're not a big company, so external storage solutions are outside our price range, but we still wanted a nice brand-name box, and the V40z was a great deal compared to smaller boxes like the HP DL385. One big problem with the sun line in general is the tiny internal storage capacity - already too small on the V40z at 5/6 drives, now ridiculous at 4 SAS drives on the galaxy series. I'm sure those little SAS drives would be great for web servers and other non-IO-intensive tasks though -- I'd love to get some X4100s in to replace our Poweredge 1750s for that. It's a smart move overall IMHO, but it's certainly not great for database serving. Thanks Leigh - Luke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] bad performance on Solaris 10
Hi, thanks for all replys. I've done a few tests. Remounting the fs where $PGDATA lives with forcedirectio (together with logging, that is default) did not help (if not harm...) performance. Doing what http://blogs.sun.com/roller/page/jkshah suggests: wal_sync_method = fsync (unchanged) wal_buffers = 128 (was 8) checkpoint_segments = 128 (was 3) bgwriter_all_percent = 0 (was 0.333) bgwriter_all_maxpages = 0 (was 5) and leaving everything else default (solarispackages from pgfoundry) increased performance ~ 7 times! Playing around with these modifications I find that it's actually just the wal_buffers = 128 alone which makes all the difference! Quickly playing around with wal_buffers on Linux and Mac OS X I see it influences the performance of my test a bit, maybe in the 10-20% range (I'm really doing quick tests, nothing systematic), but nowhere near as spectacularly as on Solaris. I'm happy so far, but I find it very surprising that this single parameter has such an impact (only on) Solaris 10. (my test program is a bulk inserts using PQputCopyData in large transactions - all test were 8.1.3). Bye, Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
