Сегодня я хочу вам рассказать о партиционировании больших таблиц в моей любимой PostgreSQL.
Итак, начнём с определения:
Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям. Партиционированные или секционированные таблицы призваны улучшить производительность и управляемость базами данных.
Вроде понятно. Теперь идём дальше. Как же разбить таблицу на партиции или секции?
В PostgreSQL эта процедура потребует небольших усилий, но результатом вы будете довольны 🙂
Читати далі…
Вывести список всех таблиц при помощи SQL довольно просто:
SELECT
n.nspname AS "schema",
c.relname AS "table"
FROM
pg_catalog.pg_class AS c
LEFT JOIN
pg_catalog.pg_namespace AS n
ON n.oid = c.relnamespace
WHERE
n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND
c.reltablespace > 0
AND
c.relkind = 'r'
ORDER BY
c.relname ASC
В результате получим набор схема-таблица.
PPA с последней версией PostgreSQL устанавливается просто:
sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update
И потом всё просто:
sudo apt-get install postgresql-9.2
Запрос отображает использование индексов. Что позволяет увидеть наиболее часто использованные индексы, а также и наиболее редко (у которых будет index_scans_count = 0).
Учитываются только пользовательские индексы и не учитываются уникальные, т.к. они используются как ограничения (как часть логики хранения данных).
В начале отображаются наиболее часто используемые индексы (отсортированы по колонке index_scans_count).
SELECT
idstat.relname AS table_name, -- имя таблицы
indexrelname AS index_name, -- индекс
idstat.idx_scan AS index_scans_count, -- число сканирований по этому индексу
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, -- размер индекса
tabstat.idx_scan AS table_reads_index_count, -- индексных чтений по таблице
tabstat.seq_scan AS table_reads_seq_count, -- последовательных чтений по таблице
tabstat.seq_scan + tabstat.idx_scan AS table_reads_count, -- чтений по таблице
n_tup_upd + n_tup_ins + n_tup_del AS table_writes_count, -- операций записи
pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size -- размер таблицы
FROM
pg_stat_user_indexes AS idstat
JOIN
pg_indexes
ON
indexrelname = indexname
AND
idstat.schemaname = pg_indexes.schemaname
JOIN
pg_stat_user_tables AS tabstat
ON
idstat.relid = tabstat.relid
WHERE
indexdef !~* 'unique'
ORDER BY
idstat.idx_scan DESC,
pg_relation_size(indexrelid) DESC
Установить последний pgAdmin3 в Ubuntu (или в моём случаи, в Xubuntu) достаточно просто.
Нужно просто знать адрес правильный адрес PPA-репозитория 🙂
Делается так:
sudo apt-add-repository ppa:voronov84/andreyv
sudo apt-get update && sudo apt-get upgrade
И ставим, если ещё до этого не был установлен:
sudo apt-get install pgadmin3
Получить в виде списка все индексы довольно просто.
Достаточно выполнить следующее:
SELECT
n.nspname AS "schema",
c.relname AS "index"
FROM
pg_catalog.pg_class AS c
LEFT JOIN
pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE
c.relkind = 'i'
AND
n.nspname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY
c.relname ASC
И всё!
Кстати, а вот так можно получить список всех неиспользуемых индексов.
Узнаём timestamp от интересуещей даты
SELECT
extract( 'epoch' from '2012-02-01'::timestamp without time zone )::integer
(это будет 1325455200)
Используем generate_series()
SELECT
*
FROM
(
SELECT
date_trunc(
'day',
to_timestamp(
generate_series( 1325455200, 1333691315, 80000 )
)
)::date AS d
) AS s
GROUP BY
s.d
ORDER BY
s.d ASC
В результате получим что-то такое:
2012-01-02
2012-01-03
2012-01-04
2012-01-05
...
Для украинской локали
initdb --locale=uk_UA.UTF-8 --lc-collate=uk_UA.UTF-8 \
--lc-ctype=uk_UA.UTF-8 --encoding=UTF8 -D /db/postgresql
Для русской локали
initdb --locale=ru_RU.UTF-8 --lc-collate=ru_RU.UTF-8 \
--lc-ctype=ru_RU.UTF-8 --encoding=UTF8 -D /db/postgresql
Наверное, моя статья будет не интересна матерым сисадминам и покажется копипастом. Но я адресую ее тем, кто, как и я, будучи только разработчиком, впервые столкнулся с необходимостью еще и администрировать сервер, при этом решая задачи высоконагруженной БД. И чтобы гугл вас не проклял, постараюсь собрать в одном месте основные приемы для разгона сервера БД, которые мне успешно удалось реализовать.
Читати далі…
Введение
С момента написания мной предыдущей статьи по оптимизации этой связки прошло довольно много времени. Тот многострадальный Pentium 4 c 512Мб памяти, обслуживающий одновременно до тысячи человек на форуме и до 150,000 пиров на трекере уже давно покоится на какой-нить немецкой, свалке, а клуб сменил уже не один сервер. Всё сказанное в ней всё ещё остаётся актуальным, однако есть вещи которые стоит добавить.
Читати далі…