Differences

This shows you the differences between two versions of the page.

--- documentation:performance [2009/10/19 12:12] – DEFLATE conflicts with XMLENT, can't enable deflate for html / xml yet dbs
+++ documentation:performance [2016/04/21 15:19] – rjs7
@@ Line 1: / Line 1: @@
 ====== Optimizing the performance of your Evergreen server ======
-===== Apache server optimization =====
+===== PostgreSQL Database Configuration =====
+NOTE: Originally compiled at the Developer Hack-a-way, November 2015
+Operation System Considerations
+  * avoid 3.2 kernel (http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html)
+  * usually "deadline" I/O scheduler is better than CFQ
+  * mount filesystems used for Pg with the 'noatime' option
+  * disable NUMA zone-reclaim
+  * Kernel settings per http://www.postgresql.org/message-id/50E4AAB1.9040902@optionshouse.com (Be careful with sched_migration_cost, setting it too high can crash kernel on boot)
+  * consider segregating I/O for WAL and WAL archiving on different drives/RAID controllers than those used for the main data directory
+  * Disable transparent huge pages ([[http://georgialibraries.markmail.org/thread/em237qdardxj443i|see email thread]])
+Hardware Considerations:
+  * RAID Cache + Battery + Writeback Cache (when battery is present / enabled)
+  * separate RAID volumes for the data directory
+  * separate RAID volume for the xact_log
+  * BIOS
+    * hyperthreading disabled (bshum)
+  * Ideally enough RAM to cache entire database (or at least metabib tables and indexes) -- RAM is faster than SSD
+Application-level
+  * Use recent version of MARC::File::XML that uses DOM rather than SAX
+  * Avoid running anything other than Pg on the database server
+  * Apply the fix for https://bugs.launchpad.net/evergreen/+bug/1438136
+  * Check page size for searching
+  * Tuning opensrf.xml - max children - cstore/pcrud in particular
+  * Separate tasks onto different physical (or virtual) machines
+PostgreSQL
+  * https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
+  * autovacuum aggressiveness
+  * default_statistics_target
+  * random_page_cost (lower particularly if you have SSDs)
+  * log_min_duration_statement (to identify long-running queries)
+  * GIN indexes, especially for metabib tables
+  * Look at actual index usage (e.g., http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html)
+  * use of a separate, replicated database for reporting purposes
+Tools
+  * pg_prewarm - http://www.postgresql.org/docs/9.4/static/pgprewarm.html
+  * pgbadger - log analyzing and statistics: http://github.com/dalibo/pgbadger/
+  * EXPLAIN ANALYZE
+===== Apache server optimization (written in 2009) =====
 There are a number of steps you can follow to optimize your Apache server.