 |
 |
 |
 |
|
|
|
Optimizing Postgresql
Ericson Smith
Following Tim
Perdue's excellent article on the comparison between MySQL
and Postgresql, I decided to take a shot at installing and using this
database. For most of our work I use MySQL and will continue to do so,
because of its ease of use and unrivaled select query speed, and also
because there is no point in trying to mess around with production
systems that already work fine.
But some new projects suffered greatly from MySQL's table locking
feature when I needed to update data (which I do a lot). Here are my
adventures in setting up a Postgresql database server.
Our configuration for a dedicated Postgresql server was:
- Redhat 7.1
- Dual PIII 650Mhz System
- 512MB RAM
- 18Gig SCSI drive for the postgresql data partition
Downloading and Installing
I downloaded and installed the 7.1.2 RPM's from http://postgres.org
without any trouble. For a server installation, I only installed:
postgresql-server and postgresql-7.1.2 (base).
I then started the server up and running by executing:
/etc/init.d/postgresql start
A small sized database was ported from MySQL (three tables totaling about
5000 records). I created sufficient indexes for postgresql's optimizer
to use, and modified our C application to use the postgresql C client
interface for a small CGI program that would brutally query this table.
This small CGI program receives thousands of queries per minute.
[ Next Page ]
| Comments: | ||
| Upgrading Postgres 7.4 to 8.0.3 | nhan | 06/27/05 22:42 |
| RE: connecting asa 6 | Mohan Prasad Ghimire | 03/23/05 01:44 |
| RE: POSTGRESQL DOWN - HELP! | vineeth | 12/13/04 07:14 |
| Funcionar el template 1 de postgresql enWinxp | Zuleika | 11/19/04 09:05 |
| recordset data incorrect after select | rob | 10/16/02 05:10 |
| Server | john Iodence | 10/15/02 14:17 |
| How to add module for postgresql? | Vikas Singhal | 09/25/02 05:55 |
| RE: PostgresQL - JOINs | josh goldberg | 08/26/02 20:01 |
| Using Postgres with C !!! | Javi | 08/11/02 10:44 |
| POSTGRESQL DOWN - HELP! | MARCIO CORREA | 07/31/02 12:32 |
| RPMs vs Source | Martin Tsachev | 07/30/02 20:21 |
| RE: postgres database connection with VisualBasic | sam | 07/26/02 08:06 |
| RE: PostgreSQL Backup | J.M. Goikoetxea | 07/25/02 21:15 |
| PostgreSQL Backup | Kausik Halder | 07/16/02 05:20 |
| postgres database connection with VisualBasic | sekhar | 07/02/02 00:17 |
| Postgresql vs mysql | dudy rudianto | 07/01/02 03:48 |
| postgres | mauro beck | 05/20/02 15:24 |
| help needed with postgres jdbc driver insta | bvol | 05/07/02 11:44 |
| hosting provider wth PostgreSQL | Tonia Yee | 04/26/02 16:04 |
| Error during backup | Tulasi | 04/20/02 04:32 |
| Help needed on PostGres Functions | Swaraj | 04/20/02 00:44 |
| probleme connexion avec base de donnee | derouiche | 04/14/02 14:02 |
| inquirement | chakroun | 04/02/02 06:28 |
| inquirement | chakroun | 04/02/02 06:22 |
| RE: error during copying .txt file | Hano de la Rouviere | 03/20/02 06:26 |
| RE: shmmax + shared_buffers | Joek Hondius | 03/20/02 04:22 |
| error during copying .txt file | Tulasi | 03/19/02 05:16 |
| RE: PostgresQL - JOINs | mohan | 03/15/02 02:14 |
| RE: shmmax + shared_buffers | GB Clark | 03/14/02 01:22 |
| Don't forget to vacuum analyze. | Paul Joyce | 03/07/02 11:11 |
| createlang pltclu | Development | 02/25/02 15:41 |
| RE: Redhat 7.2 - ProstgreSQL - TCP/IP problem | Lem | 02/18/02 13:54 |
| shmmax + shared_buffers | Joek Hondius | 02/11/02 11:02 |
| RE: PostgreSQL on FreeBSD: before 22s, now 4s | Rick Morris | 01/28/02 22:52 |
| RE: MySQL is lame | David | 01/20/02 07:10 |
| PostgresQL - JOINs | Kannan | 01/17/02 09:07 |
| How to import the oracle database to Postgres | e.srinivasulu | 01/05/02 02:50 |
| RE: postgres gets slower HELP! | Jayme Nielsen | 12/26/01 12:34 |
| 6.3 lakh records slows down postgres | Rixon Mathew | 12/25/01 12:07 |
| RE: Redhat 7.2 - ProstgreSQL - TCP/IP problem | Tim Sutton | 12/15/01 07:37 |
| Redhat 7.2 - ProstgreSQL - TCP/IP problem | Mehmet Ceyhan | 12/11/01 06:13 |
| binaries | what | 11/15/01 23:28 |
| RE: fsync=false | Don Baccus | 11/14/01 21:30 |
| RE: Table locks are EASY to avoid! | Scott Marlowe | 10/02/01 15:56 |
| Table locks are EASY to avoid! | Jeremy Zawodny | 10/02/01 12:01 |
| RE: BIGINT indexes | Kyle VanderBeek | 09/26/01 18:48 |
| Postgresql and MySQL | George Box | 09/26/01 16:13 |
| RE: Mysql Vs Postgres | The Gad | 09/19/01 12:30 |
| PostgreSQL on FreeBSD: before 22s, now 4s | Michal Pasternak | 09/07/01 16:22 |
| PostgreSQL on FreeBSD: before 22s, now 4s | Michal Pasternak | 09/07/01 15:54 |
| RE: Other ways to optimize | Christopher Kings-Lynne | 09/04/01 23:39 |
| MySQL is lame | Christopher Kings-Lynne | 09/04/01 23:34 |
| Commercial Support (was RE: Impressive) | Tom Anderson | 09/04/01 07:49 |
| RE: Persistent Connections | Scott Marlowe | 09/04/01 06:58 |
| Support | Scott Marlowe | 09/04/01 06:53 |
| You can check this comparsion | John Carpenter | 09/04/01 06:10 |
| RE: postgres gets slower HELP! | James Hubbard | 09/03/01 22:25 |
| RE: postgres gets slower HELP! | Hans-Juergen Schoenig | 09/03/01 22:12 |
| RE: Impressive | Jonathan | 09/03/01 15:25 |
| RE: Alternative Table Handlers | Ericson Smith | 09/03/01 15:22 |
| RE: postgres gets slower HELP! | Ericson Smith | 09/03/01 15:19 |
| RE: Impressive | Ericson Smith | 09/03/01 15:12 |
| RE: Impressive | Alexandre Santos | 09/03/01 03:20 |
| Persistent Connections | Bobo | 09/01/01 22:34 |
| postgres gets slower HELP! | sheheryar sewani | 08/31/01 11:21 |
| RE: Porting MySQL database to Postgres | Gerzson | 08/31/01 09:27 |
| Not fair to reference previous article | Joshua Ginsberg | 08/28/01 23:11 |
| fsync=false | David | 08/28/01 18:27 |
| Impressive | Ben Davis | 08/28/01 12:53 |
| Mysql Vs Postgres | Russ | 08/28/01 09:21 |
| BIGINT indexes | Jason Earl | 08/27/01 12:40 |
| good article | Quentin | 08/27/01 05:23 |
| Int8 indexing | Stephan Szabo | 08/26/01 21:33 |
| Thank you | Digital Wokan | 08/26/01 00:49 |
| SAPDB | Alexandre Santos | 08/25/01 08:02 |
| Correction | Alexandre Santos | 08/25/01 07:53 |
| big int index | mlw | 08/24/01 04:21 |
| MySQL Berkeley_DB v PostgreSQL | Peter | 08/23/01 16:03 |
| Which file system? | Peter | 08/23/01 15:42 |
| Porting MySQL database to Postgres | morpheus() | 08/23/01 00:07 |
| Postgresql Vs. MySQL | Rich | 08/22/01 15:26 |
| Alternative Table Handlers | Chris Lambert | 08/22/01 10:04 |
| Other ways to optimize | Scott Marlowe | 08/22/01 08:47 |
| persistent connection | Michael Bravo | 08/22/01 03:12 |
| Schweet | vincent | 08/22/01 03:06 |
| Typo? | Kirk Parker | 08/21/01 19:39 |
|
If you are looking for help, please post on the appropriate forum here. Your questions will be answered much more quickly. | ||