FreeBSD + PostgreSQL: tuning a database server

Hello, Habr community!

Probably, my article will not be interesting to seasoned system administrators and will seem copy-paste. But I am addressing it to those who, like me, being only a developer, first encountered the need to also administer a server, while solving the tasks of a highly loaded database. And so that Google doesn’t curse you, I’ll try to collect in one place the basic techniques for overclocking the database server that I have successfully implemented.

The input to my task is as follows: a dual-processor (Intel Xeon) machine, 8 hards of 500GB and 12GB of RAM. And full, including physical, access to this good. Objective: to organize a fast database server based on the FreeBSD and PostgreSQL OS.

1. RAID

We will need the correct splitting of the available hards into raids for such a PostgreSQL feature as tablespacing (more on this below). I divided my 8 hards into pairs, organizing in this way: combined two pairs in RAID1 and two pairs in RAID0 (in general, for our purposes we need at least 6 hards - combine two pairs in RAID1, leave the other 2 as they are). If you have a larger number of hards, you can come up with something more reliable, such as RAID5, RAID10, etc., but there is a chance that this will work somewhat slower. I will not go into details on how to organize raids, as I'm not strong in iron, I can only say that I did not touch any controllers, because on the server after the BIOS, a utility is loaded that allows you to do this programmatically.

2. Installing the OS, database server and using your kernel

First, just put the junk on the first RAID1. I installed the FreeBSD 8.2 Release AMD64 distribution with all the files. A 64-bit version is needed so that the system "sees" all the RAM.

Now the most interesting part: why should we compile the kernel and what parameters should be changed? This is necessary to allow the PostgreSQL server to use as many resources as needed for high load. So, what database parameters are of interest to us. In Alexey Vasiliev’s book “Working with Postgresql. Tuning, scaling ”, the following parameters are recommended for highly loaded databases (file postgresql.conf):

• shared_buffers = 1/8 RAM or more (but not more than 1/4);
• swork_mem in 1/20 RAM;
• smaintenance_work_mem in 1/4 RAM;
• smax_fsm_relations in the planned number of tables in the databases * 1.5;
• max_fsm_pages in max_fsm_relations * 2000;
• fsync = true;
• wal_sync_method = fdatasync;
• commit_delay = 10 to 100;
• commit_siblings = 5 to 10;
• effective_cache_size = 0.9 of the cached value, which shows free;
• random_page_cost = 2 for fast cpu, 4 for slow;
• cpu_tuple_cost = 0.001 for fast cpu, 0.01 for slow;
• cpu_index_tuple_cost = 0.0005 for fast cpu, 0.005 for slow;
• autovacuum = on;
• autovacuum_vacuum_threshold = 1800;
• autovacuum_analyze_threshold = 900;

These options really suit us, except for two:

1) The maximum number of connections

depends on the specific situation. I have a script running in the crown (connect to the database and enter data), I figured that 256 should be enough:

• max_connection = 256;

But the default FreeBSD configuration does not provide this value for the number of connections. If you set this value and try to start the postgres daemon, then nothing will work. It is necessary to increase the corresponding system parameters. For this, we will assemble our core. Take the default GENERIC kernel config, make a copy called KERNEL_MAX_PERF, edit KERNEL_MAX_PERF as follows: change the number of semaphores by adding the lines to the default options:

options SEMMNI=512
options SEMMNS=1024
options SEMUME=64
options SEMMNU=512


(these are values ​​for max_connection = 256).

2) The maximum amount of RAM that PostgreSQL can take (this is important for bulk queries). The shared_buffers parameter in postgresql.conf is responsible for it. There are different recommendations regarding the value for this quantity. I came to the conclusion that if this is a server dedicated to the database, then you can give almost the entire amount of RAM to one process minus what the system needs for its needs. I selected 8GB from 12. In order for the system to allow us to set the desired value for shared_buffers, in the kernel you need to change the SHMMAXPGS option, the value of which is calculated by the formula:

SHMMAXPGS = shared_buffers / PAGE_SIZE

in my case shared_buffers = 8GB, PAGE_SIZE = 4 Kb for all i386 means
SHMMAXPGS = 8 * 1024 * 1024/4 = 2097152); Now we can write the SHMMAX parameter (calculated dynamically in the kernel). So, we write in the kernel config:

options SHMMAXPGS = 2097152
options SHMMAX = "(SHMMAXPGS*PAGE_SIZE + 1)"

It remains to compile the kernel with the KERNEL_MAX_PERF config. The kernel compilation procedure itself is simple, here I refer you to the official mana.

We load the OS with our kernel, install the latest version of PostgreSQL (I had version 9.0.4), first we start PostgreSQL with the default config for verification. If everything is ok, we change the parameters in postgresql.conf to those specified above, we restart PostgreSQL. It started - we go further.

Note: if for some reason it was not possible to compile the kernel with the parameters set, then you can write them in sysctl.conf:

kern.ipc.shmall=2097152
kern.ipc.shmmax=8589938688
kern.ipc.semmap=256

and run the junk with the default GENERIC kernel.

3. Tablespacing

Tablespacing is the ability of PostgreSQL to determine locations in the file system where files representing database objects will be stored. Simply put, if we scatter tables, indexes and logs on different disks, then reading the record / data will be faster than if all this were on the same disk.

