Железо: 2 Xeon, 2Gb, SCSI RAID1 FreeBSD 5.4
Клиническая картина:
ps -ax| grep postmast | wc -l
260
top:
last pid: 2463; load averages: 94.46, 101.50, 82.307 up 0+00:33:24 13:00:36
542 processes: 144 running, 398 sleeping
CPU states: 31.3% user, 0.0% nice, 52.4% system, 0.3% interrupt, 16.1% idle
Mem: 357M Active, 363M Inact, 236M Wired, 112M Buf, 1047M Free
Swap: 4096M Total, 4096M Free
PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
1931 pgsql -4 0 40124K 29744K semwai 2 0:08 0.20% 0.20% postgres
1911 pgsql 100 0 40124K 29752K RUN 0 0:08 0.20% 0.20% postgres
1961 pgsql 101 0 40124K 29728K RUN 2 0:07 0.20% 0.20% postgres
1731 pgsql -4 0 40120K 29760K semwai 2 0:11 0.15% 0.15% postgres
1794 pgsql 100 0 40120K 29752K RUN 0 0:10 0.15% 0.15% postgres
1900 pgsql 100 0 40124K 29744K RUN 2 0:08 0.15% 0.15% postgres
1973 pgsql 100 0 40124K 29740K CPU2 0 0:08 0.15% 0.15% postgres
1941 pgsql -4 0 40124K 29724K semwai 2 0:07 0.15% 0.15% postgres
1982 pgsql 100 0 40124K 29744K RUN 0 0:07 0.15% 0.15% postgres
2027 pgsql 100 0 40124K 29744K RUN 2 0:07 0.15% 0.15% postgres
2042 pgsql -4 0 40124K 29748K semwai 0 0:06 0.15% 0.15% postgres
2118 pgsql -4 0 40124K 29724K semwai 0 0:06 0.15% 0.15% postgres
2221 pgsql -4 0 40136K 29768K semwai 2 0:05 0.15% 0.15% postgres
2193 pgsql -4 0 40124K 29764K semwai 2 0:05 0.15% 0.15% postgres
2278 pgsql 100 0 40128K 29744K RUN 2 0:05 0.15% 0.15% postgres
1641 pgsql -4 0 40120K 29748K semwai 0 0:15 0.10% 0.10% postgres
1738 pgsql 101 0 40120K 29720K RUN 0 0:11 0.10% 0.10% postgres
1791 pgsql -4 0 40120K 29732K semwai 2 0:11 0.10% 0.10% postgres
1804 pgsql 100 0 40136K 29744K RUN 0 0:11 0.10% 0.10% postgres
1808 pgsql 100 0 40120K 29748K RUN 0 0:11 0.10% 0.10% postgres
1784 pgsql 100 0 40120K 29736K RUN 0 0:11 0.10% 0.10% postgres
1782 pgsql -4 0 40128K 29752K semwai 0 0:11 0.10% 0.10% postgres
1816 pgsql 101 0 40120K 29716K RUN 2 0:10 0.10% 0.10% postgres
1869 pgsql 100 0 40124K 29748K RUN 0 0:09 0.10% 0.10% postgres
1835 pgsql 100 0 40128K 29768K RUN 2 0:09 0.10% 0.10% postgres
1861 pgsql 100 0 40124K 29716K RUN 0 0:09 0.10% 0.10% postgres
1866 pgsql 101 0 40124K 29744K RUN 2 0:09 0.10% 0.10% postgres
1930 pgsql 101 0 40132K 29748K RUN 2 0:08 0.10% 0.10% postgres
1978 pgsql -4 0 40124K 29772K semwai 0 0:07 0.10% 0.10% postgres
2033 pgsql 100 0 40124K 29768K RUN 0 0:07 0.10% 0.10% postgres
2031 pgsql 100 0 40124K 29728K RUN 2 0:06 0.10% 0.10% postgres
2122 pgsql 100 0 40124K 29720K RUN 0 0:06 0.10% 0.10% postgres
2158 pgsql 101 0 40124K 29744K RUN 0 0:06 0.10% 0.10% postgres
2099 pgsql -4 0 40124K 29744K semwai 2 0:06 0.10% 0.10% postgres
2181 pgsql 101 0 40124K 29740K RUN 2 0:06 0.10% 0.10% postgres
2121 pgsql 100 0 40124K 29776K RUN 0 0:05 0.10% 0.10% postgres
2142 pgsql -4 0 40124K 29768K semwai 2 0:05 0.10% 0.10% postgres
2215 pgsql -4 0 40124K 29764K semwai 2 0:05 0.10% 0.10% postgres
2218 pgsql 100 0 40128K 29728K RUN 2 0:05 0.10% 0.10% postgres
2254 pgsql -4 0 40128K 29736K semwai 0 0:05 0.10% 0.10% postgres
2247 pgsql 100 0 40136K 29744K RUN 0 0:05 0.10% 0.10% postgres
2269 pgsql 100 0 40128K 29744K RUN 0 0:05 0.10% 0.10% postgres
1656 pgsql -4 0 40124K 29776K semwai 2 0:15 0.05% 0.05% postgres
1674 pgsql 100 0 40120K 29748K RUN 2 0:14 0.05% 0.05% postgres
1783 pgsql -4 0 40120K 29768K semwai 0 0:11 0.05% 0.05% postgres
1792 pgsql 100 0 40120K 29704K RUN 0 0:11 0.05% 0.05% postgres
1790 pgsql -4 0 40120K 29768K semwai 0 0:11 0.05% 0.05% postgres
vmstat -w5
procs memory page disks faults cpu
r b w avm fre flt re pi po fr sr am0 pa0 in sy cs us sy id
154 106 0 946432 1030344 1713 0 0 0 134 0 0 0 865 33620 54640 33 42 25
74 178 0 946504 1030244 91 0 0 0 62 0 8 0 715 40701 83377 26 53 21
21 236 0 946468 1029884 24 0 0 0 23 0 11 0 713 41033 82610 29 51 21
226 31 0 946432 1029616 9 0 0 0 10 0 4 0 677 41073 84741 26 53 21
244 14 0 947476 1028924 71 0 0 0 26 0 68 0 720 41160 76509 27 53 21
172 84 0 946324 1029124 36 0 0 0 67 0 14 0 1095 40867 78215 28 51 21
238 19 0 946368 1028564 30 0 0 0 26 0 9 0 1104 40435 81292 28 52 20
81 98 0 946308 1028016 181 0 0 0 151 0 17 0 1152 40692 76001 29 51 20
243 14 0 946368 1027800 82 0 0 0 61 0 8 0 588 39929 80073 28 52 20
68 189 0 946368 1027432 130 0 0 0 93 0 6 0 1086 39978 76040 29 52 19
237 20 0 946368 1027132 15 0 0 0 14 0 93 0 616 40120 76757 30 49 21
57 200 0 947452 1026536 169 0 0 0 85 0 6 0 1084 40845 76738 29 50 21
89 170 0 947800 1026468 108 0 0 0 82 0 7 0 935 39958 78516 27 53 20
84 172 0 946200 1027112 188 0 0 0 157 0 6 0 670 40103 76294 28 52 20
108 149 0 946168 1026712 29 0 0 0 30 0 11 0 1064 41193 81165 28 53 19
241 17 0 947372 1025800 75 0 0 0 28 0 14 0 1035 40741 76558 30 51 20
100 156 0 946152 1026188 198 0 0 0 80 0 69 0 685 40308 80110 30 50 20
94 162 0 946212 1025904 23 0 0 0 22 0 10 0 988 40292 79280 30 50 21
iostat -w5
tty amrd0 pass0 cpu
tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id
1 923 22.42 25 0.54 0.00 0 0.00 33 0 42 0 25
0 72 28.00 7 0.20 0.00 0 0.00 28 0 52 0 20
0 24 23.25 12 0.27 0.00 0 0.00 32 0 48 0 19
0 24 20.57 8 0.17 0.00 0 0.00 31 0 49 0 20
0 24 22.99 17 0.38 0.00 0 0.00 27 0 53 0 20
0 24 24.16 95 2.23 0.00 0 0.00 18 0 36 0 46
0 24 19.90 8 0.16 0.00 0 0.00 25 0 54 0 21
#---------------------------------------------------------------------------
Настройки:
/boot/loader.conf:
kern.maxusers="1024"
/etc/sysctl.conf:
net.inet.tcp.msl=3000
kern.maxfiles=32570
kern.ipc.somaxconn=1024
конфиг ядра:
machine i386
cpu I686_CPU
ident SERVER
options SCHED_4BSD # 4BSD scheduler
options INET # InterNETworking
#options INET6 # IPv6 communications protocols
options FFS # Berkeley Fast Filesystem
options SOFTUPDATES # Enable FFS soft updates support
options UFS_ACL # Support for access control lists
options UFS_DIRHASH # Improve performance on big directories
options MD_ROOT # MD is a potential root device
#options NFSCLIENT # Network Filesystem Client
#options NFSSERVER # Network Filesystem Server
#options NFS_ROOT # NFS usable as /, requires NFSCLIENT
#options MSDOSFS # MSDOS Filesystem
#options CD9660 # ISO 9660 Filesystem
options PROCFS # Process filesystem (requires PSEUDOFS)
options PSEUDOFS # Pseudo-filesystem framework
options GEOM_GPT # GUID Partition Tables.
options COMPAT_43 # Compatible with BSD 4.3 [KEEP THIS!]
options COMPAT_FREEBSD4 # Compatible with FreeBSD4
options SCSI_DELAY=15000 # Delay (in ms) before probing SCSI
#options KTRACE # ktrace(1) support
options _KPOSIX_PRIORITY_SCHEDULING # POSIX P1003_1B real-time extensions
options KBD_INSTALL_CDEV # install a CDEV entry in /dev
#options AHC_REG_PRETTY_PRINT # Print register bitfields in debug
# output. Adds ~128k to driver.
#options AHD_REG_PRETTY_PRINT # Print register bitfields in debug
# output. Adds ~215k to driver.
options ADAPTIVE_GIANT # Giant mutex is adaptive.
# System V shared memory and tunable parameters
options SYSVSHM # include support for shared memory
options SHMMAXPGS=65536 # max amount of shared memory pages (4k on i386)
options SHMMIN=2 # min shared memory segment size (bytes)
options SHMMNI=256 # max number of shared memory identifiers
options SHMSEG=256 # max shared memory segments per process
# System V semaphores and tunable parameters
options SYSVSEM # include support for semaphores
options SEMMAP=512 # amount of entries in semaphore map
options SEMMNI=512 # number of semaphore identifiers in the system
options SEMUME=512 # max number of undo entries per process
options SEMMNS=1024 # number of semaphores in the system
options SEMMNU=512 # number of undo structures in the system
options SEMMSL=256 # max number of semaphores per id
options SEMOPM=256 # max number of operations per semop call
# System V message queues and tunable parameters
options SYSVMSG # include support for message queues
options MSGMNB=8192 # max characters per message queue
options MSGMNI=256 # max number of message queue identifiers
options MSGSEG=8192 # max number of message segments in the system
options MSGSSZ=16 # size of a message segment MUST be power of 2
options MSGTQL=128 # max amount of messages in the system
options SMP
.... далее устройства, но это не интересно...
конфиг постгреса:
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
# - Connection Settings -
tcpip_socket = true
max_connections = 500
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
#superuser_reserved_connections = 2
port = 7891
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = '' # what interface to listen on; defaults to any
#rendezvous_name = '' # defaults to the computer name
# - Security & Authentication -
authentication_timeout = 60 # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 3000 # min 16, at least max_connections*2, 8KB each
sort_mem = 4096 # min 64, size in KB
vacuum_mem = 8192 # min 1024, size in KB
# - Free Space Map -
max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
max_files_per_process = 250 # min 25
#preload_libraries = ''
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
fsync = false # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or open_datasync
#wal_buffers = 8 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Enabling -
enable_hashagg = true
enable_hashjoin = true
enable_indexscan = true
enable_mergejoin = true
enable_nestloop = true
enable_seqscan = false
enable_sort = true
enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 20000 # typically 8KB each
random_page_cost = 4 # units are one sequential page fetch cost
cpu_tuple_cost = 0.01 # (same)
cpu_index_tuple_cost = 0.001 # (same)
cpu_operator_cost = 0.0025 # (same)
# - Genetic Query Optimizer -
geqo = true
geqo_threshold = 25
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
default_statistics_target = 10 # range 1-1000
from_collapse_limit = 8
join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Syslog -
syslog = 2 # range 0-2; 0=stdout; 1=both; 2=syslog
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
# - When to Log -
client_min_messages = error # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info, notice, warning, error
log_min_messages = error # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log, fatal,
# panic
log_error_verbosity = default # terse, default, or verbose messages
log_min_error_statement = error # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)
#log_min_duration_statement = -1 # Log all statements whose
# execution time exceeds the value, in
# milliseconds. Zero prints all queries.
# Minus-one disables.
#silent_mode = true # DO NOT USE without Syslog!
# - What to Log -
debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
log_connections = false
log_duration = false
log_pid = false
log_statement = false
log_timestamp = false
log_hostname = false
log_source_port = false
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
log_parser_stats = false
log_planner_stats = false
log_executor_stats = false
log_statement_stats = false
# - Query/Index Statistics Collector -
stats_start_collector = false
stats_command_string = false
stats_block_level = false
stats_row_level = false
stats_reset_on_server_start = true
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
check_function_bodies = false
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they may be changed
lc_messages = 'ru_RU.CP1251' # locale for system error message strings
lc_monetary = 'ru_RU.CP1251' # locale for monetary formatting
lc_numeric = 'ru_RU.CP1251' # locale for number formatting
lc_time = 'ru_RU.CP1251' # locale for time formatting
# - Other Defaults -
#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false