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 <[EMAIL PROTECTED]> 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 > <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > 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 --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
