List:General Discussion« Previous MessageNext Message »
From:Adam Powell Date:May 25 1999 1:14am
Subject:Re: problem with indexing
View as plain text  
Thanks a lot - I have made the changes to the database and it seems to be
running much better now :)

I have one question though... and this is causing NO END of problems to my
company, and I have mentioned this before, and nobody seems to acknowledge
its existence.  No offence, you are doing a great job!  I just want to point
out a potential problem!

We are basically getting a memory leak with our database server.  This is a
SUN OS 5.6 server running only mySQL v3.22.20a with 1 Gigabyte of RAM.
Every 2 days or so, it completely runs out of memory... here is the latest
excerpt from top...

10715 root     338  58    0  647M  589M cpu1   91.6H 39.92% mysqld

Now, I am not sure why this is happening, or what could cause mySQL to
demand more and more memory, but it is...  There are about 900 seperate
queries run on the database every second (1,500 at peak times), however it
seems to be leaking memory somewhere.

If somebody could help, or follow up this email with the exact information
that I need to submit to help analyse the problem further that would be
great.

Thanks a lot,
Adam

----- Original Message -----
From: Sasha Pachev <sasha@stripped>
To: Adam Powell <adam@stripped>
Cc: <mysql@stripped>
Sent: Monday, May 24, 1999 8:00 PM
Subject: Re: problem with indexing


> Adam Powell wrote:
> >
> > Hi there, I am in desperate need of speeding up a lookup on our
system...
> > from one table, however many different columns are accessed (about 5).
> > Could anybody give me some pointers on which columns to index, as
currently
> > none are, and I guess this is slowing it down a lot.
> >
> > Here is the main script (in PHP) it is accessed about 300 times a
second.
> > The three different queries below are pretty much accessed equally.  I
am
> > using PHP v3.0.6 and mySQL 3.22.20 on Sun OS 5.6.  Our database server
has
> > 2x300 Mhz RISC processors and 1GB RAM - if that helps.
> >
> >   if ( $button == "yes" ) {
> >     $str="SELECT * FROM banners WHERE flags&1=1 AND active!=-2";
> >   } else if ( $d->approved == 3 ) {
> >     $str="SELECT banners.* FROM banners, rotation WHERE
> > rotation.username='$username' AND
> > rotation.banner_name=banners.banner_name AND banners.active!=-2";
> >   } else {
> >     $str="SELECT * FROM banners WHERE (clicks_today < max_day OR
max_day=-1)
> > AND active=$rand";
> >   }
> >
> > Any ideas on how to speed this up with indexes?  The description of the
> > table is below...
> >
> > mysql> describe banners;
> > +--------------+-----------------+------+-----+---------+-------+
> > | Field        | Type            | Null | Key | Default | Extra |
> > +--------------+-----------------+------+-----+---------+-------+
> > | username     | char(10) binary |      | MUL |         |       |
> > | banner_name  | char(10) binary |      | PRI |         |       |
> > | url          | char(120)       |      |     |         |       |
> > | views_total  | int(11)         |      |     | 0       |       |
> > | clicks_total | mediumint(9)    |      |     | 0       |       |
> > | views_today  | int(11)         |      |     | 0       |       |
> > | clicks_today | mediumint(9)    |      |     | 0       |       |
> > | date_started | char(11)        |      |     |         |       |
> > | active       | tinyint(4)      |      |     | 0       |       |
> > | max_day      | mediumint(9)    |      |     | 0       |       |
> > | image_url    | char(120)       |      |     |         |       |
> > | alt_text     | char(100)       |      |     |         |       |
> > | flags        | smallint(6)     |      |     | 0       |       |
> > | max_rate     | smallint(6)     |      |     | 0       |       |
> > | payment_rate | smallint(6)     |      |     | 0       |       |
> > +--------------+-----------------+------+-----+---------+-------+
> >
> > Adam
> >
> >
>
> I can think of a couple of things:
>
> - add an index spanning flags and active (will slow down
> inserts, though)
> - add an index spanning max_day and clicks_today
> - replace username char(10) with user_id int not null
> primary key
>
> I would also recommend testing the changes on a
> developement machine first and benchmarking them
> thoroughly before putting it into production.
>
> --
> Sasha Pachev
> http://www.sashanet.com/ (home)
> http://www.direct1.com/ (work)
>

Thread
problem with indexingAdam Powell24 May
  • Re: problem with indexingSasha Pachev24 May
  • Re: problem with indexingAdam Powell25 May
    • Re: problem with indexingMichael Widenius2 Jun
  • Re: problem with indexingSasha Pachev25 May
    • Duplicate entriesRay Brighenti25 May
      • Re: Duplicate entriesThimble Smith25 May
      • Duplicate entries the Log resultsRay Brighenti25 May
      • Re: Duplicate entriesErik E Rantapaa25 May
      • Re: Duplicate entriesChris Trown25 May
  • problem with indexingMichael Widenius2 Jun