Hi Everyone,
Not sure how many people on this list use PostgreSQL, but I recently posted an article that helps gather the "generally accepted best practices" for tuning a PostgreSQL database. You can read it here:
http://www.revsys.com/writings/postgresql-performance.html
Comments, feedback, etc. are always welcome.
--------------------------------- Frank Wiles frank@wiles.org http://www.wiles.org ---------------------------------
On Thu, 23 Jun 2005, Frank Wiles wrote:
Not sure how many people on this list use PostgreSQL, but I recently posted an article that helps gather the "generally accepted best practices" for tuning a PostgreSQL database.
Fascinating article, with lots of non-postgresql-specific db performance tips as well.
I've never used postgresql, I've always used mysql. At the time that I made this decision, the "accepted wisdom" on OSS dbs seemed to be that mysql was fastest and easiest to admin, but postgresql was safest (transactions) and most featureful. Since I didn't know anything about databases anyway, I felt that the extra features would probably be wasted on me, and speed never goes out of style.
Is this view still essentially true, or was it ever? At the time mysql was the most popular open source db and having to vacuum your database seemed somehow suspect. Not that "most popular" is so much of an endorsement, I'm thinking here of a certain operating system.
You can read it here:
http://www.revsys.com/writings/postgresql-performance.html
Comments, feedback, etc. are always welcome.
Regards,
-Don
On Tue, 28 Jun 2005, Don Erickson wrote:
Is this view still essentially true, or was it ever? At the time mysql was the most popular open source db and having to vacuum your database seemed somehow suspect. Not that "most popular" is so much of an endorsement, I'm thinking here of a certain operating system.
PostgreSQL was never really slow, but with the web boom of the 90s I think most web people saw its true ACID compliance as just extra bloat. Who needs transactions with web pages (well, you should use them with e-commerce, but anyway ...). Also, it seemed like every LAMP book every written used mysql (with bad table design). If you hear something enough and there is enough documentation, you tend to use it.
The vacuum question is a good one and it goes on how the internals of many databases work. The very rough description of what is going here is that when you rollback a transaction it doesn't really delete what it has already inserted. Instead, it just marks it as deletable/recyclable. The vacuum command just goes through the database and cleans these up. The other thing it can do is analyze your table and your indices. This process allows the engine to more intelligently be able to process your queries. In general, you should run a vacuum every night on your database as you back it up. It doesn't take that much longer. When you properly maintain the database using these methods you will see PostgreSQL perform much better for you. In the new versions of PostgreSQL they have added auto-vacuum features which can help out a database administrator.
This e-mail is already long enough, so I won't go into too much more detail. If you are really interested in the differences start reading the whitepapers on the internal workings of these databases. Not all databases are created equal.
//========================================================\ || D. Hageman dhageman@dracken.com || \========================================================//
On Tue, 28 Jun 2005 15:13:09 -0500 (CDT) Don Erickson derick@zeni.net wrote:
On Thu, 23 Jun 2005, Frank Wiles wrote:
Not sure how many people on this list use PostgreSQL, but I recently posted an article that helps gather the "generally accepted best practices" for tuning a PostgreSQL database.
Fascinating article, with lots of non-postgresql-specific db performance tips as well.
Thanks!
I've never used postgresql, I've always used mysql. At the time that I made this decision, the "accepted wisdom" on OSS dbs seemed to be that mysql was fastest and easiest to admin, but postgresql was safest (transactions) and most featureful. Since I didn't know anything about databases anyway, I felt that the extra features would probably be wasted on me, and speed never goes out of style.
Is this view still essentially true, or was it ever? At the time mysql was the most popular open source db and having to vacuum your database seemed somehow suspect. Not that "most popular" is so much of an endorsement, I'm thinking here of a certain operating system.
Actually MySQL was only faster when used in small settings doing simple SQL queries, but when people hear "faster" they tend to remember it for a long time.
Since PostgreSQL 7.x came out ( 8.x is current now ), PostgreSQL has been at least on par with MySQL and in many cases faster. PosgreSQL simply blows MySQL out of the water if you have 5 or more concurrent connections whereas PostgreSQL easily scales to hundreds of connections without any noticable impact.
--------------------------------- Frank Wiles frank@wiles.org http://www.wiles.org ---------------------------------
-
D. Hageman -
Don Erickson -
Frank Wiles