A large mount of co-current queries

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

Moderator: General Moderators

amiramir
Forum Newbie
Posts: 13
Joined: Tue Jul 14, 2009 1:59 pm

Re: A large mount of co-current queries

Post by amiramir »

No pictures, or something like that.

The queries are:
$sql= "UPDATE TBL SET param1=" . $_POST["param1"] . ", param2=" . $_POST["param2"] . ", param3=" . $_POST["param3"] . ",parma4=" . $_POST["param4"] . " WHERE F=" . $_POST["F"] . " and D=" . $_POST["id"] . " and M=" . $_POST["M"];

and the second is a simple insert to TBL.

It is inside a loop for up to 10 times.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: A large mount of co-current queries

Post by jackpf »

That should not cause any problems.

How long does one update take when you use the timer code I posted before?

And you may want to think about escaping stuff btw.
amiramir
Forum Newbie
Posts: 13
Joined: Tue Jul 14, 2009 1:59 pm

Re: A large mount of co-current queries

Post by amiramir »

If it's only one query at a time - it's 0.00002 seconds.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: A large mount of co-current queries

Post by jackpf »

So where does it get slow?
amiramir
Forum Newbie
Posts: 13
Joined: Tue Jul 14, 2009 1:59 pm

Re: A large mount of co-current queries

Post by amiramir »

It got slow because of the amount of queries made by the different clients.
The server couldn't handle those queries in real-time, and a queue of queries got bigger and bigger.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: A large mount of co-current queries

Post by jackpf »

Well that doesn't make sense; if each user is executing 10 queries, and the queries take 0.00002, then the script executes in 0.0002 seconds.

So unless every user executes the script within 0.0002 seconds of eachother, how can a queue be built up?
amiramir
Forum Newbie
Posts: 13
Joined: Tue Jul 14, 2009 1:59 pm

Re: A large mount of co-current queries

Post by amiramir »

3 clients, each one of them causes 10 queries. Every second 30 queries.
Now, it seems that it is not linear. 10 queries don't last single_query_time*10. It's much more relatively.
Add to that math a <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> host and after 10 hours you've got that result apparently.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: A large mount of co-current queries

Post by Eran »

how do you time the queries? make sure you run them with SQL_NO_CACHE to obtain relevant results. Also, run EXPLAIN on each and make sure they are hitting the indexes properly.

It might also help if you posted some code
amiramir
Forum Newbie
Posts: 13
Joined: Tue Jul 14, 2009 1:59 pm

Re: A large mount of co-current queries

Post by amiramir »

Today I tried something simple: I made a query directly via the cpanel on my web site:
SELECT * FROM TABLE WHERE 1;

It took 0.05 seconds (3000 rows). No other client was connected at that time, so my DB was absolutely in idle mode. I asked the support team of my hosts and they said that this result is fine.

It seems that they are not capable to deal with real-time databases at all. Is it consideredd OK or really slow?
Post Reply