At 07:27 AM 6/13/2012, Manivannan S. wrote:
Hi all,
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.
Regards,
Manivannan S
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 mailad...@spanservices.com 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.
http://dev.mysql.com/doc/refman/5.5/en/handler.html
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
