Use ramdisk to speed up PostgreSQL access

What is ramdisk? How can we use it to improve the response speed of the database server?

Ramdisk is usedHard DiskSpaceMemory. No matter when you use ramdisk, you actually use the memory instead of the hard disk. There are both advantages and disadvantages in this regard. The most basic and biggest advantage is that you are using the memory, and everything you do will be faster, because the hard disk speed is slower than the memory speed. The biggest drawback is that if you change the databaseServerWhen the machine is restarted, all the changes will be lost.

For database server PostgreSQL, If you transfer all databases to the memory, it will greatly increase its speed.

Create a ramdisk/test

Point one of the default ramdisks to the directory/test.

Mkdir-P/test

Mkfs-T ext2/dev/ram0

Mount/dev/ram0/test

If this step fails, it may be because the compiled kernel does not support ramdisk. You should always set config_blk_dev_ram in the and configuration options to available.

The above steps can only provide you with a 4 m ramdisk. The above gives you a ramdisk with available space of just under 4 MB. See the article ramdisk article to see how to change it to 50 MB.

Create a ramdisk for PostgreSQL

Theoretically, you should have two excellent database servers. One can be modified, and the other can be copied on ramdisk. To achieve this, you should use the "pg_dump" or "pg_dumpall" command.

Note: we assume that you have already set in the file that your ramdisk is larger than the size of your database. If you want to obtain the approximate size, run the "CD/var/lib/pgsql; Du" command ".

In any case, make your PostgreSQL installed in "/var/lib/pgsql" and put them in the memory. Do this:

### Stop the current PostgreSQL Server

/Etc/rc. d/init. d/Postgres stop

### Rename the current directory

MV/var/lib/pgsql/var/lib/pgsql_main

#### Create a directory to have our ramdisk on

Mkdir-P/var/lib/pgsql_memory

#### Change the ownership of the new directory to Alibaba s or whatever

#### The actual owner is.

Chown Postgres/var/lib/pgsql_memory

#### Make an alias or link to the original name,/var/lib/pgsql

Ln-S/var/lib/pgsql_memory/var/lib/pgsql

#### Format the ramdisk

Mkfs-T ext2/dev/ram0

#### Mount the ramdisk to the PostgreSQL directory

Mount/dev/ram0/var/lib/pgsql_memory

#### Copy everything from the main directory into the ramdisk

Tar-C/var/lib/pgsql_main-CP. | tar-C/var/lib/pgsql_memory-XP

### Start the current PostgreSQL Server

/Etc/rc. d/init. d/Postgres start

About speed comments on the speed

I have tested the speed performance of postgresql on ramdisk and not on ramdisk respectively. In some cases, PostgreSQL on ramdisk is faster than 50%. Of course, sometimes this is not the case.

I have noticed that when the system sets a buffer for data, the system performance is not improved much, only about 10% to 20%. When a large amount of new data is input to the database at the same time, the system performance is significantly improved.

The biggest advantage of using ramdisk is that you can force your database to stay in the memory to avoid using hard disks. If you do not use ramdisk, your database may be swapped out of memory. When it is used, it is transferred to the memory from the hard disk.

Note

For LinuxOperating SystemWhen a file is read, it will be stored in the memory until the memory space isProgramRequired. This is the cache. Maybe this is a solution. If this is not the case, I will hear the hard disk sound when my machine has access. When ramdisk is used, the database is stored in the memory instead of being stored everywhere. If the database server is often used, I will put it in the memory.

The advantage of using ramdisk in a database is that it can achieve high speed, simple and bright. This is a great idea for read-only databases.

The disadvantage is that you must know how to manage the changes, and it is best to be sure that you have backed up the changes.