„Need for speed, czyli jak wycisnąć siódme poty z bazy PostgreSQL” - Wojciech Bublik na...

Need for speed, czyli jak wycisnąć siódme poty z

bazy PostgreSQL.

Wojciech Bublik

O EmpathyInternet Software House

www.empathy.pl

2000rok założenia

30 osóbzatrudnienie

http://www.empathy.pl/pl/aktualnosci/rok-2009.html,,0:vw:81

http://www.empathy.pl/pl/klienci/wybrani-klienci.html

http://www.empathy.pl/pl/aktualnosci/rok-2009.html,,0:vw:79

Wstęp

www.empathy.pl

Foto: www.flickr.com/photos/31935170@N08/2984070905/

Tematy

www.empathy.pl

1. Tuning konfiguracji serwera

2. O czym powinni pamiętać programiści

3. Optymalizacja zapytań na podstawie

analizy bazy danych

4. Inne możliwości optymalizacji…

Tuning konfiguracji serwera

www.empathy.pl

Foto: www.flickr.com/photos/zacky8/506835302/


www.empathy.pl

Konfiguracja sprzętowa

1. Szybkie dyski twarde – duży cache, RAID

2. Wiele procesorów – każde połączenie to

oddzielny proces

3. Duża ilość pamięci RAM


www.empathy.pl

postgresql.confmax_connections

shared_buffers

temp_buffers

max_prepared_transactions

work_mem

maintenance_work_mem

checkpoint_segments

effective_cache_size

http://www.postgresql.org/docs/8.3/interactive/kernel-

resources.html

Optymalizacja zapytańO czym powinni pamiętać programiści

www.empathy.pl

http://www.flickr.com/photos/telstar/857260510/


www.empathy.pl

1. Indeksy

CREATE INDEX idx_test1 ON mtcs_attributes (fk_attribute_class_id);

CREATE INDEX idx_test2 ON opr_cttrx (ctt_amnt, ctt_sysdate) WITH (fillfactor = 70);

CREATE INDEX idx_test3 ON opr_pptrx USING btree (ppt_date) WHERE ppt_acc = 5504100;


www.empathy.pl

2. Pola w wynikach zapytania


www.empathy.pl

3. Minimalizowanie ilości zapytań SQL

1. SELECT true FROM eicrm_company WHERE cmp_id = 1234;

UPDATE eicrm_company SET cmp_name = ‘xyz’, cmp_short_name = ‘xyz’ WHERE cmp_id = 1234;

INSERT INTO eicrm_company (cmp_name, cmp_short_name) VALUES (‘xyz’,’xyz’);

2. UPDATE eicrm_company SET cmp_name = ‘xyz’, cmp_short_name = ‘xyz’ WHERE cmp_id = 1234 RETURNING cmp_id;

INSERT INTO eicrm_company (cmp_name, cmp_short_name) VALUES (‘xyz’,’xyz’) RETURNING cmp_id;


www.empathy.pl

4. Przetwarzanie dużej ilości danych

• Transakcje,

• Prepared Statements,

• Multi-row insert,

• Copy


www.empathy.pl

5. Postać normalna? Nie za wszelką cenę.

CREATE TABLE eicrm_company( cmp_id serial NOT NULL, cmp_short_name character varying(64) NOT NULL, cmp_name character varying(256) NOT NULL, cmp_nip character varying(10));

CREATE TABLE eicrm_address( adr_id serial NOT NULL, adr_cmp_id integer, adr_street character varying(256), adr_street_no integer, adr_street_place_no character varying(8), adr_city character varying(256), adr_postal_code character varying(6));

CREATE TABLE eicrm_phone( phn_id serial NOT NULL, phn_cmp_id integer, phn_no character varying(16), phn_type smallint,);

CREATE TABLE eicrm_www( www_id serial NOT NULL, www_cmp_id integer, www_address character varying(64));


www.empathy.pl

5. Postać normalna? Nie za wszelką cenę.

SELECT cmp_name, adr_city, adr_street, adr_street_no, adr_street_place_no,(SELECT phn_no FROM eicrm_phone WHERE phn_id = cmp_main_phn_id )FROM eicrm_company LEFT OUTER JOIN eicrm_address ON (cmp_main_adr_id = adr_id)


