At 07:27 AM 6/13/2012, Manivannan S. wrote:
> I am using MySQL 5.1, in this I am inserting 5GB of
> data for two days into my database. I am trying to generate a
> report by processing these data which are available in my database.
> Our clients are planning to keep the records for 60 days then that
> will cross 150GB of data. To generate a report I have to use all
> this accumulated of 150 GB data. I have done all kind of
> optimizations in my procedure and I have tuned up my MySQL server
> parameters also. But using MySQL getting the reports for this
> amount of data, within the short time is not possible.
> I have seen the concept of NoSQL and I am planning
> to implement this NoSQL concept into my database.
> Does anyone have any idea in NoSQL especially
> MongoDB technology and how to use this ?
> Thanks in advance.
>DISCLAIMER: This email message and all attachments are confidential
>and may contain information that is privileged, confidential or
>exempt from disclosure under applicable law. If you are not the
>intended recipient, you are notified that any dissemination,
>distribution or copying of this email is strictly prohibited. If you
>have received this email in error, please notify us immediately by
>return email or to mailadmin@stripped and destroy the
>original message. Opinions, conclusions and other information in
>this message that do not relate to the official business of SPAN,
>shall be understood to be neither given nor endorsed by SPAN.
You didn't say what the problem was when you tried to produce a
report on this data.
1) Did the sorting take too long?
2) Did traversing the data take too long?
3) Were the reports tables locked by other processes?
4) Using too much resources like memory, CPU, or disk?
5) Joining tables takes too long?
You may want to look into Handler. I've used it often when I needed
to traverse very large MyISAM tables. Handler requires no physical
sorting of the table because it traverses the table using an index.
It also ignores any locks on the table (which you may or may not
like), but the Handler will start traversing the table immediately.
It will solve problems 1,3 ,4 but not #2 because traversing a large
table with an index will be slower than if the table was already
sorted. One nice thing about the Handler is it uses virtually no
additional memory regardless of table size and very little disk
activity because there is no sorting. You can run it any time and it
won't degrade other MySQL processes. I don't think Handler will join
tables together; I have used it only to traverse a single table.
One other tip. When loading the data into the table, Load Data Infile
will be much faster on an empty table so recreate your tables from
scratch before loading the data. Also build all of the indexes after
the data has been loaded using one Alter Table command, and if
possible, reduce the number of unique indexes in the table.