Re: Index bloat problem?
От | David Roussel |
---|---|
Тема | Re: Index bloat problem? |
Дата | |
Msg-id | 1114186596.23462.232500712@webmail.messagingengine.com обсуждение исходный текст |
Ответ на | Re: Index bloat problem? (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Index bloat problem?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-performance |
On Fri, 22 Apr 2005 10:06:33 -0400, "Tom Lane" <tgl@sss.pgh.pa.us> said: > David Roussel <pgsql-performance@diroussel.xsmail.com> writes: > > |dave_data_update_events r 1593600.0 40209 > > |dave_data_update_events_event_id_key i 1912320.0 29271 > > Hmm ... what PG version is this, and what does VACUUM VERBOSE on > that table show? PG 7.4 The disparity seems to have sorted itself out now, so hampering futher investigations. I guess the regular inserts of new data, and the nightly deletion and index recreation did it. However, we did suffer reduced performance and the strange cardinality for several days before it went away. For what it's worth.. ndb=# vacuum verbose iso_pjm_data_update_events; INFO: vacuuming "public.iso_pjm_data_update_events" INFO: index "iso_pjm_data_update_events_event_id_key" now contains 1912320 row versions in 29271 pages DETAIL: 21969 index pages have been deleted, 20000 are currently reusable. CPU 6.17s/0.88u sec elapsed 32.55 sec. INFO: index "iso_pjm_data_update_events_lds_idx" now contains 1912320 row versions in 7366 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 3.52s/0.57u sec elapsed 14.35 sec. INFO: index "iso_pjm_data_update_events_obj_id_idx" now contains 1912320 row versions in 7366 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 3.57s/0.58u sec elapsed 12.87 sec. INFO: "iso_pjm_data_update_events": found 0 removable, 1912320 nonremovable row versions in 40209 pages DETAIL: 159384 dead row versions cannot be removed yet. There were 745191 unused item pointers. 0 pages are entirely empty. CPU 18.26s/3.62u sec elapsed 74.35 sec. VACUUM After each insert is does this... VACUUM ANALYZE iso_pjm_DATA_UPDATE_EVENTS VACUUM ANALYZE iso_pjm_CONTROL Each night it does this... BEGIN DROP INDEX iso_pjm_control_obj_id_idx DROP INDEX iso_pjm_control_real_name_idx DROP INDEX iso_pjm_data_update_events_lds_idx DROP INDEX iso_pjm_data_update_events_obj_id_idx CREATE UNIQUE INDEX iso_pjm_control_obj_id_idx ON iso_pjm_control(obj_id) CLUSTER iso_pjm_control_obj_id_idx ON iso_pjm_control CREATE UNIQUE INDEX iso_pjm_control_real_name_idx ON iso_pjm_control(real_name) CREATE INDEX iso_pjm_data_update_events_lds_idx ON iso_pjm_data_update_events(lds) CREATE INDEX iso_pjm_data_update_events_obj_id_idx ON iso_pjm_data_update_events(obj_id) COMMIT Note there is no reference to iso_pjm_data_update_events_event_id_key which is the index that went wacky on us. Does that seem weird to you? Thanks David
В списке pgsql-performance по дате отправления:
Следующее
От: Richard PlotkinДата:
Сообщение: Re: Disk filling, CPU filling, renegade inserts and deletes?