Hacker News Reader

Hacker News

Comment by dijit | Hacker News Reader

Full thread

dijit

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.

View on HN

For 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.

Do 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.

Oh, 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.

> irritatingly reliable

This made me laugh. I have an AMD Sempron 3850 cpu+gpu sitting in a small case under the TV that has been chugging along for 5 years now. The somewhat recent addition of a cheap SSD has increased its usability even more. As much as I'd like to replace it with something more up to date, I can't justify it while it's still working fine.

It's funny to think of what is "good enough" for a lot of needs.

>irritatingly reliable

I feel the exact same way about my old Camry.

> 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.

sd 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.

Does it matter? In practice, it doesn't seem like the sd card is ever faster than the Pi USB bus.

I 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.

Yes, 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.

> 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?

Reply on news.ycombinator.com