One billion records table in Postgresql

I have a simple table in Postgersql database with character key.

CREATE TABLE public.zero_hids
(
    hexblock character(32) COLLATE pg_catalog."default" NOT NULL,
    hid bigint,
    CONSTRAINT zero_hids_pkey PRIMARY KEY (hexblock)
        USING INDEX TABLESPACE alcal
)

It is required to insert into this table 1000000000 unique records by loading from CSV file. We can split this file to 5 parts by 200 millions each and insert it from command line: 

!/bin/bash
time psql --port=5433 -d db1 -c "COPY zero_hids FROM '/tmp/table0.csv' delimiter AS ',' HEADER CSV"
...
time psql --port=5433 -d db1 -c "COPY zero_hids FROM '/tmp/table4.csv' delimiter AS ',' HEADER CSV"

Size of each part is around 9 Gigabytes. Allowed memory limit to Postgresql is 32Gb. Here is results: 

COPY 200000000
real 55m16.461s
user 0m0.040s
sys 0m0.008s
COPY 200000000
real 89m48.861s
user 0m0.048s
sys 0m0.016s
COPY 200000000
real 202m21.283s
user 0m0.044s
sys 0m0.024s
COPY 200000000
real 602m34.156s
user 0m0.056s
sys 0m0.008s
COPY 200000000
real 898m45.511s
user 0m0.052s
sys 0m0.016s

Really one record have uses 36 bytes + index size. If between first and second blocks time increased ~1,5 times, then for next 200M block it raise more then twice, and on the next (4th) increased 3 times more! But 5th block added again only 1,5 times slow then preveous. I think, after iserting ~550M records, database uses hard disk to build index. So, if I increase memory limit to 64 Gigabyte before start importing, it executes in around 10 hours, not 30.

In conclusion, set right memory limits to improve Postgresql peformance: imports would end in 10 hours, not 30, if I increased the memory limit to 64GB, I think.

Comments

Post a Comment

Popular posts from this blog

Installing and using a free GeoIP database

Today's article is about install and update GeoIP database on Debian-like systems, integrate it with PHP and Apache. To install GeoIP on Debian 8 or Ubuntu 16.04 simple run this command: apt-get install geoip-database geoip-database-extra Now you have a GeoIP installed. But databases are obsolete. To update GeoIP databases, find data files on your system: dpkg -L geoip-database | grep GeoIP.dat You get a path like /usr/share/GeoIP/ Go to that directory and download updates: wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCountry/GeoIP.dat.gz wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCity.dat.gz Unpack files with replace old: gzip -df *.gz Rename GeoLiteCity.dat to GeoIPCity.dat: mv -f GeoLiteCity.dat GeoIPCity.dat You can put all these commands into one bash file (e.g. geoipupd.sh ) to run as single command: #!/bin/bash cd /usr/share/GeoIP/ wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCountry/GeoIP.dat.gz wget ...
Read more

Android: Activity vs AppCompatActivity

By default, Android Studio create new Activity based by AppCompatActivity . In this way, additional modules, such as Android support library will added to the project. But in many cases you can use the basic Activity class. You can choose Activity insted of AppCompatActivity if: target API is 21 or higher (Android 5+)  target API is 17 or higher (Android 4.2+) and you do not use Matherial Design  target API is 15 or higher (Android 4.03+) and you do not use Nested Fragments and Matherial Design. In last case, if you want to use Nested Fragments but don't want Matherial Design, you can use FragmentActivity .
Read more

SNK GSCP 1.5 Release candidate is out!

Today RC of SNK GSCP - object-oriented PHP 5 framework was published. It contain classes with represents a XHTML documents, or forms, or any elements. Provides classes with interfaces for generate captcha, send email, etc. You can download it from SNK GSCP 1.5 home page ... and now you can create websites quickly and easy ;)
Read more