List:General Discussion« Previous MessageNext Message »
From:Qunfeng Dong Date:December 18 2002 5:48pm
Subject:RE: Can MySQL handle 120 million records? - Impressive! How do you guys do that?
View as plain text  
I am very encouraged to hear all these successful
proofs. I do want to stick to MySQL (we are using it
to develop a biology database). But I am indeed seeing
not-so-good performance (join on tables much smaller
than yours takes minutes even using index) and I seem
to read all the docs I could find on the web about how
to optimize but they are not working for me (I am
going to order Jeremy Zawodny's "Advanced MySQL" and
see if I am missing anything). Am I one of the few who
are encountering the problems? What's your secrets to
successfully run such large databases with MySQL? How
much time have you spend on fine-tune the performance?

Qunfeng

--- Peter Vertes <pvertes@stripped> wrote:
> Hi,
> 
> 	I've been using MySQL intercompany for a while now
> with great results.  Even the diehard MSSQL people
> are amazed at how fast it can be at time.  One of
> the things I use it for is to store syslog events in
> it.  I wrote a backend that parses a syslog file as
> data is being written into it and does multiple
> things with each syslog entry depending what the
> entry contains.  When I'm done with it the syslog
> entry goes into a MySQL database where I can store
> the data and let the operations team access it
> through a PHP enabled webpage to see either what is
> going on in the system real-time of be able to do
> queries about certain hosts, processes or show some
> stats (what happened to machine x on date y and what
> processes were running on it, etc...).
> 	The MySQL database is being hosted on a Dell
> Precisions 540 workstation box.  It's a P4 1.7GHz
> Xeon with 512MB of ram and a 40GB IDE disc running
> Windows 2000 Server.  That MySQL database is also
> being used for other things (nothing too intensive)
> and I muck around with it also and use it as a test
> db.  The machine also handles webserving chores and
> runs backup chores and other operations related
> tasks.
> 	The database only holds about 1 months worth of
> data in it, the rest we don't really need but we
> keep around for a while outside of the db zipped up.
>  As of when I'm writing this there were about 18.7
> million entries in that table:
> 
> mysql> select count(*) from notifications;
> +----------+
> | count(*) |
> +----------+
> | 18711190 |
> +----------+
> 1 row in set (0.00 sec)
> 
> All these entries have been accumulated from
> December 1, 2002 till present day:
> 
> mysql> select distinct syslogdate from notifications
> order by syslogdate;
> +------------+
> | syslogdate |
> +------------+
> | 2002-12-01 |
> | 2002-12-02 |
> | 2002-12-03 |
> | 2002-12-04 |
> | 2002-12-05 |
> | 2002-12-06 |
> | 2002-12-07 |
> | 2002-12-08 |
> | 2002-12-09 |
> | 2002-12-10 |
> | 2002-12-11 |
> | 2002-12-12 |
> | 2002-12-13 |
> | 2002-12-14 |
> | 2002-12-15 |
> | 2002-12-16 |
> | 2002-12-17 |
> | 2002-12-18 |
> +------------+
> 18 rows in set (12.95 sec)
> 
> 	Notice it took almost 13 seconds to complete that
> last query.  I tried this on a MSSQL server and
> after 2 minutes I turned the query off.  That kind
> of performance was unacceptable for a webapp that
> uses a database that does real time queries.  I'm
> quite happy with the performance of MySQL and I just
> love to see the MSSQL guys retreat when I show off
> how fast some queries can be (they always strike
> back with transactional stuff, blah, blah, blah :) 
> Anyway, I would suggest you use Linux for your
> dbserver with some kind of journaling file system. 
> I would go with ReiserFS because if memory serves
> correctly it can handle files up to 4 terabytes but
> you might want to double check since I'm quite
> forgetful with facts like that :)  I would also
> recommend the fastest SCSI drives you can find. 
> When I do queries in any 10 million+ database I
> barely get any CPU activity but I get A LOT of disk
> activity and I think this IDE drive is holding MySQL
> back.  When I have time I'm thinking about moving
> this database/webapp beast onto a SCSI Linux box and
> see how well it performs.  I think you'll be very
> pleased with the performance you'll get out of
> MySQL.
> 
> -Pete
> 
> P.S.: Thanks again MySQL team :)
> 
>
---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list
> archive)
> 
> To request this thread, e-mail
> <mysql-thread127803@stripped>
> To unsubscribe, e-mail
>
<mysql-unsubscribe-dongqunfeng=yahoo.com@stripped>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
Thread
Can MySQL handle 120 million records?B.G. Mahesh18 Dec
  • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
  • Re: Can MySQL handle 120 million records?Qunfeng Dong18 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
    • Re: Can MySQL handle 120 million records?Michael She18 Dec
      • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
        • RE: Can MySQL handle 120 million records?Adam Nelson18 Dec
      • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
        • RE: Can MySQL handle 120 million records?Joe Stump18 Dec
          • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
          • Re: Can MySQL handle 120 million records?Gerald Jensen18 Dec
            • Re: Can MySQL handle 120 million records?Aaron Clausen18 Dec
    • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
      • Re: Can MySQL handle 120 million records?Paul DuBois18 Dec
        • Database type questionRichard E. Perlotto II19 Dec
          • Re: Database type questionIikka Meriläinen19 Dec
      • Re[2]: Can MySQL handle 120 million records?Dyego Souza do Carmo19 Dec
    • RE: Can MySQL handle 120 million records?Wayne Lewis18 Dec
      • Excluding records?Eric Anderson19 Dec
        • Re: Excluding records?Adolfo Bello20 Dec
    • Re: Re[2]: Can MySQL handle 120 million records?Harald Fuchs19 Dec
      • Re: Re[2]: Can MySQL handle 120 million records?Paul DuBois19 Dec
      • Re: Re[2]: Can MySQL handle 120 million records?Paul DuBois19 Dec
  • Re: Can MySQL handle 120 million records?Muruganandam18 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
      • Re: Can MySQL handle 120 million records?Michael She18 Dec
        • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
          • Re: Can MySQL handle 120 million records?Michael She18 Dec
            • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
              • Re: Can MySQL handle 120 million records?Michael She18 Dec
                • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
            • Re: Can MySQL handle 120 million records?Michael Bacarella18 Dec
              • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • RE: Can MySQL handle 120 million records?Joe Stump18 Dec
              • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • Can I build a web page to extract data?Gary Hostetler18 Dec
            • Re: Can MySQL handle 120 million records?Csongor Fagyal19 Dec
          • Re: Can MySQL handle 120 million records?Michael She18 Dec
        • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
    • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
      • Re: Can MySQL handle 120 million records?W. D.18 Dec
    • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
    • Re: Can MySQL handle 120 million records?Csongor Fagyal18 Dec
      • Re: Can MySQL handle 120 million records?W. D.18 Dec
    • Re: Can MySQL handle 120 million records?Csongor Fagyal18 Dec
