Open MPI logo

MTT Devel Mailing List Archives

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

Subject: Re: [MTT devel] Database Notice
From: Josh Hursey (jjhursey_at_[hidden])
Date: 2008-01-30 11:04:54


The data misplacement is now fixed.

Over the past 2 days I have been moving the data that was accidentally
put in the master partition tables into their correct date partition
tables. I still need to clean up test_run, but I am pushing that to
this weekend since it will slow down the database for a few hours to
do so.

-- Josh

On Jan 9, 2008, at 4:27 PM, Ethan Mallove wrote:

> "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?
>
> -Ethan
>
>
>>
>>
>> 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]
>> http://www.open-mpi.org/mailman/listinfo.cgi/mtt-devel
> _______________________________________________
> mtt-devel mailing list
> mtt-devel_at_[hidden]
> http://www.open-mpi.org/mailman/listinfo.cgi/mtt-devel