Open MPI logo

MTT Devel Mailing List Archives

  |   Home   |   Support   |   FAQ   |   all MTT Devel mailing list

Subject: Re: [MTT devel] Database Notice
From: Ethan Mallove (ethan.mallove_at_[hidden])
Date: 2008-01-09 16:27:53

"pg_dump -s" seems to show that we are set until 2009?

(Just put a note in my calendar about this for late December 2008 :-))

On Wed, Jan/09/2008 04:07:01PM, Josh Hursey wrote:
> I was showing MTT to someone today and noticed that it was performing a
> bit slower than it should. After taking a look under the hood I
> discovered that we were missing the 2008 partition tables. :(
> I'll keep you posted on this. Let me know if you have any problems in
> the mean time.
> -- Josh
> Short Version:
> --------------
> We did not lose any data. The accumulated data was just put in a non-
> optimal table, thus making queries slow. I added the 2008 partition
> tables, and things should be back to normal. All new data will be
> added correctly to the partition tables per usual.
> There will be a lingering slowdown if anyone queries for results from
> Jan 1, 2008 00:00 to ~Jan 9, 2008 15:15. I'm trying to fix this at the
> moment (see Long Version).
> Long Version:
> -------------
> I totally forgot to upload the new tables to the database. Sorry guys :(
> All the data accumulated from the first of the year was put in the
> main table for mpi_install/test_build/test_run instead of in their
> respective date partitioned tables. This means that when someone
> searches for something in the date range Jan 1, 2008 00:00 to ~Jan 9,
> 2008 15:15 the database is going to do a bit of thrashing since the
> optimizer is going to try to look to the partition table first then
> failing that it will look at *all* the tables including the root
> table. Luckily the optimizer seems to start with the root table so it
> is not as bad as it could be, but still slower than it should be. :/
> Currently this effects:
> mpi_install: 434 tuples
> test_build : 2,174 tuples
> test_run : 1,077,117 tuples
> I think I can fix this but I want to experiment a bit before
> manipulating real data. I think I can create a transaction that does
> something like:
> Start Transaction
> Drop check constraints on test_run
> Save effected tuples to disk
> Drop effected tuples from test_run
> Add back effected tuples to test_run (inserting into partition tables)
> Add back check constraints
> .. do the same for test_build, and mpi_install
> End Transaction

So you need to copy the data out of the root table into *_y2008_m01_wk1?
Something like the below does not work because it might collide with
someone trying to INSERT into this week's partition table?

  SELECT * FROM mpi_install INTO mpi_install_y2008_m01_wk1;

Could another option would be to turn off submit.php for
a few minutes and do the above SELECT INTO?


> Note for Josh: start_timestamp >= DATE '2008-01-01' and
> start_timestamp < TIMESTAMP '2008-01-09 03:11'
> _______________________________________________
> mtt-devel mailing list
> mtt-devel_at_[hidden]