Can Mysql Handle 100K+ Queries Per Day?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jbh
Forum Commoner
Posts: 89
Joined: Tue Dec 05, 2006 7:01 pm

Can Mysql Handle 100K+ Queries Per Day?

Post by jbh »

Hey, I apologize for a vague question. It's hard to sum up my main concern in one text box. Basically, I created a mini ad display system that
uses remote mysql. So, imagine 5,000 sites are displaying my ads (About 10 queries to the db to perform 4 functions required to display ad rotations)
every day, that'd be 50,000 hits. Well, it might grow to 100k at some point. Is that going to be cumbersome on mysql in a virtual/dedicated hosting service where it shares no other websites/traffic? What are its limits for real world usage so it doesn't come crashing to its knees?

I just want to know that we can expand our reach over time (still have some last testing) without worrying that IF, somehow, we receive a large influx
of ad views we find Mysql goes in the toilet.

I appreciate your time. I just want to get an idea of how sturdy it is with processing requests at a large rate per day for years on end.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Can Mysql Handle 100K+ Queries Per Day?

Post by pickle »

Should be fine. The number to look at isn't queries per day but queries per second. 100K queries in 24 hours is a little over 1 query a second - easy to handle. Compress those 100K queries into 1 minute & you might have a problem.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Can Mysql Handle 100K+ Queries Per Day?

Post by onion2k »

Google's AdWords uses MySQL, albeit a slightly tweaked and optimised version. I doubt you'll have any trouble.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Can Mysql Handle 100K+ Queries Per Day?

Post by califdon »

Almost surely the limitations will be from your server and your Internet "pipe", not MySQL.
jbh
Forum Commoner
Posts: 89
Joined: Tue Dec 05, 2006 7:01 pm

Re: Can Mysql Handle 100K+ Queries Per Day?

Post by jbh »

Thank you very much, guys. I figure that since it's a virtual server only used for this one database, bandwidth/space shouldn't be an issue.
However, are there any tips on what type of hardware/settings one should look for in a major mysql project? Just curious

And it's good to know that Google can run Mysql without a hitch. I guess this means I must make sure all my indexes/etc are in check
and ready for efficient use.

Thanks again.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Can Mysql Handle 100K+ Queries Per Day?

Post by califdon »

jbh wrote:Thank you very much, guys. I figure that since it's a virtual server only used for this one database, bandwidth/space shouldn't be an issue.
However, are there any tips on what type of hardware/settings one should look for in a major mysql project? Just curious

And it's good to know that Google can run Mysql without a hitch. I guess this means I must make sure all my indexes/etc are in check
and ready for efficient use.

Thanks again.
I'm not aware of any special configuration issues (although someone who has experience with heavy duty web apps might know a lot more about it than I do). If you are thinking in terms of thousands of hits per second, then you might want to read up on MySQL clustering, load balancing and other high volume techniques, where you have to split up the database to achieve performance, but I've never heard of a simple web site that required those techniques. Our local MySQL Meetup used to meet at the Yahoo HQ and the question was once asked, How many MySQL servers do you run at Yahoo? The guy in charge of MySQL training for Yahoo said they have over 7,000 servers running, handling ALL of their web-facing database requirements. At another meeting, someone asked what was the "biggest" MySQL database anyone present had worked with. Of course, "biggest" could imply several different things, but in terms of rows of data in tables, one guy said he used to work on one with over TWO BILLION rows, spread among many tables on many servers. Got my attention!
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Can Mysql Handle 100K+ Queries Per Day?

Post by Mordred »

jbh wrote:About 10 queries to the db to perform 4 functions required to display ad rotations
Ten queries looks too much to me, care to share the details? If you'll be worrying about the large scale performance, this may be the easiest point to optimize with the biggest overall impact.
crazybeardedman
Forum Newbie
Posts: 1
Joined: Thu Jul 03, 2008 3:08 pm

Re: Can Mysql Handle 100K+ Queries Per Day?

Post by crazybeardedman »

I noticed you used the words "virtual server". I am to assume that you are hosting with a company and not using your own server? Some companies throttle their mysql in the way of limiting the number of queries within a particular amount of time. Check with your host before you assume anything.

If you are still unsure, create a script that will create a bunch of connections and execute a set of queries over and over again. Have the script log each connect and query to a logfile so that you can verify that everything is working the way you planned.


Just some ideas.

cbm
Post Reply