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: mysql@lists.mysql.com
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]
