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.
4
-
I think you approach it from the wrong end. If you want something for easing the server load, look into web caching.Bence Gajdán– Bence Gajdán2017-06-20 13:34:24 +00:00Commented Jun 20, 2017 at 13:34
-
to ease the DB load you can use slave serversPeter– Peter2017-06-20 13:44:08 +00:00Commented Jun 20, 2017 at 13:44
-
I don't think MySQL has a built-in method for queuing queries. You could use a cluster (multiple servers with synced data) which would allow more connections and would make the system faster or you could use something like Redis (JSON cache) to temporarily store frequently accessed data outside the DB.CptMisery– CptMisery2017-06-20 14:24:09 +00:00Commented Jun 20, 2017 at 14:24
-
Tune your DB properly. This is not an issue. MySQL can spawn many threads and execute queries in parallel. Of course server hardware should provide sufficient resources and processing power.Allen King– Allen King2017-07-02 23:02:02 +00:00Commented Jul 2, 2017 at 23:02
Add a comment
|
3 Answers
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?
Comments
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.
2 Comments
I. Sharma
Can't we run query in queue by changing storage engine, I am using InnoDB and I have read somewhere that it supports running query in parallel, Can I get benefit if I will start using MyIsam ?
ali
You can tune your db of course. I don't know the limits of those engines; however, RDMBs are generally the bottleneck and limited to one physical machine. It is very hard to shard and replicate so you cannot scale your solution. Rather, you can use nosql dbs, distributed cache systems, real-time streaming (big data) tools etc. This is a very common pattern for scalability. Do not reinvent the wheel.
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.
