List:General Discussion« Previous MessageNext Message »
From:JamesD Date:December 19 2002 4:05am
Subject:RE: Can MySQL handle 120 million records?
View as plain text  
i like mySQL, but it has a long way to go to
gain the level of 'confidence'  that oracle, db2 or mssql or sybase
have when it comes to frontline mission
critical stuff. I think it will in time...thats why i stick with
it. besides, confidence is often just a synonym for 'knowledge'

select count(*) from x (0.00 seconds)

if you dig into the source, you will probably find this
common select is built in and running all the time,

and we are all just getting a pre-filled variable
returned from the method " select count(*) from (x) "

I hope someone can prove me wrong...

i agree, in many common queries it is a heck of a lot faster
than MSSQL.

Jim

-----Original Message-----
From: Peter Vertes [mailto:pvertes@stripped]
Sent: Wednesday, December 18, 2002 6:47 AM
To: mysql@stripped
Subject: RE: Can MySQL handle 120 million records?


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-jim=workinout.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

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