Wij worden regelmatig benaderd met de vraag of we databaseomgevingen kunnen beoordelen, inventariseren of tunen. Vandaag de dag zijn de mogelijkheden om een databaseomgeving in te richten zo divers, dat vrijwel geen enkele databaseomgeving te vergelijken is met een ander. Daarover vertelde onze collega Dimitri al eerder in zijn blog ‘Zo krijg je je database weer fit en gezond’. Onze QuickScan gaat nog weer een spade dieper. De uitdaging is vaak wel dat het draaiende productieomgevingen betreft waar je liever niet allerlei scripts en analyse-tooling op loslaat om te voorkomen dat het primaire proces wordt verstoord. Wat vervolgens ook nog eens onnodig extra tijd kost. Daarbij komt het meer dan eens voor dat we tijdens onze QuickScan ook voor andere zaken worden bevraagd in het kader van ‘nu we er toch zijn’. Natuurlijk helpen we graag, maar tegelijk zorgen we wel dat we alsnog binnen de afgesproken tijd klaar kunnen zijn. In deze blog bespreekt Martijn Wallet, onze manager consultancy en training, een partitioneringsanalyse die hij maakte van een Amazon RDS Postgres-databaseomgeving tijdens een QuickScan.
Onderbouwde inschatting
Een tijdje geleden stond ik voor de uitdaging om de beste partitioning key in een Amazon RDS Postgres-database te bepalen. In een ideale situatie zouden we hier een project voor starten, zodat we dit in alle rust en zonder verstoring in een ontwikkelomgeving kunnen onderzoeken. Maar zoals gewoonlijk tijdens een QuickScan verwachtte de klant een zo goed mogelijke inschatting in zo min mogelijk tijd.
PostgreSQL heeft veel extensies beschikbaar die in allerlei situaties van pas kunnen komen. Zo ook voor het bepalen van de beste partitioning key. Maar een deel van mijn uitdaging werd bepaald door het feit dat niet al die extensies worden ondersteund op Amazon RDS PostgreSQL. HypoPG wordt echter wel ondersteund en ik heb het als volgt aangepakt:
- Een Postgres 11 opgespind via lokale Vagrant.
- Vervolgens downloadde ik de bètaversie van HypoPG omdat daar partitioning in zit.
- Ik heb de software geïnstalleerd en probeerde de extensie te bouwen, maar daar waren een aantal aanvullende packages en variabelen voor nodig:
tar xvfz 2.0.0beta.tar.gz
cd hypopg-2.0.0beta/
export PATH=$PATH:/usr/pgsql-11/bin/
yum install postgresql11-devel -y
yum install postgresql11-libs -y
yum install epel-release -y
yum install centos-release-scl -y
yum install postgresql11-devel -y
yum install gcc -y
make install
- Nu is de extensie klaar voor gebruik en kon worden ingeschakeld in een userdatabase met:
CREATE EXTENSION hypopg;
In mijn specifieke geval wilde ik de partitionering testen op een kolom met de naam user_id. Ik heb er eerst voor gezorgd dat deze kolom deel uitmaakte van elke unieke constraint, evenals de primary key. In de meeste gevallen moest ik de kolom toevoegen aan de unieke indexen en de primary key. De volgende stappen waren:
select hypopg_partition_table('shop_order','PARTITION BY RANGE(user_id)');
select tablename from hypopg_add_partition('shop_order_1_10000', 'PARTITION OF shop_order FOR VALUES FROM (1) TO (10000)');
select tablename from hypopg_add_partition('shop_order_10000_20000', 'PARTITION OF shop_order FOR VALUES FROM (10000) TO (20000)');
....
SELECT * FROM hypopg_create_index('CREATE INDEX ON shop_order (user_id)') ;
Het starten van een explain of explain analyze zal de partitioning inzichtelijk maken.
Voorbeeld:SELECT "shop_order"."uuid" FROM "shop_order" WHERE ("shop_order"."user_id" = 39626 AND "shop_order"."processed_at" IS NULL);This query has the following current execution plan:
Bitmap Heap Scan on shop_order (cost=51.42..4297.60 rows=490 width=16)
Recheck Cond: ((user_id = 39626))
Filter: (processed_at IS NULL)
-> Bitmap Index Scan on shop_order_list_idx (cost=0.00..51.29 rows=1073 width=0)
Index Cond: ((user_id = 39626))
With partitioning on user_id the query plan would look like:
Append (cost=0.03..8.06 rows=1 width=16)
-> Index Scan using btree_shop_order_user_id_order_cr on shop_order shop_order_30000_40000 (cost=0.03..8.05 rows=1 width=16)
Index Cond: ((user_id = 39626))
Filter: (processed_at IS NULL)
Simuleren in de werkelijke omgeving zonder directe invloed op die omgeving
In live draaiende productieomgevingen is het vaak lastig realtime wijzigingen door te voeren of partitioneringsanalyses te maken. Dat kost veel tijd en dat heb je simpelweg niet. Met HypoPG simuleer je ‘session-bound’ zonder dat dit direct invloed heeft op de werkelijke omgeving, terwijl je wel gebruik maakt van die werkelijke omgeving. Hierdoor krijg je een realistisch beeld van wat je wilt analyseren zonder daarvoor een ontwikkelomgeving in te moeten richten.
Gebruikmaken van elkaars kracht
Als je in een andere situatie een indexanalyse wilt creëren, werkt dat op dezelfde manier. HypoPG is een prachtige tool en illustreert hoe het is om te werken in een waardevolle community met open source. Door zo samen op te trekken maak je gebruik van zowel de beschikbare tooling als elkaars kracht.
Meer weten?
Neem gerust vrijblijvend contact met ons op als je eens meer in detail wilt sparren over beschikbare PostgreSQL extensies zoals HypoPG of als je interesse hebt in een QuickScan.