slow update of index during insert/copy
От | Thomas Finneid |
---|---|
Тема | slow update of index during insert/copy |
Дата | |
Msg-id | 48BA9D65.2020001@ifi.uio.no обсуждение исходный текст |
Ответы |
Re: slow update of index during insert/copy
Re: slow update of index during insert/copy |
Список | pgsql-performance |
Hi I am working on a table which stores up to 125K rows per second and I find that the inserts are a little bit slow. The insert is in reality a COPY of a chunk of rows, up to 125K. A COPY og 25K rows, without an index, is fast enough, about 150ms. With the index, the insert takes about 500ms. The read though, is lightning fast, because of the index. It takes only 10ms to retrieve 1000 rows from a 15M row table. As the table grows to several billion rows, that might change though. I would like the insert, with an index, to be a lot faster than 500ms, preferrably closer to 150ms. Any advice on what to do? Additionally, I dont enough about pg configuring to be sure I have included all the important directives and given them proportional values, so any help on that as well would be appreciated. Here are the details: postgres 8.2.7 on latest kubuntu, running on dual Opteron quad cores, with 8GB memory and 8 sata disks on a raid controller (no raid config) table: create table v1 ( id_s integer, id_f integer, id_st integer, id_t integer, value1 real, value2 real, value3 real, value4 real, value5 real, ... value20 real ); create index idx_v1 on v1 (id_s, id_st, id_t); - insert is a COPY into the 5-8 first columns. the rest are unused so far. postgres config: autovacuum = off checkpoint_segments = 96 commit_delay = 5 effective_cache_size = 128000 fsync = on max_fsm_pages = 208000 max_fsm_relations = 10000 max_connections = 20 shared_buffers = 128000 wal_sync_method = fdatasync wal_buffers = 256 work_mem = 512000 maintenance_work_mem = 2000000
В списке pgsql-performance по дате отправления: