Sleepy database

Hi group,

I have a site, which content is mostly based on what it finds in a
mySQL database.
All queries etc. are performed by PHP. Somehow, no matter what section
of the
site (still in test fase) i access, either small, with little DB info,
or bif with big DB info,
it seems that when i haven't accessed the page in at least 5 minutes,
access it again
the database is a lot slower the first load.
As if it has to wake up, and get started again. When it has loaded the
first time, and
i refresh almost immediately speed is normal again ...

How or why is this happening?

Frizzle.

re: Sleepy database

frizzle wrote:[color=blue]
> Hi group,
>
> I have a site, which content is mostly based on what it finds in a
> mySQL database.
> All queries etc. are performed by PHP. Somehow, no matter what section
> of the
> site (still in test fase) i access, either small, with little DB info,
> or bif with big DB info,
> it seems that when i haven't accessed the page in at least 5 minutes,
> access it again
> the database is a lot slower the first load.
> As if it has to wake up, and get started again. When it has loaded the
> first time, and
> i refresh almost immediately speed is normal again ...
>
> How or why is this happening?
>
> Frizzle.
>[/color]

After you load it the first time, the info probably is in the cache -
and MySQL just pulls it from there.

Sounds like you need to look into why your first query runs so slowly.

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

re: Sleepy database

Could be connection overhead. The PHP has to connect to the database
using the TCP connection on first connect. After that the connection is
kept alive for a short period of time for use in subsequent requests.
But this should only really be an issue if the mysql server is on a
seperate machine on a rather slow network.

Alternatively it could be the cache on the database server. On first
load the data has to be loaded from the hard disk into a memory cache
where it is kept for a short period of time. Subsequent accesses can
then draw from the cache directly.

Either way, when your site goes live and you are getting plenty of hits
you shouldn't encounter these delays as all connections and caches
would be kept intact by the load itself. Also, there is most likely
something in your code which is sub-optimal. Analyse your queries
closely and see where you can make improvements. Move as much
processing as possible to the database site when it will result in less
data moving between PHP and MySQL.

You might also want to consider moving to a more capable database such
as PostgreSQL. PostgreSQL has some great features you can leverage for
creating smarter queries such as PL/pgSQL, triggers, stored procedures
and lots more.

-Robert


frizzle wrote:[color=blue]
> Hi group,
>
> I have a site, which content is mostly based on what it finds in a
> mySQL database.
> All queries etc. are performed by PHP. Somehow, no matter what section
> of the
> site (still in test fase) i access, either small, with little DB info,
> or bif with big DB info,
> it seems that when i haven't accessed the page in at least 5 minutes,
> access it again
> the database is a lot slower the first load.
> As if it has to wake up, and get started again. When it has loaded the
> first time, and
> i refresh almost immediately speed is normal again ...
>
> How or why is this happening?
>
> Frizzle.[/color]

re: Sleepy database

frizzle wrote:
[color=blue]
> it seems that when i haven't accessed the page in at least 5 minutes,
> access it again the database is a lot slower the first load.[/color]

Is it a shared server with limited memory?

If so, it may be that when your data hasn't been accessed in a while it is
swapped out of main memory onto disk to make way for other people's data;
then when requested again, it needs to be loaded back into memory.

It's probably something along those lines.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact

re: Sleepy database

Toby Inkster wrote:[color=blue]
> frizzle wrote:
>[color=green]
> > it seems that when i haven't accessed the page in at least 5 minutes,
> > access it again the database is a lot slower the first load.[/color]
>
> Is it a shared server with limited memory?
>
> If so, it may be that when your data hasn't been accessed in a while it is
> swapped out of main memory onto disk to make way for other people's data;
> then when requested again, it needs to be loaded back into memory.
>
> It's probably something along those lines.
>
> --
> Toby A Inkster BSc (Hons) ARCS
> Contact Me ~ http://tobyinkster.co.uk/contact[/color]

Thanks all, i understand there isn't really anything i can do about it
.... :(
Except for switching DB-system (not really an option now) or optimize
the PHP codes, which i already made as good as i'm capable of at the
moment ...

About the caching of data: if i first 'wake the server up' by accessing
the news
section of the site, and then go to links (both from DB) links is still
a lot faster,
as when visited immediately, though it's not in cache ...
(First load really takes up to 10 secs, second up to 0.8 (still quite
slow), can't be
normal ... )

The site is on a shared host.

Anyway, thanks again!

re: Sleepy database

frizzle wrote:[color=blue]
> Toby Inkster wrote:
>[color=green]
>>frizzle wrote:
>>
>>[color=darkred]
>>>it seems that when i haven't accessed the page in at least 5 minutes,
>>>access it again the database is a lot slower the first load.[/color]
>>
>>Is it a shared server with limited memory?
>>
>>If so, it may be that when your data hasn't been accessed in a while it is
>>swapped out of main memory onto disk to make way for other people's data;
>>then when requested again, it needs to be loaded back into memory.
>>
>>It's probably something along those lines.
>>
>>--
>>Toby A Inkster BSc (Hons) ARCS
>>Contact Me ~ http://tobyinkster.co.uk/contact[/color]
>
>
> Thanks all, i understand there isn't really anything i can do about it
> ... :(
> Except for switching DB-system (not really an option now) or optimize
> the PHP codes, which i already made as good as i'm capable of at the
> moment ...
>
> About the caching of data: if i first 'wake the server up' by accessing
> the news
> section of the site, and then go to links (both from DB) links is still
> a lot faster,
> as when visited immediately, though it's not in cache ...
> (First load really takes up to 10 secs, second up to 0.8 (still quite
> slow), can't be
> normal ... )
>
> The site is on a shared host.
>
> Anyway, thanks again!
>[/color]

Switching DB systems won't help. Every major DB implements some kind of
caching mechanism for performance reasons.

But it may not be the data which is cached, either. It might be the db
server has been swapped out, or any number of things. I really wouldn't
expect a different db server to act differently - because I don't think
it's a MySQL problem.

But since you're on a shared host, there's no way for you to
troubleshoot the problem. Have you tried talking to your hosting
company's support group? And if they can't help you, find a competent
hosting company.



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

re: Sleepy database

In message <yrmdnaY-s71OTZPZRVn-rg@comcast.com>, Jerry Stuckle
<jstucklex@attglobal.net> writes
<snip>[color=blue]
>
>But it may not be the data which is cached, either. It might be the db
>server has been swapped out, or any number of things. I really
>wouldn't expect a different db server to act differently - because I
>don't think it's a MySQL problem.
>
>But since you're on a shared host, there's no way for you to
>troubleshoot the problem. Have you tried talking to your hosting
>company's support group? And if they can't help you, find a competent
>hosting company.[/color]

Has the OP tried this at home? E.g. if he has a PC at home set up with
the same database & scripts, does he have the same problem? If not then
in my view that points the finger at the hosted database, but if he has
it at home them maybe it *is* the query tuning etc.

BTW is he *sure* that the schemas of the two databases are identical?
It's not unknown for people to create indexes on test databases to
resolve performance issues and then forget to create them on the live...
:)

--
Surfer!
Email to: ramwater at uk2 dot net