Accelerate the database. Bcache
For work we use the postgresql + postgis database with data for the entire planet from osm.org . It takes about 350 Gb on the disk and does not work fast, and it is stored on a regular 2Tb 7200rpm hard drive, without a RAID-a. Because the load on the database is gradually increasing, it was decided to speed up the disk subsystem, while spending a minimum of money. There were not many options:
After weighing the pros and cons, as well as asking reviews of friends, I decided to stay at bcache. About him and tell you more.
To try bcache, you need to build the 3.9 patched kernel. The project is actively developing and its sources are not even distributed as tar.bz2. Want to download - download the repository (700MB somewhere).
After this step, you will need to choose which options to use for the added kernel parameters. I left everything by default, only bcache compiled as a kernel module. Debug keys bcache left off.
If this kernel is the most recent version - it will load automatically, if you have something new installed - the kernel with bcache can be selected when booting into grub2 in the Advanced boot options menu.
To work with bcache drives must be preformatted. (do not forget to backup data)
Next, the disks must be registered in the kernel.
After registration, the disk will appear as / dev / bcache0, and the cache as / sys / fs / bcache / <UDID>. We format the disk into a file system of choice and mount it.
What would the disk know which cache to work with it, the cache must be connected. While the cache set is one disk, but in the future they plan to add work with several cache devices in the cache set. You can add one cache set to several disks, which is very convenient for systems with several slow disks. To connect the cache, we need its UDID.
Well that's all. You have a disk in your system, with a writetrough cache connected to it. If the main load on the database is sampling, without modifying the data, such a caching mode will be enough and the SSD will live longer. Writeback can be enabled with the command:
After filling the cache, the database began to work much faster. It’s hard for me to give some numbers, because before switching to bcache, I did not make reference queries. But I can show the approximate speed ratio. So a spherical query with an empty cache is executed in 171 seconds. It is executed again in 3.2 seconds. To eliminate the impact of database caches on processing speed, I restarted the database, after which the query was executed in 4 seconds. With such an increase in speed, processing the data of the entire planet does not seem an impossible task on our relatively weak gland.
A nice addition to bcache is his statistics, which he carefully collects. You can look at the statistics of the cache, how many miss, how many hits, how many percent falls on hits, how many caches are full, how much information has been written to the cache, etc. This is what he is showing at the moment:
Thanks for attention. Report typos and inaccuracies in lichku, I will try to answer questions in the comments.
ps The bcache documentation is very detailed and well written, I recommend reading it.
- buy one more hard drive and merge them into raid-0.
- Buy a small SSD and organize a quick cache on it:
- dm-cache It was added to the kernel 3.9, put simply.
- bcache Judging by the reviews the fastest. The main disadvantage is that the disks should be formatted before use. Officially added to kernel 3.10, distributed as patched kernel 3.9.
- EnhanceIO. In the reviews, I came across a mention of it as the slowest but easy to use.
After weighing the pros and cons, as well as asking reviews of friends, I decided to stay at bcache. About him and tell you more.
Bcache build
To try bcache, you need to build the 3.9 patched kernel. The project is actively developing and its sources are not even distributed as tar.bz2. Want to download - download the repository (700MB somewhere).
# sudo apt-get install kernel-package fakeroot build-essential ncurses-dev bc # bcache git clone http://evilpiepirate.org/git/linux-bcache.git cd linux-bcache # bcache cat /boot/config-`uname -r`>.config make oldconfig After this step, you will need to choose which options to use for the added kernel parameters. I left everything by default, only bcache compiled as a kernel module. Debug keys bcache left off.
make-kpkg clean # -j . sudo time fakeroot make-kpkg -j8 --initrd kernel_image kernel_headers # 24 . . sudo dpkg -i ../linux-image-3.9.0+_3.9.0+-10.00.Custom_amd64.deb ../linux-headers-3.9.0+_3.9.0+-10.00.Custom_amd64.deb # bcache-tools cd .. git clone http://evilpiepirate.org/git/bcache-tools.git cd bcache-tools make sudo make install If this kernel is the most recent version - it will load automatically, if you have something new installed - the kernel with bcache can be selected when booting into grub2 in the Advanced boot options menu.
Configure bcache
To work with bcache drives must be preformatted. (do not forget to backup data)
sudo -i umount /dev/sdb4 # HDD make-bcache -B /dev/sdb4 # SSD make-bcache - /dev/sda Next, the disks must be registered in the kernel.
echo /dev/sdb4 > /sys/fs/bcache/register echo /dev/sda > /sys/fs/bcache/register After registration, the disk will appear as / dev / bcache0, and the cache as / sys / fs / bcache / <UDID>. We format the disk into a file system of choice and mount it.
mkfs.ext4 /dev/bcache0 mount /dev/bcache0 /mnt What would the disk know which cache to work with it, the cache must be connected. While the cache set is one disk, but in the future they plan to add work with several cache devices in the cache set. You can add one cache set to several disks, which is very convenient for systems with several slow disks. To connect the cache, we need its UDID.
echo <UUID> > /sys/block/bcache0/bcache/attach Well that's all. You have a disk in your system, with a writetrough cache connected to it. If the main load on the database is sampling, without modifying the data, such a caching mode will be enough and the SSD will live longer. Writeback can be enabled with the command:
echo writeback > /sys/block/bcache0/cache_mode Bcache impressions
After filling the cache, the database began to work much faster. It’s hard for me to give some numbers, because before switching to bcache, I did not make reference queries. But I can show the approximate speed ratio. So a spherical query with an empty cache is executed in 171 seconds. It is executed again in 3.2 seconds. To eliminate the impact of database caches on processing speed, I restarted the database, after which the query was executed in 4 seconds. With such an increase in speed, processing the data of the entire planet does not seem an impossible task on our relatively weak gland.
A nice addition to bcache is his statistics, which he carefully collects. You can look at the statistics of the cache, how many miss, how many hits, how many percent falls on hits, how many caches are full, how much information has been written to the cache, etc. This is what he is showing at the moment:
$ cd /sys/block/bcache0/bcache/stats_hour $ cat cache_hit_ratio 90 $ cat cache_hits 836233 $ cat cache_misses 91086 $ cd cache/cache0 $ cat btree_written 92.3M $ cat metadata_written 481M $ cat written 41.3G Thanks for attention. Report typos and inaccuracies in lichku, I will try to answer questions in the comments.
ps The bcache documentation is very detailed and well written, I recommend reading it.
')
Source: https://habr.com/ru/post/182372/
All Articles