Re: Partition Help
Date: Mon, 02 Oct 2006 13:22:37 -0400 To: mysql@lists.mysql.com From: Michael Gargiullo [EMAIL PROTECTED] Subject: RE: Partition Help Message-id: [EMAIL PROTECTED] snip/ Daily partitions are created then sub partitioned across 6 data disks and 6 index disks. We attempted to build a new table per hour, and merge them after 3 hours. We killed the processes after 2 hours. 1 hour of data is approx 18GB. The server only has 12GB of RAM. I wish we could partition down to TO_HOUR instead of TO_DAY There's some discussion of this issue on the Partitioning Forum - http://forums.mysql.com/list.php?106 - and you're more likely to get topic-specific attention there from users and MySQL developers working with partitioning than you are here on the General list. Also, have you checked out the recent articles on partitioning available from our DevZone? These include: http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning_with_dates.html http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitions.html - both of which discuss date-based partitioning techniques that you might find useful. cheers jon. -- Jon Stephens - [EMAIL PROTECTED] Technical Writer - MySQL Documentation Team ___ Brisbane, Australia (GMT +10.00) _x_ Bangkok, Thailand (GMT +07.00) ___ Office: +61 (7) 3209 1394 _x_ Office: +66 0 2740 3691 5 ext. #201 Mobile: +61 402 635 784 MySQL AB: www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Partition Help
snip Thanks for the advice. We've got 12GB of RAM, I'll increase the key_buffer_size. Unfortunately I can't turn off indexes, then index after. At these rates, I'd never catch up. I don't agree. It takes longer to build the index than to load the data if you have indexes active when loading the data. But if you disable the index, or not have any indexes on the table during the Load Data, then re-enable the index later, MySQL will build the index at least 10x faster if you have a large key_buffer_size because it does it all in memory. I've had Load Data go from 24 hours to 40 minutes just by adding more memory to key_buffer_size and disabling the index and re-enabling it later. I'd recommend using at least 6000M for key_buffer_size as a start. You want to try and get as much of the index in memory as possible. I had hoped I could use partitions like in Oracle. 1 partition every hour (or 3). I don't think the merge tables will work however. We currently only keep 15 days of data and that fills the array. If a merge table uses disk space, it won't work for us. A Merge Table can be built in just ms. It is a logical join between the tables and does *not* occupy more disk space. Think of it as a view that joins tables of similar schema together vertically so it looks like 1 large table. Mike Ah, very cool. Thanks again. Loading 500,000 rows with 200M rows in the DB with Indexes on takes 22 Minutes. Loading 500,000 rows with 200M rows in the DB with indexes turned off and then build indexes after the load took over 75 minutes. This would probably work if we only inserted 40-80 million rows a day total, or had a few hours where data was not being inserted. Daily partitions are created then sub partitioned across 6 data disks and 6 index disks. We attempted to build a new table per hour, and merge them after 3 hours. We killed the processes after 2 hours. 1 hour of data is approx 18GB. The server only has 12GB of RAM. I wish we could partition down to TO_HOUR instead of TO_DAY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Partition Help
I'm working on a project in which we'd like to convert from Oracle to
MySQL. We need to partition our data for speed concerns. Currently in
Oracle I create 8, 3 hour partitions for each day (Currently running
450M -750M rec inserts/day). I was looking for matching functionality in
MySQL, but it seams daily partitions are as close as I'm going to come.
We're running 5.1.10 and I'm having a bit of trouble creating partitions
in both new tables and altering old tables. Below is one example of
what I've tried.
Can anyone shed some light on this subject?
-Mike
create table t1 (c1 int default NULL, c2 varchar(30) default NULL, c3
datetime default NULL) engine=myisam PARTITION BY RANGE(to_days(c3))
PARTITION p0 VALUES LESS THAN (to_days('2006-09-24'))(
SUBPARTITION s0a
DATA DIRECTORY = '/FW_data1'
INDEX DIRECTORY = '/FW_indx1'
),
PARTITION p1 VALUES LESS THAN (to_days('2006-09-26'))(
SUBPARTITION s1a
DATA DIRECTORY = '/FW_data2'
INDEX DIRECTORY = '/FW_indx2'
)
PARTITION p2 VALUES LESS THAN (to_days('2006-09-28'))(
SUBPARTITION s2a
DATA DIRECTORY = '/FW_data3'
INDEX DIRECTORY = '/FW_indx3'
)
);
Re: Partition Help
At 02:03 PM 9/26/2006, you wrote:
I'm working on a project in which we'd like to convert from Oracle to
MySQL. We need to partition our data for speed concerns. Currently in
Oracle I create 8, 3 hour partitions for each day (Currently running
450M -750M rec inserts/day). I was looking for matching functionality in
MySQL, but it seams daily partitions are as close as I'm going to come.
We're running 5.1.10 and I'm having a bit of trouble creating partitions
in both new tables and altering old tables. Below is one example of
what I've tried.
Can anyone shed some light on this subject?
-Mike
Mike,
How is this table being updated?
a) From one source like a batch job?
b) Or from hundreds of users concurrently?
If a), then why not just create 1 table per day (or 3 tables per day) and
when you want to reference (the entire day or) a week, just create a Merge
Table?
http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html
If b), then you need to use InnoDb tables because that has row locks
compared to MyISAM's table locks.
Mike
create table t1 (c1 int default NULL, c2 varchar(30) default NULL, c3
datetime default NULL) engine=myisam PARTITION BY RANGE(to_days(c3))
PARTITION p0 VALUES LESS THAN (to_days('2006-09-24'))(
SUBPARTITION s0a
DATA DIRECTORY = '/FW_data1'
INDEX DIRECTORY = '/FW_indx1'
),
PARTITION p1 VALUES LESS THAN (to_days('2006-09-26'))(
SUBPARTITION s1a
DATA DIRECTORY = '/FW_data2'
INDEX DIRECTORY = '/FW_indx2'
)
PARTITION p2 VALUES LESS THAN (to_days('2006-09-28'))(
SUBPARTITION s2a
DATA DIRECTORY = '/FW_data3'
INDEX DIRECTORY = '/FW_indx3'
)
);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Partition Help
-Original Message-
From: mos [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 26, 2006 3:40 PM
To: mysql@lists.mysql.com
Subject: Re: Partition Help
At 02:03 PM 9/26/2006, you wrote:
I'm working on a project in which we'd like to convert from Oracle to
MySQL. We need to partition our data for speed concerns. Currently in
Oracle I create 8, 3 hour partitions for each day (Currently running
450M -750M rec inserts/day). I was looking for matching functionality
in
MySQL, but it seams daily partitions are as close as I'm going to come.
We're running 5.1.10 and I'm having a bit of trouble creating
partitions
in both new tables and altering old tables. Below is one example of
what I've tried.
Can anyone shed some light on this subject?
-Mike
Mike,
How is this table being updated?
a) From one source like a batch job?
b) Or from hundreds of users concurrently?
If a), then why not just create 1 table per day (or 3 tables per day)
and
when you want to reference (the entire day or) a week, just create a
Merge
Table?
http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html
If b), then you need to use InnoDb tables because that has row locks
compared to MyISAM's table locks.
Mike
We're using the Load infile function to load the data generated by
another process. We do not do updates, but occasionally need to either
walk the table or run a query against it. On Oracle, we currently need 3
hour partitions to keep the 5 indexes timely.
This system handles 450-750 Million inserted rows per day with 5 fields
being indexed. This number will be closer to 2 Billion records / day by
Spring 2007 we've been told.
For example, I diverted the full flow of data to MySQL for 15 minutes
and inserted 9 Million records with a back up of loader files. I need
to speed this up. Unfortunately, table structure and indexes are static
and cannot be changed.
-Mike
create table t1 (c1 int default NULL, c2 varchar(30) default NULL, c3
datetime default NULL) engine=myisam PARTITION BY RANGE(to_days(c3))
PARTITION p0 VALUES LESS THAN (to_days('2006-09-24'))(
SUBPARTITION s0a
DATA DIRECTORY = '/FW_data1'
INDEX DIRECTORY = '/FW_indx1'
),
PARTITION p1 VALUES LESS THAN (to_days('2006-09-26'))(
SUBPARTITION s1a
DATA DIRECTORY = '/FW_data2'
INDEX DIRECTORY = '/FW_indx2'
)
PARTITION p2 VALUES LESS THAN (to_days('2006-09-28'))(
SUBPARTITION s2a
DATA DIRECTORY = '/FW_data3'
INDEX DIRECTORY = '/FW_indx3'
)
);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Partition Help
At 02:53 PM 9/26/2006, Michael Gargiullo wrote:
-Original Message-
From: mos [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 26, 2006 3:40 PM
To: mysql@lists.mysql.com
Subject: Re: Partition Help
At 02:03 PM 9/26/2006, you wrote:
I'm working on a project in which we'd like to convert from Oracle to
MySQL. We need to partition our data for speed concerns. Currently in
Oracle I create 8, 3 hour partitions for each day (Currently running
450M -750M rec inserts/day). I was looking for matching functionality
in
MySQL, but it seams daily partitions are as close as I'm going to come.
We're running 5.1.10 and I'm having a bit of trouble creating
partitions
in both new tables and altering old tables. Below is one example of
what I've tried.
Can anyone shed some light on this subject?
-Mike
Mike,
How is this table being updated?
a) From one source like a batch job?
b) Or from hundreds of users concurrently?
If a), then why not just create 1 table per day (or 3 tables per day)
and
when you want to reference (the entire day or) a week, just create a
Merge
Table?
http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html
If b), then you need to use InnoDb tables because that has row locks
compared to MyISAM's table locks.
Mike
We're using the Load infile function to load the data generated by
another process. We do not do updates, but occasionally need to either
walk the table or run a query against it. On Oracle, we currently need 3
hour partitions to keep the 5 indexes timely.
This system handles 450-750 Million inserted rows per day with 5 fields
being indexed. This number will be closer to 2 Billion records / day by
Spring 2007 we've been told.
For example, I diverted the full flow of data to MySQL for 15 minutes
and inserted 9 Million records with a back up of loader files. I need
to speed this up. Unfortunately, table structure and indexes are static
and cannot be changed.
-Mike
Mike,
I've done a lot of Load Data with large tables and as you no doubt
discovered, as the number of rows in the table increases, the insert speed
decreases. This is due to the extra effort involved in maintaining the
index as the rows are being loaded. As the index grows in size, it takes
longer to maintain the index. This is true of any database. MyISAM tables
are going to be faster than InnoDb in this case.
You can speed it up by:
1) Add as much memory as possible in the machine because building the index
will be much faster if it has lots of ram.
2) Modify your My.Cnf file so key_buffer_size=1500M or more. (Assuming you
have 3gb or more installed) This allocates memory for building the index.
3) If the table is empty before you add any rows to it, Load Data will run
much faster because it will build the index *after* all rows have been
loaded. But if you have as few as 1 row in the table before running Load
Data, the index will have to be maintained as the rows are inserted and
this slows down the Load Data considerably.
4) Try throwing an exclusive lock on the table before loading the data. I'm
not sure but this might help.
5) If your table already has rows in it before running Load Data, and the
table has indexes defined, it is much faster if your disable the indexes to
the table before running Load Data, and then enable the index after Load
Data has completed. See Alter Table Enable/Disable Indexes for more info.
6) If you are using Alter Table to add indexes after the table has data,
make sure you are adding all indexes in one Alter Table statement because
MySQL will copy the table each time the Alter Table is run.
If you are going to be adding 2 billion rows per day, you might want to try
1 table per hour which will reduce the number of rows to 100 million
which may be more manageable (assuming 24 hour day). You can then create a
merge table on the 24 rows so you can traverse them. You can of course
create a merge table just for the morning hours, afternoon hours, evening
hours etc.. Name each table like: 20060925_1400 for 4PM on 9/25/2006. Of
course you may also want to summarize this data into a table so you don't
need all of this raw data lying around.
Hope this helps.
Mike
create table t1 (c1 int default NULL, c2 varchar(30) default NULL, c3
datetime default NULL) engine=myisam PARTITION BY RANGE(to_days(c3))
PARTITION p0 VALUES LESS THAN (to_days('2006-09-24'))(
SUBPARTITION s0a
DATA DIRECTORY = '/FW_data1'
INDEX DIRECTORY = '/FW_indx1'
),
PARTITION p1 VALUES LESS THAN (to_days('2006-09-26'))(
SUBPARTITION s1a
DATA DIRECTORY = '/FW_data2'
INDEX DIRECTORY = '/FW_indx2'
)
PARTITION p2 VALUES LESS THAN (to_days('2006-09-28'))(
SUBPARTITION s2a
DATA DIRECTORY = '/FW_data3'
INDEX DIRECTORY = '/FW_indx3'
)
);
--
MySQL General Mailing List
For list archives: http
RE: Partition Help
Mike We're using the Load infile function to load the data generated by another process. We do not do updates, but occasionally need to either walk the table or run a query against it. On Oracle, we currently need 3 hour partitions to keep the 5 indexes timely. This system handles 450-750 Million inserted rows per day with 5 fields being indexed. This number will be closer to 2 Billion records / day by Spring 2007 we've been told. For example, I diverted the full flow of data to MySQL for 15 minutes and inserted 9 Million records with a back up of loader files. I need to speed this up. Unfortunately, table structure and indexes are static and cannot be changed. -Mike Mike, I've done a lot of Load Data with large tables and as you no doubt discovered, as the number of rows in the table increases, the insert speed decreases. This is due to the extra effort involved in maintaining the index as the rows are being loaded. As the index grows in size, it takes longer to maintain the index. This is true of any database. MyISAM tables are going to be faster than InnoDb in this case. You can speed it up by: 1) Add as much memory as possible in the machine because building the index will be much faster if it has lots of ram. 2) Modify your My.Cnf file so key_buffer_size=1500M or more. (Assuming you have 3gb or more installed) This allocates memory for building the index. 3) If the table is empty before you add any rows to it, Load Data will run much faster because it will build the index *after* all rows have been loaded. But if you have as few as 1 row in the table before running Load Data, the index will have to be maintained as the rows are inserted and this slows down the Load Data considerably. 4) Try throwing an exclusive lock on the table before loading the data. I'm not sure but this might help. 5) If your table already has rows in it before running Load Data, and the table has indexes defined, it is much faster if your disable the indexes to the table before running Load Data, and then enable the index after Load Data has completed. See Alter Table Enable/Disable Indexes for more info. 6) If you are using Alter Table to add indexes after the table has data, make sure you are adding all indexes in one Alter Table statement because MySQL will copy the table each time the Alter Table is run. If you are going to be adding 2 billion rows per day, you might want to try 1 table per hour which will reduce the number of rows to 100 million which may be more manageable (assuming 24 hour day). You can then create a merge table on the 24 rows so you can traverse them. You can of course create a merge table just for the morning hours, afternoon hours, evening hours etc.. Name each table like: 20060925_1400 for 4PM on 9/25/2006. Of course you may also want to summarize this data into a table so you don't need all of this raw data lying around. Hope this helps. Mike Thanks for the advice. We've got 12GB of RAM, I'll increase the key_buffer_size. Unfortunately I can't turn off indexes, then index after. At these rates, I'd never catch up. I had hoped I could use partitions like in Oracle. 1 partition every hour (or 3). I don't think the merge tables will work however. We currently only keep 15 days of data and that fills the array. If a merge table uses disk space, it won't work for us. I'll check out the key buffer size though. Thanks. -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Partition Help
At 03:37 PM 9/26/2006, you wrote: Mike We're using the Load infile function to load the data generated by another process. We do not do updates, but occasionally need to either walk the table or run a query against it. On Oracle, we currently need 3 hour partitions to keep the 5 indexes timely. This system handles 450-750 Million inserted rows per day with 5 fields being indexed. This number will be closer to 2 Billion records / day by Spring 2007 we've been told. For example, I diverted the full flow of data to MySQL for 15 minutes and inserted 9 Million records with a back up of loader files. I need to speed this up. Unfortunately, table structure and indexes are static and cannot be changed. -Mike Mike, I've done a lot of Load Data with large tables and as you no doubt discovered, as the number of rows in the table increases, the insert speed decreases. This is due to the extra effort involved in maintaining the index as the rows are being loaded. As the index grows in size, it takes longer to maintain the index. This is true of any database. MyISAM tables are going to be faster than InnoDb in this case. You can speed it up by: 1) Add as much memory as possible in the machine because building the index will be much faster if it has lots of ram. 2) Modify your My.Cnf file so key_buffer_size=1500M or more. (Assuming you have 3gb or more installed) This allocates memory for building the index. 3) If the table is empty before you add any rows to it, Load Data will run much faster because it will build the index *after* all rows have been loaded. But if you have as few as 1 row in the table before running Load Data, the index will have to be maintained as the rows are inserted and this slows down the Load Data considerably. 4) Try throwing an exclusive lock on the table before loading the data. I'm not sure but this might help. 5) If your table already has rows in it before running Load Data, and the table has indexes defined, it is much faster if your disable the indexes to the table before running Load Data, and then enable the index after Load Data has completed. See Alter Table Enable/Disable Indexes for more info. 6) If you are using Alter Table to add indexes after the table has data, make sure you are adding all indexes in one Alter Table statement because MySQL will copy the table each time the Alter Table is run. If you are going to be adding 2 billion rows per day, you might want to try 1 table per hour which will reduce the number of rows to 100 million which may be more manageable (assuming 24 hour day). You can then create a merge table on the 24 rows so you can traverse them. You can of course create a merge table just for the morning hours, afternoon hours, evening hours etc.. Name each table like: 20060925_1400 for 4PM on 9/25/2006. Of course you may also want to summarize this data into a table so you don't need all of this raw data lying around. Hope this helps. Mike Thanks for the advice. We've got 12GB of RAM, I'll increase the key_buffer_size. Unfortunately I can't turn off indexes, then index after. At these rates, I'd never catch up. I don't agree. It takes longer to build the index than to load the data if you have indexes active when loading the data. But if you disable the index, or not have any indexes on the table during the Load Data, then re-enable the index later, MySQL will build the index at least 10x faster if you have a large key_buffer_size because it does it all in memory. I've had Load Data go from 24 hours to 40 minutes just by adding more memory to key_buffer_size and disabling the index and re-enabling it later. I'd recommend using at least 6000M for key_buffer_size as a start. You want to try and get as much of the index in memory as possible. I had hoped I could use partitions like in Oracle. 1 partition every hour (or 3). I don't think the merge tables will work however. We currently only keep 15 days of data and that fills the array. If a merge table uses disk space, it won't work for us. A Merge Table can be built in just ms. It is a logical join between the tables and does *not* occupy more disk space. Think of it as a view that joins tables of similar schema together vertically so it looks like 1 large table. Mike I'll check out the key buffer size though. Thanks. -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Partition Help
-Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 5:27 PM To: mysql@lists.mysql.com Subject: RE: Partition Help At 03:37 PM 9/26/2006, you wrote: Mike We're using the Load infile function to load the data generated by another process. We do not do updates, but occasionally need to either walk the table or run a query against it. On Oracle, we currently need 3 hour partitions to keep the 5 indexes timely. This system handles 450-750 Million inserted rows per day with 5 fields being indexed. This number will be closer to 2 Billion records / day by Spring 2007 we've been told. For example, I diverted the full flow of data to MySQL for 15 minutes and inserted 9 Million records with a back up of loader files. I need to speed this up. Unfortunately, table structure and indexes are static and cannot be changed. -Mike Mike, I've done a lot of Load Data with large tables and as you no doubt discovered, as the number of rows in the table increases, the insert speed decreases. This is due to the extra effort involved in maintaining the index as the rows are being loaded. As the index grows in size, it takes longer to maintain the index. This is true of any database. MyISAM tables are going to be faster than InnoDb in this case. You can speed it up by: 1) Add as much memory as possible in the machine because building the index will be much faster if it has lots of ram. 2) Modify your My.Cnf file so key_buffer_size=1500M or more. (Assuming you have 3gb or more installed) This allocates memory for building the index. 3) If the table is empty before you add any rows to it, Load Data will run much faster because it will build the index *after* all rows have been loaded. But if you have as few as 1 row in the table before running Load Data, the index will have to be maintained as the rows are inserted and this slows down the Load Data considerably. 4) Try throwing an exclusive lock on the table before loading the data. I'm not sure but this might help. 5) If your table already has rows in it before running Load Data, and the table has indexes defined, it is much faster if your disable the indexes to the table before running Load Data, and then enable the index after Load Data has completed. See Alter Table Enable/Disable Indexes for more info. 6) If you are using Alter Table to add indexes after the table has data, make sure you are adding all indexes in one Alter Table statement because MySQL will copy the table each time the Alter Table is run. If you are going to be adding 2 billion rows per day, you might want to try 1 table per hour which will reduce the number of rows to 100 million which may be more manageable (assuming 24 hour day). You can then create a merge table on the 24 rows so you can traverse them. You can of course create a merge table just for the morning hours, afternoon hours, evening hours etc.. Name each table like: 20060925_1400 for 4PM on 9/25/2006. Of course you may also want to summarize this data into a table so you don't need all of this raw data lying around. Hope this helps. Mike Thanks for the advice. We've got 12GB of RAM, I'll increase the key_buffer_size. Unfortunately I can't turn off indexes, then index after. At these rates, I'd never catch up. I don't agree. It takes longer to build the index than to load the data if you have indexes active when loading the data. But if you disable the index, or not have any indexes on the table during the Load Data, then re-enable the index later, MySQL will build the index at least 10x faster if you have a large key_buffer_size because it does it all in memory. I've had Load Data go from 24 hours to 40 minutes just by adding more memory to key_buffer_size and disabling the index and re-enabling it later. I'd recommend using at least 6000M for key_buffer_size as a start. You want to try and get as much of the index in memory as possible. I had hoped I could use partitions like in Oracle. 1 partition every hour (or 3). I don't think the merge tables will work however. We currently only keep 15 days of data and that fills the array. If a merge table uses disk space, it won't work for us. A Merge Table can be built in just ms. It is a logical join between the tables and does *not* occupy more disk space. Think of it as a view that joins tables of similar schema together vertically so it looks like 1 large table. Mike Ah, very cool. Thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
