List:General Discussion« Previous MessageNext Message »
From:steve Date:June 14 2009 12:19am
Subject:Re: BULK DATA HANDLING 0.5TB
View as plain text  
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


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