www.empathy.pl

6. Widoki

• Przejrzyste zapytania SQL

• Dodatkowa warstwa logiki na bazie danych

• Możliwość modyfikacji zapytań bez zmian w

kodzie aplikacji

• Możliwość konwersji na …


www.empathy.pl

7. Warto próbować różnych sposobów

COST: 9940 CZAS WYKONANIA: 108 ms

SELECT DISTINCT product_group.pgr_id, product_group.pgr_nameFROM product_group INNER JOIN product_list ON (eis_product_group.pgr_id = tmp_prd_list_view.pgr_id)


SELECT product_group.pgr_id, product_group.pgr_nameFROM product_group WHERE pgr_id IN (SELECT DISTINCT pgr_id FROM product_list)


www.empathy.pl

7. Warto próbować różnych sposobów


SELECT product_group.pgr_id, product_group.pgr_nameFROM product_group WHERE pgr_id IN (SELECT pgr_id FROM product_list)


SELECT product_group.pgr_id, product_group.pgr_nameFROM product_group WHERE EXISTS (SELECT 1 FROM product_list WHERE product_list.pgr_id = product_group.pgr_id)

Optymalizacja zapytańAnaliza bazy danych

www.empathy.pl

Foto: FABIO MUZZI/AFP/Getty Images


www.empathy.pl

Analiza bazy danych

1. Analiza statystyk serweratrack_counts (stats_start_collector, stats_row_level),

pg_stat_user_tables, pg_stat_user_indexes

2. Analiza logów

log_min_duration_statement, pgFouine


www.empathy.pl

Analiza statystyk serwera

postgresql.conf:

track_counts (stats_start_collector, stats_row_level)

SELECT * FROM pg_stat_user_tables;

SELECT * FROM pg_stat_user_indexes;

Optymalizacja zapytańAnaliza bazy danych - analiza statystyk serwera

www.empathy.pl


www.empathy.pl

Analiza logów serwerapostgresql.conf:

log_statement, log_min_duration_statement

Optymalizacja zapytańAnaliza bazy danych - analiza logów serwera

www.empathy.pl

