Thread: My index doesn't write anymore but read
Hi all, We have a large database with postgre 9.3 (>300 Gb) and our queries are (really) long for 6 days without changing anything. After seeing our log, I saw that the same query on an old data was quick but the same query with new data are really slow. Let me show you a sample of my query on my table (>50 Gb): Here, the query is really speed, less than 100 ms, thanks to the index on date. With this query, and the date after the 17th, the query is really slow (>10 000 ms) How can we resolve that ? A VACUUM FULL maybe ? Why my index doesn't work for "new" data ? Notice : we are working on SSD and the partition has space. Thanks a lot for your help. -- View this message in context: http://postgresql.nabble.com/My-index-doesn-t-write-anymore-but-read-tp5849689.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
ben.play <benjamin.cohen@playrion.com> wrote: > Hi all, > > We have a large database with postgre 9.3 (>300 Gb) and our queries are > (really) long for 6 days without changing anything. > > After seeing our log, I saw that the same query on an old data was quick but > the same query with new data are really slow. please provide us a explain <your query>. Please try a 'analyse <table>'. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Thank you for your quick answer ! And ... you are a genius :) A simple "analyse " resolved my problem. Do We have to do it regularly ? Thank you a lot ! -- View this message in context: http://postgresql.nabble.com/My-index-doesn-t-write-anymore-but-read-tp5849689p5849699.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
ben.play <benjamin.cohen@playrion.com> wrote: > Thank you for your quick answer ! > > And ... you are a genius :) Yeah, i know ;-) > > A simple "analyse > " resolved my problem. > Do We have to do it regularly ? it's running regulary (via vacuum-process), but you can (and should) run it after mass data changes. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Hello Ben,
Looks like you need to tune autovacuum to be more aggressive. Make sure autovacuum=ON (the default), increase autovacuum_max_workers (at least 1 per database, more if autovacuum is falling behind), autovacuum_vacuum_scale_factor to be ~half of the default and can be set per table to be lower for large tables, autovacuum_vacuum_scale_factor to be ~half of the default.
You can run the following SQL to see the last time each table was vacuumed. If it's still not frequent enough you can try to decrease autovacuum_naptime.
This SQL is from Bucardo's check_postgres:
SELECT current_database() AS datname,
nspname AS sname,
relname AS tname,
CASE
WHEN v IS NULL THEN -1
ELSE round(extract(epoch
FROM now()-v))
END AS ltime,
CASE
WHEN v IS NULL THEN '?'
ELSE TO_CHAR(v, 'HH24:MI FMMonth DD, YYYY')
END AS ptime
FROM
(SELECT nspname,
relname,
GREATEST(pg_stat_get_last_analyze_time(c.oid), pg_stat_get_last_autoanalyze_time(c.oid)) AS v
FROM pg_class c,
pg_namespace n
WHERE relkind = 'r'
AND n.oid = c.relnamespace
AND n.nspname <> 'information_schema'
ORDER BY 3) AS foo;
And review the bloat of each table (should be ~1. If far above 1 vacuum is falling behind):
SELECT schemaname,
relname,
(pg_relation_size(relid)) AS table_bytes,
n_live_tup,
n_dead_tup,
(n_live_tup::float+n_dead_tup)/n_live_tup AS bloat
FROM pg_stat_user_tables
WHERE n_live_tup>(current_setting('autovacuum_vacuum_threshold')::bigint*10)+1;
Will J. Dunn
On Mon, May 18, 2015 at 3:51 AM, ben.play <benjamin.cohen@playrion.com> wrote:
Thank you for your quick answer !
And ... you are a genius :)
A simple "analyse
" resolved my problem.
Do We have to do it regularly ?
Thank you a lot !
--
View this message in context: http://postgresql.nabble.com/My-index-doesn-t-write-anymore-but-read-tp5849689p5849699.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thank your William for your help. After 1 week, I do not see any improvement. Worse, I have to manually run "ANALYSE" daily. Here is my config : My bloat for this table is 1.04151. What does it mean exactly ? Why do we have to analyse the table daily ? Is it normal ? Thank you so much for your help ! -- View this message in context: http://postgresql.nabble.com/My-index-doesn-t-write-anymore-but-read-tp5849689p5852012.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.