SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 29
  1. #1
    SitePoint Evangelist
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How fast is mysql?

    Would php and mysql be fast enough to handle a website with a million members who makes a total of 20million queries in a month?

  2. #2
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mySQL is blisteringly fast - it's built for speed. HOWEVER, it is not too good at handling a huge number of simultaneous connections. I have no experience runing mySQL on a high traffic site, but there are a few guys on here such as MattR who have plenty of experience with this kind of thing and will hopefully give you a good answer In the meantime it would be handy to know a few more things about your site - how many hits it gets, how much database interaction you want to include etc etc etc.

  3. #3
    SitePoint Member
    Join Date
    Dec 2000
    Location
    Poland
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not a high-end expert, but what I saw and read around is:

    1. MySQL is the fastest database if you only read from it (SELECT ...). If your site would be something like a site with news from the IT industry, where many people only "read" pages and updates are quite rare (comparing to "reads") the MySQL would be the best. I still don't know if it scales to such a heavy load.

    2. If your site is something which is often updated as well as read (e.g. a huge message board), a databases like PostgreSQL seems better, as they better handle locking data during writes.

    I know that there are newest updates to MySQL, which brings transactions and probably better locking to MySQL but I haven't seen any test of it.
    On the other hand I heard that PostreSQL is getting more stable and faster with each release.

    Chris

  4. #4
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL is fast, but it depends, 20 million queries a month is about 7-8 per second wich is fine - even access could handle that, but of course the average is just that, an average, more likely at 'peak' times the load will be several hundred per second, and a million users is a lot!

    So I guess you are right at the top of MySQL's limits, and perhaps something more robust is on the cards.

    Next Q is open-source or are you willing to spend $ on the DB, I know postgreSQL is free, although I am not yet convinced of its 'superiority' over MySQL, are sybase or informix free? I am sure someone will post about that, and its more in the league you are looking at.

  5. #5
    SitePoint Evangelist
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    While my site is not experiencing much traffic currently, I'm expecting it to make 20 update and select queries per task performed by the user. So, if a user perform 10tasks a day, it would be off to 200 update and select queries.

    Just wondering if it would be big enough to handle millions of users if I grow real big, which would be equal to 200million update and select queries a month.

    I've seen my type of site reach those figures and much more.

  6. #6
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,939
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No.

    Get something like Sybase (which you can get a version of for free)

    And also a dedicated server.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  7. #7
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by firepages
    MySQL is fast, but it depends, 20 million queries a month is about 7-8 per second wich is fine - even access could handle that, but of course the average is just that, an average, more likely at 'peak' times the load will be several hundred per second, and a million users is a lot!

    So I guess you are right at the top of MySQL's limits, and perhaps something more robust is on the cards.

    Next Q is open-source or are you willing to spend $ on the DB, I know postgreSQL is free, although I am not yet convinced of its 'superiority' over MySQL, are sybase or informix free? I am sure someone will post about that, and its more in the league you are looking at.
    From reading Mattr's posts, older versions of Sybase are free (I htink), but Informix is MOST DEFINITELY not free. I believe it is upwards of 40-50k.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  8. #8
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, Sybase 11.x (i think it's 11.x) for Linux is free. I am not a big Sybase advocate mainly because I don't have a lot of experience with it, but we have some Sybase servers running here at work (Naval Weapon Station) and they keep up just fine.

    Sybase is an enterprise class database, and to be able to get it (even though it's an older version) for free should be a godsend for a lot of massively large sites.

    I don't have a lot of experience with PostgreSQL, but I have heard nothing but good about it.

    I wouldn't reccommend MySQL for a site that is allready massive, as it is only likely to get larger. Your users will be getting a lot of "too many users in MySQL database" error messages

    --UPDATE--
    I just popped over to Sybase's site to get a bit more information.
    http://www.sybase.com/linux/ase/
    that is their linux link for Adaptive Server Enterprise (the database server).
    Here is a quick breakdown of the version available for linux and how they may be used:
    12.5beta - this is a current release and a free download. Site doesn't state how *this* release can be used, but I am positive it is free for development/trial but you must purchase a license for commercia use/deployment
    11.9.2 - free for development, must purchase license for deployment
    11.0.3.3 - free for all It's a free download, it's free to develop for and free to use. however, you don't get any support!

    hope that helps
    Last edited by Alarion; Jun 29, 2001 at 07:25.
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alarion is correct in the version listing although 12.5 was released on Monday so you're downloading the GA release now.

    You get a little CD key in your e-mail which makes it last until Dec 31 2001 for 12.5 (I think 11.9.2 download is like that as well although I'm not sure).

    12.5 times out after a certain amount of time but is for development use only
    11.9.2 is for development use only
    11.0.3 is for devel / prod use or anything else you can think of.

    I am enthused about 12.5 for Linux because it includes the really great full-text search engine that Sybase neglected to release for the 11.0.3 and 11.9.2 versions. This means I can full-text index my site with no work other than pointing the software at a certain table and saying 'full-text index me!'.

    But yes for something which is 95% reads then MySQL will 0wn anything out there. We were thinking of using MySQL as a search engine because of Sybase' (Sybases'?) decision not to release the full text untill I looked at the download page and saw the full-text RPM.

    Upgrading to 12.5 from 11.9.2 is free if you own a 11.9.2 license and have support still.

    Informix was purchased by IBM to roll into their UDB. Not sure how long until Informix is killed (I think they still sell it maybe?).

    HOWEVER, if you are making 20 update (insert?) queries per user action (probably per page load) I’d seriously re-think what you are doing there. That seems ridiculously high and seriously *any* RDBMS is going to wonder what you are doing to it when you get 100 pages per second or more.

    There’s no ‘max’ queries that you want to stick to but the rule-of-thumb is as few as possible per page view.

  10. #10
    SitePoint Evangelist
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You got me wrong there.
    20 updates and selects is not done per page, but they are done when a user does something. You got me wrong there.

  11. #11
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by FreebieGuy
    You got me wrong there.
    20 updates and selects is not done per page, but they are done when a user does something. You got me wrong there.
    How do you define 'when a user does something' then? Generally for internet apps that is 'when a user requests a page' .

  12. #12
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm amazed at the lack of comment on the internet about Interbase. Does anyone use it? It was recently switched to open source and supposedly industrial strength. Surely there are some Cobalt users who have used it (as it is the standard DB shipped with Cobalt servers). I would like to know how it compared to say Sybase.

  13. #13
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've heard it is ok.. However when you say Cobalt (RAQ?) I generally think 'low power' (a la Access, Sybase ASA, Oracle Personal, MS SQL Server Standard, etc.).

  14. #14
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    holy shnikes Matt.

    I just went to your sports forum and was astounded at the speed at which the pages loaded. They were up almost instantaneously.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  15. #15
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by creole
    holy shnikes Matt.

    I just went to your sports forum and was astounded at the speed at which the pages loaded. They were up almost instantaneously.
    Really? Be honest now! We're having random network slowdowns in which pages will take like 10 seconds to come up (trace routes will show 900 ms on all hops through AT&T) so if you're seeing lightning quick pages then that means you're network isn't busted.

    But yeah, for what little hardware we have (DB box is dual PIII 750, 1GB RAM, 9 GB SCSI) Sybase ASE 11.9.2 for Linux runs exceptionally well (when the network doesn't kill it). I've seen it with 650 users online (this Monday) and it was just as fast. It's astounding what performance a good RDBMS can give you.. However I think that most people have a false sense of speed with MySQL (since it is so fast they can write crappy DB design and get away with it) and are shocked when they basically change their SQL to PostGRES, or Sybase or Oracle, etc. and find the speed is a DOG. It's not the fault of the RDBMS, it can only do so much when you throw stupid table and query designs at it.

    However when you properly index, normalize, query, structure, etc. your application and take advantage of triggers, stored procedures, rules, etc. is when you see the MAJOR benefits of the enterprise RDBMS. You can further fine-tune your memory caches, your tables (partitioning them up, etc.) and such. MySQL's simple cfg file pales in comparison to the multitude of performance-enhancing things you can do on big RDBMS'. Also the reporting tools are simply mind blowing.. You can run reports on the DB which tell you on however level of granularity you want exactly what the DB was doing at any one moment...

    In any rate, we have two web servers which are 1GHz each (single CPU load balanced) but with the performance of PHP 4.0.5 and the Zend Cache / Optimizer 1.1 combo we're thinking of rolling those together into a single dual CPU machine (probably dual PIII800 or so).

  16. #16
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Blazing!

    Tons faster than SitePoint...
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  17. #17
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by creole
    Blazing!

    Tons faster than SitePoint...
    Wow -- that's great to hear!

  18. #18
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR
    In any rate, we have two web servers which are 1GHz each (single CPU load balanced) but with the performance of PHP 4.0.5 and the Zend Cache / Optimizer 1.1 combo we're thinking of rolling those together into a single dual CPU machine (probably dual PIII800 or so).
    Redundency is a good thing though?

  19. #19
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freakysid


    Redundency is a good thing though?
    Yeah but a lot more expensive. We've only had to use it once or twice now (e.g. a nic card went bad on one of the boxes) and it was nice to have; not sure if it is worth the extra $1,000 a month.

  20. #20
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to take this off topic but who are you hosting with (if I may ask)?

  21. #21
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freakysid
    Sorry to take this off topic but who are you hosting with (if I may ask)?
    Rackspace

  22. #22
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That explains the price

  23. #23
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freakysid
    That explains the price
    RackSpace has treated us like dirt; if you have someplace better then my ears are open.

  24. #24
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, I don't personally have any recommendation for you. I am going to be getting my first dedicated server in a few weeks. I keep my ears open over at www.webhostingtalk.com though.

  25. #25
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MattR - If you know how to admin your own servers then I suggest trying DataColo we have had extreme reliability with them, good speed and great support (When we've ever needed it, which is rare as everything runs perfect). I can say with some certainty that you wouldn't be paying an extra $1000 per month for that second web server.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •