с использованием PostgreSQL 9.1.2
Я наблюдаю чрезмерную загрузку процессора и большое количество операций записи на диск из задач postmaster. Это происходит даже тогда, когда мое приложение почти ничего не делает (10 секунд вставок в МИНУТУ). Однако открыто разумное количество подключений.
Я пытался определить, что в моем приложении вызывает это. Я новичок в postgresql и пока ничего не добился. Я включил некоторые параметры ведения журнала в своем конфигурационном файле и посмотрел на соединения в таблице pg_stat_activity, но все они неактивны. Тем не менее, каждое соединение потребляет ~ 50% ЦП и записывает на диск ~ 15 Мбит / с (ничего не читает).
Я в основном использую стандартный postgresql.conf с очень небольшими изменениями. Буду признателен за любые советы или подсказки о том, что я могу сделать, чтобы отследить это.
Вот пример того, что мне показывает top / iotop:
Cpu(s): 18.9%us, 14.4%sy, 0.0%ni, 53.4%id, 11.8%wa, 0.0%hi, 1.5%si, 0.0%st
Mem: 32865916k total, 7263720k used, 25602196k free, 575608k buffers
Swap: 16777208k total, 0k used, 16777208k free, 4464212k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17057 postgres 20 0 236m 33m 13m R 45.0 0.1 73:48.78 postmaster
17188 postgres 20 0 219m 15m 11m R 42.3 0.0 61:45.57 postmaster
17963 postgres 20 0 219m 16m 11m R 42.3 0.1 27:15.01 postmaster
17084 postgres 20 0 219m 15m 11m S 41.7 0.0 63:13.64 postmaster
17964 postgres 20 0 219m 17m 12m R 41.7 0.1 27:23.28 postmaster
18688 postgres 20 0 219m 15m 11m R 41.3 0.0 63:46.81 postmaster
17088 postgres 20 0 226m 24m 12m R 41.0 0.1 64:39.63 postmaster
24767 postgres 20 0 219m 17m 12m R 41.0 0.1 24:39.24 postmaster
18660 postgres 20 0 219m 14m 9.9m S 40.7 0.0 60:51.52 postmaster
18664 postgres 20 0 218m 15m 11m S 40.7 0.0 61:39.61 postmaster
17962 postgres 20 0 222m 19m 11m S 40.3 0.1 11:48.79 postmaster
18671 postgres 20 0 219m 14m 9m S 39.4 0.0 60:53.21 postmaster
26168 postgres 20 0 219m 15m 10m S 38.4 0.0 59:04.55 postmaster
Total DISK READ: 0.00 B/s | Total DISK WRITE: 195.97 M/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
17962 be/4 postgres 0.00 B/s 14.83 M/s 0.00 % 0.25 % postgres: aggw aggw [local] idle
17084 be/4 postgres 0.00 B/s 15.53 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle
17963 be/4 postgres 0.00 B/s 15.00 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle
17188 be/4 postgres 0.00 B/s 14.80 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle
17964 be/4 postgres 0.00 B/s 15.50 M/s 0.00 % 0.24 % postgres: aggw aggw [local] idle
18664 be/4 postgres 0.00 B/s 15.13 M/s 0.00 % 0.23 % postgres: aggw aggw [local] idle
17088 be/4 postgres 0.00 B/s 14.71 M/s 0.00 % 0.13 % postgres: aggw aggw [local] idle
18688 be/4 postgres 0.00 B/s 14.72 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
24767 be/4 postgres 0.00 B/s 14.93 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
18671 be/4 postgres 0.00 B/s 16.14 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
17057 be/4 postgres 0.00 B/s 13.58 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
26168 be/4 postgres 0.00 B/s 15.50 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
18660 be/4 postgres 0.00 B/s 15.85 M/s 0.00 % 0.00 % postgres: aggw aggw [local] idle
Обновить: Большая часть файлов записывается в некоторые временные (?) Файлы в каталоге $ PG_DATA / base /. Мое понимание файловой структуры здесь заключается в том, что каждая таблица в основном хранится как файл, имя которого является OID таблицы. Однако существует множество файлов с именами tnn_nnnnnnn, и кажется, что именно эти файлы постоянно записываются (возможно, перезаписываются). Для чего нужны эти файлы? Есть ~ 4700 файлов, все размером 8 КБ:
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t12_1430975
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t16_1432736
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t28_1439066
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t24_1436243
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t24_1436210
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t19_1393372
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t28_1439051
-rw-------. 1 postgres postgres 8192 Jul 3 23:08 t8_1430334
Обновить: Запуск strace в процессах postmaster в основном показывает много файлового ввода-вывода:
open("base/16388/t24_1435947_fsm", O_RDWR) = -1 ENOENT (No such file or directory)
open("base/16388/t24_1435947_vm", O_RDWR) = -1 ENOENT (No such file or directory)
open("base/16388/t24_1435947", O_RDWR) = 9
lseek(9, 0, SEEK_END) = 8192
ftruncate(9, 0) = 0
lseek(9, 0, SEEK_END) = 0
open("base/16388/t24_1435941", O_RDWR) = 18
lseek(18, 0, SEEK_END) = 0
write(9, "\0\0\0\0\0\0\0\0\1\0\0\0000\0\360\37\360\37\4 \0\0\0\0b1\5\0\2\0\0\0"..., 8192) = 8192
lseek(18, 0, SEEK_END) = 0
close(9) = 0
open("base/16388/t24_1435947", O_RDWR) = 9
lseek(9, 0, SEEK_END) = 8192
close(18) = 0
close(9) = 0
open("base/16388/t24_1435944_fsm", O_RDWR) = -1 ENOENT (No such file or directory)
open("base/16388/t24_1435944_vm", O_RDWR) = -1 ENOENT (No such file or directory)
open("base/16388/t24_1435944", O_RDWR) = 9
lseek(9, 0, SEEK_END) = 0
close(9) = 0
Обновить: Итак, эта проблема, похоже, связана с временными таблицами. Мы изменили нашу настройку, так что временные таблицы являются «обычными» таблицами, и вся дисковая активность исчезла, а производительность вернулась к той, которой я ожидал. Теперь это изменение было просто быстрой и грязной проверкой: если мы действительно собираемся использовать обычные таблицы, у нас есть проблемы с параллелизмом и очисткой. Неужели временные таблицы такие злые, или мы ими злоупотребляем?
Обновить: Еще немного предыстории. Я пользуюсь собственной разработкой ПО промежуточного слоя репликации на основе операторов. Он довольно зрелый и использовался в ряде проектов в течение нескольких лет, но с использованием MySQL. Мы работаем с PostgreSQL только последние год или два. По сути, мы использовали временные таблицы как часть механизма репликации. Каждый раз, когда устанавливается новое соединение, мы создаем временную таблицу для каждой таблицы в базе данных. С 10-20 (долгоживущими) соединениями и ~ 50 таблицами это может составить множество временных таблиц. Все временные таблицы были созданы с помощью:
CREATE TEMPORARY TABLE... ON COMMIT DELETE ROWS;
Семантика временных таблиц очень хорошо согласуется с нашей схемой репликации и упрощает большую часть кода, который мы должны были использовать для MySQL, но похоже, что реализация также была нечестной. Судя по тому небольшому исследованию, которое я провел, я не думаю, что временные таблицы действительно предназначались для той функции, для которой мы их использовали.
Я не являюсь штатным экспертом (даже близко) по этому вопросу, я просто пользуюсь им, поэтому мое объяснение может быть не на 100% точным, но я думаю, что оно довольно близко.
Ваша конфигурация PostgreSQL не работает. Это было подозрительно из вашего первоначального сообщения,
Cpu(s): 18.9%us, 14.4%sy, 0.0%ni, 53.4%id, 11.8%wa, 0.0%hi, 1.5%si, 0.0%st
Mem: 32865916k total, 7263720k used, 25602196k free, 575608k buffers
Swap: 16777208k total, 0k used, 16777208k free, 4464212k cached
Из 32 ГБ на вашем сервере ~ 25 ГБ свободно, не считая ~ 575 МБ буфера.
Из вашего файла postgresql.conf,
shared_buffers = 32MB # min 128kB
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
...
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB
Я предполагаю, что это выделенная база данных. Если да, измените его на следующие параметры и перезагрузите / перезапустите,
shared_buffers = 16GB # min 128kB
temp_buffers = 128MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
...
work_mem = 8MB # min 64kB
maintenance_work_mem = 64MB # min 1MB
max_stack_depth = 4MB
Сообщите мне, как это повлияет на вашу производительность, и дайте мне возможность настроить ее по мере необходимости.
Что касается незарегистрированных таблиц, если ваши временные таблицы содержат временные данные, которые являются эфемерными и, как вы упомянули, создаются во время сеанса, лучше использовать незарегистрированные таблицы.
Вы можете усечь свои таблицы после сеанса, если это приемлемо.
Подробнее здесь - http://michael.otacoo.com/postgresql-2/unlogged-table-performance-in-postgresql-9-1/
Я не уверен, зачем вам временные таблицы для репликации. Разве вы не можете использовать потоковую репликацию PostgreSQL?
Не могли бы вы опубликовать свой файл postgresql.conf? Ваш postgresql, похоже, значительно не оптимизирован.
Не могли бы вы также опубликовать:
Если вы используете незарегистрированные таблицы для временных таблиц?
Сколько дисков и в какой конфигурации RAID?
Использование временных таблиц и наличие длительных подключений (вероятно, задействован пул подключений) может стать обузой, если ваш сервер к этому не подготовлен. Один из параметров PostgreSQL, с которым вы можете попробовать, - это temp_buffers который управляет ОЗУ, выделенным для временных таблиц. Эти временные буферы выделяются для каждого соединения, и значение по умолчанию (8 МБ), вероятно, слишком мало для вашего сайта.
Возможно, вам также нужно немного изменить поведение вашего клиентского приложения, в зависимости от того, как вы используете свои временные таблицы. Есть аналогичный вопрос с хороший ответ на переполнение стека.