Re: [MTT devel] Database Notice
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-de...@open-mpi.org http://www.open-mpi.org/mailman/listinfo.cgi/mtt-devel ___ mtt-devel mailing list mtt-de...@open-mpi.org http://www.open-mpi.org/mailman/listinfo.cgi/mtt-devel
Re: [MTT devel] Database Notice
"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
[MTT devel] Database Notice
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'
