PHP And SQL UPDATE problem

['~=_Slawek_=~']

I have problem with SQL update. Sometimes it can take 2-10 seconds. I need
to make this update every time page is opened - ASYNCHRONOUSLY.

I have read forums, php.net etc about running php code 'in background' after
all data generated is received by user browser. However i cant simply use
it. Nothing works for me, im on a paid server.

I have checked the headers and all content my PHP outputs is zipped and send
compressed by apache (content-length header filled automatically). Is there
any way of running SQL query asynchronously to PHP script (so send query to
SQL, query is placed in some buffer and function returns immediatelly?)

TIA for help.

[Michael Austin]

Re: PHP And SQL UPDATE problem

'~=_Slawek_=~' wrote:
[color=blue]
> I have problem with SQL update. Sometimes it can take 2-10 seconds. I need
> to make this update every time page is opened - ASYNCHRONOUSLY.
>
> I have read forums, php.net etc about running php code 'in background' after
> all data generated is received by user browser. However i cant simply use
> it. Nothing works for me, im on a paid server.
>
> I have checked the headers and all content my PHP outputs is zipped and send
> compressed by apache (content-length header filled automatically). Is there
> any way of running SQL query asynchronously to PHP script (so send query to
> SQL, query is placed in some buffer and function returns immediatelly?)
>
> TIA for help.
>
>[/color]

There are many places that an SQL update can get bogged down... to name a few:
000) poorly designed logical and/or physical database
001) poorly written update statements
make sure the column you are updating is NOT the column in an index.
010) Number of rows in a non-indexed lookup to do the update
011) Number of users concurrently updating the same site
(locking issues)
100) System/Disk/Network latencies
since it is a "paid" service you have no control over this.

And since you have not provided us with any code segments to look at everything
is just speculation.

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

['~=_Slawek_=~']

Re: PHP And SQL UPDATE problem

Hello Michael.

Thanks for thinking over my problem. Your 4. assumption is right i cant tell
all other are wrong but I think they are. For my code:

