Open MPI logo

MTT Devel Mailing List Archives

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

Subject: [MTT devel] Database Notice
From: Josh Hursey (jjhursey_at_[hidden])
Date: 2008-01-09 16:07:01

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

Note for Josh: start_timestamp >= DATE '2008-01-01' and
start_timestamp < TIMESTAMP '2008-01-09 03:11'