Обсуждение: optimizing daily data storage in Pg
I have been struggling with this for a while now, have even gone down a few paths but struck out, so I turn now to the community for ideas. First, the problem: Store six daily variables for ~ 25 years for cells in a grid. * Number of vars = 6 * Number of cells ~ 13 million * Number of days ~ 9125 (25 * 365) Optimize the store for two different kinds of queries: Query one: Retrieve the value of a single var for all or a portion of the cells for a single day. This is analogous to an image where every pixel is the value of a single var. Query two: Retrieve values for all the days or a duration of days for a single var for a single cell. This is like grabbing a column out of a table in which each row holds all the vars for a single day. So, I set about designing the db. The "grid" is in a table with 13 million rows CREATE TABLE cells ( cell_id INTEGER, other_data .. ) WITH ( OIDS=FALSE ) A single table *where every row is one day's values for one cell* looks like so CREATE TABLE d ( yr SMALLINT, yday SMALLINT, a SMALLINT, b SMALLINT, d SMALLINT, e SMALLINT, f SMALLINT, g SMALLINT, cell_id INTEGER ) WITH ( OIDS=FALSE ) The data would look like so yr yday a b c d e f g cell_id ---------------------------------------------------- 1980 1 x x x x x x x 1 .. 1980 365 x x x x x x x 1 ... 1981 1 x x x x x x x 1 .. 1981 365 x x x x x x x 1 ... ... 2005 1 x x x x x x x 1 .. 2005 365 x x x x x x x 1 ...... 1980 1 x x x x x x x 2 .. 1980 365 x x x x x x x 2 ... I could now (theoretically) conduct my queries like so: Query 1a: Retrieve the value of a single var for all the cells for a single day. This is analogous to an image where every pixel is the value of a single var. SELECT <var> FROM d WHERE yr = ? AND yday = ?; I assuming I would need an index on yr and yday, or perhaps even a compound index on (yr, yday). Query 1b: Retrieve the value of a single var for a portion of the cells for a single day. This is analogous to an image where every pixel is the value of a single var. SELECT <var> FROM d WHERE yr = ? AND yday = ? AND cell_id IN (?,?,?...); I assuming I would need an index on yr and yday, or perhaps even a compound index on (yr, yday) AND an index on cell_id. Query 2: Retrieve values for all the days or a duration of days for a single var for a single cell. This is like grabbing a column out of a table in which each row holds all the vars for a single day. SELECT <var> FROM d WHERE cell_id = ?; SELECT <var> FROM d WHERE cell_id IN (?,?,?...); Once again, an index on cell_id would assist in the above. The problem: The above table would have 13 M * 9125 rows ~ 118 billion rows. Huge indexes, slow queries, etc. In fact, major issues loading the data in the first place. Since I am loading data in batches, I drop the indexes (takes time), COPY data into the table (takes time), build the indexes (takes time), experiment with improving the performance (takes time), fail, rinse, lather, repeat. I actually tried the above with a subset of data (around 100 M rows) and experienced all of the above. I don't remember the query times, but they were awful. So, I partitioned the table into years like so CREATE TABLE d_<yyyy> ( CHECK ( yr = <yyyy> ) ) INHERITS (d) Hmmm... still no satisfaction. I ended up with 1 master table + 25 inherited tables. Each of the year tables now had ~ 4.75 billion rows (13 M * 365), and the queries were still very slow. So, I partitioned it all by years and days like so CREATE TABLE d_<yyyy>_<yday> ( CHECK ( yr = <yyyy> AND yday = <yday> ) ) INHERITS (d) Each table now has 13 million rows, and is reasonably fast (although still not satisfactorily fast), but now I have 9K tables. That has its own problems. I can't query the master table anymore as Pg tries to lock all the tables and runs out of memory. Additionally, I can't anymore conduct query two above. I could do something like SELECT a FROM d_1980_1 WHERE cell_id = 1 UNION SELECT a FROM d_1980_2 WHERE cell_id = 1 UNION SELECT a FROM d_1980_3 WHERE cell_id = 1 UNION SELECT a FROM d_1980_4 WHERE cell_id = 1 UNION ... But the above is hardly optimal. Any suggestions, ideas, brainstorms would be appreciated. Perhaps Pg, or even a RDBMS, is not the right tool for this problem, in which case, suggestion for alternatives would be welcome as well. Right now I am testing this on a dual Xeon dual core 3 GHz Xserve with 12 GB RAM. The PGDATA directory is located on an attached RAID that is configured as RAID5. Reasonable time for a query would be under 500 ms, although ultimately I would love to have the query be done under 250 ms, perhaps with RAID10, and a machine with more RAM. I have access to a machine with dual Xeon quad core 3 GHz Xserve with 32 GB RAM, and an internal RAID, but before I try to move the data, I want to actually conclusively prove that Pg is the best solution (or not). Perhaps flat files are better, perhaps blobs are better, or perhaps Pg's array column type. Any ideas/suggestions welcome. Oh, one more thing. Once the data are loaded and everything is working, the data are readonly. They are historical, so they don't need to be changed. -- Puneet Kishor
On 7/22/2010 9:41 AM, P Kishor wrote: > I have been struggling with this for a while now, have even gone down > a few paths but struck out, so I turn now to the community for ideas. > First, the problem: Store six daily variables for ~ 25 years for cells > in a grid. > > * Number of vars = 6 > * Number of cells ~ 13 million > * Number of days ~ 9125 (25 * 365) > > Optimize the store for two different kinds of queries: > > Query one: Retrieve the value of a single var for all or a portion of > the cells for a single day. This is analogous to an image where every > pixel is the value of a single var. > SELECT<var> FROM d WHERE yr = ? AND yday = ?; > SELECT<var> FROM d WHERE yr = ? AND yday = ? AND cell_id IN (?,?,?...); > > Query two: Retrieve values for all the days or a duration of days for > a single var for a single cell. This is like grabbing a column out of > a table in which each row holds all the vars for a single day. > SELECT<var> FROM d WHERE cell_id = ?; > SELECT<var> FROM d WHERE cell_id IN (?,?,?...); First, I must admit to not reading your entire email. Second, Query 1 should be fast, regardless of how you layout the tables. Third, Query 2 will return 13M rows? I dont think it matters how you layout the tables, returning 13M rows is always going to be slow. -Andy
On Thu, Jul 22, 2010 at 4:56 PM, Andy Colson <andy@squeakycode.net> wrote: > On 7/22/2010 9:41 AM, P Kishor wrote: >> >> I have been struggling with this for a while now, have even gone down >> a few paths but struck out, so I turn now to the community for ideas. >> First, the problem: Store six daily variables for ~ 25 years for cells >> in a grid. >> >> * Number of vars = 6 >> * Number of cells ~ 13 million >> * Number of days ~ 9125 (25 * 365) >> >> Optimize the store for two different kinds of queries: >> >> Query one: Retrieve the value of a single var for all or a portion of >> the cells for a single day. This is analogous to an image where every >> pixel is the value of a single var. > >> SELECT<var> FROM d WHERE yr = ? AND yday = ?; >> SELECT<var> FROM d WHERE yr = ? AND yday = ? AND cell_id IN >> (?,?,?...); > > >> >> Query two: Retrieve values for all the days or a duration of days for >> a single var for a single cell. This is like grabbing a column out of >> a table in which each row holds all the vars for a single day. > >> SELECT<var> FROM d WHERE cell_id = ?; >> SELECT<var> FROM d WHERE cell_id IN (?,?,?...); > > > > First, I must admit to not reading your entire email. I am not sure how to respond to your feedback give that you haven't read the entire email. Nevertheless, thanks for writing... > > Second, Query 1 should be fast, regardless of how you layout the tables. It is not fast. Right now I have data for about 250,000 cells loaded. That comes to circa 92 million rows per year. Performance is pretty sucky. > > Third, Query 2 will return 13M rows? I dont think it matters how you layout > the tables, returning 13M rows is always going to be slow. > Yes, I understand that. In reality I will never get 13 M rows. For display purposes, I will probably get around 10,000 rows to 50,000 rows. When more rows are needed, it will be to feed a model, so that can be offline (without an impatient human being waiting on the other end). Right now, my main problem is that I have either too many rows (~4 B rows) in a manageable number of tables (25 tables) or manageable number of rows (~13 M rows) in too many tables (~9000 tables). > > -Andy > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science =======================================================================
Does ordering the data in the table by day, cell_id help at all? How big ARE the indexes we're talking about? If the total size of all the indexes you need to do your queries run into the hundreds of gigabytes, going from 12 to 32 Gigs of RAM may be like flapping your arms our the window of your car in terms of effectiveness. If the indexes that you need at one time add up to something in the 32 to 256Gig range then adding enough memory to hold most or all of them at once would help and is doable, but the price goes up fast when you get to the > 64Gig range. If everything is read only, then pg may or may not be the best fit. It sounds more like what you're doing is batch processing, or at least batch processing friendly. If you could roll up your data, either in the db or beforehand while preparing it, that might be a big win. Is your machine IO bound when running these queries? What does "iostat -xd 1" say about throughput and % utilization? I'm assuming you're running an OS with sysstat available. If you're on something else, then you'll need to research how to see your IO workload on that OS. htop is a nice graphical way of seeing your wait states as well, with the red bars representing IO wait on a machine. If your machine is IO bound, and you've gotten enough ram to hold the working set of your indexes, then you'll need more hard drives under good controllers to make it faster. The bad news is that RAID controllers and lots of hard drives can be expensive, the good news is that reporting servers (which is sounds like what this is) use a lot of sequential access, and throwing more drives at the problem gives big gains, usually. As far as partitioning goes, I think you either need to use fewer partitions, or just use individual tables without using the parent table to access them. It's a well known problem with partitioning that past a few hundred or so child tables things get slow pretty fast. Having 200 to 500 tables, maybe even 1,000 is workable, but past that no, not really. If you're IO bound, then you'll likely need more CPU horsepower.
On 7/23/2010 12:39 AM, P Kishor wrote: > On Thu, Jul 22, 2010 at 4:56 PM, Andy Colson<andy@squeakycode.net> wrote: >> On 7/22/2010 9:41 AM, P Kishor wrote: >>> >>> I have been struggling with this for a while now, have even gone down >>> a few paths but struck out, so I turn now to the community for ideas. >>> First, the problem: Store six daily variables for ~ 25 years for cells >>> in a grid. >>> >>> * Number of vars = 6 >>> * Number of cells ~ 13 million >>> * Number of days ~ 9125 (25 * 365) >>> >>> Optimize the store for two different kinds of queries: >>> >>> Query one: Retrieve the value of a single var for all or a portion of >>> the cells for a single day. This is analogous to an image where every >>> pixel is the value of a single var. >> >>> SELECT<var> FROM d WHERE yr = ? AND yday = ?; >>> SELECT<var> FROM d WHERE yr = ? AND yday = ? AND cell_id IN >>> (?,?,?...); >> >> >>> >>> Query two: Retrieve values for all the days or a duration of days for >>> a single var for a single cell. This is like grabbing a column out of >>> a table in which each row holds all the vars for a single day. >> >>> SELECT<var> FROM d WHERE cell_id = ?; >>> SELECT<var> FROM d WHERE cell_id IN (?,?,?...); >> >> >> >> First, I must admit to not reading your entire email. > > I am not sure how to respond to your feedback give that you haven't > read the entire email. Nevertheless, thanks for writing... > Heh, sorry, my point was, you put a lot of information into your email, and I was going to only use one bit of it: row counts. >> >> Second, Query 1 should be fast, regardless of how you layout the tables. > > It is not fast. Right now I have data for about 250,000 cells loaded. > That comes to circa 92 million rows per year. Performance is pretty > sucky. > > This query should return one record, correct? This should be very fast, PG should be able to find the record in the index within 5 seeks, and then find the data in one seek. Can you post 'explain analyze' for this query. (Or, it could be the case, I totally misunderstood your data) >> >> Third, Query 2 will return 13M rows? I dont think it matters how you layout >> the tables, returning 13M rows is always going to be slow. >> > > Yes, I understand that. In reality I will never get 13 M rows. For > display purposes, I will probably get around 10,000 rows to 50,000 > rows. When more rows are needed, it will be to feed a model, so that > can be offline (without an impatient human being waiting on the other > end). > > Right now, my main problem is that I have either too many rows (~4 B > rows) in a manageable number of tables (25 tables) or manageable > number of rows (~13 M rows) in too many tables (~9000 tables). > Searching by just cell_id is not going to be very selectable, and with large result-sets I can see this one being slow. As Scott talked about in his response, this one will come down to hardware. Have you dd tested your hardware? -Andy