Throttling database requests

Question

A user on a web site can click on a button to vote for one image after another. This sends some requests to a MySQL database through jQuery. I'm not sure but the web site account probably allows 10 connections maximum.

In this context, can repeated clicking by a single user cause multiple requests and in turn cause performance issues? If so, what simple ways exist to throttle requests?

Edit: Question reformulated as flagged as unclear.

"I've noticed when clicking on a button that the display of results lags a bit" - perhaps a faster server is required? Have you got covering indexes in place? — Mitch Wheat, Jul 09 '11 at 06:13
possible duplicate of [How to throttle or prioritize a query in MySql](http://stackoverflow.com/questions/3134350/how-to-throttle-or-prioritize-a-query-in-mysql) — Tudor Constantin, Jul 09 '11 at 06:20
@Mitch Wheat: I have reformulated my question. The issue is whether a single user (a site user and not SQL user) can cause some real performance issues by clicking repeatedly on a button which is triggering SQL requests. — James P., Jul 09 '11 at 08:15

Tudor Constantin · Answer 1 · 2011-07-09T08:26:14.827

MySQL Setting Account Resource Limits

Starting from MySQL 4.0.2, you can limit access to the following server resources for individual accounts:

The number of queries that an account can issue per hour

The number of updates that an account can issue per hour

The number of times an account can connect to the server per hour

Additional Info -

MySQL does not have a query timeout value, or any other built-in way to throttle an individual query. However, it is pretty trivial to write a script that will kill long-running queries. You could even capture the user and query so that you can beat the offending user/programmer later.

Here is an example of one using PERL.

Edit to address aditional info

Depending on the queries your app makes, even a single click can put down the whole site. Although, voting can be implemented in a very lightweight way, so even if you have 1000 users giving repeated clicks to have the site responsive.

You could also limit the DB hits at application level - for example by holding in a session variable the id of the photos the user already voted on - check that variable before hitting the DB and you should be ok

MySQL user query limits are pretty coarse-grained, and end up just disabling everything when you run into them -- turning a slow web site into one that doesn't work at all. — , Jul 09 '11 at 06:54
@duskwuff you are right, but the question was how to throttle them, not how to improve them — Tudor Constantin, Jul 09 '11 at 07:18
I've reformulated the question. The kind of throttling I'm looking for is probably best done on the PHP side although I'm open to any ideas that might help with the database. — James P., Jul 09 '11 at 08:18

score 1 · Answer 2 · answered Jul 09 '11 at 06:19

First ensure that your problem is the database, maybe you have some logic on your webserver that is taking too long to process.

But, to 'throttle' your requests, you should have to optimize your queries, use indexes, cache on the server, or maybe redesign your database structure.

Hope this helps. Cheers

score 1 · Accepted Answer · answered Jul 09 '11 at 06:41

If there is no reason for anyone to ever click the button more then once every few seconds, just setup a disable function for that button. So when it is clicked it is disabled for 5 seconds, then renables again.

You may want to look up a better timer method, this is one way i know off hand.

You can use Button onclick-

document.getElementById("button").disabled = true;
var enableButton = setInterval( "enableButton()", 5000 );

function enableButton()
{
document.getElementById("button").disabled = false;
clearInterval(enableButton)
}

Good Luck

But if you suspect foul play, the request itself will not be limited, and this would be easily circumvented by said 'attacker'. — Nanne, Jun 25 '12 at 14:53

Throttling database requests

3 Answers3