Here we will need our raids. Let me remind you that we have four partitions: two RAID1 and two RAID0. On the first RAID1 we have installed OS and postgres. On the second RAID1 we will store the tables of our database. Suppose it is mounted as / disk1. On the first RAID0 we will store indexes. Let it be mounted in the file system as / disk2. We leave the logs to the second RAID0, we assume that it is mounted as / disk3.

The following steps must be taken:

1. create folders for tables, indexes and a log:
   

   #mkdir -p /disk1/postgresql/tables
#mkdir -p /disk2/postgresql/ind
#mkdir -p /disk3/postgresql/log

2. make postgres overer for these folders, and take away all the rights from the rest (I remind you that postgres is the user who starts up when installing PostgreSQL, if the installation is done in the standard way according to the official mana):
   

   #chown -R postgres /disk1/postgresql/tables /disk2/postgresql/ind /disk3/postgresql/log
#chmod -R go-rwx /disk1/postgresql/tables /disk2/postgresql/ind /disk3/postgresql/log

   
   
3. go into the psql client under postgres and create two tablespace:
   

   CREATE TABLESPACE space_table LOCATION '/ disk1 / postgresql / tables'; 
   CREATE TABLESPACE space_index LOCATION '/ disk2 / postgresql / ind';

   
   
4. if your database’s ober is not postgres, but, for example, myuser, then you need to give the user myuser the rights to created tablespace (you can also do this in the client):
   

   GRANT CREATE ON TABLESPACE space_table TO myuser;
   GRANT CREATE ON TABLESPACE space_index TO myuser;

   
   
5. now under myuser, you can change the tablespace for tables and indexes:
   

   ALTER TABLE mytable SET TABLESPACE space_table;
   ALTER INDEX mytable SET TABLESPACE space_index;

   
   
6. stop the postgres daemon, move the log folder and make a symbolic link to it:
   

   #/usr/local/bin/rc.d/postgres.sh stop
#mv /usr/local/pgsql/data/pg_xlog /disk3/postgresql/log
#cd /usr/local/pgsql/data
#ln -s /disk3/postgresql/log/pg_xlog

   
   Run postgres:
   

   #/usr/local/bin/rc.d/postgres.sh start

   
   If everything is done correctly, the daemon should start.
   

4. Partitioning

Partitioning is the logical partitioning of one large table into small physical pieces. This can significantly speed up query execution time if the table is really large.

I have a fairly typical situation: a script works in the crown, collecting statistics on a certain dimension. By the web interface, the user can see these statistics. About 10 million rows are inserted into the table per week. If everything is written in one table, then you will be cursed. All this will work terribly slowly.

Let's try to break this table into pieces, taking time as the criterion for breaking. In this case, when the user wants to look at the statistics, and you can see it only for a certain time period, the database server will have to wool not just the whole large table, but several small ones that fall within the selected time period.

Unfortunately, in PostgreSQL, partitioning is not implemented at the database level, so you will have to do it manually using the table inheritance property.

So, we have a table measure_data_master where we write our measurements. Suppose, as a time interval, one week suits. Go:

1. for the measure_data_master master table, DO NOT make any check integrity constraints and DO NOT create indexes
2. in the postgresql.conf config, edit the option:
   

   constraint_exclusion = on

   
   
3. create descendant tables of the form:
   

   CREATE TABLE measure_data_y2011m06d06 (CHECK (measure_time = DATE '2011-06-06' AND measure_time DATE '2011-06-13')
   ) INHERITS (measure_data_master);

   
   
4. create indexes for child tables:
   

   CREATE INDEX measure_data_y2011m06d06_key ON measure_data_y2011m06d06 (measure_time);

   
   
5. it is necessary that when inserting a new row, it is written to the desired descendant table. Let's create a trigger function for this:
   

   CREATE OR REPLACE FUNCTION measure_insert_trigger ()
   RETURNS TRIGGER AS $$ 
   BEGIN 
       IF (NEW.measure_time> = DATE '2011-06-06' AND
          NEW.measure_time <DATE '2011-06-13') THEN
            INSERT INTO measure_data_y2011m06d06 VALUES (NEW. *);
       ELSIF (NEW.measure_time> = DATE '2011-06-13' AND
             NEW.measure_time <DATE '2011-06-20') THEN 
               INSERT INTO measure_data_y2011m06d13 VALUES (NEW. *);
   .....................................
       ELSIF (NEW.measure_time> = DATE '2011-12- 19 'AND
             NEW.measure_time <DATE' 2011-12-26 ') THEN
               INSERT INTO measure_data_y2011m12d19 VALUES NEW. *);
       ELSE
           RAISE EXCEPTION 'Date out of range.Fix the measure_insert_trigger () function!';
       END IF;
       RETURN NULL;
   END
   $$
   LANGUAGE plpgsql;

   
   
6. Well, the trigger itself, which will call the function:
   

   CREATE TRIGGER insert_measure_trigger
       BEFORE INSERT ON measure_data_master
       FOR EACH ROW EXECUTE PROCEDURE measure_insert_trigger ();

   
   

Of course, writing such large queries is inconvenient. I wrote a php script that creates tables and all that they need for a whole year ahead.

That, perhaps, is all that I wanted to tell. If you share your experience from this area, I will be very grateful.