pgFouine (http://pgfouine.projects.postgresql.org)php pgfouine.php -file postgresql.log > sample_default.html

Optymalizacja zapytańAnaliza bazy danych - analiza logów serwera

www.empathy.pl

Optymalizacja zapytańZrozumieć explain…

www.empathy.pl

SELECT eicrm_user.usr_fname, eicrm_user.usr_lname, eicrm_user_type.utp_name,eicrm_company.cmp_city, eicrm_company.cmp_street, eicrm_company.cmp_name,

(SELECT pnc_name FROM eicrm_province WHERE eicrm_user.usr_pnc_id = eicrm_province.pnc_id ) as pnc_name

FROM eicrm_user JOIN eicrm_user_user_type ON eicrm_user_user_type.uut_usr_id = eicrm_user.usr_id JOIN eicrm_user_type ON eicrm_user_user_type.uut_utp_id = eicrm_user_type.utp_id LEFT OUTER JOIN eicrm_user_company ON eicrm_user_company.ucm_usr_id = eicrm_user.usr_id LEFT OUTER JOIN eicrm_company ON eicrm_company.cmp_id = eicrm_user_company.ucm_cmp_id WHERE

eicrm_user_user_type.uut_utp_id = 13 ANDeicrm_user.usr_lname = 'Kowalski' AND eicrm_user.usr_city = 'Kraków'

LIMIT 100


www.empathy.pl

Bez warunków, bez limit 100


www.empathy.pl

Bez warunków, z limit 100


www.empathy.pl

Z warunkamieicrm_user.usr_lname = 'Kowalski’, eicrm_user.usr_city = 'Kraków'bez indeksu na warunkach


www.empathy.pl

Z warunkamieicrm_user.usr_lname = 'Kowalski‘, eicrm_user.usr_city = 'Kraków'z indeksem usr_lname, usr_city


www.empathy.pl

Z warunkamieicrm_user.usr_lname = 'Kowalski‘, eicrm_user.usr_city = 'Kraków'z indeksami usr_lname oraz usr_city

Optymalizacja zapytańInne możliwości optymalizacji

www.empathy.pl

http://www.flickr.com/photos/leesam/3666918775/


www.empathy.pl

Zmaterializowane widokihttp://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views

• Tabela na podstawie wyników zapytania SQL

zawierająca snapshot danych

• Odświeżana cyklicznie lub z poziomu triggerów

• Możliwości indeksowania zmaterializowanego widoku

• Proste i skuteczne rozwiązanie dające bardzo duży

wzrost wydajności

• Zagrożenia! Kopia nie odzwierciedla aktualnego stanu

bazy danych.


www.empathy.pl

Partycjonowanie tabel• Podział dużej tabeli na wiele mniejszych

• Bardzo duży wzrost wydajności zapytań –

zapytania skierowane na tabele

z odpowiednim fragmentem danych

• Ułatwione zarządzanie danymi –

przechowywanie danych na wielu

tablespace

• Niezależne blokowanie tabel

• Brak unikalności klucza głównego

constraint_exclusion = on


www.empathy.pl

Partycjonowanie tabelCREATE TABLE opr_okktr( okk_id serial NOT NULL, okk_trnref character varying(25), okk_date date NOT NULL, okk_datelocal date, okk_crdno character varying(19), okk_acc bigint NOT NULL, okk_amnt numeric(14,2), okk_curr_id smallint, okk_amntset numeric(14,2), okk_currset_id smallint, okk_dateset date, okk_amntbill numeric(14,2), okk_currbill_id smallint, okk_ctt_id integer, okk_ref text, CONSTRAINT okk_id PRIMARY KEY (okk_id),);

CREATE TABLE opr_okktr_201001( CONSTRAINT opr_okktr_201001_date_check CHECK ( okk_date >= '2010-01-01'::date AND okk_date < '2010-02-01'::date )) INHERITS (opr_okktr);

CREATE TABLE opr_okktr_201002( CONSTRAINT opr_okktr_201002_date_check CHECK ( okk_date >= '2010-02-01'::date AND okk_date < '2010-03-01'::date )) INHERITS (opr_okktr);


www.empathy.pl

Partycjonowanie tabelCREATE OR REPLACE RULE opr_okktr_ins201001 AS ON INSERT TO opr_okktr WHERE new.okk_date >= '2010-01-01'::date AND new.okk_date < '2010-02-01'::date

DO INSTEAD

INSERT INTO opr_okktr_201001 (okk_id, okk_trnref, okk_date, okk_datelocal, okk_crdno, okk_acc, okk_amnt, okk_curr_id, okk_amntset, okk_currset_id, okk_dateset, okk_amntbill, okk_currbill_id, okk_ctt_id, okk_ref) VALUES (new.okk_id, new.okk_trnref, new.okk_date, new.okk_datelocal, new.okk_crdno, new.okk_acc, new.okk_amnt, new.okk_curr_id, new.okk_amntset, new.okk_currset_id, new.okk_dateset, new.okk_amntbill, new.okk_currbill_id, new.okk_ctt_id, new.okk_ref);


www.empathy.pl

Partycjonowanie tabelSELECT * FROM opr_okktr


www.empathy.pl

Partycjonowanie tabelSELECT * FROM opr_okktr WHERE okk_date >= ‘2008-06-15’ AND okk_date <= ‘2008-08-08’ AND okk_acc = 999999999

Optymalizacja

www.empathy.pl

http://www.flickr.com/photos/sakuto/1388525817/

www.imagineblog.pl

www.empathy.pl

Gdzie nas znaleźćNasz blog

www.empathy.pl

1. Wejdź na www.empathy.pl/facebook

2. Polub naszą stronę3. Napisz na naszej tablicy,

co robisz, aby zoptymalizować/ przyspieszyć swoją pracę.

4. To nie musi być poważne;)

5. Jutro o 12:00 rozpoczynamy 24h głosowanie na „lajki”

6. Wygraj nagrodę:)

KonkursFacebook

Dziękuję :)

[email protected]

„Need for speed, czyli jak wycisnąć siódme poty z bazy PostgreSQL” - Wojciech Bublik na...

Technology

Transcript of „Need for speed, czyli jak wycisnąć siódme poty z bazy PostgreSQL” - Wojciech Bublik na...