Re: Can MySQL handle 120 million records?B.G. Mahesh18 Dec
  • Re: Can MySQL handle 120 million records?David T-G18 Dec
    • RE: Can MySQL handle 120 million records?JamesD18 Dec
      • Re: Can MySQL handle 120 million records?David T-G18 Dec
RE: Can MySQL handle 120 million records?Peter Vertes18 Dec
  • RE: Can MySQL handle 120 million records? - Impressive! How do you guys do that?Qunfeng Dong18 Dec
    • Re: Can MySQL handle 120 million records? - Impressive! How doyou guys do that?R. Hannes Niedner18 Dec
    • Re: Can MySQL handle 120 million records? - Impressive! How do youguys do that?Michael T. Babcock18 Dec
      • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Qunfeng Dong18 Dec
      • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Qunfeng Dong18 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Jocelyn Fournier19 Dec
          • Thanks! Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too...Qunfeng Dong20 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Ryan Fox19 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Brent Baisley19 Dec
  • RE: Can MySQL handle 120 million records?JamesD19 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny21 Dec
      • Re: Can MySQL handle 120 million records?Michael She21 Dec
        • Re: Can MySQL handle 120 million records?Michael T. Babcock23 Dec
      • Re: Can MySQL handle 120 million records?Michael She21 Dec
      • RE: Can MySQL handle 120 million records?JamesD21 Dec
        • Re: Can MySQL handle 120 million records?Jeremy Zawodny22 Dec
          • Re: Can MySQL handle 120 million records?Michael T. Babcock23 Dec
            • Re: Can MySQL handle 120 million records?Jeremy Zawodny23 Dec
            • DELETE with no WHERE clauseGordon23 Dec
              • Re: DELETE with no WHERE clauseDennis Salguero24 Dec
              • re: DELETE with no WHERE clauseVictoria Reznichenko24 Dec
      • RE: Can MySQL handle 120 million records?JamesD21 Dec
        • RE: Can MySQL handle 120 million records?Dean Harding21 Dec
          • RE: Can MySQL handle 120 million records?JamesD21 Dec
RE: Can MySQL handle 120 million records?David Brodbeck18 Dec
RE: Can MySQL handle 120 million records?Greg_Cope18 Dec
Re: Can MySQL handle 120 million records?Paul DuBois18 Dec
RE: Can MySQL handle 120 million records?RBRoa19 Dec
RE: Can MySQL handle 120 million records?Dana Diederich19 Dec
RE: Can MySQL handle 120 million records?John Griffin19 Dec
Re: Excluding records?King_Henree@yahoo.com20 Dec