Have you tried reworking your queries a bit? I try to avoid using "IN"
as much as possible. What does EXPLAIN say about how the long queries
are executed? If I have to match something against a lot of values, I
select the values into a HEAP table and then do a join. Especially if
YOU are going to be reusing the values within the current session.
Are you storing images (img1, img2, img3) in the database? I would
recommend against that in a high load database, it bloats the database
size forcing the database to use a lot more RAM to cache the database.
It also prevents you from creating a database with fixed length
records. Keeping the images as files will push the loading of the
images out to the file system and web server.
What kind of RAID setup do you have? You just said you had 73GB 10K
disks. Why didn't you go with 15k disks? Cost?
On Jan 26, 2004, at 3:42 PM, Adam Goldstein wrote:
> Yes, I saw this port before... I am not sure why I cannot allocate
> more ram on this box- It is a clean 10.3 install, with 10.3.2 update.
> I got this box as I love OSX, and have always loved apple, but, this
> is not working out great. Much less powerful (and less expensive)
> units can do a better job of this (the entire site was run on ONE dual
> athlon box with 3G ram, and it seems to have made -NO- difference
> moving the mysql to the dedicated G5.)
>
> Obviously, there is something wrong somewhere- And, I need to find
> where. My client (site creator) is depending on me to help him boost
> the ability of the site to handle more users, but we've always been
> able to do it on a light budget. I need to know where to look first,
> as we are running out of time... His users are a fickle bunch, and
> will likely migrate off to other sites if this slowness continues (it
> has been degrading for past 3-4 months from slow at peak, to dead for
> all peak hours).
>
> These are example queries from the heavier pages:
>
> 1: SELECT E AS name, id_parent, fvc_parent_path, fvc_parent_path_E AS
> fvc_parent_path_txt, fvc_childs, items FROM categories WHERE id = 4204
> LIMIT 0,1
> Time: 0.0004551410675 sec / Type: Buffered
> 2: SELECT e.id_enchere, e.id_vendeur, e.titre, e.img1, e.img2, e.img3,
> e.prix_depart, e.price_present, e.fnb_buyitnow_price, e.date_debut,
> e.date_debut_origine, e.date_fin, e.fin_bids, e.fvc_active FROM
> enchere e WHERE e.id_categorie IN (4204) AND (e.date_fin >=
> '2004-01-26 14:41:59') ORDER BY date_fin ASC LIMIT 0, 80
> Time: 37.60733294 sec / Type: Buffered
> 3: SELECT COUNT(e.id_enchere) AS nbre FROM enchere e WHERE
> e.id_categorie IN (4204) AND (e.date_fin >= '2004-01-26 14:41:59')
> Time: 0.9267110825 sec / Type: Buffered
> 4: INSERT INTO td_loadtime SET fvc_page = '/liste.php', fvc_query =
> 'language=E&cat=4204&sql_log=Y', fdt_date = '2004-01-26', fdt_time =
> '14:42:38', fnb_seconds = 39.22
> Time: 0.005410909653 sec / Type: Buffered
>
> making the page take > 40 seconds to load.
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
