Re: [HACKERS] Block level parallel vacuum

Поиск
Список
Период
Сортировка
От Prabhat Sahu
Тема Re: [HACKERS] Block level parallel vacuum
Дата
Msg-id CANEvxPpK1Lf4HW0B-uJJ3KHmxkohod2es3pAKVVe_BtSB8jfUw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [HACKERS] Block level parallel vacuum  (Amit Kapila <amit.kapila16@gmail.com>)
Список pgsql-hackers
Дерево обсуждения
Hi all,

I would like to share my observation on this PG feature "Block-level parallel vacuum".
I have tested the earlier patch (i.e v48) with below high-level test scenarios, and those are working as expected.
  • I have played around with these GUC parameters  while testing 
max_worker_processes                                                
autovacuum = off                                                                                                                    
shared_buffers                                                          
max_parallel_workers                                                      
max_parallel_maintenance_workers                                          
min_parallel_index_scan_size
vacuum_cost_limit                                                    
vacuum_cost_delay
  • Tested the parallel vacuum with tables and Partition tables having possible datatypes and Columns having various indexes(like btree, gist, etc.) on part / full table.
  • Tested the pgbench tables data with multiple indexes created manually and ran script(vacuum_test.sql) with DMLs and VACUUM for multiple Clients, Jobs, and Time as below.
./pgbench  -c 8 -j 16 -T 900  postgres -f vacuum_test.sql
We observe the usage of parallel workers during VACUUM.
  • Ran few isolation schedule test cases(in regression) with huge data and indexes, perform DMLs -> VACUUM
  • Tested with PARTITION TABLEs -> global/local indexes ->  DMLs -> VACUUM
  • Tested with PARTITION TABLE having different TABLESPACE in different location -> global/local indexes -> DMLs -> VACUUM
  • With Changing STORAGE options for columns(as PLAIN / EXTERNAL / EXTENDED)  -> DMLs -> VACUUM
  • Create index with CONCURRENTLY option / Changing storage_parameter for index as below  -> DMLs -> VACUUM
with(buffering=auto) / with(buffering=on) / with(buffering=off) / with(fillfactor=30);
  • Tested with creating Simple and Partitioned tables ->  DMLs  -> pg_dump/pg_restore/pg_upgrade -> VACUUM
Verified the data after restore / upgrade / VACUUM.
  • Indexes on UUID-OSSP data ->  DMLs -> pg_upgrade -> VACUUM
  • Verified with various test scenarios for better performance of parallel VACUUM as compared to Non-parallel VACUUM.
         Time taken by VACUUM on PG HEAD+PATCH(with PARALLEL) <  Time taken by VACUUM on PG HEAD (without PARALLEL)

Machine configuration: (16 VCPUs / RAM: 16GB / Disk size: 640GB)
PG HEAD:
VACUUM tab1;
Time: 38915.384 ms (00:38.915)
Time: 48389.006 ms (00:48.389)
Time: 41324.223 ms (00:41.324)
Time: 37640.874 ms (00:37.641) --median
Time: 36897.325 ms (00:36.897)
Time: 36351.022 ms (00:36.351)
Time: 36198.890 ms (00:36.199)

PG HEAD + v48 Patch:
VACUUM tab1;
Time: 37051.589 ms (00:37.052)
Time: 33647.459 ms (00:33.647) --median
Time: 31580.894 ms (00:31.581)
Time: 34442.046 ms (00:34.442)
Time: 31335.960 ms (00:31.336)
Time: 34441.245 ms (00:34.441)
Time: 31159.639 ms (00:31.160)



--

With Regards,
Prabhat Kumar Sahu
EnterpriseDB: http://www.enterprisedb.com

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Amit Kapila
Дата:
Сообщение: Re: [HACKERS] Block level parallel vacuum
Следующее
От: Robert Haas
Дата:
Сообщение: Re: remove some STATUS_* symbols