Re: PostgreSQL performance issues
От | Cédric Villemain |
---|---|
Тема | Re: PostgreSQL performance issues |
Дата | |
Msg-id | 471CA85E.2010606@dalibo.com обсуждение исходный текст |
Ответ на | Re: PostgreSQL performance issues (Gregory Stark <stark@enterprisedb.com>) |
Ответы |
Re: PostgreSQL performance issues
|
Список | pgsql-hackers |
Gregory Stark a écrit : > "Tiago J. Adami" <adamitj@gmail.com> writes: > > >> The issue topics: >> 1) As the database grows on our customers, lower performance occurs. After >> one week of use, the I/O on database is extremely high. It appears that >> VACUUM FULL and/or VACUUM ANALYZE doesn't work on this databases. >> > > VACUUM FULL is a last-resort utility for recovering from a bad situation. It > shouldn't be necessary under normal operation. The intended mode is to run > VACUUM (or VACUUM ANALYZE) frequently -- possibly several times a day -- to > maintain the data density. > > How frequently are you running VACUUM (or VACUUM ANALYZE)? How many updates > and deletes are you executing in that interval? > > If you run VACUUM (or VACUUM ANALYZE) interactively what does it print at the > end of the operation? > > >> 2) We have a very complex view mount on other views. When we cancel a simple >> SELECT on this top-level view (expecting return a max. of 100 rows for >> example) the PostgreSQL process starts a infinite loop (we left more than 4 >> days and the loop doesn't stops), using 100% of all processors on the >> server. >> > > That does sound bad. Would it be possible to attach to the process when it's > spinning and get a back trace? Also, what version is this precisely? Have you > taken all the bug-fix updates for the major version you're using? > > >> 3) On these servers, the disk usage grows very small than the records loaded >> into database. For example, after restoring a backup, the database DIR have >> about 40 Gb (with all indexes created). After one week of use, and about >> 500,000 new records on tables, the database size grows to about 42 Gb, but >> on Windows 2003 Server we can see the high fragmentation of disk (maybe on >> linux this occurs too). >> > > Postgres does extend files as needed and some filesystems may deal better with > this than others. I think this is something we don't know much about on > Windows. > humm, kernel 2.6.23 introduce fallocate ... (I am perhaps about re-lauching a flamewar) Does postgresql use posix_fallocate ? > You might find running a CLUSTER on the fragmented tables improves matters. > CLUSTER effectively does a full vacuum too so it would leave you in a good > situation to monitor the growth and vacuum frequency necessary from that point > forward too. The downsides are that CLUSTER locks the table while it runs and > it requires enough space to store a whole second copy of the table and its > indexes. > >
В списке pgsql-hackers по дате отправления: