>
>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]
