June 6, 2013 at 13:30We speed up the database. Bcache
For work, we use the postgresql + postgis database with data for the entire planet from osm.org . On the disk, it takes about 350 Gb and does not work fast, and it is stored on a regular 2Tb 7200rpm hard drive, without RAID-a. Because the database load is gradually growing, 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 the reviews of friends, I decided to stop at bcache. I’ll tell you more about him.
To try bcache, you need to build a patched kernel 3.9. The project is actively developing and its sources are not even distributed as tar.bz2. If you want to download it, 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 bcache keys left off.
If this kernel is the latest version - it will boot automatically, if you have something newer installed - the kernel with bcache can be selected when loading in grub2 in the Advanced boot options menu.
To work with bcache, disks must first be formatted. (do not forget to back up data)
Next, the disks must be registered in the kernel.
After registration, the drive appears as / dev / bcache0, and the cache as / sys / fs / bcache /. Format the disk to the file system of your choice and mount it.
In order for the disk to know which cache to work with, the cache must be connected. While the cache set, this is one disk, but in the future they plan to add work with several cache devices in the cache set. One cache set can be added to several disks, which is very convenient for systems with several slow disks. To connect the cache, we need its UDID.
That's it. You have a disk in your system with a writetrough cache connected to it. If the main load on the database is samples, without data modification, 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 several times faster. It’s hard for me to cite some figures, because Before switching to bcache, I did not make reference requests. But I can show an approximate ratio of speeds. So a spherical request with an empty cache is completed in 171 seconds. Repeatedly it is performed in 3.2 seconds. In order to exclude the influence of database caches on processing speed, I restarted the database, after which the request was completed 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 hardware.
A nice addition to bcache is its statistics, which it carefully collects. You can look at the statistics of the cache, how many miss, how many hits, how many percent are hits, how much cache is full, how much information was written to the cache, etc. Here is what he is showing me at the moment:
Thanks for attention. Report typos and inaccuracies in PM, I will try to answer questions in the comments.
ps The documentation for bcache is very detailed and well written, I recommend reading it.
- buy another one of the same hard drive and combine 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, it is put simply.
- bcache. Judging by the reviews, the fastest. The main disadvantage is that you need to format the disks before using. Officially added to the 3.10 kernel, distributed as a patched 3.9 kernel.
- EnhanceIO. In the reviews I came across the mention of it as the slowest, but easiest to use.
After weighing the pros and cons, as well as asking the reviews of friends, I decided to stop at bcache. I’ll tell you more about him.
Build the bcache kernel
To try bcache, you need to build a patched kernel 3.9. The project is actively developing and its sources are not even distributed as tar.bz2. If you want to download it, 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 bcache keys 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 latest version - it will boot automatically, if you have something newer installed - the kernel with bcache can be selected when loading in grub2 in the Advanced boot options menu.
Configure bcache
To work with bcache, disks must first be formatted. (do not forget to back up 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 drive appears as / dev / bcache0, and the cache as / sys / fs / bcache /
mkfs.ext4 /dev/bcache0
mount /dev/bcache0 /mnt
In order for the disk to know which cache to work with, the cache must be connected. While the cache set, this is one disk, but in the future they plan to add work with several cache devices in the cache set. One cache set can be added to several disks, which is very convenient for systems with several slow disks. To connect the cache, we need its UDID.
echo > /sys/block/bcache0/bcache/attach
That's it. You have a disk in your system with a writetrough cache connected to it. If the main load on the database is samples, without data modification, 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
Impressions from bcache
After filling the cache, the database began to work several times faster. It’s hard for me to cite some figures, because Before switching to bcache, I did not make reference requests. But I can show an approximate ratio of speeds. So a spherical request with an empty cache is completed in 171 seconds. Repeatedly it is performed in 3.2 seconds. In order to exclude the influence of database caches on processing speed, I restarted the database, after which the request was completed 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 hardware.
A nice addition to bcache is its statistics, which it carefully collects. You can look at the statistics of the cache, how many miss, how many hits, how many percent are hits, how much cache is full, how much information was written to the cache, etc. Here is what he is showing me 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 PM, I will try to answer questions in the comments.
ps The documentation for bcache is very detailed and well written, I recommend reading it.
