RE: Adding and Removing tables from MERGE tables dynamically
Thanks for the information. I want to make sure that I understand: Do you run ALTER TABLE command on a live database(table) that is doing inserts; Or, do you stop accepting Remote connections, flush the tables, run the ALTER TABLE command, start accepting connections? Thank you, Raymond -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 10:24 To: Jacob, Raymond A Jr; mysql@lists.mysql.com Subject: Re: Adding and Removing tables from MERGE tables dynamically I've got a similar setup, total records across about 8 tables hoovers around 200 million. To change a merge table just issue an alter table with a new union. ALTER TABLE mergetable UNION=(table1, table2, table3,...); - Original Message - From: "Jacob, Raymond A Jr" <[EMAIL PROTECTED]> To: Sent: Tuesday, September 05, 2006 5:29 PM Subject: Adding and Removing tables from MERGE tables dynamically Problem: I use two applications one called snort, the client that inserts data into eleven(11) tables. The other application BASE joins the tables into one table. When the tables become 4GB in size, deleting records for one month becomes unbearably slow(20-30days). The search(Select ) is slow too but that is a problem with the BASE application. I thought that using MERGE tables would allow an administrator to create a monthly table, using the original table names, composed of daily or weekly tables, by appending the date of creation to the table i.e. data_table1_-MM-DD and join_table_-MM-DD. From the documentation: creating the table with INSERT_METHOD = FIRST results in INSERTs being done to first table in the MERGE UNION statement. I will assume that the first table is the latest table. So one of the first tables should look like: CREATE TABLE original_table { ... } TYPE = MERGE UNION = (data_table1_2006-09-12 ,data_table1_2006-09-05) Using cron and depending on the interval chosen daily, weekly, bi-weekly, monthly, quarterly, or yearly at the start of a new interval, a new table would be created with current date in the -MM-DD format. For example: if the start of new interval begins a week from today on 2006-09-12. At 12:00am on 2006-09-12, a script would create new tables that would look like: CREATE TABLE data_table1_2006-09-12 { ... } One the MERGE TABLES should look like: CREATE TABLE original_table { ... } TYPE = MERGE UNION = (data_table1_2006-09-12 ,data_table1_2006-09-05) On every Tuesday(in this case) from now on, new tables are created ending with date in the format -MM-DD and merged into the original table. So that by 2006-09-30, one of the MERGE tables should look something like ... } TYPE = MERGE UNION = (data_table1_2006-09-26, data_table1_2006-09-19, data_table1_2006-09-12, data_table1_2006-09-05) On 2006-10-05 at 00:00hrs the newest table data_table1_2006-10-05 should be created and merged into the original_table. The oldest table in this case data_table1_2006-09-05 should be removed from one of the MERGE tables in this case original_table. The resulting merge table should look something like ... } TYPE = MERGE UNION = (data_table1_2006-10-05,data_table1_2006-09-26, data_table1_2006-09-19, data_table1_2006-09-12) Question: How does one add data_table1_2006-09-12 to original_table dynamically? Question: How does one remove data_table1_2006-09-05 from the original_table dynamically? Question: In other words, can tables be added and removed dynamically to/from a MERGE TABLE? Benefit: I hope is to archive individual tables. When I need to review old data I will use a copy of the BASE application, then Merge the tables that I am interested in, in order to search smaller tables without changing the BASE application. Question: Is this possible. Do these question make sense? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding and Removing tables from MERGE tables dynamically
I've got a similar setup, total records across about 8 tables hoovers around 200 million. To change a merge table just issue an alter table with a new union. ALTER TABLE mergetable UNION=(table1, table2, table3,...); - Original Message - From: "Jacob, Raymond A Jr" <[EMAIL PROTECTED]> To: Sent: Tuesday, September 05, 2006 5:29 PM Subject: Adding and Removing tables from MERGE tables dynamically Problem: I use two applications one called snort, the client that inserts data into eleven(11) tables. The other application BASE joins the tables into one table. When the tables become 4GB in size, deleting records for one month becomes unbearably slow(20-30days). The search(Select ) is slow too but that is a problem with the BASE application. I thought that using MERGE tables would allow an administrator to create a monthly table, using the original table names, composed of daily or weekly tables, by appending the date of creation to the table i.e. data_table1_-MM-DD and join_table_-MM-DD. From the documentation: creating the table with INSERT_METHOD = FIRST results in INSERTs being done to first table in the MERGE UNION statement. I will assume that the first table is the latest table. So one of the first tables should look like: CREATE TABLE original_table { ... } TYPE = MERGE UNION = (data_table1_2006-09-12 ,data_table1_2006-09-05) Using cron and depending on the interval chosen daily, weekly, bi-weekly, monthly, quarterly, or yearly at the start of a new interval, a new table would be created with current date in the -MM-DD format. For example: if the start of new interval begins a week from today on 2006-09-12. At 12:00am on 2006-09-12, a script would create new tables that would look like: CREATE TABLE data_table1_2006-09-12 { ... } One the MERGE TABLES should look like: CREATE TABLE original_table { ... } TYPE = MERGE UNION = (data_table1_2006-09-12 ,data_table1_2006-09-05) On every Tuesday(in this case) from now on, new tables are created ending with date in the format -MM-DD and merged into the original table. So that by 2006-09-30, one of the MERGE tables should look something like ... } TYPE = MERGE UNION = (data_table1_2006-09-26, data_table1_2006-09-19, data_table1_2006-09-12, data_table1_2006-09-05) On 2006-10-05 at 00:00hrs the newest table data_table1_2006-10-05 should be created and merged into the original_table. The oldest table in this case data_table1_2006-09-05 should be removed from one of the MERGE tables in this case original_table. The resulting merge table should look something like ... } TYPE = MERGE UNION = (data_table1_2006-10-05,data_table1_2006-09-26, data_table1_2006-09-19, data_table1_2006-09-12) Question: How does one add data_table1_2006-09-12 to original_table dynamically? Question: How does one remove data_table1_2006-09-05 from the original_table dynamically? Question: In other words, can tables be added and removed dynamically to/from a MERGE TABLE? Benefit: I hope is to archive individual tables. When I need to review old data I will use a copy of the BASE application, then Merge the tables that I am interested in, in order to search smaller tables without changing the BASE application. Question: Is this possible. Do these question make sense? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding and Removing tables from MERGE tables dynamically
Problem: I use two applications one called snort, the client that inserts data into eleven(11) tables. The other application BASE joins the tables into one table. When the tables become 4GB in size, deleting records for one month becomes unbearably slow(20-30days). The search(Select ) is slow too but that is a problem with the BASE application. I thought that using MERGE tables would allow an administrator to create a monthly table, using the original table names, composed of daily or weekly tables, by appending the date of creation to the table i.e. data_table1_-MM-DD and join_table_-MM-DD. From the documentation: creating the table with INSERT_METHOD = FIRST results in INSERTs being done to first table in the MERGE UNION statement. I will assume that the first table is the latest table. So one of the first tables should look like: CREATE TABLE original_table { ... } TYPE = MERGE UNION = (data_table1_2006-09-12 ,data_table1_2006-09-05) Using cron and depending on the interval chosen daily, weekly, bi-weekly, monthly, quarterly, or yearly at the start of a new interval, a new table would be created with current date in the -MM-DD format. For example: if the start of new interval begins a week from today on 2006-09-12. At 12:00am on 2006-09-12, a script would create new tables that would look like: CREATE TABLE data_table1_2006-09-12 { ... } One the MERGE TABLES should look like: CREATE TABLE original_table { ... } TYPE = MERGE UNION = (data_table1_2006-09-12 ,data_table1_2006-09-05) On every Tuesday(in this case) from now on, new tables are created ending with date in the format -MM-DD and merged into the original table. So that by 2006-09-30, one of the MERGE tables should look something like ... } TYPE = MERGE UNION = (data_table1_2006-09-26, data_table1_2006-09-19, data_table1_2006-09-12, data_table1_2006-09-05) On 2006-10-05 at 00:00hrs the newest table data_table1_2006-10-05 should be created and merged into the original_table. The oldest table in this case data_table1_2006-09-05 should be removed from one of the MERGE tables in this case original_table. The resulting merge table should look something like ... } TYPE = MERGE UNION = (data_table1_2006-10-05,data_table1_2006-09-26, data_table1_2006-09-19, data_table1_2006-09-12) Question: How does one add data_table1_2006-09-12 to original_table dynamically? Question: How does one remove data_table1_2006-09-05 from the original_table dynamically? Question: In other words, can tables be added and removed dynamically to/from a MERGE TABLE? Benefit: I hope is to archive individual tables. When I need to review old data I will use a copy of the BASE application, then Merge the tables that I am interested in, in order to search smaller tables without changing the BASE application. Question: Is this possible. Do these question make sense?