RE: Can MySQL handle 120 million records?

[JamesD]

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