in reply to Re: Migrating to static structure?
in thread Migrating to static structure?

Thanks for your reply.
Load balancing is not a solution as we already have it (done with F5 directing traffic to 4 PIII/600Mhz www Dell machines and 3 similiar MySQL servers).
mod_perl tuning is also not a solution - giving these SELECTS manually from mysql client takes similiar ammount of time (up to 2 seconds for 1 SELECT), so probably it won't speed us up too much :-(
Redesiging the MySQL structure? Hmm, maybe, but I'm not in the mood of rewriting 3MB of Perlcode right now.
Another solution that comes to my head is some kind of cache, but as for now no idea how it should look...


Thanks, Peter

Replies are listed 'Best First'.
Re: Re: Re: Migrating to static structure?
by ignatz (Vicar) on Aug 01, 2002 at 10:23 UTC
    3MB??!!

    It's bloated size ( /. 3k, PerlMonks 6k, Salon 1.3MB ) is all the more reason to refactor. If it is not worth it, it should be scrapped all together. No matter what, it sounds like a cache is the only thing that would really help in the mean time. 

    ()-()
 \"/
  `
[reply]
      Yeap,

      [pit@tux devel]$ du -s cgi-local
      3032 ./cgi-local

      Ok, probably some scraps are there too, but it's at least about 2.5MB (I'm counting here all the scripts, custom modules and libraries)

      Peter
[reply]
Re: Re: Re: Migrating to static structure?
by Tomte (Priest) on Aug 01, 2002 at 11:31 UTC

    As a polite question from someone fallen in this pit once: Are you sure the indexes/indices(sp?) set for the tables are used in your queries?
    It seems unbelievable how well a database can operate if the indices set match the queries made. (From >10sec to <0.02 in my case for )

    another option is to look, if you can optimize the database-server itself, if not alreday done, to better accomodate to your special circumstances.

    Maybe a look into the slashcode or everything helps you find a cache solution.

    regards,
    tomte
[reply]
      Yeap, all the indices are set for the tables.
      The problem is, that some of these selects are making joins from 3-5 tables and that's probably slows MySQL down.
      DB servers are also kinda full-blown (as for current budget, running at PIII/600Mhz and 1GB of memory (this "slow" db is about 150MB big)).
      We were concidering switching to PostgreSQL as it was slighty faster with these "5-lines-long-SELECTS" but on the other hand we're using the same MySQL servers with great MySQL preformance for our search engine (Postgres was not so good at fulltext search).

      Peter
[reply]
        some of these selects are making joins from 3-5 tables and that's probably slows MySQL down.

        Yepp, IIRC most indices on the 'later' tables in a join aren't used by MySQL (read that in the annotated html-manual).

        You may consider splitting the 5-lines-long select into smaller ones implementing the join logic in perl, that should provide a noticable speed-up gained by local changes to the perl-code.
        Coupled with a cache this should make things smooth for times to come. Implementing a cache without a db-speedup would be IMHO a half-legged solution, as some visitors would have to wait the 10+ secs on a cache-miss.

        regards,
        Tom
[reply]

In Section Seekers of Perl Wisdom