I'm setting up my first PostgreSQL server to replace an existing MySQL server. I've been reading Gregory Smith's book Postgres 9.0 High Performance and also Riggs/Krosing's PostgreSQL 9 Administration Cookbook. While both of these books are excellent, I am completely new to PostgreSQL and I cannot possibly read and understand every aspect of tuning in the short amount time before I have to have this server running.
I started out by using the 11 step process for tuning a new dedicated server (page 145 in Gregory Smith's book) but I found I had more questions than I could get answered in the short amount of time I have. So, plan B is to use pgtune to get a ballpark configuration and then fine tune later as I learn more.
I ran some performance tests where I imported my 11Gb database from our old MySQL server into PostgreSQL 9.0.3. In my testing I left the postgresql.conf at default values. The PostgreSQL test database completely blew away the old MySQL server in performance. Again, the postgresql.conf was never optimized so I feel I will be OK if I just get in the ballpark with tuning the postgresql.conf file.
I'd like to run my plan by you guys to see if it seems sane and make sure I'm not leaving out anything major.
I'll be running PostgreSQL 9.0.3 on a Solaris 10 64 bit (Sparc) box with 16G of RAM. The local file system is ZFS. The database file systems are UFS and are SAN mounted from VERY fast disks with battery backed write cache. I don't know anybody else who is running a mix of ZFS and UFS file systems, I cannot change this. ZFS has it's own file system cache so I'm concerned about the ramifications of having caches for both ZFS and UFS. The only database related files that are stored on the local ZFS file system are the PostgreSQL binaries and the system logs.
>From the extensive reading I've done, it seems generally accepted to set the UFS file system cache to use 50% of the system RAM. That leaves 8G left for PostgreSQL. Well, not really 8G, I've reserved 1G for system use which leaves me with 7G for PostgreSQL to use. I ran pgtune and specified 7G as the memory ( 7 * 1024 * 1024 = 7340032 ) and 300 connections. The resulting postgresql.conf is what I plan to use.
After reading Gregory Smith's book, I've decided to put the database on one UFS file system, the WAL on a separate UFS file system (mounted with forcedirectio) and the archive logs on yet another UFS file system. I'll be on Solaris 10 so I've set wal_sync_method = fdatasync based on recommendations from other Solaris users. Did a lot of google searches on wal_sync_method and Solaris.
That's what I plan to go live with in a few days. Since my test server with default configs already blows away the old database server, I think I can get away with this strategy. Time is not on my side.
I originally installed the 32 bit PostgreSQL binaries but later switched to 64 bit binaries. I've read the 32 bit version is faster and uses less memory than the 64 bit version. At this point I'm assuming I need the 64 bit binaries in order to take full advantage the the 7G of RAM I have allocated to PostgreSQL. If I am wrong here please let me know.
This has been a lot of information to cram down in the short amount of time I've had to deal with this project. I'm going to have to go back and read the PostgreSQL 9.0 High Performance book two or three more times and really dig in to the details but for now I'm going to cheat and use pgtune as described above. Thank you in advance for any advice or additional tips you may be able to provide.
Rick
I started out by using the 11 step process for tuning a new dedicated server (page 145 in Gregory Smith's book) but I found I had more questions than I could get answered in the short amount of time I have. So, plan B is to use pgtune to get a ballpark configuration and then fine tune later as I learn more.
I ran some performance tests where I imported my 11Gb database from our old MySQL server into PostgreSQL 9.0.3. In my testing I left the postgresql.conf at default values. The PostgreSQL test database completely blew away the old MySQL server in performance. Again, the postgresql.conf was never optimized so I feel I will be OK if I just get in the ballpark with tuning the postgresql.conf file.
I'd like to run my plan by you guys to see if it seems sane and make sure I'm not leaving out anything major.
I'll be running PostgreSQL 9.0.3 on a Solaris 10 64 bit (Sparc) box with 16G of RAM. The local file system is ZFS. The database file systems are UFS and are SAN mounted from VERY fast disks with battery backed write cache. I don't know anybody else who is running a mix of ZFS and UFS file systems, I cannot change this. ZFS has it's own file system cache so I'm concerned about the ramifications of having caches for both ZFS and UFS. The only database related files that are stored on the local ZFS file system are the PostgreSQL binaries and the system logs.
>From the extensive reading I've done, it seems generally accepted to set the UFS file system cache to use 50% of the system RAM. That leaves 8G left for PostgreSQL. Well, not really 8G, I've reserved 1G for system use which leaves me with 7G for PostgreSQL to use. I ran pgtune and specified 7G as the memory ( 7 * 1024 * 1024 = 7340032 ) and 300 connections. The resulting postgresql.conf is what I plan to use.
After reading Gregory Smith's book, I've decided to put the database on one UFS file system, the WAL on a separate UFS file system (mounted with forcedirectio) and the archive logs on yet another UFS file system. I'll be on Solaris 10 so I've set wal_sync_method = fdatasync based on recommendations from other Solaris users. Did a lot of google searches on wal_sync_method and Solaris.
That's what I plan to go live with in a few days. Since my test server with default configs already blows away the old database server, I think I can get away with this strategy. Time is not on my side.
I originally installed the 32 bit PostgreSQL binaries but later switched to 64 bit binaries. I've read the 32 bit version is faster and uses less memory than the 64 bit version. At this point I'm assuming I need the 64 bit binaries in order to take full advantage the the 7G of RAM I have allocated to PostgreSQL. If I am wrong here please let me know.
This has been a lot of information to cram down in the short amount of time I've had to deal with this project. I'm going to have to go back and read the PostgreSQL 9.0 High Performance book two or three more times and really dig in to the details but for now I'm going to cheat and use pgtune as described above. Thank you in advance for any advice or additional tips you may be able to provide.
Rick
