Optimizing MySQL

[TristaSD]

Hi,

I wrote a php+MySQL-based reservations system for a local restaurant.
MySQL is running on AMD Athlon 3000+ 2GHz with 1.5Gb of RAM. The
system is accessed through Intranet by 4-5 machines.

The main reservations table weighs only 40k with around 150 entries,
but I'm getting weird lags in the middle of the pages - half of the
table loads ... LAG! ... then the other half loads. This is especially
annoying considering the table will contain around 3,000 - 5,000
entries per year, entries older than 2 years will be removed.

The lags persist on MySQL server (locally), so it's not a network
issue. The lags are espeically bad when I do a query like "SELECT
name, phone, notes FROM reservations WHERE date > %today%" - it
literally takes 5-6 seconds to pull one row.

I don't use "SELECT ALL" queries, my tables are optimized, etc.

Help!

[Jonathan]

Re: Optimizing MySQL

TristaSD wrote:[color=blue]
> Hi,
>
> I wrote a php+MySQL-based reservations system for a local restaurant.
> MySQL is running on AMD Athlon 3000+ 2GHz with 1.5Gb of RAM. The
> system is accessed through Intranet by 4-5 machines.
>
> The main reservations table weighs only 40k with around 150 entries,
> but I'm getting weird lags in the middle of the pages - half of the
> table loads ... LAG! ... then the other half loads. This is especially
> annoying considering the table will contain around 3,000 - 5,000
> entries per year, entries older than 2 years will be removed.
>
> The lags persist on MySQL server (locally), so it's not a network
> issue. The lags are espeically bad when I do a query like "SELECT
> name, phone, notes FROM reservations WHERE date > %today%" - it
> literally takes 5-6 seconds to pull one row.
>
> I don't use "SELECT ALL" queries, my tables are optimized, etc.
>
> Help!
>[/color]
You might be better of posting details in a dedicated mysql group. Did
you already try the explain keyword to assure that you have correct
indexes and optimization?

http://dev.mysql.com/doc/refman/5.0/en/explain.html

Jonathan

[reandeau]

Re: Optimizing MySQL

[color=blue]
> The lags persist on MySQL server (locally), so it's not a network
> issue. The lags are espeically bad when I do a query like "SELECT
> name, phone, notes FROM reservations WHERE date > %today%" - it
> literally takes 5-6 seconds to pull one row.[/color]

Try adding indexes to fields in the table that you are searching on.
ALTER TABLE reservations ADD INDEX (date);

Let me know how it goes.

Jon Tjemsland

[Chung Leong]

Re: Optimizing MySQL

TristaSD wrote:[color=blue]
> Hi,
>
> I wrote a php+MySQL-based reservations system for a local restaurant.
> MySQL is running on AMD Athlon 3000+ 2GHz with 1.5Gb of RAM. The
> system is accessed through Intranet by 4-5 machines.
>
> The main reservations table weighs only 40k with around 150 entries,
> but I'm getting weird lags in the middle of the pages - half of the
> table loads ... LAG! ... then the other half loads. This is especially
> annoying considering the table will contain around 3,000 - 5,000
> entries per year, entries older than 2 years will be removed.
>
> The lags persist on MySQL server (locally), so it's not a network
> issue. The lags are espeically bad when I do a query like "SELECT
> name, phone, notes FROM reservations WHERE date > %today%" - it
> literally takes 5-6 seconds to pull one row.
>
> I don't use "SELECT ALL" queries, my tables are optimized, etc.
>
> Help![/color]

Sounds like you're running into a serious case of lock contention.
Here's a good article on the topic:

http://www.devshed.com/c/a/MySQL/MyS...zation-part-2/

[R. Rajesh Jeba Anbiah]

Re: Optimizing MySQL


TristaSD wrote:
<snip>[color=blue]
> The lags persist on MySQL server (locally), so it's not a network
> issue. The lags are espeically bad when I do a query like "SELECT
> name, phone, notes FROM reservations WHERE date > %today%" - it
> literally takes 5-6 seconds to pull one row.[/color]

What about when you remove the "%" from condition part (as in
%today%)?

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

[TristaSD]

Re: Optimizing MySQL

hahaha I was just implying that some VALID code goes in between % %.
:)



R. Rajesh Jeba Anbiah wrote:[color=blue]
> TristaSD wrote:
> <snip>[color=green]
> > The lags persist on MySQL server (locally), so it's not a network
> > issue. The lags are espeically bad when I do a query like "SELECT
> > name, phone, notes FROM reservations WHERE date > %today%" - it
> > literally takes 5-6 seconds to pull one row.[/color]
>
> What about when you remove the "%" from condition part (as in
> %today%)?
>
> --
> <?php echo 'Just another PHP saint'; ?>
> Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/[/color]