Hi Hackers, For Copy From Binary files, there exists below information for each tuple/row. 1. field count(number of columns) 2. for every field, field size(column data length) 3. field data of field size(actual column data)
Currently, all the above data required at each step is read directly from file using fread() and this happens for all the tuples/rows. One observation is that in the total execution time of a copy from binary file, the fread() call is taking upto 20% of time and the fread() function call count is also too high. For instance, with a dataset of size 5.3GB, 10million tuples with 10 columns, total exec time in sec total time taken for fread() fread() function call count 101.193 *21.33* 210000005 101.345 *21.436* 210000005 The total time taken for fread() and the corresponding function call count may increase if we have more number of columns for instance 1000. One solution to this problem is to read data from binary file in RAW_BUF_SIZE(64KB) chunks to avoid repeatedly calling fread()(thus possibly avoiding few disk IOs). This is similar to the approach followed for csv/text files. Attaching a patch, implementing the above solution for binary format files. Below is the improvement gained. total exec time in sec total time taken for fread() fread() function call count 75.757 *2.73* 160884 75.351 *2.742* 160884 *Execution is 1.36X times faster, fread() time is reduced by 87%, fread() call count is reduced by 99%.* Request the community to take this patch for review if this approach and improvement seem beneficial. Any suggestions to improve further are most welcome. Attached also is the config file used for testing the above use case. With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com
Postgres configuration used for above testing: shared_buffers = 40GB max_worker_processes = 32 max_parallel_maintenance_workers = 24 max_parallel_workers = 32 synchronous_commit = off checkpoint_timeout = 1d max_wal_size = 24GB min_wal_size = 15GB autovacuum = off System Configuration: RAM: 503GB Disk Type: SSD Disk Size: 1.6TB Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 128 On-line CPU(s) list: 0-127 Thread(s) per core: 2 Core(s) per socket: 8 Socket(s): 8 NUMA node(s): 8 Vendor ID: GenuineIntel CPU family: 6 Model: 47 Model name: Intel(R) Xeon(R) CPU E7- 8830 @ 2.13GHz Stepping: 2 CPU MHz: 1064.000 CPU max MHz: 2129.0000 CPU min MHz: 1064.0000 BogoMIPS: 4266.62 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 24576K
v1-0001-Performance-Improvement-For-Copy-From-Binary-File.patch
Description: Binary data
