Re: [PERFORM] Follow-Up: How to improve db performance with $7K?
I'd use two of your drives to create a mirrored partition where pg_xlog resides separate from the actual data. RAID 10 is probably appropriate for the remaining drives. Fortunately, you're not using Dell, so you don't have to worry about the Perc3/Di RAID controller, which is not so compatible with Linux... -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 31, 2005, at 9:01 PM, Steve Poe wrote: Thanks for everyone's feedback on to best improve our Postgresql database for the animal hospital. I re-read the PostgreSQL 8.0 Performance Checklist just to keep focused. We purchased (2) 4 x 146GB 10,000rpm SCSI U320 SCA drive arrays ($2600) and (1) Sun W2100z dual AMD64 workstation with 4GB RAM ($2500). We did not need a rack-mount server, so I though Sun's workstation would do fine. I'll double the RAM. Hopefully, this should out-perform our dual 2.8 Xeon with 4GB of RAM. Now, we need to purchase a good U320 RAID card now. Any suggestions for those which run well under Linux? These two drive arrays main purpose is for our database. For those messed with drive arrays before, how would you slice-up the drive array? Will database performance be effected how our RAID10 is configured? Any suggestions? Thanks. Steve Poe ---(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] What about utility to calculate planner cost constants?
If by not practical you mean, no one has implemented a multivariable testing approach, I'll agree with you. But multivariable testing is definitely a valid statistical approach to solving just such problems. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 21, 2005, at 11:51 AM, Josh Berkus wrote: That's not really practical. There are currently 5 major query tuning parameters, not counting the memory adjustments which really can't be left out. You can't realistically test all combinations of 6 variables. ---(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] Upgrading from from 7.4.2 to 8.0
It should be noted that users who use Slony can create a subscriber node running 8.0 that subscribes to a node running 7.4.x and can transition with only the downtime required for failover. This obviates the need for a dump/restore. See http://slony.info/. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jan 26, 2005, at 1:51 PM, Michael Fuhr wrote: On Wed, Jan 26, 2005 at 12:51:14PM -0600, James Gunzelman wrote: Will I have to dump and reload all my databases when migrating from 7.4.2 to 8.0? Yes -- the Release Notes mention it under Migration to version 8.0: http://www.postgresql.org/docs/8.0/static/release.html#RELEASE-8-0 Those unfamiliar with doing an upgrade might want to read If You Are Upgrading in the Installation Instructions chapter of the documenation, and Migration Between Releases in the Backup and Restore chapter: http://www.postgresql.org/docs/8.0/static/install-upgrading.html http://www.postgresql.org/docs/8.0/static/migration.html (Install or upgrade questions should probably go to pgsql-admin or pgsql-general instead of pgsql-performance.) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Alternatives to Dell?
I've been at companies where we've had good experiences with Penguin Computing servers. http://www.penguincomputing.com/ I always evaluate their offerings when considering server purchases or recommendations. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] Performance Anomalies in 7.4.5
I'm seeing some weird behavior on a repurposed server that was wiped clean and set up to run as a database and application server with postgres and Apache, as well as some command-line PHP scripts. The box itself is a quad processor (2.4 GHz Intel Xeons) Debian woody GNU/Linux (2.6.2) system. postgres is crawling on some fairly routine queries. I'm wondering if this could somehow be related to the fact that this isn't a database-only server, but Apache is not really using any resources when postgres slows to a crawl. Here's an example of analysis of a recent query: EXPLAIN ANALYZE SELECT COUNT(DISTINCT u.id) FROM userdata as u, userdata_history as h WHERE h.id = '18181' AND h.id = u.id; QUERY PLAN Aggregate (cost=0.02..0.02 rows=1 width=8) (actual time=298321.421..298321.422 rows=1 loops=1) - Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual time=1.771..298305.531 rows=2452 loops=1) Join Filter: (inner.id = outer.id) - Seq Scan on userdata u (cost=0.00..0.00 rows=1 width=8) (actual time=0.026..11.869 rows=2452 loops=1) - Seq Scan on userdata_history h (cost=0.00..0.00 rows=1 width=8) (actual time=0.005..70.519 rows=41631 loops=2452) Filter: (id = 18181::bigint) Total runtime: 298321.926 ms (7 rows) userdata has a primary/foreign key on id, which references userdata_history.id, which is a primary key. At the time of analysis, the userdata table had 2,500 rows. userdata_history had 50,000 rows. I can't imagine how even a seq scan could result in a runtime of nearly 5 minutes in these circumstances. Also, doing a count( * ) from each table individually returns nearly instantly. I can provide details of postgresql.conf and kernel settings if necessary, but I'm using some pretty well tested settings that I use any time I admin a postgres installation these days based on box resources and database size. I'm more interested in knowing if there are any bird's eye details I should be checking immediately. Thanks. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Performance Anomalies in 7.4.5
The irony is that I had just disabled pg_autovacuum the previous day during analysis of a wider issue affecting imports of data into the system. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Oct 21, 2004, at 4:05 PM, Dennis Bjorklund wrote: On Thu, 21 Oct 2004, Thomas F.O'Connell wrote: Aggregate (cost=0.02..0.02 rows=1 width=8) (actual time=298321.421..298321.422 rows=1 loops=1) - Nested Loop (cost=0.00..0.01 rows=1 width=8) (actual time=1.771..298305.531 rows=2452 loops=1) Join Filter: (inner.id = outer.id) - Seq Scan on userdata u (cost=0.00..0.00 rows=1 width=8) (actual time=0.026..11.869 rows=2452 loops=1) - Seq Scan on userdata_history h (cost=0.00..0.00 rows=1 width=8) (actual time=0.005..70.519 rows=41631 loops=2452) Filter: (id = 18181::bigint) It looks like you have not run ANALYZE recently. Most people run VACUUM ANALYZE every night (or similar) in a cron job. -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
