GooglemoИмеется сайт dota2nw.com на котором ОЧЕНЬ большая база данных, за 3месяца она разрослась до(состоит из 3х таблиц):
matches: 6гб с индексами 15гб
participates: 65гб с индексами 138гб
item_orders: 153гб с индексами 329гб

бд постргресс 9.2,
система убунту 12.04,
сервер:
Intel® Core™ i7-4770
Quadcore Haswell
incl. Hyper-Threading Technology
RAM32 GB DDR3 RAMHard Drive2 x 2 TB SATA 6 Gb/s 7200 rpm
HDD (Software-RAID 1)
Class EnterpriseConnection 1 Gbit/s-Port
Guaranteed Bandwidth 200 Mbit/s

есть три таблицы: на текущий момент в них 50кк 500кк и 2ккк записей соответственно
все данные в них – целые числа integer (1,2,3,4) – в зависимости от максимальных значений
таблицы заполняются равномерно, со скоростью 10кк 100кк и 600кк записей в месяц (примерно)
вставка происходит в основном командой "COPY"

к таблицам применяются две тяжелые выборки:
1) Группировка данных – работает несколько часов, как только завершилась сразу начинается заново.
2) Выборка из второй таблицы. Используется условие по 1-3 столбцам. На всех столбцах простые или мультииндексы. Операция стала выполняться от 100ms до нескольких секунд а должна мгновенно. Explain операции показывает Index Scan или даже Index Only Scan. На требуемые поля установлен CLUSTER index.

проблемы начались с ростом данных.

Надо:
1)составить структуру железа(или требования к железу), возможно несколько серверов или 1 другой, на которой сайт будет качественно функционировать.
2)возможно переписать структуру БД или перенести на другую или еще что-то...

Если оставаться в пределах postgresql штатного то
1)диски диски и еще раз диски.... 12-48 SAS 15000RPM на нормальном рейде или SSD (тоже нормальные)
2)ну и памяти 128gb хотябы
3)подумать точно ли нужны все эти данные и нельзя ли старые данные или удалять или вынести в архив где к ним особо не будут обращаться...

Т.е. у вас все сейчас в диски упирается.

На счет всего остального это уже предметный разговор нужен (надо смотреть на структуру базы и запросы).. можете попробовать к нам стукнуться.

--Maxim Boguk
www.postgresql-consulting.ru

rovanMaxim Boguk, кстати, может ТСу попробовать cstore ? Не поделитесь своим мнением?
В озвученной ситуации (в частности, вставка преимущественно COPY), как мне кажется, можно попробовать применить этот модуль.
2Googlemo - вообще, кроме железа, неплохо бы перепроектировать базу под хранилищные нагрузки. Обращайтесь, мб что-то подскажу.

cstore - может и поможет...но поскольку я его лично не проверял под нагрузками и тд - то советовать не буду

GooglemoНу вот какой запрос выполняется 9с
EXPLAIN ANALYSE
select pos, point_of_skill, matches_count
, round(100*round((0.5+(win_count*1.0000/matches_count)/2),4),2) as win_rate
,case deaths when 0 then 0 else round((kills+assists)/(deaths+0.0),2) end as kda, (select ru from period order by id desc limit 1) as descr,99 as id
from top where player_id=93824522 and game_mode=99 and lobby_type=99
UNION
select pos, point_of_skill, matches_count, round(100*round((0.5+(win_count*1.0000/matches_count)/2),4),2) as win_rate
,case deaths when 0 then 0 else round((kills+assists)/(deaths+0.0),2) end as kda,ru,id
from top_arch t join period p on t.period=p.id
where player_id=93824522 and game_mode=99 and lobby_type=99
order by id desc

Выполняется он при открытии страницы ЛЮБОГО игрока:
http://dota2nw.com/player/152854735

приведите результаты explain (analyze, costs, buffers, timing) для этого запроса

GooglemorovanMaxim Boguk, кстати, может ТСу попробовать cstore ? Не поделитесь своим мнением?
В озвученной ситуации (в частности, вставка преимущественно COPY), как мне кажется, можно попробовать применить этот модуль.
2Googlemo - вообще, кроме железа, неплохо бы перепроектировать базу под хранилищные нагрузки. Обращайтесь, мб что-то подскажу.

Собссно насчет железа можете ссылками дать какие-то варианты, чтобы хоть цифры(стоимости) видеть, Всё работало более или менее нормально с базой где-то в 200-300гб, потом случился резкий спад производительности, притом лагает только при определенных запросах, поэтому вполне вероятно нужна оптимизация.

просто база доросла до такого размера что cache hit rate упал ну например с 99.9% до 99%... вроде и не много а по факту увеличение нагрузки на дисковую подсистему в 10 раз... а она у вас в 100 раз медленее чем ноутбучный SSD (не говоря уж про серверные ssd)...
если вы посмотрите на iostat там у вас наверняка 100% IO utilization...

про сервера - ну вот например
http://www.etegro.ru/configurator/servers/rackmount/enterprise/rs230g4
в нормальной конфигурации получится порядка 600-700 тысяч (из которых 70% - диски)

PS: нельзя иметь большую активно растущую базу на никакой дисковой подсистеме...

Googlemo,

>притом лагает только при определенных запросах, поэтому вполне вероятно нужна оптимизация.

попали в данные в памяти - быстро
попали на убитые диски - медленно... в вашем случае медленее в 10.000 - 100.000 раз (если не больше)

оптимизация тут очень вряд ли поможет хотя конечно надо смотреть

еще дешевый вариант - убрать item_orders на другой сервер... но это только отсрочка...

GooglemoВот еще запрос:
Index Scan using participates_on_player_id on participates (cost=0.57..50987.54 rows=24212 width=58) (actual time=273.125..27472.903 rows=508 loops=1)
Index Cond: (player_id = 125620773)
Buffers: shared hit=5 read=216
Total runtime: 27473.139 ms

ну вот все же видно: 216 чтений с диска заняли 27 секунд... т.е. больше 0.1 секунды на чтение...
что делать
или а)ставить больше памяти и больше shared buffers чтобы было больше shared hit и меньше read (в идеале 0 read)
или б)ставить диски быстрее...
или а+б

сосбтвенно ничего больше тут сделать нельзя и никая оптимизация настроек базы вам не поможет

PS: для надежности стоит включить track_iotimings в postgresql.conf и тогда в explain будет писаться сколько времени ушло на дисковый ввод-вывод.

GooglemoMaxim Boguk,

Возможно ли реализовать какую-то связку с SSD и обычными дисками? для ускорения работы?

есть всякие варианты ssd cache на уровне файловой системы или рейд контроллера (детали смотрите в гугле).
насколько они помогут именно в вашем случае - вопрос спорный, все зависит от того есть ли у вас часть данных которая часто вызывается но не влезает в память, или у вас в среднем все данные одинаково (или близко к тому) запрашиваются (и тогда от кеша толку будет мало)...
можно еще просто индексы на ssd вынести в отдельный tablespace, тоже может помочь.

LeXa NalBatGooglemoпропущено...
Это уже сделали, помогло, но сейчас увы, этого мало.но ради 508-ми строк приходится читать 216+5 дисковых страниц, это слишком много. наверное кластеризация уже исчезла, надо выполнять CLUSTER регулярно.

идея правильная но регулярно кластер на фоне
"participates: 65гб с индексами 138гб " + "Hard Drive2 x 2 TB SATA 6 Gb/s 7200 rpm HDD (Software-RAID 1)"
боюсь будет проблемой