4 ms·
I only read the title; but I'm expecting performance generally to be abysmal. I/O is quite poor on rPIs and that's what PostgreSQL needs more than anything. Mo
by dijit 8y ago
I only read the title; but I'm expecting performance generally to be abysmal.
I/O is quite poor on rPIs and that's what PostgreSQL needs more than anything. More than memory or CPU for sure.
The best thing you can ever do for your database is (in this order):
1) Have enough ram to fit your entire dataset (mostly due to joins)
2) Have the best raid card money can buy.
--
An SD card is shocking enough but on the rPI it's going over a USB bridge which takes CPU cycles too!
Still probably performs better than postgresql on WSL; since the I/O is the major bottleneck there too.
- Quarrelsome 8y ago> that's what PostgreSQL needs more than anything depends what the queries are. If they're all for exactly the same data then I/O is gonna be a one off. Even then dbs are usually relatively smart about minimising disk reads and trying to avoid them where possible.
- jpablo 8y agosd card access doesn't go trougth the USB controller in any of the rpi models. You are confusing it with the Ethernet port which is connected trougth USB.
- jsight 8y agoDoes it matter? In practice, it doesn't seem like the sd card is ever faster than the Pi USB bus.
- jng 8y agoI seem to recall the USB bus on the RPi3 is limited to 480Mb/s, the fastest SD cards in the market are around 150MB/s, and the SDcard port on the RPi3 is below 25Mb/s.
- jsight 8y agoYes, that makes sense. The SDCard port on the pi seems to be slower than the USB bus. I will say that I think your estimate for the USB bus on the pi is probably too high. In practice, I don't think it can do more than 250 or so.
- dsr_ 8y agoFor comparison's sake, I ran exactly the test described in the blog post on my house server, which is an AMD FX4130 with 32 GB RAM. The database was stored on SATA SSDs managed by ZFS. Lots of other things were going on at the same time. pgbench -c 10 -j 2 -T 600 -P 60 bench_test starting vacuum...end. progress: 60.0 s, 5373.1 tps, lat 1.860 ms stddev 0.709 progress: 120.0 s, 5277.3 tps, lat 1.894 ms stddev 1.383 progress: 180.0 s, 5323.4 tps, lat 1.878 ms stddev 0.733 progress: 240.0 s, 4606.6 tps, lat 2.170 ms stddev 1.508 progress: 300.0 s, 4122.0 tps, lat 2.425 ms stddev 1.517 progress: 360.0 s, 4062.0 tps, lat 2.461 ms stddev 1.532 progress: 420.0 s, 3957.3 tps, lat 2.526 ms stddev 1.598 progress: 480.0 s, 3939.6 tps, lat 2.537 ms stddev 1.599 progress: 540.0 s, 3906.5 tps, lat 2.559 ms stddev 1.627 progress: 600.0 s, 3808.6 tps, lat 2.624 ms stddev 1.747 transaction type: <builtin: TPC-B (sort of)> scaling factor: 10 query mode: simple number of clients: 10 number of threads: 2 duration: 600 s number of transactions actually processed: 2662601 latency average = 2.252 ms latency stddev = 1.435 ms tps = 4437.570722 (including connections establishing) tps = 4437.599312 (excluding connections establishing) So you get roughly an 11x performance improvement by running on a 7 year old desktop processor and rather cheap SSDs. On the other hand, if we say that the Pi setup cost $50 and we estimate the current value of my server at $500, performance/price is basically linear.
- bloopernova 8y agoDo you know roughly how much power your server draws? Like you said, the cost/performance may be linear but I wonder what happens when you add in power to that equation. Idle speculation, to be sure, but it would be fascinating to see what amount of solar panels plus wind power plus batteries would be needed to power each system.
- dsr_ 8y agoOh, loads of power. The CPU is spec'd for 125W and would be completely outclassed by a 65W Ryzen 5 1600 (150% cores, 300% threads, each thread probably 2x performance) and the rest of the system likely draws another 100W or so. It's irritatingly reliable and my house server needs really don't encompass a lot of CPU power, so replacing it hasn't been a priority.
- viraptor 8y ago> Have the best raid card money can buy. Does this matter anymore? Especially if your CPU is not the bottleneck and you're running internally cached/battery backup SSDs? I get the idea if you can gain something by offloading the parity calculations, but otherwise?