Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
ignitor
Member

Откуда: Уфа
Сообщений: 134
Железо - Intel Xeon 5110 1.6GHz памяти 2GB винты SATA 3 шт без RAID на одном операционка на другом база на третьем копии. ОС Windows server 2003 Std SP2

До изменений из 3 баз сливалась в ко мне за 30минут, теперь 3 часа. Помогите советом!

Спасибо

Переменные через show all;


#----------------------------------------
# эти параметры я менял
#----------------------------------------
listen_addresses *
max_connections 50

shared_buffers 256MB
max_prepared_transactions 50
work_mem 8MB
maintenance_work_mem 64MB
max_stack_depth 3MB

max_fsm_pages 200000
max_fsm_relations 2000

max_files_per_process 200

vacuum_cost_delay 10ms

bgwriter_all_maxpages 0
bgwriter_all_percent 0

fsync on
full_page_writes on
wal_sync_method fsync
wal_buffers 1MB

checkpoint_segments 3
checkpoint_timeout 20min
checkpoint_warning 0

effective_cache_size 400MB

default_statistics_target 500
constraint_exclusion on

client_min_messages warning
log_min_messages warning

autovacuum off

deadlock_timeout 10ms
max_locks_per_transaction 10

escape_string_warning off
#--------
# а это остальные переменные по умолчанию
#--------
add_missing_from off
allow_system_table_mods off
archive_command
archive_timeout 0
array_nulls on
authentication_timeout 1min
autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold 250
autovacuum_freeze_max_age 200000000
autovacuum_naptime 1min
autovacuum_vacuum_cost_delay -1
autovacuum_vacuum_cost_limit -1
autovacuum_vacuum_scale_factor 0.2
autovacuum_vacuum_threshold 500
backslash_quote safe_encoding
bgwriter_delay 200ms
bgwriter_lru_maxpages 5
bgwriter_lru_percent 1
block_size 8192
bonjour_name
check_function_bodies on
client_encoding UNICODE
commit_delay 0
commit_siblings 5
config_file D:/PGSQL/postgresql.conf
cpu_index_tuple_cost 0.005
cpu_operator_cost 0.0025
cpu_tuple_cost 0.01
custom_variable_classes
data_directory D:/PGSQL
DateStyle ISO, DMY
db_user_namespace off
debug_assertions off
debug_pretty_print off
debug_print_parse off
debug_print_plan off
debug_print_rewritten off
default_tablespace
default_transaction_isolation read committed
default_transaction_read_only off
default_with_oids off
dynamic_library_path $libdir
enable_bitmapscan on
enable_hashagg on
enable_hashjoin on
enable_indexscan on
enable_mergejoin on
enable_nestloop on
enable_seqscan on
enable_sort on
enable_tidscan on
explain_pretty_print on
external_pid_file
extra_float_digits 0
from_collapse_limit 8
geqo on
geqo_effort 5
geqo_generations 0
geqo_pool_size 0
geqo_selection_bias 2
geqo_threshold 12
gin_fuzzy_search_limit 0
hba_file D:/PGSQL/pg_hba.conf
ident_file D:/PGSQL/pg_ident.conf
ignore_system_indexes off
integer_datetimes off
join_collapse_limit 8
krb_caseins_users off
krb_server_hostname
krb_server_keyfile FILE:/usr/local/pgsql/etc/krb5.keytab
krb_srvname postgres
lc_collate Russian_Russia.20866
lc_ctype Russian_Russia.20866
lc_messages Russian_Russia.20866
lc_monetary Russian_Russia.20866
lc_numeric Russian_Russia.20866
lc_time Russian_Russia.20866
local_preload_libraries
log_connections off
log_destination stderr
log_directory pg_log
log_disconnections off
log_duration off
log_error_verbosity default
log_executor_stats off
log_filename postgresql-%Y-%m-%d_%H%M%S.log
log_hostname off
log_line_prefix
log_min_duration_statement -1
log_min_error_statement error
log_min_messages warning
log_parser_stats off
log_planner_stats off
log_rotation_age 1d
log_rotation_size 10MB
log_statement none
log_statement_stats off
log_truncate_on_rotation off
max_function_args 100
max_identifier_length 63
max_index_keys 32
password_encryption on
port 5432
post_auth_delay 0
pre_auth_delay 0
random_page_cost 4
redirect_stderr off
regex_flavor advanced
search_path $user",public"
seq_page_cost 1
server_encoding UTF8
server_version 08.02.2004
server_version_num 80204
shared_preload_libraries
silent_mode off
sql_inheritance on
ssl off
standard_conforming_strings off
statement_timeout 0
stats_block_level off
stats_command_string on
stats_reset_on_server_start off
stats_row_level off
stats_start_collector on
superuser_reserved_connections 3
tcp_keepalives_count 0
tcp_keepalives_idle 0
tcp_keepalives_interval 0
temp_buffers 1024
TimeZone Asia/Yekaterinburg
timezone_abbreviations Default
trace_notify off
trace_sort off
transaction_isolation read committed
transaction_read_only off
transform_null_equals off
unix_socket_directory
unix_socket_group
unix_socket_permissions 511
update_process_title on

