Thanks, Unfornatly, i can't update pgsql to 8.3 since it's not in debian stable.
So i'm going to play with work_mem & shared_buffers. With big shared_buffers pgsql tells me shmget(cle=5432001, taille=11183431680, 03600). so i do "echo 13183431680 > /proc/sys/kernel/shmmax" ( 10Go + 2Go just in case) but pgsql tells me again that it there's not enought shm.. How can i compute the go shmmax for my server ? On Wed, 01 Oct 2008 12:36:48 +0100, Richard Huxton <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: >> 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). > > Unless you're committed to this version, I'd seriously look into 8.3 > from backports (or compiled yourself). I'd expect some serious > performance improvements for the workload you describe. > >> 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, ...) > > OK, so not a lot of updates, but big aggregation queries. You might want > to pre-summarise older data as the system gets larger. > >> 1°) The server will handle max 15 queries at a time. >> So this is my postgresql.conf >> >> max_connections = 15 > > Well, I'd allow 20 - just in case. > >> shared_buffers = 995600 # ~1Go >> temp_buffers = 1000 >> work_mem = 512000 # ~512Ko > > I'd be tempted to increase work_mem by a lot, possibly even at the > expense of shared_buffers. You're going to be summarising large amounts > of data so the larger the better, particularly as your database is > currently smaller than RAM. Start with 5MB then try 10MB, 20MB and see > what difference it makes. > >> maintenance_work_mem = 1048576 # 1Mo >> >> max_fsm_pages = 41522880 # ~40Mo >> max_fsm_relations = 8000 > > See what a vacuum full verbose says for how much free space you need to > track. > >> checkpoint_segments = 10 >> checkpoint_timeout = 3600 > > With your low rate of updates shouldn't matter. > >> effective_cache_size = 13958643712 # 13Go > > Assuming that's based on what "top" or "free" say, that's fine. Don't > forget it will need to be reduced if you increase work_mem or > shared_buffers. > >> stats_start_collector = on >> stats_command_string = on >> stats_block_level = on >> stats_row_level = on >> autovacuum = off > > Make sure you're vacuuming if autovacuum is off. > >> How can i optimize the configuration? > > Looks reasonable, so far as you can tell from an email. Try playing with > work_mem though. > >> 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 > > Index on (tyear,tmonth) might pay off, or one on exploitant_id perhaps. > >> 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') ) > > It looks like you're comparing two dates by converting them to text. > That's probably not the most efficient way of doing it. Might not be an > issue here. > >> 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 ? > > The only way to find out is to test. You'll want to run EXPLAIN after > adding each index to see what difference it makes. Then you'll want to > see what impact this has on overall workload. > > Mostly though, I'd try out 8.3 and see if that buys you a free > performance boost. > > -- > Richard Huxton > Archonet Ltd > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
