"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-de...@open-mpi.org > http://www.open-mpi.org/mailman/listinfo.cgi/mtt-devel