vacuum_cost_limit 200
vacuum_cost_page_dirty 20
vacuum_cost_page_hit 1
vacuum_cost_page_miss 10
vacuum_freeze_min_age 100000000
zero_damaged_pages off
17 авг 07, 13:07    [4539422]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
Andrey Daeron
Member

Откуда: Киев
Сообщений: 1027
Может кто еще чего поумнее посоветует...
Я бы для начала поднял бы память:
work_mem	                        8MB
maintenance_work_mem	            64MB
до каких-то более исмпатичных значений.
Может поможет.
17 авг 07, 14:04    [4539929]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
ignitor
Member

Откуда: Уфа
Сообщений: 134
Andrey Daeron
Может кто еще чего поумнее посоветует...
Я бы для начала поднял бы память:
work_mem	                        8MB
maintenance_work_mem	            64MB
до каких-то более исмпатичных значений.
Может поможет.


Судя по доке, ета память отвечает вовсе не за INSERT. Добавлю так же, что инсерты я делаю в базы без индексов, т.е. DROP , куча INSERT, затем CREATE INDEX.

PG 8.4.2

work_mem (integer)
Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files. The value is defaults to one megabyte (1MB). Note that for a complex query, several sort or hash operations might be running in parallel; each one will be allowed to use as much memory as this value specifies before it starts to put data into temporary files. Also, several running sessions could be doing such operations concurrently. So the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.

maintenance_work_mem (integer)
Specifies the maximum amount of memory to be used in maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 16 megabytes (16MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings may improve performance for vacuuming and for restoring database dumps.
17 авг 07, 14:27    [4540110]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
zsm
Member

Откуда: Санкт-Петербург
Сообщений: 71
А я бы попробовал отключить fsync.
Хорошо помогает ;-)
17 авг 07, 15:31    [4540779]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
ignitor
Member

Откуда: Уфа
Сообщений: 134
zsm
А я бы попробовал отключить fsync.
Хорошо помогает ;-)

Ага, потом базу с бэкапа поднимать ... Нет fsync и раньше был включен и меня все устраивало.
Я увеличил

work_mem = 64MB # min 64kB
maintenance_work_mem = 200MB # min 1MB

Но vacuum выполняется безумно долго. Что за беда?
17 авг 07, 15:39    [4540852]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
ignitor
Member

Откуда: Уфа
Сообщений: 134
Уточню - VACUUM FULL ANALYZE

Просто VACUUM ANALYZE выполняется:

