List:General Discussion« Previous MessageNext Message »
From:mos Date:June 13 2012 3:06pm
Subject:Re: NoSQL help
View as plain text  
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 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.

http://dev.mysql.com/doc/refman/5.5/en/handler.html

Mike 

Thread
NoSQL helpManivannan S .13 Jun
  • Re: NoSQL helpJohan De Meersman13 Jun
    • RE: NoSQL helpRick James13 Jun
      • Re: NoSQL helpAnanda Kumar13 Jun
        • RE: NoSQL helpManivannan S .14 Jun
          • Re: NoSQL helpAnanda Kumar14 Jun
            • RE: NoSQL helpManivannan S .14 Jun
            • RE: NoSQL helpManivannan S .14 Jun
              • Re: NoSQL helpAnanda Kumar14 Jun
                • RE: NoSQL helpRick James14 Jun
          • RE: NoSQL helpRick James14 Jun
  • Re: NoSQL helpmos13 Jun
    • Handler?hsv25 Jun