Debian: PostgreSQL performance for 1C

Although the Internet is already full of articles about the "correct" configuration of the PostgreSQL ligament and 1C 8.2, there is always room for pitfalls. When comparing the performance of PostgreSQL DBMS on different operating systems, the indicators differ significantly. The biggest offense was brought by the beloved Ubuntu (humanity). After long days and nights spent behind the console of this OS, she finally disappointed.

PostgreSQL brakes in Ubuntu Server. What to do with them? How many times can you step on a rake?

So, what we have: a toy IBM x3650 M4 with two processors, 32 GB RAM, an array of RAID 10 from 6 disks with a total volume of ~ 900GB. Being a supporter of open source software and considerable experience with systems a la Debian and derivatives, I decided to choose the most "humane" of them - Ubuntu Server x64. As it turned out later - this was my first mistake.

I am familiar with the DBMS not by hearsay, but I still have little experience working specifically on the Linux platform. Therefore, if somewhere was mistaken, I ask you to kick strictly with advice. I'm not the only one after all.
')
Finally, 1C released a fresh PostgreSQL build for Debian / Ubuntu that works almost out of the box.

The installation process has been simplified to a dozen console commands.

admin@srv1c:~# sudo su 

1. Increase the maximum size of the memory segment to 8GB. For less powerful machines install from 64MB to half the amount of RAM.

 root@srv1c:~# echo "kernel.shmmax=8589934592" >>/etc/sysctl.conf root@srv1c:~# sysctl -p 

2. We generate the Russian locale and set the LANG environment variable; the database initialization script will work with it.

 root@srv1c:~# locale-gen en_US ru_RU ru_RU.UTF-8 root@srv1c:~# export LANG="ru_RU.UTF-8" 

3. Install the necessary dependencies.

 root@srv1c:~# apt-get install libssl0.9.8 ssl-cert postgresql-common libossp-uuid16 libxslt1.1 

4. We take from the site users.v8.1c.ru an archive with PostgreSQL 9.1.2 for 64-bit DEB-systems, unpack and install the necessary components. There are a lot of necessary and unnecessary components in the archive, so that everything would work enough for postgresql, postgresql-client and postgresql-contrib.

 root@srv1c:~# tar zxf postgresql_9_1_2_deb_x86_64_tar.gz 

5. Installing the packages:

 root@srv1c:~# cd ./postgres root@srv1c:~# dpkg -i postgresql-9.1_9.1.2-1.1C_amd64.deb libpq5_9.1.2-1.1C_amd64.deb postgresql-client-9.1_9.1.2-1.1C_amd64.deb postgresql-contrib-9.1_9.1.2-1.1C_amd64.deb 

6. After installation, you need to tweak the configuration file a little more, if not strangely being delivered in the 1c package, it contains incorrect settings for handling escape characters, and when creating the 1c base, it produces “syntax error at or near“ SECOND ”at character 127 ″ or “Syntax error at or near“ SECOND ”at character 227 ″. We fix the following parameters in the /etc/postgresql/9.1/main/postgresql.conf file.

 root@srv1c:~# nano /etc/postgresql/9.1/main/postgresql.conf 

backslash_quote = on escape_string_warning = off standart_conforming_strings = off
And close with saving: Ctrl + x, Y

7. Restart the service.

 root@srv1c:~# service postgresql restart 

8. We change the password for the postgres user - this is the password that we will set when creating the database.

 root@srv1c:~# su postgres postgres@srv1c:/root$ cd ~ postgres@srv1c:~$ psql -U postgres -c "alter user postgres with password ' ';" postgres@srv1c:~$ exit 

9. Disable update for 1c PostgreSQL packages.

 root@srv1c:~# echo "libpq5" hold | dpkg --set-selections root@srv1c:~# echo "postgresql-9.1" hold | dpkg --set-selections root@srv1c:~# echo "postgresql-client-9.1" hold | dpkg --set-selections root@srv1c:~# echo "postgresql-contrib-9.1" hold | dpkg --set-selections 

10. Restart the service and check if PostgreSQL is running:

 root@srv1c:~# service postgresql restart root@srv1c:~# netstat -atn|grep 5432 

The answer should be something like this:

 tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 

or

 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 

Here the installation of PostgreSQL is complete can be considered complete.

When comparing the speed of the bundle 1C 8.2.16 + PostgeSQL 9.1.2, terrible brakes were found under Ubuntu Server 12.04. The test from Gilyov "TPC_1C_GILV" in Ubuntu on average showed 10-14 points, which is due to the test base, which does not involve controlled locks. For comparison, on a less powerful system with a quad-core i5 processor, 8GB RAM, under Win 2k8 and IBM DB2, the same test showed 52 parrots. Carrying documents for the month took three less time for a younger car. Similar results were obtained with PostgreSQL. Some colleagues speak of CentOS results with similar parameters. So on CentOS get 56-62 points for the same test and for net Debian - from 54 points. All tests used identical PG settings with fsync disabled. In Ubuntu ext4 was checked, in CentOS LVM + ext3.

Nothing was put on all the platform forms except for PG and 1C. On Ubuntu, several versions of PG were tested, from Etersoft, assembled manually with patches from 1C and build from 1C, under CentOS the version of Etersoft was used.

Are there any performance improvement options in Ubuntu?

I would like to make a vote on the choice of a Linux distribution for installing 1C + PostgreSQL. I think the leader of the ranking will be CentOS, but Debian is closer to my heart.

It would be nice to share in the comments successful installation experiences with test results.

I am already preparing the system for CentOS. About test results accomplish your goal in a new article.