Downloaded 37 times
![Memory Monitoring • pmap -sax $pid > To understand the details of memory consumption of a process # pmap -sax 20787 20787: /export/home/postgres/pgsql/bin/postgres Address Kbytes RSS Anon Locked Pgsz Mode Mapped File 0000000000400000 4 4 - - 4K r-x-- postgres 0000000000401000 340 - - - - r-x-- postgres .. 0000000000C80000 12 12 12 - 4K rw--- [ heap ] 0000000000C83000 4 - - - - rw--- [ heap ] 0000000000C84000 4 4 4 - 4K rw--- [ heap ] FFFFFD7F00000000 120832 120832 120832 120832 2M rwxsR [ ism shmid=0x36 ] FFFFFD7F07600000 3115440 3115440 3115440 3115440 4K rwxsR [ ism shmid=0x36 ] FFFFFD7FFF010000 64 8 - - - rwx-- [ anon ] FFFFFD7FFF040000 4 4 - - - rwx-- [ anon ] FFFFFD7FFF050000 16 16 - - 4K r-x-- en_US.ISO8859-1.so.3 ... FFFFFD7FFFDF7000 20 20 20 - 4K rw--- [ stack ] FFFFFD7FFFDFC000 12 12 - - - rw--- [ stack ] FFFFFD7FFFDFF000 4 4 4 - 4K rw--- [ stack ] ---------------- ---------- ---------- ---------- ---------- total Kb 3247600 3245420 3239316 3236272](https://image.slidesharecdn.com/postgresqlconferenceeast2008-bestpracticesofpostgresqlwithsolaris-160110071313/75/Best-Practices-with-PostgreSQL-on-Solaris-23-2048.jpg)
![Basic Locks Monitoring • Dtrace Scripts based on postgresql provider > PostgreSQL level locking profile #!/usr/sbin/dtrace -qs dtrace:::BEGIN { lckmode[0] = "Exclusive"; lckmode[1] = "Shared"; } postgresql*:::lwlock-startwait { self->ts[arg0]=timestamp; @count[arg0, lckmode[arg1]] = count(); } postgresql*:::lwlock-endwait /self->ts[arg0]/ { @time[arg0 ,lckmode[arg1]] = sum (timestamp - self->ts[arg0]); self->ts[arg0]=0; } dtrace:::END { printf("n%20s %15s %15sn", "Lock Id", "Mode", "Count"); printa("%20d %15s %@15dn",@count); printf("n%20s %15s %20sn", "Lock Id","Mode", "Combined Time (ns)"); printa("%20d %15s %@20dn",@time); } tick-10sec { exit(0);} # ./lwlock_wait.d](https://image.slidesharecdn.com/postgresqlconferenceeast2008-bestpracticesofpostgresqlwithsolaris-160110071313/75/Best-Practices-with-PostgreSQL-on-Solaris-31-2048.jpg)
Uploaded byJignesh Shah
Best Practices with PostgreSQL on Solaris
This document provides best practices for deploying PostgreSQL on Solaris, including: - Using Solaris 10 or latest Solaris Express for support and features - Separating PostgreSQL data files onto different file systems tuned for each type of IO - Tuning Solaris parameters like maxphys, klustsize, and UFS buffer cache size - Configuring PostgreSQL parameters like fdatasync, commit_delay, wal_buffers - Monitoring key metrics like memory, CPU, and IO usage at the Solaris and PostgreSQL level
Best Practices with PostgreSQL on Solaris
- 1. Best Practices withPostgreSQL on Solaris Jignesh Shah – Staff Engineer, ISV Engineering, Sun Microsystems Inc PostgreSQL East Conference March 2008
- 2. About Me • Workingwith Sun Microsystems for about 7 1/2 years > Primarily responsibility at Sun is to make ISV and Open Source Community Software applications work better on Solaris • Prior to Sun worked as ERP Consultant • Worked with various databases (DB2 UDB, PostgreSQL, MySQL, Progress OpenEdge, Oracle) • Worked with various ERP (QAD, Lawson) and CRM (Dispatch-1), etc • Previous responsibilities also included : Low Cost BIDW
- 3. Agenda • Deployment tips >Storage > File systems > Solaris tunables > PostreSQL tunables • Monitoring > Solaris and other tools • Summary • References
- 4. What is notcovered • Solaris Internals • PostgreSQL Internals • Replication and Clustering • High Availability
- 5. Best Practices todeploy PostgreSQL on Solaris
- 6. Solaris 10 orSolaris Express? • Start with Solaris 10 8/07 or latest Solaris Express • How to decide whether Solaris 10 8/07 or Solaris Express (or OpenSolaris binary distribution) ? > If long term support from Sun is desired use Solaris 10 8/07 > If latest version/feature of Solaris is desired without depending on Support from Sun use latest Solaris Express builds • Make sure to use at least PostgreSQL 8.2 (and not the default PostgreSQL 8.1 on Solaris 10) • For 64-bit versions and/or PostgreSQL 8.3 use latest Solaris Express builds
- 7. Understanding PostgreSQL IO •For simplified view consider three buckets of IO > $PGDATA – Includes CLOG, control file etc – All 8K, > pg_xlog, Write Ahead Log: Mostly writes: IO size 8KB-256KB (observed) (Depends on various postgresql.conf tunables) > Relation Files: Data Tables, Index, etc: IO Size 8KB Mix of reads and writes, mix of random,sequential • Similarly create at least three file systems on Solaris so each file system can be tuned according to the needs of the type of IO • Relation Files can be easily separated and/or further broken down using TABLESPACES in PostgreSQL
- 8. Setting up IOand UFS Tunable • By default, UFS aims to cache only small files. Any file bigger than 32KB (like PostgreSQL data files) is generally NOT cached for long. • By default, on X86 / X64 biggest IO is limited to 56KB by default (compared to 128KB on SPARC) • By default effective UFS cache is 12% on SPARC (segmap_percent) and only 64MB on x64 (segmapsize) • Setting Effective cache higher in PostgreSQL on Solaris without tuning UFS caching could hurt performance
- 9. Setting up IOand UFS Tunables • Increase Maximum IO Size and in effect Readahead for UFS (not in case of directio) • Set maxphys and klustsize to 1MB > set maxphys=1048576 > set klustsize=1048576 • UFS buffer map might need tuning > X86 /X64 > set ufs:freebehind=0 > set segmapsize=1073741824 > SPARC > set freebehind=0 > set segmap_percent=25
- 10. Best Practices forStorage LUNS • For LUNS exported from external storage arrays, most efficient strip size depends on postgresql.conf parameters. Start with 128K strip size. > If using commit_delay then higher stripsize is preferred (128k-256K) for log filesystem. Recommended to use RAID1 or RAID10. (Avoid RAID-5 for logs) > For other file systems , even though most common IO block size in PostgreSQL is 8K, do not use less than 32KB strip size for layouts. Recommended between 32KB-256KB depending on workload > Sequential scan from database tables and indexes is slow using lower stripsize . Bigger stripsize impacts response times specially for OLTP Workload.
- 11. Best Practices forStorage/Directory Layout (using UFS) • Start with three filesystems for PostgreSQL databases > One for $PGDATA - UFS Buffered (default) > One for $PGDATA/pg_xlog – UFS DirectIO (forcedirectio) > One for default tablespace for the database to be created - UFS Directio (forcedirectio,noatime) > (This could require noforcedirectio depending on workload)
- 12. Best Practices forStorage/Directory Layout (using ZFS) • Separate pool for log filesystem > Mirror device (no RAID-Z) with recordsize 128K > Enable commit_delay in postgresql.conf to allow logs to write multiple log records in 1 write (bigger than 8K) • Defaults good for $PGDATA • For default tablespace, defaults probably okay > (but highly workload dependent) Need to figure out the ratio of sequential scans and random Ios happening first before changing recordsize • System should not be RAM or CPU bound for ZFS to perform best
- 13. Setting up Resourcesfor User • Setup resources as follows: > projadd -U pguser user.pguser > projmod -a -K "project.max-shm-ids=(priv,32768,deny)" user.pguser > projmod -a -K "project.max-sem-ids=(priv,4096,deny)" user.pguser > projmod -a -K "project.max-shm-memory=(priv, 13589934592,deny)" user.pguser > projmod -a -K "project.max-msg-ids=(priv,4096,deny)" user.pguser • Check /etc/project > user.pguser:100::pguser::project.max-msg-ids=(priv, 4096,deny);project.max-sem-ids=(priv,4096,deny);project.max- shm-ids=(priv,32768,deny);project.max-shm-memory=(priv, 13589934592,deny) • Higher limits do not result in wasted memory but are really limited to avoid Denial of Service attacks via user logins
- 14. Best Practices forSolaris • Use libumem > LD_PRELOAD_32=/usr/lib/libumem.so; export LD_PRELOAD_32 > LD_PRELOAD_64=/usr/lib/64/libumem.so; export LD_PRELOAD_64 > $POSTGRES_HOME/bin/pg_ctl -o -i -D $PGDATA -l $PGDATA/server.log start • Use FX Scheduler > /usr/bin/priocntl -s -c FX -i projid 100
- 15. Best Practices forPostgreSQL.conf • Use fdatasync or open_datasync (default) > (Jury divided between the two. I prefer fdatasync) • Enable commit_delay for high load environment > (unlike the new async commit in 8.3, there is no loss of transactions ) • Increase checkpoint_segments • Increase wal_buffers > (when response times are critical with many users) • Default background writer parameters good in 8.2 > Background writer in 8.1 needs tuning
- 16. Best Practices forPostgreSQL.conf • Shared Bufferpool getting better in 8.2 worth to increase it to 3GB (for 32-bit PostgreSQL) but still not great to increase it more than 10GB (for 64-bit PostgreSQL) • Temp buffers and work mem are workload dependent > (For high number of connections they have x max connections impact – sometimes more than connections) • Maintenance work mem is good between 256MB to 512MB • (Meant for bigger than 5GB database - Ofcourse)
- 17. Best Practices forGlassfish • Use Extra JDBC Wrapper driver to cache statements in Glassfish • Number of steady connections should be increased with caution beyond 100
- 18. Other Tips forDeployment • If you are using multiple applications or instances use separate userids or projectids to make monitoring easy (Zones/Containers are also another easy options) • If you plan to use CPU usage “limits” by application (by using Resource Constraints feature of Solaris Containers), allow some time to gather statistical data first on its CPU usage
- 19. Monitoring PostgreSQLon Solaris SunFire X4150 (2 Socket, 8 Cores) Project user.pguser: PostgreSQL User Project user.app Glassfish App Server Project default: Everything else Note:This is just an example and not an actual recommendation for Sun Fire X4150. Actual distribution should consider all applications running on the system.
- 20. Monitoring PostgreSQLon Solaris SunFire X4150 (2 Socket, 8 Cores) Project user.pguser: PostgreSQL User Project user.appuer Glassfish App Server Project default: Everything else MemoryMonitoring BasicLockMonitoring IO Monitoring by Filesystems CPU Utilization monitoring
- 21. Memory Monitoring • vmstat3 > Keep an eye on swap/free memory # vmstat 3 kthr memory page disk faults cpu r b w swap free re mf pi po fr de sr s0 s1 s2 s3 in sy cs us sy id 0 0 0 1600992 2622832 0 6 0 0 0 0 0 0 0 0 0 1018 287 475 1 1 98 0 0 0 1347744 2400480 31 37 0 0 0 0 0 0 0 0 0 1061 2772 900 23 2 75 0 0 0 1282420 2335524 0 0 0 0 0 0 0 0 0 0 0 1053 2728 899 24 2 74 0 0 0 1217088 2270192 0 0 0 0 0 0 0 0 0 0 0 1057 2653 889 23 2 75
- 22. Memory Monitoring • prstat-a > Memory usage by process (rough aggregation by user) Total: 55 processes, 188 lwps, load averages: 1.02, 0.82, 0.44 PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 20787 pguser 3173M 3166M cpu0 0 0 0:08:29 25% postgres/1 20789 root 3332K 2804K cpu3 59 0 0:00:01 0.1% prstat/1 133 root 4296K 3284K sleep 59 0 0:00:00 0.0% picld/4 142 root 3536K 2344K sleep 59 0 0:00:00 0.0% devfsadm/6 290 daemon 2036K 1288K sleep 60 -20 0:00:00 0.0% lockd/2 20772 root 2648K 1716K sleep 59 0 0:00:00 0.0% bash/1 291 root 2268K 1180K sleep 59 0 0:00:00 0.0% keyserv/3 138 daemon 3956K 2012K sleep 59 0 0:00:00 0.0% kcfd/3 1982 root 6888K 3504K sleep 59 0 0:01:53 0.0% nscd/28 299 root 2132K 1324K sleep 59 0 0:00:00 0.0% ttymon/1 292 root 1752K 944K sleep 59 0 0:00:01 0.0% sac/1 348 root 2456K 996K sleep 59 0 0:00:00 0.0% cron/1 272 daemon 2536K 1348K sleep 59 0 0:01:20 0.0% rpcbind/1 9 root 10M 9440K sleep 59 0 0:01:22 0.0% svc.configd/17 7 root 12M 11M sleep 59 0 0:00:41 0.0% svc.startd/13 NPROC USERNAME SWAP RSS MEMORY TIME CPU 8 pguser 3175M 3178M 20% 0:28:15 25% 40 root 68M 76M 0.5% 0:41:52 0.1% 1 smmsp 1260K 4132K 0.0% 0:00:01 0.0% 6 daemon 27M 28M 0.2% 0:01:26 0.0% Total: 55 processes, 188 lwps, load averages: 1.02, 0.83, 0.44
- 23. Memory Monitoring • pmap-sax $pid > To understand the details of memory consumption of a process # pmap -sax 20787 20787: /export/home/postgres/pgsql/bin/postgres Address Kbytes RSS Anon Locked Pgsz Mode Mapped File 0000000000400000 4 4 - - 4K r-x-- postgres 0000000000401000 340 - - - - r-x-- postgres .. 0000000000C80000 12 12 12 - 4K rw--- [ heap ] 0000000000C83000 4 - - - - rw--- [ heap ] 0000000000C84000 4 4 4 - 4K rw--- [ heap ] FFFFFD7F00000000 120832 120832 120832 120832 2M rwxsR [ ism shmid=0x36 ] FFFFFD7F07600000 3115440 3115440 3115440 3115440 4K rwxsR [ ism shmid=0x36 ] FFFFFD7FFF010000 64 8 - - - rwx-- [ anon ] FFFFFD7FFF040000 4 4 - - - rwx-- [ anon ] FFFFFD7FFF050000 16 16 - - 4K r-x-- en_US.ISO8859-1.so.3 ... FFFFFD7FFFDF7000 20 20 20 - 4K rw--- [ stack ] FFFFFD7FFFDFC000 12 12 - - - rw--- [ stack ] FFFFFD7FFFDFF000 4 4 4 - 4K rw--- [ stack ] ---------------- ---------- ---------- ---------- ---------- total Kb 3247600 3245420 3239316 3236272
- 24. IO Monitoring • iostat-xcznpm 3 > Figure out if IO is distributed well enough on various devices. Keep an eye on %b (busy) and asvc_t # iostat -xcznmp 3 .... cpu us sy wt id 25 1 0 74 extended device statistics r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 29.3 0.0 3754.6 0.0 0.7 0.1 23.3 2.2 6 6 c5t5d0 29.3 0.0 3754.6 0.0 0.7 0.1 23.3 2.2 6 6 c5t5d0s0 29.0 0.0 3712.0 0.0 0.7 0.1 23.4 2.2 6 6 c6t5d0s0 28.0 0.0 3584.0 0.0 0.6 0.1 22.4 2.1 6 6 c7t5d0s0 29.0 0.0 3712.0 0.0 0.7 0.1 23.4 2.2 6 6 c4t5d0s0 27.7 0.0 3541.3 0.0 0.6 0.1 22.1 2.1 6 6 c1t5d0s0 27.7 0.0 3541.3 0.0 0.8 0.1 30.5 2.7 7 8 c0t5d0s0 29.0 0.0 3712.0 0.0 0.7 0.1 23.4 2.2 6 6 c6t5d0 28.0 0.0 3584.0 0.0 0.6 0.1 22.4 2.1 6 6 c7t5d0 29.0 0.0 3712.0 0.0 0.7 0.1 23.4 2.2 6 6 c4t5d0 27.7 0.0 3541.3 0.0 0.6 0.1 22.1 2.1 6 6 c1t5d0 27.7 0.0 3541.3 0.0 0.8 0.1 30.5 2.7 7 8 c0t5d0
- 25. IO Monitoring • fsstat/mountpoints 3 > IO by filesystems # fsstat /pgtbs2 3 new name name attr attr lookup rddir read read write write file remov chng get set ops ops ops bytes ops bytes 68.9K 235 0 28.1M 17 259K 162 362M 2.83T 86.5M 692G /pgtbs2 0 0 0 0 0 0 0 7.72K 61.7M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.65K 61.2M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.55K 60.4M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.72K 61.7M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.64K 61.1M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.38K 59.1M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.58K 60.6M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.59K 60.7M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.61K 60.9M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.65K 61.2M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.68K 61.4M 0 0 /pgtbs2 0 0 0 0 0 0 0 7.59K 60.7M 0 0 /pgtbs2
- 26. IO Monitoring • zpooliostat 3 > If ZFS pools are used, then a simplified iostat by pool # zpool iostat 5 capacity operations bandwidth pool used avail read write read write ---------- ----- ----- ----- ----- ----- ----- pgdata 108G 2.16T 1 4 87.1K 141K pglog 43.7G 2.22T 0 0 25.4K 52.4K pgtbs1 24.4G 3.15T 3 0 310K 37.7K pgtbs2 90.5G 2.63T 9 1 1.13M 95.8K pgtbs3 93.0G 2.63T 1 0 97.8K 47.2K pgtbs4 20.5G 2.70T 5 0 620K 45.8K pgtbs5 13.6G 3.16T 0 0 91.4K 18.6K pgtbs6 6.12G 1.81T 0 0 66.2K 22.8K ---------- ----- ----- ----- ----- ----- ----- pgdata 108G 2.16T 0 0 0 0 pglog 43.7G 2.22T 0 0 0 0 pgtbs1 24.4G 3.15T 0 0 0 0 pgtbs2 90.5G 2.63T 153 0 19.1M 0 pgtbs3 93.0G 2.63T 0 0 0 0 pgtbs4 20.5G 2.70T 0 0 0 0 pgtbs5 13.6G 3.16T 0 0 0 0 pgtbs6 6.12G 1.81T 0 0 0 0 ---------- ----- ----- ----- ----- ----- -----
- 27. CPU Monitoring • prstat-am / prstat -Jm > CPU utilization by process and aggregation by user/Project id PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG PROCESS/NLWP 20787 pguser 96 2.5 0.0 0.0 0.0 0.0 1.1 0.0 2 62 13K 0 postgres/1 20814 root 0.0 0.7 0.0 0.0 0.0 0.0 99 0.0 21 0 198 0 prstat/1 268 root 0.0 0.1 0.0 0.0 0.0 0.0 100 0.0 3 0 37 0 in.routed/1 16643 pguser 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 68 3 69 0 postgres/1 16644 pguser 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 78 1 94 0 postgres/1 20615 root 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 15 0 30 0 iosum_amd/1 272 daemon 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 1 0 8 0 rpcbind/1 20758 root 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 2 0 16 0 sshd/1 570 root 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 5 0 7 0 snmpd/1 16645 pguser 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 8 0 6 0 postgres/1 9122 root 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 3 0 10 0 sendmail/1 133 root 0.0 0.0 0.0 0.0 0.0 25 75 0.0 0 0 0 0 picld/4 142 root 0.0 0.0 0.0 0.0 0.0 50 50 0.0 0 0 0 0 devfsadm/6 290 daemon 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 0 0 0 0 lockd/2 20772 root 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 0 0 0 0 bash/1 NPROC USERNAME SWAP RSS MEMORY TIME CPU 8 pguser 3175M 3178M 20% 0:44:40 25% 40 root 68M 76M 0.5% 0:41:51 0.1% 6 daemon 27M 28M 0.2% 0:01:26 0.0% 1 smmsp 1260K 4132K 0.0% 0:00:01 0.0% Total: 55 processes, 188 lwps, load averages: 1.01, 1.00, 0.82
- 28. CPU Monitoring • mpstat5 > Utilization by CPU useful to see if one of them is more stressed while there are idle CPUs available # mpstat 5 CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 0 2 0 148 482 263 179 3 20 11 0 94 2 0 0 98 1 1 0 168 167 3 175 3 19 10 0 77 1 0 0 99 2 2 0 47 215 62 65 2 13 8 0 63 1 1 0 98 3 1 0 41 155 6 56 2 7 8 0 54 1 1 0 98 CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 0 0 0 0 384 248 2 11 1 1 0 2519 94 4 0 2 1 0 0 13 196 2 331 0 25 9 0 26 0 1 0 99 2 2 0 93 391 305 266 1 24 8 0 35 0 2 0 98 3 0 0 0 158 4 235 0 11 5 0 72 2 0 0 98 CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 0 0 0 0 414 247 184 5 19 6 0 1256 47 2 0 51 1 0 0 13 93 2 169 6 16 4 0 1148 42 2 0 56 2 0 0 0 298 230 198 2 23 8 0 113 3 2 0 95 3 0 0 1 73 4 130 0 11 5 0 129 4 0 0 96 CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 0 0 0 0 14546 250 82 13 8 5 0 2428 92 6 0 2 1 0 0 42457 185 2 359 1 32 13 0 14 0 5 0 94 2 0 0 1 14635 382 326 0 35 16 0 36 0 4 0 96 3 0 0 17 14332 4 302 1 20 10 0 22 0 2 0 98
- 29. Basic Locks Monitoring •lockstat sleep 5 > Kernel level locking profile # lockstat sleep 5 Adaptive mutex spin: 156 events in 5.022 seconds (31 events/sec) Count indv cuml rcnt spin Lock Caller ------------------------------------------------------------------------------- 67 43% 43% 0.00 4 0xffffffffa4af99a8 taskq_thread+0xe3 22 14% 57% 0.00 4 0xffffffffa4af99a8 cv_wait+0x70 ... ------------------------------------------------------------------------------- Adaptive mutex block: 2 events in 5.022 seconds (0 events/sec) Count indv cuml rcnt nsec Lock Caller ------------------------------------------------------------------------------- 1 50% 50% 0.00 8587 0xffffffffa4af99a8 taskq_dispatch+0x1b8 1 50% 100% 0.00 6383 0xffffffffa4af99a8 cv_wait+0x70 ------------------------------------------------------------------------------- Spin lock spin: 21 events in 5.022 seconds (4 events/sec) Count indv cuml rcnt spin Lock Caller ------------------------------------------------------------------------------- 7 33% 33% 0.00 6 cpu0_disp disp_lock_enter+0x1e 5 24% 57% 0.00 6 cpu0_disp disp_lock_enter_high+0x9 ------------------------------------------------------------------------------- Thread lock spin: 1 events in 5.022 seconds (0 events/sec) Count indv cuml rcnt spin Lock Caller ------------------------------------------------------------------------------- 1 100% 100% 0.00 7123 transition_lock ts_update_list+0x52 -------------------------------------------------------------------------------
- 30. Basic Locks Monitoring •plockstat -p $pid > User Process level locking profile. Useful when prstat -am option shows value under LCK #
- 31. Basic Locks Monitoring •Dtrace Scripts based on postgresql provider > PostgreSQL level locking profile #!/usr/sbin/dtrace -qs dtrace:::BEGIN { lckmode[0] = "Exclusive"; lckmode[1] = "Shared"; } postgresql*:::lwlock-startwait { self->ts[arg0]=timestamp; @count[arg0, lckmode[arg1]] = count(); } postgresql*:::lwlock-endwait /self->ts[arg0]/ { @time[arg0 ,lckmode[arg1]] = sum (timestamp - self->ts[arg0]); self->ts[arg0]=0; } dtrace:::END { printf("n%20s %15s %15sn", "Lock Id", "Mode", "Count"); printa("%20d %15s %@15dn",@count); printf("n%20s %15s %20sn", "Lock Id","Mode", "Combined Time (ns)"); printa("%20d %15s %@20dn",@time); } tick-10sec { exit(0);} # ./lwlock_wait.d
- 32. DTrace Monitoring • Todebug specific issues based on outputs of previous tools • Recommended to use Dtrace Toolkit for easy scripts for most common scripts
- 33. DTrace Monitoring • Examples:Scripts from DTrace Toolkit • http://www.opensolaris.org/os/community/dtrace/dtracetoolkit/ # ./hotuser -p 20787 Sampling... Hit Ctrl-C to end. ^C FUNCTION COUNT PCNT postgres`mdread 1 0.0% postgres`smgrread 1 0.0% .... libc.so.1`memcpy 1789 5.2% postgres`AllocSetFree 1949 5.7% postgres`numeric_add 2087 6.1% postgres`add_abs 2979 8.7% postgres`AllocSetAlloc 4092 12.0% # ./prustat -p 20787 5 PID %CPU %Mem %Disk %Net COMM 20787 24.52 19.87 0.00 0.00 postgres PID %CPU %Mem %Disk %Net COMM 20787 24.47 19.87 0.37 0.00 postgres PID %CPU %Mem %Disk %Net COMM 20787 24.47 19.87 0.34 0.00 postgres PID %CPU %Mem %Disk %Net COMM 20787 24.50 19.87 0.32 0.00 postgres
- 34. Performance Of PostgreSQL onSolaris
- 35. PostgreSQL on Solaris:Performance • Published 2 SpecJAppServer2004 result using PostgreSQL 8.2 on Solaris > 778.14 JOPS with Glassfish v1 > 813.73 JOPS with Glassfish v2 Mandatory Disclosure: SPECjAppServer2004 JOPS@standard Sun Fire X4200 M2 (4 chips, 8 cores) - 813.73 SPECjAppServer2004 JOPS@Standard Sun Fire X4200 M2 (6 chips, 12cores) - 778.14 SPECjAppServer2004 JOPS@Standard SPEC, SPECjAppServer reg tm of Standard Performance Evaluation Corporation. All results from www.spec.org as of Jan 8,2008
- 36. PostgreSQL on Solaris: Scalability
- 37. Summary • File systemlayout and tuning key to Performance with PostgreSQL on Solaris • Make use of default tablespace for database – Easier to Monitor and Tune • Solaris tools including DTrace are your friends in understanding bottlenecks with PostgreSQL on Solaris
- 38. More Information • PostgreSQLQuestion: <postgresql-question@sun.com> • Blogs on PostgreSQL > Josh Berkus: http://blogs.ittoolbox.com/database/soup > Jignesh Shah: http://blogs.sun.com/jkshah/ > Tom Daly: http://blogs.sun.com/tomdaly/ > Robert Lor: http://blogs.sun.com/robertlor/ • PostgreSQL on Solaris Wiki: http://wikis.sun.com/display/DBonSolaris/PostgreSQL • OpenSolaris databases community: databases-discuss@opensolaris.org
- 39.
- 40.
- 41. PostgreSQL for Solaris •Integrated into Solaris package > Optimized to perform on Solaris > Incorporated Solaris performance and security features > ZFS > DTrace > Zones > Most reliable and secure DB/OS product > No licensing fees • Global, 24x7, enterprise-level support from Sun > Service contract available for “PostgreSQL for Solaris” > Community support is also available from PostgreSQL.org
- 42. What's included andwhat's coming? • Included > PostgreSQL Core distribution (32-bit) > PostgreSQL JDBC Driver > Most of PostgreSQL Contrib modules > Optional procedural languages PL/pgSQL, PL/perl, PL/Python and PL/Tcl > Supports OpenSSL and Kerberos • Coming soon: > PgAdmin III GUI for PostgreSQL > PostgreSQL Distribution (64-bit)
- 43. What is inSolaris 10? • Solaris 10 6/06 (U2) > PostgreSQL 8.1.2 (32-bit) • Solaris 10 11/06 (U3) > PostgreSQL 8.1.4 (32-bit) • Solaris 10 8/07 (U4) > PostgreSQL 8.1.9 (32-bit) and PostgreSQL 8.2.4 (32-bit) > Latest Patch upgrade available to 8.2.6 and 8.1.11 for Solaris 10 • Solaris 10 (U5) (Coming Soon) > PgAdminIII
- 44. What is inSolaris Express and OpenSolaris? • Solaris Express build 79a (Developer Release) > PostgreSQL 8.2.5 and 8.1.10 > PgAdminIII • Solaris Express build 87+ > PostgreSQL 8.3 (32-bit) as well as 8.3 (64-bit) • OpenSolaris Developer Preview (Project Indiana ) > Use pkg(5) to get PostgreSQL 8.2, 8.1 releases