Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PG-Strom: SSD-To-GPU Direct SQL Execution

github.com

Post reply on HN
Print view View original

31–38 of 38 posts

Re: PG-Strom: SSD-To-GPU Direct SQL Execution

#31

Post by anarazel »

carbocation wrote: post #29
anarazel wrote: post #26

Earlier quoted context omitted.

Not trying to doubt your experience, just collecting datapoints for improvements. I'd appreciate if you could describe what your problems around write amplification are. Is it - heap bloat - index bloat - CPU overhead of index manipulations - Write-ahead-log overhead of updates - increased overhead / frequency of VACUUM - total amount of disk writes Thanks! Edit: formatting.

This is a great question. I have to say I haven't profiled Postgres to that level of granularity, so I am making an assumption about why my updates are slow. What I notice is relatively slow UPDATE performance, with high total disk writes and a write-ahead-log that seems to be large. The bloat on disk isn't an issue for me (or, rather, I have started using lower fillfactors to reduce the variability in DB size as the…

> I have to say I haven't profiled Postgres to that level of granularity, so I am making an assumption about why my updates are slow. What I notice is relatively slow UPDATE performance, with high total disk writes and a write-ahead-log that seems to be large.

Hm, that could be quite independent things. Could you check what your checkpoint_segments (When you say your write-ahead-log is large, do you mean the pg_xlog directory, or the amount of writes to it?

Another common reason for slowness can be foreign keys, if you've not indexed both sides of it. E.g. if there's a foreign key referencing the table you're updating, and the referencing columns aren't indexed, you'll end up with seqscans...

> Do you have a favored resource that you can direct me to that would help me identify the factors that you cited and ultimately allow me to make more nuanced criticisms (or to find a fixable error in my approach, which would of course be the ideal result)?

I'd strongly suggest enabling log_checkpoints - that'll log information about every checkpoint, allowing at least some diagnosis.

Another useful thing would be to run pg_xlogdump --stats, that'd give you information about what takes up space in your WAL.

I'd also look at EXPLAIN (ANALYZE, BUFFERS) one_of_your_updates; - that'll tell you a bit more about the timing, including trigger runtime.

Edit: brevity

Re: PG-Strom: SSD-To-GPU Direct SQL Execution

#32

Post by ris »

throwaway-1209 wrote: post #24

Where clause is the worst possible use case for GPU: GPUs don't tolerate branches or non-coalesced memory access all that well, and WHERE executes a ton of branches. Even so, things seem to be faster. Go figure.

I think it hugely depends on what your WHERE clause is.

Re: PG-Strom: SSD-To-GPU Direct SQL Execution

#33

Post by eriknstr »

walterbell wrote: post #6
blopeur wrote: post #2

Slides : https://www.slideshare.net/kaigai/20170602ossummitanintellig...

Could OpenCL (AMD, FPGA) be supported in the future?

A bit off topic on my side here but, I've been a aware of both OpenCL and FPGAs for quite a while but never thought of these being combined in any way.

Would anyone care to comment on their experience with regards to the following questions I have about this?

1. Are you using an FPGA implementation of OpenCL, or are you synthesizing a specific piece of OpenCL code into the FPGA?

2. How does performance compare between OpenCL running on a high-end GPU and a high-end FPGA?

3. How does performance compare between implementing someting in FPGA directly and implementing it in OpenCL and running that on an FPGA?

4. How does working with OpenCL for FPGA compare to working with the FPGA directly? Iow, what are the advantages and disadvantages you find of OpenCL as opposed to Verilog or VHDL (or other ways you program an FPGA)?

5. Based on the above, if you had X amount of dollars that could buy you either a number of GPUs or a (probably different) number of FPGAs, which would you choose? Consider also the electricity cost of running these as I've heard that FPGAs generally waste less electricity because they produce less heat.

Re: PG-Strom: SSD-To-GPU Direct SQL Execution

#34

Post by nova1313 »

X86BSD wrote: post #19

This seems pretty nice. Does anyone know if there is anything similar in the oracle camp? This seems like a big win over them if not.

I suspect it might cut into their Exadata appliance market if they did, but I'd love to hear anything from Oracle indicating they were adding something like this to a new version. If you try to go massively parallel for workloads they seem to push us towards Exadata. Parallel table/index scans may be your best bet at the moment on Oracle.

As far as I know you can't do anything like custom scan providers in Oracle. I've tried searching a few times for functionality like that and always came up empty. Even if that was an available feature, I'm not sure how you'd even begin costing it for the optimizer to take that into account, unless it was something you'd just enable via hint.

I have OpenCL code running from within Oracle stored procedures via Java. You can at least write your own logic from within the DB to utilize your GPUs. I've done things like push 2 tables into GPU memory and let the GPU do the joins once you manually load the columns to join on. Return the set to SQL and use an indexed column to return the data you want from the table. It's not something I'd consider running in production, but it was a neat exercise and you can get it to work.

Re: PG-Strom: SSD-To-GPU Direct SQL Execution

#35

Post by throwaway-1209 »

ris wrote: post #32
throwaway-1209 wrote: post #24

Where clause is the worst possible use case for GPU: GPUs don't tolerate branches or non-coalesced memory access all that well, and WHERE executes a ton of branches. Even so, things seem to be faster. Go figure.

I think it hugely depends on what your WHERE clause is.

I guess if your where clause is very selective, you could avoid the hit since the same branch is taken by all threads in a warp. But then there are better approaches such as BRIN indexes, RLE and dictionary encoding with which you don't even touch the data that doesn't match your clause.

Re: PG-Strom: SSD-To-GPU Direct SQL Execution

#36

Post by sixdimensional »

ccrush wrote: post #22
sixdimensional wrote: post #15

I still am hoping for affordable SSD to FPGA direct execution someday!

Direct execution? With GPUDirect from NVIDIA you could have an FPGA get a memcpy() from graphics RAM to its data has. Still, not sure how you plan on using this. I would hope that NVIDIA could give us programmable fabric on the GPU silicon, and it could somehow access each compute units instruction execution pipeline. Would be interesting indeed. Or just massive, fast FPGAs with included highly parallel compute IP bu…

Thinking something along the lines of, implementing/modelling SQL operators in FPGA hardware, using massive parallelization through the FPGA. That needs direct I/O access to very fast storage to be optimal, and probably still additional processing via a CPU or GPU.

Expensive custom data warehouse appliances like Netezza already do something like this, but I'm hoping for an implementation that is less expensive and more generally available, that can be exploited to accelerate any database's operations. Bonus points if it can scale up to a distributed model somehow.

Re: PG-Strom: SSD-To-GPU Direct SQL Execution

#37

Post by kaigai »

jeltz wrote: post #28
nikanj wrote: post #23

Earlier quoted context omitted.

This is the first time the concept has ever been shown publicly. You must be exceptionally brave to use it in production. Most CTOs are cynical, and always wait for the .1 version

Hm? I have not followed PGStorm but the project has been around for at least 5 years, probably more.

Early version of PG-Strom didn't plan SSD2GPU Direct SQL Execution, however, some users concerns didn't concern about CPU performance only but I/O also. So, I began to implement the feature to accelerate I/O also. Its development started at Dec-2015.

Re: PG-Strom: SSD-To-GPU Direct SQL Execution

#38

Post by kaigai »

nosefouratyou wrote: post #27
en4bz wrote: post #16

Note that GPUDirect is only available on nVidia Quadro and Tesla series GPUs.

Does that mean that this library[1] requires GPUDirect? That's sad. [1]: https://github.com/pg-strom/nvme-strom

Yes, Tesla or Quadro are required. In addition, Tesla P40/P100 are recommended due to size of i/o mappable region.
Post reply on HN