CODEWITHSUNDEEP
phpmysqlinnodb
I. Sharma
I. Sharma

Reputation: 33

Running Query in Queue MYSQL

I want to know something really important for my website, Imagine if I have thousand of users that accessed the website, then, At that time there probably be thousand of queries that might be running at the same, So, Don't that fill up the DB pool as at the same time there are thousand of queries.So, to overcome this , how can I run the MYSQL queries in Queue ? Also, I am using InnoDB as MYSQL Storage Engine, Any other suggestion is also appreciated.

Upvotes: 1

Views: 1163

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562791

I suggest you monitor a couple of status variables in MySQL:

SHOW GLOBAL STATUS LIKE 'Threads_%';

Threads_connected is the number of clients are currently connected and occupying a thread in the MySQL server. Typically this is one per concurrent PHP request (if you're using PHP).

Threads_running is the number of clients who are connect and actually executing a query at that moment.

You might have hundreds of threads connected, but only a handful of threads running at any given time. This is because a PHP script runs code in between the SQL queries. During this in-between time, the MySQL connection is still open, but not doing anything in the MySQL server. This is normal.

You might have thousands of users, but since human beings typically view a web page for at least a few seconds before they click on something to request the next web page, there is some time in between page views where neither your web server or your database server need to do anything for a given user.

So it's typically true that the MySQL server can handle the traffic even when you have thousands of users, because only a tiny fraction of them are running a query simultaneously.

Upvotes: 0

Rick James
Rick James

Reputation: 142518

The problem does not exist.

  • Properly written queries can come and go in the blink of an eye.
  • You won't have "thousands" of users actually executing queries at the same time. The real metric is "pages per minute".
  • If you were to "queue" the queries, latency would be bad, and the users would move to some competing site.
  • If you do have performance problems, it is almost always because of lack of index, poorly phrasing of queries, and/or improper schema design.

Another, more appropriate approach, for scaling is to use Replication go allow for multiple read-only Slaves and have multiple clients (on other serves) hitting those Slaves through a load-balancer. This is what Yahoo, for example, does to handle its traffic. And it needs only a handful of servers for each property (news, weather, sports, finance, etc). Are you at that scale yet?

Upvotes: 2

ali
ali

Reputation: 1351

RDMS handles the query queues internally. You can use caches systems in order to increase the response time and use nosql databases in order to scale your solution. On the other hand, if your intent is to throttle the visitors, you have to handle it in your application side.

Upvotes: 0

Related Questions