INFO:  vacuuming "public.accanl"
INFO:  scanned index "idx1" to remove 20621 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 1.46 sec.
INFO:  scanned index "idx_1" to remove 20621 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 1.56 sec.
INFO:  scanned index "idx2" to remove 20621 row versions
DETAIL:  CPU 0.00s/0.04u sec elapsed 3.98 sec.
INFO:  scanned index "idx20" to remove 20621 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 1.96 sec.
INFO:  scanned index "idx33" to remove 20621 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 2.71 sec.
INFO:  scanned index "idx30" to remove 20621 row versions
DETAIL:  CPU 0.00s/0.00u sec elapsed 2.01 sec.
INFO:  scanned index "idx31" to remove 20621 row versions
DETAIL:  CPU 0.03s/0.00u sec elapsed 5.89 sec.
INFO:  scanned index "idx32" to remove 20621 row versions
DETAIL:  CPU 0.00s/0.01u sec elapsed 1.92 sec.
INFO:  "accanl": removed 20621 row versions in 5633 pages
DETAIL:  CPU 0.09s/0.10u sec elapsed 55.53 sec.
INFO:  index "idx1" now contains 59377 row versions in 293 pages
DETAIL:  14243 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx_1" now contains 59377 row versions in 243 pages
DETAIL:  14243 index row versions were removed.
6 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx2" now contains 59377 row versions in 561 pages
DETAIL:  14243 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx20" now contains 59377 row versions in 249 pages
DETAIL:  14243 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx33" now contains 59377 row versions in 234 pages
DETAIL:  14243 index row versions were removed.
30 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx30" now contains 59377 row versions in 226 pages
DETAIL:  14243 index row versions were removed.
34 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx31" now contains 59377 row versions in 323 pages
DETAIL:  14243 index row versions were removed.
42 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "idx32" now contains 59377 row versions in 221 pages
DETAIL:  14243 index row versions were removed.
49 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "accanl": found 20621 removable, 59377 nonremovable row versions in 11144 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 255 unused item pointers.
5489 pages contain useful free space.
0 pages are entirely empty.
CPU 0.12s/0.21u sec elapsed 93.68 sec.
INFO:  vacuuming "pg_toast.pg_toast_226403"
INFO:  index "pg_toast_226403_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_226403": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.accanl"
INFO:  "accanl": scanned 11144 of 11144 pages, containing 59377 live rows and 0 dead rows; 59377 rows in sample, 59377 estimated total rows

Total query runtime: 112813 ms.

Хотя раньше с FULL хватало 20 секунд
17 авг 07, 15:55    [4541034]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
zsm
Member

Откуда: Санкт-Петербург
Сообщений: 71
А если восстановить все парамерты по-умолчанию и найти все отличия, которые были вами внесены? Хотя предполагаю, что вы это уже сделали...
И еще одна мысль. У нас были проблемы с производительностью дисковой подсистемы (SATA on RAID) сразу после уставновки Windows server 2003 Std SP2. С SP1 все работало замечательно.
Есть преположение, что это как-то связано с драйверами. Эту проблему пока так и не решили (руки никак не дойдут).
Возможно, есть смысл измерить, все ли здесь в порядке...
17 авг 07, 16:01    [4541091]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
Thamerlan
Member

Откуда: Рига
Сообщений: 203
checkpoint_segments 3
имхо, маловато будет. Поставьте 16 хотя бы
17 авг 07, 16:13    [4541176]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
ignitor
Member

Откуда: Уфа
Сообщений: 134
Andrey Daeron
Может кто еще чего поумнее посоветует...
Я бы для начала поднял бы память:
work_mem	                        8MB
maintenance_work_mem	            64MB
до каких-то более исмпатичных значений.
Может поможет.

Поставил 64MB и 300МВ соответственно

Thamerlan
checkpoint_segments 3
имхо, маловато будет. Поставьте 16 хотя бы

Поставил 16

Стал тестировать один процесс - стабильно давал 120 секунд выполнения.

Затем стал менять настройки, пробуя уменьшать\увеличивать значения

В итоге получилось стабильно (среднее из 7 значений от 83 до 112) 92 секунды выполнения при следующих параметрах:

set shared_buffers            =512MB;-- было 256MB
set work_mem                  =100MB;-- было 64MB 
set wal_buffers               =192kB;-- было 1MB
set maintenance_work_mem      =500MB;-- было 200MB
--ну и для кучи
set effective_cache_size      =800MB;-- было 400MB

особенно отличился wal_buffers