mysql_query("UP DATE liczniki SET value=value+1 WHERE
owner_id='".$th is->get_user_id(). "' AND
owner_licznik_n umber='$owner_l icznik_number'" );

It takes 80ms to a couple of seconds, on shared hosting with DB overloaded
by other users (I assume some kind of write behind caching is responsible
for that). Table size is for now 100 records.

For table structure:
FIELD::TYPE::NU LL::KEY::DEFAUL T
id::int(10) unsigned::::PRI ::NULL::auto_in crement
owner_id::int(1 0) unsigned::YES:: MUL::NULL::
owner_user::var char(255)::YES: :MUL::NULL::
owner_licznik_n umber::int(11) unsigned::::MUL ::0::
value::int(10) unsigned::::::0 ::
type::enum('inn y','online','wy swietlen','odwi edzin')::::::in ny::
sub_type::enum( 'tekstowy','gra ficzny')::::::t ekstowy::
style::blob::YE S::::NULL::
settings::blob: :YES::::NULL::
last_updated::t imestamp::YES:: ::0000-00-00 00

TIA.

[Alex]

Re: PHP And SQL UPDATE problem

I think even if your server is overloaded, is this happening every
time, every day, every minute? is your site getting a massive number
of hits?

I have seen before when you leave connections open, result sets not
freed, etc. this memory is not necessarily reallocated or destroyed and
thus everytime the system needs to allocate more memory for your PHP
script it may need to swap some to the disk, and this would cause your
latency. Remember to close all your connects and free all of your
result sets.

Alex

http://prepared-statement.blogspot.com

[Jerry Stuckle]

Re: PHP And SQL UPDATE problem

'~=_Slawek_=~' wrote:[color=blue]
> Hello Michael.
>
> Thanks for thinking over my problem. Your 4. assumption is right i cant tell
> all other are wrong but I think they are. For my code:
>
> mysql_query("UP DATE liczniki SET value=value+1 WHERE
> owner_id='".$th is->get_user_id(). "' AND
> owner_licznik_n umber='$owner_l icznik_number'" );
>
> It takes 80ms to a couple of seconds, on shared hosting with DB overloaded
> by other users (I assume some kind of write behind caching is responsible
> for that). Table size is for now 100 records.
>
> For table structure:
> FIELD::TYPE::NU LL::KEY::DEFAUL T
> id::int(10) unsigned::::PRI ::NULL::auto_in crement
> owner_id::int(1 0) unsigned::YES:: MUL::NULL::
> owner_user::var char(255)::YES: :MUL::NULL::
> owner_licznik_n umber::int(11) unsigned::::MUL ::0::
> value::int(10) unsigned::::::0 ::
> type::enum('inn y','online','wy swietlen','odwi edzin')::::::in ny::
> sub_type::enum( 'tekstowy','gra ficzny')::::::t ekstowy::
> style::blob::YE S::::NULL::
> settings::blob: :YES::::NULL::
> last_updated::t imestamp::YES:: ::0000-00-00 00
>
> TIA.
>
>
>[/color]

Sounds like it may be time to find another host which doesn't overload
their servers like this. A simple update for 100 records shouldn't take
anywhere near 2 seconds.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

['~=_Slawek_=~']

Re: PHP And SQL UPDATE problem

Maybe you have some suggestions what host should I try? My hosting is very
good (cpanel, unlimited ftp, mail, domains, awstat, server logs, custom
error pages, directory protection, installed nearly all PHP extensions, 6
postgre + mysql database without limits, 10 GIG month transfer, 2GIG on disk
for only $35 yearly).

@Alex:
Yes i wrote its overloaded. Sometimes there is 10MBytes of data/sec taken
from DB (probably some site written badly).

I just taken some stats, the question is: should i change hosting. I mean I
only updated 25 X 4-byte fields that are not primary indexes and delay is
IMO huge (but im new to DB optimisation so I can be wrong). How slow itll
get when i finally have 2000 records table (its my destination, not much
IMO).

I guess that after 4'th update my table finally all go to cache the >WHOLE<
25 rows >LOL<.

/*[23:22:21][ 344 ms]*/ UPDATE liczniki SET value=1
/*(25 row(s) affected)*/
/*[23:22:21][ 406 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 609 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 219 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 62 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 47 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 47 ms]*/ UPDATE liczniki SET value=1
/*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1

[Jerry Stuckle]

Re: PHP And SQL UPDATE problem

'~=_Slawek_=~' wrote:[color=blue]
> Maybe you have some suggestions what host should I try? My hosting is very
> good (cpanel, unlimited ftp, mail, domains, awstat, server logs, custom
> error pages, directory protection, installed nearly all PHP extensions, 6
> postgre + mysql database without limits, 10 GIG month transfer, 2GIG on disk
> for only $35 yearly).
>
> @Alex:
> Yes i wrote its overloaded. Sometimes there is 10MBytes of data/sec taken
> from DB (probably some site written badly).
>
> I just taken some stats, the question is: should i change hosting. I mean I
> only updated 25 X 4-byte fields that are not primary indexes and delay is
> IMO huge (but im new to DB optimisation so I can be wrong). How slow itll
> get when i finally have 2000 records table (its my destination, not much
> IMO).
>
> I guess that after 4'th update my table finally all go to cache the >WHOLE<
> 25 rows >LOL<.
>
> /*[23:22:21][ 344 ms]*/ UPDATE liczniki SET value=1
> /*(25 row(s) affected)*/
> /*[23:22:21][ 406 ms]*/ UPDATE liczniki SET value=1
> /*[23:22:22][ 609 ms]*/ UPDATE liczniki SET value=1
> /*[23:22:22][ 219 ms]*/ UPDATE liczniki SET value=1
> /*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1
> /*[23:22:22][ 62 ms]*/ UPDATE liczniki SET value=1
> /*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1
> /*[23:22:22][ 47 ms]*/ UPDATE liczniki SET value=1
> /*[23:22:22][ 47 ms]*/ UPDATE liczniki SET value=1
> /*[23:22:22][ 63 ms]*/ UPDATE liczniki SET value=1
>
>
>[/color]

Well, you also get what you pay for. Hosting companies have to pay for
the servers, connections to the world, electricity, personnel, etc.

If they're going to charge a cheap price, they have to put a lot of
people on a server. And if your server is being overloaded by another
user (or multiple users), there's not much you can do about it.

You can request a different server from your host. If they move you,
you might or might not get better response. Or you can find another
hosting company. Again, if you go with a $35/yr hosting company you may
or may not get better response.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===