List:General Discussion« Previous MessageNext Message »
From:walter harms Date:June 19 2009 11:46am
Subject:Re: BULK DATA HANDLING 0.5TB
View as plain text  

steve@stripped schrieb:
> At 11:10 AM +0530 6/13/09, Krishna Chandra Prajapati wrote:
>> Hi guys,
>>
>> I'm working in a telecom company. I have table called deliverylog in which
>> 30 million records gets inserted per/day. The table has grown to 0.5TB I
>> have to keep 60days record in the table. So, 60days * 30 million = 1800
>> million records. The query is taking a lot of time to fetch the result.
>>
>> Please sugget me what storage engine must be used and how i can get the
>> things done. Is there any other alternative.
>>
>> Any response is highly appreciated.
>>
>> Thanks,
>> Krishna
> 
> 
> Can you provide us with more details about the current configuration? Eg,
> MySQL version, current database engine, and the result of an EXPLAIN on
> the problematic queries.
> 
> Just offhand, unless you need transactions/foreign keys/all the other
> niceties of InnoDB, I would suspect MyISAM would be the fastest engine,
> but hard to say for sure. There's a lot of room for performance
> optimization with all of the system variables as well (eg; increasing key
> buffers if you have adequate RAM). You can eke out more performance by
> putting indexes and tables on different drives on different channels.
> 
> Some references:
> 
> Book: High Performance MySQL, Second Edition
> http://oreilly.com/catalog/9780596101718/
> 
> Useful tips from the authors of the above book:
> http://www.mysqlperformanceblog.com/
> 
> And assuming you are using MySQL 5.0:
> 
> Optimization Overview
> http://dev.mysql.com/doc/refman/5.0/en/optimize-overview.html
> 
> Table OPTIMIZE command
> http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html
> 
> Using EXPLAIN
> http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
> http://dev.mysql.com/doc/refman/5.0/en/explain.html
> 
> MySQL system variables
> http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html
> 
> 	steve


and take a look at partions (available with >=5.1), btw do not forget to force
one-file-per-table
that make handling a lot more easy.

re,
 wh




Thread
BULK DATA HANDLING 0.5TBKrishna Chandra Prajapati13 Jun
  • Re: BULK DATA HANDLING 0.5TBMartijn Engler13 Jun
Re: BULK DATA HANDLING 0.5TBsteve14 Jun
  • Re: BULK DATA HANDLING 0.5TBwalter harms19 Jun