[From documentation]: ====================== SELECT pg_stat_get_backend_pid(S.backendid) AS procpid,pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.usename, pg_stat_get_backend_activity(s.backendid) AS current_query FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_shadow u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.usesysid)); SELECT relname, idx_tup_fetch as seeks, n_tup_ins + n_tup_upd + n_tup_del as writes FROM pg_stat_user_tables ORDER BY relname; SELECT pid, mode, current_query FROM pg_locks, pg_stat_activity WHERE granted=false AND locktype = 'transactionid' AND pid=procpid ORDER BY pid, granted; [blog www.depesz.com]: ====================== // w bajtach wielkosc bazy select pg_database_size(db); // czas najdluzszego wykonywanego aktualnie zapytania select cast(extract(epoch from now() - query_start) * 1000 as int8) from pg_stat_activity; // zwraca w bajtach wielkosc tabeli/indexu select pg_relation_size(nazwa_relacji); // ile transakcji (od ostatniego restartu) postgres wykonal (niezaleznie czy byly one zatwierdzone czy wycofane) select sum(xact_commit) + sum(xact_rollback) from pg_stat_database; // replikacja via slony select st_lag_num_events, cast(extract(epoch from st_lag_time) * 1000 as int8) from _slony.sl_status [other queries]: SELECT datname, COUNT(*) AS open_connections, MAX(backend_start) AS oldest_connection, MIN(backend_start) AS newest_connection FROM pg_stat_activity GROUP BY datname UNION SELECT 'Summary', COUNT(*), MAX(backend_start), MIN(backend_start) FROM pg_stat_activity; [Size queries]: =============== Databases: SELECT datname,pg_size_pretty(pg_database_size(oid)) FROM pg_database ORDER BY pg_database_size(oid) DESC; Tables and indexes: SELECT relname,pg_size_pretty(pg_relation_size(oid)) FROM pg_class WHERE relname NOT LIKE 'pg_%' ORDER BY pg_relation_size(oid) DESC; Tables only: SELECT pg_tables.tablename, pg_tables.schemaname, pg_size_pretty(pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text)) AS pg_size_pretty, pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text) AS rs FROM pg_tables ORDER BY pg_relation_size((pg_tables.schemaname::text || '.'::text) || pg_tables.tablename::text) DESC; Indexes only: SELECT pg_indexes.indexname, pg_size_pretty(pg_relation_size((pg_indexes.schemaname::text || '.'::text) || pg_indexes.indexname::text)) AS pg_size_pretty, pg_relation_size((pg_indexes.schemaname::text || '.'::text) || pg_indexes.indexname::text) AS rs FROM pg_indexes ORDER BY pg_relation_size((pg_indexes.schemaname::text || '.'::text) || pg_indexes.indexname::text) DESC; Introspection If there's a relation with name FOO SELECT COUNT(relname) FROM pg_class WHERE relname = 'FOO'; [Env]: ====== export PGDATA=path/to/cluster export PGPORT=5432 export PGHOST=/tmp_or_path_to_".pgsql"_socket [Utils]: ======== ptop PgBouncer: https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer [Parameters] shared_buffers - 10-25% RAM (15%!) fullpage_writes ?? = raid cache protected by batteries(?!) log_min_duration_statement. log_timestamp = true http://www.westnet.com/~gsmith/content/postgresql/ http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm http://notemagnet.blogspot.com/2008/08/linux-write-cache-mystery.html http://www.westnet.com/~gsmith/content/linux-pdflush.htm http://www.kaltenbrunner.cc/blog/index.php?/archives/21-guid.html http://blog.charcoalphile.com/category/postgresql/ IO contention discussed: http://qaix.com/postgresql-database-development/337-722-tuning-postgres-for-large-data-import-using-copy-from-read.shtml http://archives.postgresql.org/pgsql-performance/2005-08/msg00144.php http://www.mail-archive.com/pgsql-performance@postgresql.org/msg18102.html wylaczac foreign key i indeksy przy loadzie ! COPY dump ip4r type # 50 % RAM effective_cache_size = # jesli czescniej niz x sekund # 2..5 minut? checkpoint_warning = 300 reduced cpu_index_tuple_cost to 0.0005 (encourages indexes which may reduce disk hits) increased work_mem to 15000 - sort/create index(sort!) "commit=600" and writeback for ext3 "This tunable is used to define when dirty data is old enough to be eligible for writeout by the pdflush daemons. It is expressed in 100'ths of a second. Data which has been dirty in memory for longer than this interval will be written out next time a pdflush daemon wakes up.": echo 60000 > /proc/sys/vm/dirty_expire_centisecs alter table set statistics "The default settings for autovacuum are very conservative, though, and are more suitable for a very small database. I generally use something aggressive like: -D -v 400 -V 0.4 -a 100 -A 0.3 This vacuums tables after 400 rows + 40% of the table has been updated or deleted, and analyzes after 100 rows + 30% of the table has been inserted, updated or deleted. The above configuration also lets me set my max_fsm_pages to 50% of the data pages in the database with confidence that that number won't be overrun, causing database bloat. We are currently testing various settings at OSDL and will have more hard figures on the above soon." max_fsm_relations = ilosc tabel w pgsql + zapas max_fsm_pages = minimum na tyle ile wynosi suma liczb stron wywietlanych przez VACUUM VERBOSE dla wszystkich tabel. 0 3 * * * root psql -c 'VACUUM FULL;' test 0 3 * * * root vacuumdb -a -f SELECT relfilenode, relpages * 8 AS kilobytes FROM pg_class ORDER BY relpages DESC; SELECT c2.relname, c2.relpages * 8 AS kilobytes FROM pg_class c, pg_class c2, pg_index i WHERE c.oid = i.indrelid AND c2.oid = i.indexrelid ORDER BY c2.relname; FSM: VACUUM ANALYZE VERBOSE ; CLUSTER - reorganizuje tabele na bazie indeksu (alfanumeric sort, dane blisko siebie w przypadku index range scanu) http://www.wlug.org.nz/PostgreSQLNotes commit_delay = 20000 commit_siblings = 3 wal_buffers = 128 checkpoint_timeout = 600 checkpoint_warning = 300 checkpoint_segments = (wal_write_rate * 600) / 16 checkpoint_segments is (300 * 3) / 16 = 56 For example, to complete recovery within 5 minutes at 3.0MiB/sec http://www.postgresonline.com/journal/index.php?/archives/10-How-does-CLUSTER-ON-improve-index-performance.html "It is impossible to tune checkpoint_segments for worst-case WAL activity, such as a bulk load using COPY FROM STDIN SQL command. This might generate 30MiB/s or more of WAL activity. 30MiB/s for 5 minutes is 8.7GiB of WAL data! The best suggestion appears to be let the server run for a while, then study the timestamps on the WAL segment files in pg_xlog to calculate the WAL write rate." COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. However, this consideration does not apply when archive_mode is set, as all commands must write WAL in that case. Temporary increasing the maintenance_work_mem configuration variable when loading large amounts of data can lead to improved performance. This will help to speed up CREATE INDEX commands and ALTER TABLE ADD FOREIGN KEY commands. It won't do much for COPY itself, so this advice is only useful when you are using one or both of the above techniques. First for fairly static tables such as large lookup tables, that rarely change or when they change are bulk changes, there is little point in leaving blank space in pages. It takes up disk space and causes Postgres to scan thru useless air. In these cases - you basically want to set your FillFactor high to like 99. CREATE UNIQUE INDEX (!) osobny tablespace indextblspace \set ECHO_HIDDEN t 2. Give the file system a hint that you work with larger block sizes. Ext3: mke2fs -b 4096 -j -R stride=2 /dev/sda1 -L LABEL I made a I/O test with PostgreSQL on a RAID system with stripe size of 64kByte and block size of 8 kByte in the RAID system. Stride=2 was the best value. 64/4 = 16 largefile! For example if you have a 4 drive raid 5 and it is using 64K chunks, your stripe size will be 256K. Given a 4K filesystem block size you would then have a stride of 64 (256/4). If it was 4 disk RAID0 array, than it would be 64(4x64k/4k=64). If it was 4 disk RAID10 array, than it would be 32 ((4/2)*64k/4k=32) 4 dyski przez 2 (RAID10) = 2 * 64kB (stripe-size) / 4kB (fs-block-size) === 32 dla 64kB chunk size -------------8<-----------_ A good tool that I have used in past for such scenario is a contrib module called "pgstattuple". It returns the table length, percentage of the "dead" tuples of a table and other info. This may help users to determine whether vacuum is necessary or not. An example for it can be: test=# select * from pgstattuple('pg_catalog.pg_proc'); table_len | 458752 tuple_count | 1470 tuple_len | 438896 tuple_percent | 95.67 dead_tuple_count | 11 dead_tuple_len | 3157 dead_tuple_percent | 0.69 free_space | 8932 free_percent | 1.95 -------------8<-----------_ # dump wszystkich funkcji pg_dump -s blah|awk '/CREATE FUNCTION/,/LANGUAGE/ { print $0;}' # tunning random_page_cost http://archives.postgresql.org/pgsql-performance/2004-02/msg00274.php "(1) Cache well below database size (for example 6 GB or 12 GB RAM on a box running a 210 GB database): #seq_page_cost = 1.0 random_page_cost = 2.0 (2) On a database which is entirely contained within cache: seq_page_cost = 0.1 random_page_cost = 0.1 (3) Where caching is very significant, but not complete, we have to test to see where performance is best. One example that significantly beat both of the above in production on a particular box: seq_page_cost = 0.3 random_page_cost = 0.5" Wyciagniecie definicji wszystkich indexow na tabeli z regex ^cos: ================================================================= SELECT pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) || ';' FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND i.indisprimary = 'f' AND c2.relname ~ '^cos_' ORDER BY c2.relname; For example, to complete recovery within 5 minutes at 3.0MiB/sec, the maximum setting of checkpoint_segments is (300 * 3) / 16 = 56 http://people.planetpostgresql.org/usamadar/index.php?/archives/1-Autovacuum-internals.html RAID @ Linux: http://makarevitch.org/rant/raid/ good DTRACE probes (!): http://labs.omniti.com/trac/project-dtrace/wiki/Applications http://labs.omniti.com/trac/project-dtrace/browser/trunk/postgresql/8.3.1.patch ======== Regular maintenance is required to keep heap/index bloat in check (VACUUM and VACUUM FULL) Normal VACUUM may not shrink the heap, VACUUM FULL can but requires exclusive lock on the table VACUUM requires two passes over the heap and one or more passes over each index. VACUUM generates lots of IO activity and can impact the normal performance of the database. Must be configured properly ======== mammoth replicator walmgr HA (keepalived/heartbeat): http://osdir.com/ml/db.postgresql.skytools.user/2008-06/msg00089.html http://qaix.com/postgresql-database-development/500-030-problem-with-pqsendquery-pqgetresult-and-copy-from-statement-read.shtml ========= 2. @master pg_ctl -m fast stop waiting for server to shut down.... done server stopped pgdbarw@ibm1:~$ walmgr.py wal-master.ini stop 2009-01-15 15:15:37,606 29006 INFO Disabling WAL archiving 2009-01-15 15:15:37,607 29006 INFO Using config file: /home/pgdbarw/pg_okazjerw/postgresql.conf 2009-01-15 15:15:37,608 29006 INFO Sending SIGHUP to postmaster 2009-01-15 15:15:37,608 29006 INFO Pidfile /home/pgdbarw/pg_okazjerw/postmaster.pid exists, attempting to stop syncdaemon. 2009-01-15 15:15:37,711 29006 INFO Done pgdbarw@ibm1:~$ 3. @slave walmgr.py wal-slave.ini boot pgdbarw@ibm2:~$ walmgr.py wal-slave.ini boot 2009-01-15 15:17:07,150 7656 INFO Stopping recovery mode pgdbarw@ibm2:~$ 4. check connection from old-slave/new master conenctivity: psql -l 5. setup old slave as new master (enables archiving): pgdbarw@ibm2:~$ walmgr.py wal-master.ini setup 2009-01-16 12:56:51,712 24990 INFO Configuring WAL archiving 2009-01-16 12:56:51,713 24990 INFO Using config file: /home/pgdbarw/pg_okazjerw/postgresql.conf 2009-01-16 12:56:51,714 24990 INFO Sending SIGHUP to postmaster 2009-01-16 12:56:53,002 24990 INFO Done pgdbarw@ibm2:~$ 6. backup new master/old slave to the new slave (physically backup is stored on ibm1): pgdbarw@ibm2:~$ walmgr.py wal-master.ini backup 2009-01-16 12:58:45,377 2205 INFO Backup lock obtained. 2009-01-16 12:58:45,377 2205 INFO got SystemExit(0), exiting 2009-01-16 12:58:45,434 25072 INFO Execute SQL: select pg_start_backup('FullBackup'); [dbname=template1] 2009-01-16 13:00:28,663 25072 INFO Checking tablespaces 2009-01-16 13:00:33,411 3910 INFO First useful WAL file is: 0000000100000006000000F3 2009-01-16 13:00:33,643 3917 INFO Backup lock released. 2009-01-16 13:00:33,704 25072 INFO Full backup successful pgdbarw@ibm2:~$ 7. Restore backup on ibm1: pgdbarw@ibm1:~$ walmgr.py wal-slave.ini restore 2009-01-16 13:01:58,455 4710 WARNING Old data directory is in the way, gotta move it. 2009-01-16 13:01:58,456 4710 INFO Move /home/pgdbarw/pg_okazjerw to /home/pgdbarw/pg_okazjerw.0 2009-01-16 13:01:58,456 4710 INFO Move /home/pgdbarw/walmgr/data.master to /home/pgdbarw/pg_okazjerw 2009-01-16 13:01:58,535 4710 INFO Copy /home/pgdbarw/pg_okazjerw.0/postgresql.conf to /home/pgdbarw/pg_okazjerw/postgresql.conf 2009-01-16 13:01:58,847 4710 INFO Copy /home/pgdbarw/pg_okazjerw.0/pg_hba.conf to /home/pgdbarw/pg_okazjerw/pg_hba.conf 2009-01-16 13:01:58,976 4710 INFO Copy /home/pgdbarw/pg_okazjerw.0/pg_ident.conf to /home/pgdbarw/pg_okazjerw/pg_ident.conf 2009-01-16 13:01:59,323 4710 INFO Write /home/pgdbarw/pg_okazjerw/recovery.conf 2009-01-16 13:01:59,323 4710 INFO Starting postmaster: /usr/postgresql/8.3.1-1/bin/pg_ctl -D /home/pgdbarw/pg_okazjerw -l /home/pgdbarw/pg_okazjerw/serverlog start server starting pgdbarw@ibm1:~$ Old pg_okazjerw is renamed to *.0 pgdbarw@ibm1:~$ ls -ald pg_okazjerw* drwx------ 11 pgdbarw pgdbarw 4096 2009-01-16 13:02 pg_okazjerw drwx------ 11 pgdbarw pgdbarw 4096 2009-01-15 15:15 pg_okazjerw.0 pgdbarw@ibm1:~$ pgdbarw@ibm1:~$ du -sh pg_okazjerw* 1.2G pg_okazjerw 1.2G pg_okazjerw.0 pgdbarw@ibm1:~$ 8a) Verify slave is working: pgdbarw@ibm1:~$ psql -l psql: FATAL: the database system is starting up pgdbarw@ibm1:~$ The database shouldn't be available (this is correct message). 8b) Check wal-slave.log pgdbarw@ibm1:~$ tail wal-slave.log 2009-01-16 13:01:58,847 4710 INFO Copy /home/pgdbarw/pg_okazjerw.0/pg_hba.conf to /home/pgdbarw/pg_okazjerw/pg_hba.conf 2009-01-16 13:01:58,976 4710 INFO Copy /home/pgdbarw/pg_okazjerw.0/pg_ident.conf to /home/pgdbarw/pg_okazjerw/pg_ident.conf 2009-01-16 13:01:59,323 4710 INFO Write /home/pgdbarw/pg_okazjerw/recovery.conf 2009-01-16 13:01:59,323 4710 INFO Starting postmaster: /usr/postgresql/8.3.1-1/bin/pg_ctl -D /home/pgdbarw/pg_okazjerw -l /home/pgdbarw/pg_okazjerw/serverlog start 2009-01-16 13:02:00,464 4734 INFO 00000001.history: not found, ignoring 2009-01-16 13:02:00,464 4734 INFO got SystemExit(1), exiting 2009-01-16 13:02:00,673 4738 INFO 0000000100000006000000F3.00000068.backup: Found 2009-01-16 13:02:01,071 4738 INFO {count: 1} 2009-01-16 13:02:01,177 4744 INFO 0000000100000006000000F3: Found 2009-01-16 13:02:02,261 4744 INFO {count: 1}