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)
>
