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

A large mount of co-current queries

Post by amiramir »

Hi guys,

I have written a PHP script that performsaround 10 queries.
And tested it intensively with a few client at the moment for along time and it loaded the database and caused a very long query time.

How can I prevent it? Should I lock and unlock the table in the beginning and the end of the PHP script?

Thanks!
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: A large mount of co-current queries

Post by onion2k »

10 queries isn't very much at all. I've got scripts that do thousands of queries that are fine. I think there's a bigger problem somewhere.
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 »

What are the queries doing?
amiramir
Forum Newbie
Posts: 13
Joined: Tue Jul 14, 2009 1:59 pm

Re: A large mount of co-current queries

Post by amiramir »

The update / insert to the table or just select.
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 »

If there aren't any joins then it can't be slow at all with 10 queries, unless your server is a heap of junk.
amiramir
Forum Newbie
Posts: 13
Joined: Tue Jul 14, 2009 1:59 pm

Re: A large mount of co-current queries

Post by amiramir »

I made a stress test. 6 clients that call a PHP script every a few seonds.
The PHP makes about 10 queries. It started fine, but as the time passed it became slower and slower...
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 »

Try using this code with various queries to see if there is a bottleneck.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: A large mount of co-current queries

Post by onion2k »

amiramir wrote:I made a stress test. 6 clients that call a PHP script every a few seonds.
The PHP makes about 10 queries. It started fine, but as the time passed it became slower and slower...
Could be running out of memory. Do the queries select massive resultsets?
amiramir
Forum Newbie
Posts: 13
Joined: Tue Jul 14, 2009 1:59 pm

Re: A large mount of co-current queries

Post by amiramir »

Most of the queries update or insert to the table.
The select finds a unique row.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: A large mount of co-current queries

Post by onion2k »

Is it inserting loads of data? That can slow things down a lot.
amiramir
Forum Newbie
Posts: 13
Joined: Tue Jul 14, 2009 1:59 pm

Re: A large mount of co-current queries

Post by amiramir »

Around 10 rows per script execution.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: A large mount of co-current queries

Post by onion2k »

Well, be the sounds of it the script should run fine with loads of concurrent users. There's something seriously wrong with the code if it isn't working properly.
amiramir
Forum Newbie
Posts: 13
Joined: Tue Jul 14, 2009 1:59 pm

Re: A large mount of co-current queries

Post by amiramir »

Thank you guys for your help.

It seems that I created a huge load on the server. This server is my web host, and is not capable of dealing with a big amount of queries.

I'll start my project as a pilot mode, and when time comes, I will find a databse hosting service for that purpose.

Thanks again :)
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 »

10 queries is not a lot.

As onion2k said, scripts can execute thousands in under a second.

Sounds like you need new hosts, or they need to upgrade their hardware.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: A large mount of co-current queries

Post by s.dot »

When I look in phpmyadmin stats it constantly handles thousands of queries per second, so there is something wrong with your queries. Show us them. On the crappiest machine it should not be noticeably slow.

Show us your code. :)

Are you inserting pictures? Are your tables HUGE? Indexed properly?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply