[HN Gopher] Scaling PostgresML to 1M Requests per Second
___________________________________________________________________
Scaling PostgresML to 1M Requests per Second
Author : bubblehack3r
Score : 166 points
Date : 2022-11-08 12:47 UTC (10 hours ago)
(HTM) web link (postgresml.org)
(TXT) w3m dump (postgresml.org)
| tucnak wrote:
| We have used PostgresML to a great success albeit in a very
| limited setting.
|
| The whole philosophy of pushing Postgres to the absolute limit of
| what is possible is very attractive; think what Timescale[1] does
| for time series, and what PostgresML does for machine learning by
| eliminating the need to perform any kind of ETL jobs to cut down
| on the latencies as to how late the data is consumed after it's
| initially produced, et cetera. The very fact that we can now
| train and deploy regressions/classifiers using nothing but a
| couple views and timely SQL calls is already a fairly attractive
| proposition, however I'm really looking towards further adoption,
| library support, case studies and stuff like that.
|
| For example, I've been considering how Timescale could be used
| together with PostgresML to provide a single way to treat data
| from the point of consumption. In fact, I've reached out to
| Timescale Cloud on this very matter, and was sad to learn that
| they can't support PostgresML as part of their Cloud offering due
| to some security considerations related to it being a Python
| extension. Self-hosted it is. At any rate, they have some time
| back introduced what they call continuous aggregates[2]; a
| materialised view on top of a hypertable that doesn't require
| explicit refreshing, and is in fact realtime-accurate due to some
| waterline logic; it performs a normal view-like query for the
| most recent bits while retaining the materialised component in
| the compressed partial form. (Normal time-based compression and
| retention policies apply like they would to any other hypertable
| which is how the continuous aggregate views are implemented under
| the hood.) The idea here is that you can potentially reduce
| hundreds of million data points to a set of particular continuous
| aggregates within respective time frames; downsampling of past
| data is something you get for free. This is where I think the
| value lies for PostgresML; using a continuous aggregates for
| continuous training and as a store of historical predictions that
| you would normally want to keep separate from the training set
| itself. This could prove a reliable way of feeding new data for
| re-training along with downsampled selection of past data, and
| comparing various predictions on similar data points over time to
| keep track of how it goes. The data is compressed away while
| readily available and as long as it doesn't have to change
| (historical predictions apply) the data points in the
| materialised component are never going to be materialised more
| than once.
|
| There are limitations[3] to this approach, of course, however
| they can be circumvented.
|
| If you're interested in this, you should check out some of the
| "hyperfunctions" they have to offer such as histogram[4] which is
| what I've had the pleasure of using previously to distil some of
| the time series data into a fixed-size feature vector, and
| naturally this would be a good place to start if you're ever
| going to seriously consider this as something that you yourself
| would want to use for similar purpose. To me this is a natural
| step forwards in the data lifecycle/ supply chain approach.
| First, you get rid of ETLs as such by adopting PostgresML, and
| next you specify the "contract" of how your data is going to be
| produced, reduced, distilled, modelled, sampled, and ultimately
| evaluated-- over prolonged periods of time and multiple
| iterations of the implementation.
|
| [1]
| https://docs.timescale.com/timescaledb/latest/overview/core-...
|
| [2] https://docs.timescale.com/timescaledb/latest/how-to-
| guides/...
|
| [3]
| https://docs.timescale.com/timescaledb/latest/overview/limit...
|
| [4]
| https://docs.timescale.com/api/latest/hyperfunctions/histogr...
| tomrod wrote:
| I haven't heard of PostgresML before today. How does it compare
| to Feast?
| pridkett wrote:
| Feast, for the most part, is a feature store that serves up
| features to machine learning models for inference or training.
| PostgresML is closer to Big Query ML. They provide the
| framework to building, deployment, and inference of models from
| within the database - using extensions for PostgreSQL. This
| allows analysts and other users to build models off primarily
| tabular data without needing to figure out the whole deployment
| mess.
| ehayes wrote:
| What is a good algorithm-to-purpose map for ML beginners? Looking
| for something like "Algo X is good for making predictions when
| your data looks like Y," etc.
| remram wrote:
| This maybe? https://scikit-
| learn.org/stable/tutorial/machine_learning_ma...
| bagels wrote:
| This is what I would have replied with too.
| sandGorgon wrote:
| xgboost. always xgboost. it will scale all the way from college
| kaggle problems (where it is the top performer almost always)
| to cloud scale.
|
| xgboost is one of the few frameworks supported by Sagemaker,
| etc
| xdfgh1112 wrote:
| Vowpal Wabbit is not the best anymore, but it is incredibly
| simple. You train it by piping text files in, then pipe your
| input into it for predictions.
| [deleted]
| alberth wrote:
| 4 load balancers with local caches + 5 replica databases.
|
| I don't want to discredit the achievement but the 1MM/sec seems
| less exciting when you learn they horizontally scaled the
| architecture.
|
| Especially when SQLite, in a client/server config (BedrockDB),
| achieved 4MM/sec from a _single_ server.
|
| https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
| gmhafiz wrote:
| For perspective, that single server is - 1TB of DDR4 RAM - 3TB
| of NVME SSD storage - 192 physical 2.7GHz cores (384 with
| hyperthreading
| [deleted]
| Foobar8568 wrote:
| Sql Server + R 6 years ago, scoring transactions at 1M/sec
| https://blog.revolutionanalytics.com/2016/09/fraud-detection...
| sidpatil wrote:
| I'm not sure the two are comparable--OP's article is about 1
| million _ML predictions_ per second, while your link is about 4
| million _queries_ per second.
| alberth wrote:
| Thanks for pointing out the difference.
|
| Genuine question: is a ML prediction slower than to fetch a
| row from a database and/or more difficult to scale?
| simonw wrote:
| From the article:
|
| > XGBoost cannot serve predictions concurrently because of
| internal data structure locks. This is common to many other
| machine learning algorithms as well, because making
| predictions can temporarily modify internal components of
| the model.
| ComputerGuru wrote:
| I wonder if finer-grained mutex wa or other concurrency
| protection types covering interior components would be an
| option.
| mrfox321 wrote:
| It's slower than a row lookup.
|
| Specifically, this model is an ensemble of decision trees.
|
| This involves
|
| 1. Row lookup
|
| 2. N (tree depth) inequality checks on fields in the row
| for M trees
|
| 3. Weighted sum over M trees
| tucnak wrote:
| In this case, they are talking about xgboost inference,
| which can verge on the hungry side of things, but this is
| no match for deep learning, although word embeddings and
| transformers are supported[1] by PostgresML regardless.
| GPUs are supported[2] for Transformers as well as xgboost,
| so this approach is also kind of future-proof in the sense
| that you're not going to ever be limited by your compute,
| as well as the size of your replica set. But now, yes, to
| answer your original question-- yes, for the most part
| typical regression-type task inference is marginally slower
| than fetching and involves crossing the process boundary,
| can't be easily parallelised, and so on. Hopefully, this is
| something that could be dealt with on PostgresML side, as
| Postgres exposes some neat parallelism primitives AFAIK.
| COMBINEFUNC is how you make your aggregates parallel and
| with SERIALFUNC potentially multinode-friendly, although
| here I'm not sure as I haven't maintained custom aggregates
| over a high-availability Postgres cluster... At any rate,
| this is a clear area of future research; now that there's
| inference, how to best parallelise it in the model of
| Postgres.
|
| [1] https://postgresml.org/user_guides/transformers/setup/
|
| [2] https://postgresml.org/user_guides/setup/gpu_support/
| ComputerGuru wrote:
| Can you train xgboost on a GPU and use that model to make
| predications on the CPU?
| derefr wrote:
| Speaking in generalities rather than specifically about
| Postgres/PostgresML, this is how you should think about
| speccing hardware to solve these kinds of problems:
|
| A DBMS that does mostly point-query reads -- fetching
| single "cold" rows from a large storage, with high
| concurrency -- is an IO-bottlenecked workload. Given a
| sufficiently mainframe-y "DMA from disk straight through to
| the NIC" architecture (which modern microcomputer servers
| are rapidly approaching), the CPU is just "guiding" that
| transfer -- acting as a PCIe switchboard operator -- rather
| than actually performing it. The only things you need to
| worry about are JBOD IO-queue depth and (multi-aggregated)
| NIC total ring-buffer size. This means that you can go very
| far with "vertical" scaling for these workloads, in the
| sense that you can shove more disks and NICs into the same
| machine and it'll be able to serve more RPS, without really
| increasing CPU load. For IO-bottlenecked workloads, you
| only start to suffer when your PCIe bandwidth is exhausted.
| This is why systems built for "SAN" or "DB" or
| "hyperconverged" use-cases like this always use dual-socket
| lower-cores-per-socket CPUs (lately: dual Epyc 7532s)
| rather than single higher-core-count CPUs -- as each socket
| gets its own PCIe channels, so the lower-powered dual-
| socket CPUs actually maximize PCIe bandwidth per machine.
|
| Meanwhile, any kind of ML prediction is going to be a CPU-
| (or _maybe_ GPU-) bottlenecked workload. If you have 64 CPU
| cores in a machine, then you can only be doing 64 ML
| predictions at a time on that machine. You might be able to
| resolve any individual prediction "quickly"; but unlike an
| IO-bottlenecked workload, that "quickly" doesn't involve
| the worker thread ever blocking + yielding the CPU to other
| workloads while waiting on anything; it's solid 100%-pinned
| CPU-cycles on that core, all the way through the
| prediction. There is no advantage in oversubscribing the
| machine with workloads; running KN predictions across N
| cores makes each query take (slightly more than) K times as
| long to run. You may as well just make each machine the
| smallest size it can reasonably be to run the model (as
| each machine then gets its own IO memory and IO "breathing
| room", and you avoid the price-premium of using latest-gen
| parts), stamp out horizontal replicas of it, and load-
| balance between them.
|
| This is why so many phones and other low-power devices are
| shipping with "AI/ML accelerators": putting ASICs in, tuned
| to run specific ML-model architectures faster, can vastly
| lower the power budget for the device as a whole, as
| running (concurrent) models on the device would otherwise
| have to ramp up the CPU to its highest frequency and pin it
| at 100% to do one of these predictions.
| montanalow wrote:
| RAM and the Postgres shared buffers, as well OS page
| cache can also be important factors when there is an
| active working subset, e.g. the active sessions on a
| website might be reused hundreds of times per session,
| with only relatively small portion of them active at any
| time.
|
| Shared RAM across all cores can be a reason to use fewer
| larger machines rather than more smaller ones in such a
| case. Postgres does give you options either way though.
| bagels wrote:
| Right, if we're allowed horizontal scaling, 1M instances,
| 1request/s would do the trick.
___________________________________________________________________
(page generated 2022-11-08 23:02 UTC)