В процессе используется DROP;CREATE TABLE;COPY FROM; UPDATE; CREATE INDEX;VACUUM FULL ANALYZE;
Однако на скорость INSERT это не повлияло. Теперь я стал припоминать, что давно не обращал на этот процесс внимания и SP2 на 2003 сервер поставил недавно. Так что я все больше склоняюсь к мнению, что причина в нем. Попробую проверить на резервном сервере.
17 авг 07, 19:47    [4542392]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
ignitor
Member

Откуда: Уфа
Сообщений: 134
Нашел гада!
Подложил я пустой postgresql.conf (ну не совсем, чтобы сконнектится можно было) и, о чудо! Все вернулось на место! Стал я по одному присваивать параметры, что менял и смотреть что будет.
Сделал процесс который вставляет 10000 записей и попробовал на чистом конфиге.
Вышло 18 секунд, потом стал "улучшать" и дошел до 16 секунд. Но! Как только я высатавил

wal_sync_method = fsync

тут же получил 286 секунд (почти 17 раз). Для интереса попробовал все остальные методы. fdatasync,open_sync не запустился сервис
fsync_writethrough 283 секунды. Итак выбор редакции:

wal_sync_method = open_datasync

что и было в настройках по умолчанию.
Закончу тесты на остальные параметры, которые я потрогал - опубликую "золотой" postgresql.conf
18 авг 07, 18:24    [4543599]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
Shweik
Member

Откуда:
Сообщений: 705
Советую ознакомиться.
http://www.opennet.ru/docs/RUS/postgresql_tune/
Там кстати кой-чего сказано и про параметр который ты "соптимизировал"
;)
18 авг 07, 19:41    [4543669]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
ignitor
Member

Откуда: Уфа
Сообщений: 134
А как же моя вчерашняя задача. Первые опыты показали, что с моими успешными для инсерта настройками задача показывает в среднем 109 сек. В чем же разница? И тут я вспомнил, про:
Thamerlan
checkpoint_segments 3
имхо, маловато будет. Поставьте 16 хотя бы

И действительно
checkpoint_segments=16

привело к тому, что вчерашний рекорд был побит и среднее время выполнения задачи свелось к 66 секундам. Непонятно только одно, при значении 3 разброс по абсолютным значениям был от 79 до 115, а при 16 составил от 51 до 101 секунды.

Впрочем я доволен и публикую последний вариант postgresql.conf

listen_addresses = '*'
port = 5432
max_connections = 100

shared_buffers = 512MB
work_mem = 100MB
maintenance_work_mem = 500MB
max_stack_depth = 3MB

max_fsm_pages = 20000
max_fsm_relations = 1000

max_files_per_process = 200

fsync = true
wal_sync_method = open_datasync
wal_buffers = 24

checkpoint_segments = 16

effective_cache_size = 800MB
cpu_tuple_cost = 0.001
cpu_index_tuple_cost = 0.0001
cpu_operator_cost = 0.00025

default_statistics_target = 100

escape_string_warning = off

datestyle = 'iso, dmy'
lc_messages = 'Russian_Russia.20866'
lc_monetary = 'Russian_Russia.20866'
lc_numeric = 'Russian_Russia.20866'
lc_time = 'Russian_Russia.20866'

constraint_exclusion = on
18 авг 07, 19:47    [4543681]     Ответить | Цитировать Сообщить модератору
 Re: Поигрался с postgresql.conf и теперь на INSERT получил замедление в 6 раз. Help me!  [new]
ignitor
Member

Откуда: Уфа
Сообщений: 134
Shweik
Советую ознакомиться.
http://www.opennet.ru/docs/RUS/postgresql_tune/
Там кстати кой-чего сказано и про параметр который ты "соптимизировал"
;)

Ну читать-то я ее читал, а сунулся оптимизировать после того как посмотрел
здешний postgresql.conf

Почесал я в затылке и подумал: "У них 16GB у меня 2GB". Короче, что касалось памяти поделил на 8. В общем - балда. Зато наука.
18 авг 07, 20:01    [4543697]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить