From: paul@wayr.org
To: pgsql-performance@postgresql.org
Subject: dedicated server & postgresql 8.1 conf tunning
Date: Mon, 29 Sep 2008 15:25:53 +0200
Message-ID: <cac82bcf377db918f9102ef389bd8219@localhost> (raw)
Hello
I'm running pgsql 8.1.11 (from debian stable) on a server with 16GB RAM
(Linux helios 2.6.18-6-amd64 #1 SMP Tue Aug 19 04:30:56 UTC 2008 x86_64
GNU/Linux).
I have a table "tickets" with 1 000 000 insert by month ( ~2600 each 2hours
) (for the moment 13000000 rows for 5GB )
and i have to extract statistics ( number of calls, number of calls less
than X seconds, number of news calles, number of calls from the new
callers, ...)
1°) The server will handle max 15 queries at a time.
So this is my postgresql.conf
max_connections = 15
shared_buffers = 995600 # ~1Go
temp_buffers = 1000
work_mem = 512000 # ~512Ko
maintenance_work_mem = 1048576 # 1Mo
max_fsm_pages = 41522880 # ~40Mo
max_fsm_relations = 8000
checkpoint_segments = 10
checkpoint_timeout = 3600
effective_cache_size = 13958643712 # 13Go
stats_start_collector = on
stats_command_string = on
stats_block_level = on
stats_row_level = on
autovacuum = off
How can i optimize the configuration?
2°) My queries look like
SELECT tday AS n,
COUNT(DISTINCT(a.appelant)) AS new_callers,
COUNT(a.appelant) AS new_calls
FROM cirpacks.tickets AS a
WHERE LENGTH(a.appelant) > 4
AND a.service_id IN ( 95, 224, 35, 18 )
AND a.exploitant_id = 66
AND a.tyear = 2008
AND a.tmonth = 08
AND EXISTS ( SELECT 1 FROM cirpacks.clients AS b WHERE b.appelant =
a.appelant AND b.service_id IN ( 95, 224, 35, 18 ) AND b.heberge_id = 66
HAVING to_char(MIN(b.premier_appel), 'YYYYMMDD') = to_char(a.date,
'YYYYMMDD') )
GROUP BY n
ORDER BY n;
or select ... SUM( CASE WHEN condition THEN value ELSE 0) ... FROM
cirpacks.tickets WHERE tyear = ... and tmonth = ... and tday = ... AND
audiotel IN ( '...', '...' ....);
or select ... SUM( CASE WHEN condition THEN value ELSE 0) ... FROM
cirpacks.tickets WHERE '2007-01-01' <= date AND date <= '2008-08-31' AND
audiotel IN ( '...', '...' ....);
which indexes are the best ?
case 0:
index_0_0 (service_id, exploitant_id, palier_id, habillage_id, tweek, tday,
thour, tmonth, tyear, length(appelant::text))
index_0_1 (audiotel, cat, tweek, tday, thour, tmonth, tyear,
length(appelant::text))
or case 1
index_1_0 (audiotel, cat, service_id, exploitant_id, palier_id,
habillage_id, tweek, tday, thour, tmonth, tyear, length(appelant::text))
or case 2:
index_2_0 (tweek, tday, thour, tmonth, tyear, length(appelant::text))
index_2_1 (service_id, exploitant_id, palier_id, habillage_id)
index_2_2 (audiotel, cat)
or even (case 3)
index_3_0 (service_id, exploitant_id, palier_id, habillage_id, tyear,
length(appelant::text))
index_3_1 (service_id, exploitant_id, palier_id, habillage_id, tmonth,
tyear, length(appelant::text))
index_3_2 (service_id, exploitant_id, palier_id, habillage_id, tday,
tmonth, tyear, length(appelant::text))
[...]
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: paul@wayr.org
Subject: Re: dedicated server & postgresql 8.1 conf tunning
In-Reply-To: <cac82bcf377db918f9102ef389bd8219@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox