X-Itools: Email/Web Log Search Engine Wiki
Strong Email & Apache Log Analysis with Active Security Features
Brought to you by:
hahnn
Menu
▾
▴
PostgreSQL database tuning
ELSE: PostgreSQL >= 9.2 database tuning for performance
Edited by Nicolas HAHN < hahnn@x-itools.com > / < hahnn@erios.org >
Here below is a sample postgresql.conf configuration file I use in a large scale environment, and giving good performances in term of database operations and I/O.
I/O are extremely crucial for the ELSE. The extremely huge amount of log lines to be parsed, in an ISP type environment, and various other operations performed by the database, make necessary to take extreme caution to the way PostgreSQL is configured, but not only.
The ELSE is database write intensive.
Hardware
Configuration of PostgreSQL is one thing, but having the right hardware is another extremely important thing. And this will vary according the number of emails/day you'll have to process.
GOLD RULE: forget virtualized servers: don't install the ELSE and its database on virtual servers, but on real physical servers ONLY! Otherwise... you'll be in permanent nightmare.
For the configuration file below, it is considered the Linux server hosting the full ELSE bundle + database has below configuration:
- 256 GB RAM
- 24 CPU cores @ 3 GHz
- 2 TB storage in RAID 5 (5 standard hard disks)
- standard RAID5 hardware on server mainboard (it's not of the level of a strong dedicated ADAPTEC RAID card)
- 4 x 1 GBits/s NICs
- RHEL 6.x x86_64
- our storage is splitted in mainly 2 partitions: one for the database indexes (40% of available space), the other one for the data (60% of the available space)
- OS is installed on a RAID1 storage, and PostgreSQL WAL are written to this storage as well
There is an example of optimized hardware solution for an ELSE server having to deal with huge email flows and huge PostgreSQL database, at the end of this page.
OS tuning
We must also tune some Linux OS things to enhance the I/O level.
Kernel variables
First of them are some kernel variables. We're going to add specific values to some of them in the /etc/sysctl.conf file.
For an ELSE database server, we're supposed to have a server with huge GB of RAM: something like more than 128 GB. For such quantity of RAM, we want to disable SWAP.
The ELSE Database, we wrote it already, is write intensive. And usually a database server needs to open a large number of files at the same time. So, fs.file-max needs to be set at 65535.
We also want to avoid huge I/O lag spikes and delayed writings to storage. By default, the more your server have RAM, bigger are write buffers. Then when the buffer are full they are flushed to storage. And writing several GB of data on storage generate huge I/O lag spikes. In addition, storage devices or SAN are built-in with large quantity of RAM for caching, avoiding the need to do it in modern OS like Linux kernel. We need to lower the vm.dirty_bytes at 64 MB and vm.dirty_background_bytes at 8 MB to avoid that. As a recommendation, you can set those values at no more than max(half the value of your storage controller cache, 64MB).
So, you can add the lines below at the end of the /etc/sysctl.conf file:
# PostgreSQL tuning
vm.swappiness = 0
fs.file-max = 65535
vm.dirty_background_bytes = 8388608
vm.dirty_bytes = 67108864
Limits
We don't forget to set specific ulimit values for postgres user, in term of number of allowed open files.
Add the lines below to the /etc/security/limits.conf file:
# PostgreSQL tuning
postgres soft nofile 65535
postgres hard nofile 65535
I/O scheduler
Then comes the kernel I/O scheduler. This component is the one passing the data to the underlying storage layer. There are 4 different strategies the I/O scheduler is using, and one is best for databases:
- noop
- anticipatory
- deadline
- cfq
By default on RHEL/CentOS 6.x, CFQ is used. You can see what is configured by issuing this command in a shell:
[root@dbs ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]
For databases, this strategy must be set to deadline by the command below:
[root@dbs ~]# echo deadline > /sys/block/sda/queue/scheduler
And that will be fine until next reboot. To keep this setting permanent, on RHEL/CentOS 6.x, the parameters passed to the kernel by the bootloader (GRUB) have to be updated by adding elevator=deadline as you can see in the example below:
title CentOS (2.6.32-573.7.1.el6.x86_64)
root (hd0,0)
kernel /vmlinuz-2.6.32-573.7.1.el6.x86_64 ro root=/dev/mapper/vg_dbs-lv_root elevator=deadline
initrd /initramfs-2.6.32-573.7.1.el6.x86_64.img
Please note that, for RHEL/CentOS 7.x, deadline is the default strategy so there is no need to modify it.
Readahead buffer
By default on Linux, the readahead buffer is set to 256. By setting this to values between 1024 and 4096 (1024, 2048 or 4096, even until 8192) on your hard drives dedicated to databases, you'll get better read performance especially for sequential reads.
To display current readahead configuration, use the command below and check the RA column:
[root@dbs ~]# blockdev --report
RO RA SSZ BSZ Sect.Début Taille Périphérique
rw 256 512 4096 0 2684354560000 /dev/sda
rw 256 512 1024 2048 5242880000 /dev/sda1
rw 256 512 4096 1026048 2679101194240 /dev/sda2
rw 256 512 4096 0 44996493312 /dev/dm-0
rw 256 512 4096 0 8405385216 /dev/dm-1
rw 256 512 4096 0 21231566848 /dev/dm-2
rw 256 512 4096 0 53687091200 /dev/dm-3
Then use the command below to set 1024 for /dev/sda:
[root@dbs ~]# blockdev --setra 1024 /dev/sda
And a new report should give you this:
[root@dbs ~]# blockdev --report
RO RA SSZ BSZ Sect.Début Taille Périphérique
rw 1024 512 4096 0 2684354560000 /dev/sda
rw 1024 512 1024 2048 5242880000 /dev/sda1
rw 1024 512 4096 1026048 2679101194240 /dev/sda2
rw 256 512 4096 0 44996493312 /dev/dm-0
rw 256 512 4096 0 8405385216 /dev/dm-1
rw 256 512 4096 0 21231566848 /dev/dm-2
rw 256 512 4096 0 53687091200 /dev/dm-3
Filesystems & Mount options
Don't create your partitions - the ones dedicated to your database server - on LVM. This implies some overhead because there is an additional layer below ext4. So, forget LVM and directly create ext4 partitions on your storage.
In term of filesystem format, by my own experience, it's best to keep ext4. You can also use XFS where several PostgreSQL users start to report it's a really good and reliable one, or ZFS. But just forget about the others (BTRFS is too young and not mature enough, ReiserFS, ext3, ...). Of course, the old ext2 filesystem would be better because there is no time spent in journaling... But they are other drawbacks, not saying the fact it's now too old. Any way, EnterpriseDB recommends to use ext2 for pg_xlog, which makes sense, but for nothing else.
When the various ext4 partitions will have to be created with mkfs.ext4 command, it's important to define the number of inodes by yourselves and not let the system do it by default, because for a PostgreSQL database, where database files can be seen as blocks of data of maximum 1 GB size, there is no need to have millions or even billions of inodes in the partition dedicated to SQL tables.
What you can do to determine a valid and enough number of inodes is just multiply the total partition size (in GB) by a factor between 4 to 10.
For a partition of 2 TB, that means 2048 GB. So you can set the number of inodes between 8192 and 20480.
Let's say ext4 is used. Here are the mount options to be used to improve performances:
- noatime: every time a file is accessed on the filesystem, the date & time of this access is updated. That means there is a write operation. The effect of this option is that there will be no writes any more every time a file is read. For a heavily loaded database like the ELSE database can be, this option is important. In addition, nobody cares to know when a database file has been accessed for the last time.
- nodiratime: this is the same as the previous option but for directories
- writeback: data ordering is not preserved. That means the data may be written into the file system after its metadata has been committed to the journal. In case of OS crash or power loss, this can lead to data corruptions and content file losses. Use it only if your RAID card or storage is backuped with a battery. Otherwise in case of crash you'll get important corruptions
- barrier=0: this can greatly improve I/O performances. Use it only if your RAID card or storage is backuped with a battery. Otherwise in case of crash you'll get important corruptions.
So, you can update the /etc/fstab file to mount the database partition(s) (/dev/sda2 in my example) with minimum set of options below:
/dev/sda2 /dbs ext4 defaults,noatime,nodiratime 1 2
Or if you like to take risks or if you have correct hardware in an Enterprise environment (backed-up by UPS, Raid Card with battery, ...), you can use the options below:
/dev/sda2 /dbs ext4 defaults,noatime,nodiratime,writeback,barrier=0 1 2
PostgreSQL tunning
Keep in mind, for this configuration, that the ELSE is not providing a vital service: it mainly process logs and compute statistics. The vital service is the messaging service itself. So, we don't have to be sure all data sent to the database are immediately sync to the storage.
In the PostgreSQL configuration file proposed below, shared buffers are set to 64 GB, working memory is increased to 32 MB, maintenance work mem is set to 2 GB, synchronous commits and full page writes are disabled, wal writer delay is set to 1 second, commit delay is set to 100, WAL segments is set to 128, effective cache size is set to 192 GB, and sequential scans are disabled.
Also very important, the database used by ELSE version 0.9.17 has been reworked to make intensive use of table partitioning. So, the database schema is totally different from the database schema that was used by ELSE version 0.9.16 and below, where this was not used.
In this way, we can probably say the ELSE version 0.9.17 with its database schema, is a totally different product than previous ELSE versions: it's better to reinstall the ELSE if you want to go to ELSE 0.9.17, instead of migrating an old DB schema to the new schema, first reason is that for huge database, this migration would take days and days of intense PostgreSQL engine work.
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: kB = kilobytes Time units: ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# h = hours
# d = days
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '' # write an extra PID file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 500 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directory = '' # (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour = off # advertise server via Bonjour
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#ssl_renegotiation_limit = 512MB # amount of data between renegotiations
#ssl_cert_file = 'server.crt' # (change requires restart)
#ssl_key_file = 'server.key' # (change requires restart)
#ssl_ca_file = '' # (change requires restart)
#ssl_crl_file = '' # (change requires restart)
#password_encryption = on
#db_user_namespace = off
# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres' # (Kerberos only)
#krb_caseins_users = off
# - TCP Keepalives -
# see "man 7 tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 64GB # min 128kB
#shared_buffers = 32MB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB # min 64kB
work_mem = 32MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
maintenance_work_mem = 2GB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Disk -
#temp_file_limit = -1 # limits per-session temp file space
# in kB, or -1 for no limit
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
# - Asynchronous Behavior -
#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
#wal_level = minimal # minimal, archive, or hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # synchronization level;
synchronous_commit = off # synchronization level;
# off, local, remote_write, or on
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
full_page_writes = off # recover from partial page writes
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
wal_writer_delay = 1000ms # 1-10000 milliseconds
#commit_delay = 0 # range 0-100000, in microseconds
commit_delay = 100 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
checkpoint_timeout = 10min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
# - Archiving -
#archive_mode = off # allows archiving to be done
# (change requires restart)
#archive_command = '' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Server(s) -
# Set these on the master and on any standby that will send replication data.
#max_wal_senders = 0 # max number of walsender processes
# (change requires restart)
#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
#replication_timeout = 60s # in milliseconds; 0 disables
# - Master Server -
# These settings are ignored on a standby server.
#synchronous_standby_names = '' # standby servers that provide sync rep
# comma-separated list of application_name
# from standby(s); '*' = all
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
# - Standby Servers -
# These settings are ignored on a master server.
#hot_standby = off # "on" allows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
#wal_receiver_status_interval = 10s # send replies at least this often
# 0 disables
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
enable_seqscan = off
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#effective_cache_size = 128MB
effective_cache_size = 192GB
# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0
# - Other Planner Options -
default_statistics_target = 200 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# This is only relevant when logging to eventlog (win32):
#event_source = 'PostgreSQL'
# - When to Log -
#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#log_min_messages = warning # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
#log_min_error_statement = error # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default # terse, default, or verbose messages
#log_hostname = off
#log_line_prefix = '' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
log_line_prefix = '%m: '
#log_lock_waits = off # log lock waits >= deadlock_timeout
#log_statement = 'none' # none, ddl, mod, all
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = 'UTC'
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#track_functions = none # none, pl, all
#track_activity_query_size = 1024 # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'UTC'
#timezone_abbreviations = 'Default' # Select the set of available time zone
# abbreviations. Currently, there are
# Default
# Australia
# India
# You can create your own file in
# share/timezonesets/.
#extra_float_digits = 0 # min -15, max 3
#client_encoding = sql_ascii # actually, defaults to database
# encoding
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
# - Other Defaults -
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
#max_pred_locks_per_transaction = 64 # min 10
# (change requires restart)
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#quote_all_identifiers = off
#sql_inheritance = on
#standard_conforming_strings = on
#synchronize_seqscans = on
# - Other Platforms and Clients -
#transform_null_equals = off
#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
#exit_on_error = off # terminate session on any error?
#restart_after_crash = on # reinitialize after backend crash?
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
This hardware, OS and software design is able to handle peaks at 50 emails/s all ways (incoming + outgoing), and a daily volume between 2 and 3 millions of emails. The database expected size (indexes + data) is 1.2 TB, for 3 months of data kept. But please note it's not an ideal hardware configuration.
Enterprise class ELSE server
This section shows you a hardware configuration example designed for huge email flows processing and huge PostgreSQL database.
- 512 GB RAM
- 2 x 24 CPU cores @ 3 GHz
- Dedicated hardware Adaptec RAID 0, 1, 10, 5, 6 card backed-up with a battery
- 5 TB local storage in RAID 10 (2 x 5 disks of 1 TB capacity minimum, make it 2 x 10 disks of 512 GB capacity if you can)
- 1.2 TB PCI-Express x3 dedicated SSD card (must support permanent and huge write operations)
- 128 GB SATA3 SSD drive (dedicated for the OS)
- 4 x 1 GBits/s NICs, but 4 x 4 to 10 GBits/s Fiber NICS if you can
- RHEL 6.7 x86_64
The Linux OS is installed on the SATA3 SSD drive. No SWAP partition is created.
PostgreSQL WAL files (pg_xlog directory) are written in a specific ext4 partition created on the PCI-Express x3 SSD card.
A PostgreSQL tablespace is created on the PCI-Express x3 SSD card, and is dedicated to indexes.
Another PostgreSQL tablespace is created on the 5 TB RAID 10 storage, in an ext4 partition. This tablespace will be for the data (the SQL tables). Please note that some heavily used SQL tables can also be placed on the PCI-Express x3 SSD card theorically dedicated to indexes and WAL files only.
PostgreSQL logs files (pg_log directory) can be written to the SATA3 SSD drive.
