Debian: PostgreSQL performance for 1C

Although the Internet is already full of articles on the “proper” configuration of PostgreSQL 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 greatest offense was brought by the beloved Ubuntu (humanity). After long days and nights spent at the console of this OS, she completely disappointed.

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

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

I’m familiar with the DBMS firsthand, but I still have little experience working specifically on the Linux platform. Therefore, if I made a mistake somewhere, please kick strictly with advice. I'm not the only one after all.

Finally, 1C released a fresh build of PostgreSQL under Debian / Ubuntu, which 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 the archive from PostgreSQL 9.1.2 for 64-bit DEB systems from users.v8.1c.ru , unpack and install the necessary components. There are many necessary and unnecessary components in the archive, so that everything would work enough postgresql, postgresql-client and postgresql-contrib.

root@srv1c:~# tar zxf postgresql_9_1_2_deb_x86_64_tar.gz

5. Installing 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, as it is not strange being delivered in package 1c, it contains incorrect settings for processing escaping characters, and when creating base 1c it gives errors “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. 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 the update for PostgreSQL 1s 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 has started:

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 PostgreSQL installation is complete, you can consider it finished.

When comparing the speed of the 1C 8.2.16 + PostgeSQL 9.1.2 bundle, terrible brakes were detected under Ubuntu Server 12.04. The test from Gilev “TPC_1С_GILV” in Ubuntu showed an average of 10-14 points, which is due to the test base, which does not use managed locks. For comparison, on a less powerful system with a quad-core i5 processor, 8GB of RAM, under Win 2k8 and IBM DB2, the same test showed 52 parrots. Holding documents in a month took three times less time for a younger car. Similar results were obtained with PostgreSQL. Some colleagues report on CentOS results with similar parameters. So on CentOS they get 56-62 points on the same test, and on pure Debian - from 54 points. All tests used identical PG settings with fsync disabled. Ubuntu tested ext4, on CentOS LVM + ext3.

On all platforms, nothing was set except PG and 1C. On Ubuntu, several versions of PG were tested, from Etersoft, assembled manually with patches from 1C and assembly from 1C, for CentOS, the version of Etersoft was used.

Are there any options for improving performance in Ubuntu?

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

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

I am already preparing the system for CentOS. I will unsubscribe about the test results in a new article.