Hi,
Running v7.2.2 on Linux RH 7.2 with 256Mb RAM: inserts "apparently"
slowed down to taking a couple of seconds when the table reached around
600K recs / 190 Mb. "Apparently" b/c it's not entirely consistent and
it may be related to client applications. However, this system has a
number of different databases with identical structures, all being
accessed the same way and only the big fella is having this problem.
The db has essentially one table, with each row being about 100 bytes.
Repeated VACUUM ANALYZE's and even restarts at first seemed to work for
an hour or two and then did no good at all. Currently,
MAX_FSM_RELATIONS is 100 and MAX_FSM_PAGES is 10,000. I'm not sure what
other info to give you, but here's the essentials from the VACUUM and an
EXPLAIN on a search on the key field. Suggestions?
Joe
###### VACUUM and other goop follows ##############
NOTICE: --Relation docs--
NOTICE: Index d_id_index: Pages 9523; Tuples 590810: Deleted 2738.
CPU 8.19s/0.35u sec elapsed 38.15 sec.
NOTICE: Index d_date_index: Pages 5772; Tuples 590810: Deleted 2738.
CPU 1.38s/0.27u sec elapsed 35.97 sec.
NOTICE: Removed 2738 tuples in 76 pages.
CPU 0.01s/0.01u sec elapsed 0.11 sec.
NOTICE: Pages 23291: Changed 127, Empty 0; Tup 590810: Vac 2738, Keep
0, UnUsed
407683.
Total CPU 54.92s/0.72u sec elapsed 138.25 sec.
mydb=# EXPLAIN ANALYZE SELECT * FROM docs WHERE id='SSR300-1';
NOTICE: QUERY PLAN:
Index Scan using d_id_index on docs (cost=0.00..5.79 rows=1 width=147)
(actual
time=49.90..49.91 rows=1 loops=1)
Total runtime: 50.58 msec
mydb=# EXPLAIN ANALYZE SELECT COUNT(*) FROM docs;
NOTICE: QUERY PLAN:
Aggregate (cost=30676.12..30676.12 rows=1 width=0) (actual
time=63966.20..63966
.21 rows=1 loops=1)
-> Seq Scan on docs (cost=0.00..29199.10 rows=590810 width=0)
(actual time=2
5.64..52439.05 rows=590810 loops=1)
Total runtime: 63966.33 msec