cat /etc/debian_version
9.3
cat /proc/cpuinfo | grep "model name"
model name : Intel(R) Xeon(R) CPU E5-2603 v2 @ 1.80GHz
model name : Intel(R) Xeon(R) CPU E5-2603 v2 @ 1.80GHz
model name : Intel(R) Xeon(R) CPU E5-2603 v2 @ 1.80GHz
model name : Intel(R) Xeon(R) CPU E5-2603 v2 @ 1.80GHz
cat /proc/meminfo | grep MemTotal
MemTotal: 4100128 kB
show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
2
(1 ligne)
show shared_buffers;
shared_buffers
----------------
128MB
(1 ligne)
show effective_cache_size;
effective_cache_size
----------------------
4GB
(1 ligne)
show work_mem;
work_mem
----------
4MB
(1 ligne)
create table geants(
idg serial primary key,
genre char(1),
taille smallint,
masse smallint,
actif boolean,
devise varchar(128),
pw smallint,
heureux boolean,
couleur varchar(8),
veteran boolean,
clan smallint,
gabarit varchar(8),
revenu integer,
pm smallint,
berserk boolean,
tutelaire smallint,
ere varchar(10),
cyclope boolean);
CREATE TABLE
WITH serie(i, r1) AS (SELECT generate_series(1,10000000,1), random())
insert into geants(genre, taille, masse, actif, devise, pw, heureux, couleur, veteran, clan, gabarit, revenu, pm, berserk, tutelaire, ere, cyclope)
select
case when random() < 0.45 then 'M' else 'F' end,
200 + (trunc(random() * 200 + 1)),
300 + (trunc(random() * 200 + 1)),
case when random() < 0.01 then false when random() > 0.5 and random() < 0.99 then false else true end,
upper(md5(random()::text)),
(trunc(random()*100 + 1)),
case when random() < 0.1 then false else true end,
case when r1 <= 0.29998 then 'GRIS' when r1 <= 0.59998 then 'NOIR' else 'BLEU' end,
case when random() < 0.9 then false else true end,
(trunc(random()*1000 + 1)),
case when r1 <= 0.29999 then 'PETIT' when r1 <= 0.59999 then 'MOYEN' else 'GRAND' end,
(trunc(random()*1000000 + 1)),
(trunc(random()*10 + 1)),
case when r1 <= 0.3 then true when r1 <= 0.6 then false else null end,
(trunc(random()*10 + 1)),
case when r1 <= 0.30001 then 'TAUREAU' when r1 <= 0.60001 then 'LICORNE' else 'DRAGON' end,
case when r1 <= 0.30002 then true when r1 <= 0.60002 then false else null end
from serie;
INSERT 0 10000000
create table lancers(dtl timestamp, idg integer, perf integer);
CREATE TABLE
WITH serie(i) AS (SELECT generate_series(25000000,1,-1))
insert into lancers(dtl, idg, perf)
select
current_timestamp - (i || ' minutes')::interval,
trunc(random() * 10000000 + 1),
case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end
from serie;
INSERT 0 25000000
analyze geants;
ANALYZE
analyze lancers;
ANALYZE
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+----------------+----------+--------------+------------+-------------
public | geants | table | postgres | 1116 MB |
public | geants_idg_seq | séquence | postgres | 8192 bytes |
public | lancers | table | postgres | 1056 MB |
(3 lignes)
select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
(1 ligne)
explain select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise order by 3 desc fetch first 3 rows only;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=706888.80..706888.81 rows=3 width=69)
-> Sort (cost=706888.80..706950.14 rows=24536 width=69)
Sort Key: (avg(l.perf)) DESC
-> GroupAggregate (cost=705804.93..706571.68 rows=24536 width=69)
Group Key: g.idg
-> Sort (cost=705804.93..705958.28 rows=61340 width=41)
Sort Key: g.idg
-> Hash Join (cost=219535.40..699037.00 rows=61340 width=41)
Hash Cond: (l.idg = g.idg)
-> Seq Scan on lancers l (cost=0.00..385137.60 rows=25000160 width=8)
-> Hash (cost=219228.70..219228.70 rows=24536 width=37)
-> Gather (cost=1000.00..219228.70 rows=24536 width=37)
Workers Planned: 2
-> Parallel Seq Scan on geants g (cost=0.00..215775.10 rows=10223 width=37)
Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text))
(15 lignes)
-- demo01.sql
set application_name=demo01;
select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise order by 3 desc fetch first 3 rows only;
-- demo01.sh
time ./traqueur.sh -d 60 -p -o "pid, substr(query, 1, 64)" -o "wait_event_type" -o "wait_event_type, wait_event" -w "select pid from pg_stat_activity where application_name = 'demo01'" &
time psql -f "demo01.sql" &
wait
-- demo01.sh a ete executee plusieurs fois avant cette execution pour mettre les donnees en cache
./demo01.sh
traqueur 2.03.00 - outil de diagnostic performance pour PostgreSQL 9.3 => 11
SET
INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ...
INFORMATION, connecte a la base traqueur
INFORMATION, version de PostgreSQL detectee : 90606
INFORMATION, preparation de la collecte ...
INFORMATION, execution de la collecte et presentation des resultats ...
idg | devise | avg
---------+----------------------------------+--------------------
5312509 | 5BF343B0868129279CCD20FDD2B0F99A | 99598.000000000000
6162793 | A70EB0C56731E6D109EE6B9497E1A8F6 | 97807.000000000000
3098880 | 915FE00B25A6D296D6F3D1433758538F | 96939.000000000000
(3 lignes)
real 0m40,438s
user 0m0,088s
sys 0m0,036s
busy_pc | distinct_exe | pid | substr | non_cpu_pc | mem | swapped
---------+--------------+------+------------------------------------------------------------------+------------+---------+---------
64 | 1 / 382 | 2662 | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 1 | 5772 kB | 0 bytes
(1 ligne)
busy_pc | distinct_exe | wait_event_type | non_cpu_pc | mem | swapped
---------+--------------+-----------------+------------+---------+---------
64 | 1 / 382 | | 1 | 5772 kB | 0 bytes
(1 ligne)
busy_pc | distinct_exe | wait_event_type | wait_event | non_cpu_pc | mem | swapped
---------+--------------+-----------------+------------+------------+---------+---------
64 | 1 / 382 | | | 1 | 5772 kB | 0 bytes
(1 ligne)
Charge CPU moyennne globale (hors traqueur)
---------------------------------------------
18
(1 ligne)
Temps_CPU_User_en_s | Temps_CPU_Systeme_en_s | Temps_CPU_Idle_en_s | Temps_IO_en_s
---------------------+------------------------+---------------------+---------------
38 | 9 | 189 | 11
(1 ligne)
real 1m2,943s
user 0m0,540s
sys 0m0,308s
select version();
version
---------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.1 on x86_64-pc-linux-gnu (Debian 10.1-2.pgdg90+1), compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
(1 ligne)
create statistics geants_couleur_gabarit_ere_berserk_cyclope(dependencies, ndistinct) on couleur, gabarit, ere, berserk, cyclope from geants;
CREATE STATISTICS
analyze geants;
ANALYZE
analyze lancers;
ANALYZE
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+----------------+----------+--------------+------------+-------------
public | geants | table | postgres | 1116 MB |
public | geants_idg_seq | séquence | postgres | 8192 bytes |
public | lancers | table | postgres | 1056 MB |
(3 lignes)
\x
Affichage étendu activé.
-[ RECORD 1 ]-
stxrelid | 1426067
stxname | geants_couleur_gabarit_ere_berserk_cyclope
stxnamespace | 2200
stxowner | 10
stxkeys | 9 12 15 17 18
stxkind | {d,f}
stxndistinct | {"9, 12": 4, "9, 15": 4, "9, 17": 4, "9, 18": 4, "12, 15": 4, "12, 17": 4, "12, 18": 4, "15, 17": 3, "15, 18": 3, "17, 18": 3, "9, 12, 15": 5, "9, 12, 17": 5, "9, 12, 18": 5, "9, 15, 17": 4, "9, 15, 18": 4, "9, 17, 18": 4, "12, 15, 17": 4, "12, 15, 18": 4, "12, 17, 18": 4, "15, 17, 18": 3, "9, 12, 15, 17": 5, "9, 12, 15, 18": 5, "9, 12, 17, 18": 5, "9, 15, 17, 18": 4, "12, 15, 17, 18": 4, "9, 12, 15, 17, 18": 5}
stxdependencies | {"9 => 12": 0.601100, "9 => 15": 0.601100, "9 => 17": 0.601100, "9 => 18": 0.601100, "12 => 9": 0.700300, "12 => 15": 0.601133, "12 => 17": 0.601133, "12 => 18": 0.601133, "15 => 9": 0.700267, "15 => 12": 0.700267, "15 => 17": 1.000000, "15 => 18": 1.000000, "17 => 9": 0.700267, "17 => 12": 0.700267, "17 => 15": 1.000000, "17 => 18": 1.000000, "18 => 9": 0.700267, "18 => 12": 0.700267, "18 => 15": 1.000000, "18 => 17": 1.000000, "9, 12 => 15": 0.601133, "9, 12 => 17": 0.601133, "9, 12 => 18": 0.601133, "9, 15 => 12": 0.999933, "9, 15 => 17": 1.000000, "9, 15 => 18": 1.000000, "9, 17 => 12": 0.999933, "9, 17 => 15": 1.000000, "9, 17 => 18": 1.000000, "9, 18 => 12": 0.999933, "9, 18 => 15": 1.000000, "9, 18 => 17": 1.000000, "12, 15 => 9": 0.700300, "12, 15 => 17": 1.000000, "12, 15 => 18": 1.000000, "12, 17 => 9": 0.700300, "12, 17 => 15": 1.000000, "12, 17 => 18": 1.000000, "12, 18 => 9": 0.700300, "12, 18 => 15": 1.000000, "12, 18 => 17": 1.000000, "15, 17 => 9": 0.700267, "15, 17 => 12": 0.700267, "15, 17 => 18": 1.000000, "15, 18 => 9": 0.700267, "15, 18 => 12": 0.700267, "15, 18 => 17": 1.000000, "17, 18 => 9": 0.700267, "17, 18 => 12": 0.700267, "17, 18 => 15": 1.000000, "9, 12, 15 => 17": 1.000000, "9, 12, 15 => 18": 1.000000, "9, 12, 17 => 15": 1.000000, "9, 12, 17 => 18": 1.000000, "9, 12, 18 => 15": 1.000000, "9, 12, 18 => 17": 1.000000, "9, 15, 17 => 12": 0.999933, "9, 15, 17 => 18": 1.000000, "9, 15, 18 => 12": 0.999933, "9, 15, 18 => 17": 1.000000, "9, 17, 18 => 12": 0.999933, "9, 17, 18 => 15": 1.000000, "12, 15, 17 => 9": 0.700300, "12, 15, 17 => 18": 1.000000, "12, 15, 18 => 9": 0.700300, "12, 15, 18 => 17": 1.000000, "12, 17, 18 => 9": 0.700300, "12, 17, 18 => 15": 1.000000, "15, 17, 18 => 9": 0.700267, "15, 17, 18 => 12": 0.700267, "9, 12, 15, 17 => 18": 1.000000, "9, 12, 15, 18 => 17": 1.000000, "9, 12, 17, 18 => 15": 1.000000, "9, 15, 17, 18 => 12": 0.999933, "12, 15, 17, 18 => 9": 0.700300}
\x
Affichage étendu désactivé.
explain select g.idg, g.devise, avg(l.perf) from geants g join lancers l on (g.idg = l.idg) where g.couleur = 'GRIS' and g.gabarit = 'PETIT' and g.ere = 'TAUREAU' and berserk = true and cyclope = true group by g.idg, g.devise order by 3 desc fetch first 3 rows only;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=876384.98..876384.99 rows=3 width=69)
-> Sort (cost=876384.98..876901.51 rows=206610 width=69)
Sort Key: (avg(l.perf)) DESC
-> Finalize GroupAggregate (cost=817689.78..873714.59 rows=206610 width=69)
Group Key: g.idg
-> Gather Merge (cost=817689.78..869065.86 rows=413220 width=69)
Workers Planned: 2
-> Partial GroupAggregate (cost=816689.76..820370.00 rows=206610 width=69)
Group Key: g.idg
-> Sort (cost=816689.76..817227.80 rows=215219 width=41)
Sort Key: g.idg
-> Hash Join (cost=322056.68..775868.25 rows=215219 width=41)
Hash Cond: (l.idg = g.idg)
-> Parallel Seq Scan on lancers l (cost=0.00..239303.33 rows=10416733 width=8)
-> Hash (cost=317859.05..317859.05 rows=206610 width=37)
-> Seq Scan on geants g (cost=0.00..317859.05 rows=206610 width=37)
Filter: (berserk AND cyclope AND ((couleur)::text = 'GRIS'::text) AND ((gabarit)::text = 'PETIT'::text) AND ((ere)::text = 'TAUREAU'::text))
(17 lignes)
-- demo01.sh, ajout de backend_type avec PostgreSQL 10
time ./traqueur.sh -d 60 -p -o "pid, backend_type, substr(query, 1, 64)" -o "wait_event_type" -o "wait_event_type, wait_event" -w "select pid from pg_stat_activity where application_name = 'demo01'" &
time psql -f "demo01.sql" &
wait
./demo01.sh
traqueur 2.03.00 - outil de diagnostic performance pour PostgreSQL 9.3 => 11
SET
INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ...
INFORMATION, connecte a la base traqueur
INFORMATION, version de PostgreSQL detectee : 100001
INFORMATION, preparation de la collecte ...
INFORMATION, execution de la collecte et presentation des resultats ...
idg | devise | avg
---------+----------------------------------+--------------------
4499073 | B46FCED8CA98516A46A56ACBB9425C4A | 99928.000000000000
2696199 | C20CDF8F985C4DDE8D0A4B373F631D0F | 99758.000000000000
8301498 | 05E61B95D5688A823410C5A2A64950EF | 99456.000000000000
(3 lignes)
real 0m27,811s
user 0m0,100s
sys 0m0,028s
busy_pc | distinct_exe | pid | backend_type | substr | non_cpu_pc | mem | swapped
---------+--------------+------+-------------------+------------------------------------------------------------------+------------+---------+---------
42 | 1 / 254 | 1477 | client backend | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 3 | 5992 kB | 0 bytes
42 | 1 / 253 | 1479 | background worker | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 14 | 5272 kB | 0 bytes
42 | 1 / 253 | 1480 | background worker | select g.idg, g.devise, avg(l.perf) from geants g join lancers l | 14 | 5284 kB | 0 bytes
(3 lignes)
busy_pc | distinct_exe | wait_event_type | non_cpu_pc | mem | swapped
---------+--------------+-----------------+------------+---------+---------
92 | 3 / 549 | | 6 | 5572 kB | 0 bytes
22 | 3 / 131 | IPC | 28 | 5283 kB | 0 bytes
11 | 3 / 68 | IO | 14 | 5519 kB | 0 bytes
2 | 3 / 12 | LWLock | 3 | 5517 kB | 0 bytes
(4 lignes)
busy_pc | distinct_exe | wait_event_type | wait_event | non_cpu_pc | mem | swapped
---------+--------------+-----------------+---------------------+------------+---------+---------
92 | 3 / 549 | | | 6 | 5572 kB | 0 bytes
22 | 2 / 130 | IPC | MessageQueueSend | 28 | 5278 kB | 0 bytes
8 | 3 / 47 | IO | BufFileWrite | 18 | 5536 kB | 0 bytes
2 | 3 / 10 | IO | DataFileRead | 4 | 5562 kB | 0 bytes
2 | 3 / 11 | IO | BufFileRead | 3 | 5407 kB | 0 bytes
2 | 3 / 12 | LWLock | buffer_io | 3 | 5517 kB | 0 bytes
0 | 1 / 1 | IPC | MessageQueueReceive | 61 | 5992 kB | 0 bytes
(7 lignes)
Charge CPU moyennne globale (hors traqueur)
---------------------------------------------
29
(1 ligne)
Temps_CPU_User_en_s | Temps_CPU_Systeme_en_s | Temps_CPU_Idle_en_s | Temps_IO_en_s
---------------------+------------------------+---------------------+---------------
54 | 17 | 165 | 10
(1 ligne)
real 1m3,469s
user 0m0,604s
sys 0m0,200s