At 11:10 AM +0530 6/13/09, Krishna Chandra Prajapati wrote:
>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.
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.
Book: High Performance MySQL, Second Edition
Useful tips from the authors of the above book:
And assuming you are using MySQL 5.0:
Table OPTIMIZE command
MySQL system variables