pit has asked for the wisdom of the Perl Monks concerning the following question:

Hi everyone! Ok, here is the problem:
@ work we have some system (kinda portal with user's discussions) which runs under mod_perl and MySQL.
But, we have some major problems with preformance - it takes about 10 seconds to get the result while browsing the structure - because of all the SELECTS that have to be done (it takes about 8 seconds for MySQL to process these SELECTS).
So, while moving to mod_perl is not an answer (we are already here) and changing the structure itself not an aswer too we came to idea of moving the "structure browsing" to static files.
The site suffers a peak load of about 100 sessions per webserver (there are 4 of them, sharing one www diskspace via NFS).
In the peak time some part (at most 1%) of the structure may be rebuild about 1 time for minute.

Now the question(s): does migrating to static files is resonable in our case?
If no - than any ideas to improve the preformance (except buying more hardware :-)
If yes - where we can shot ourselfs in foot? (ok, file-locking while modyfing static files is obvious)

Thanks in advance

Peter

Replies are listed 'Best First'.
Re: Migrating to static structure?
by PodMaster (Abbot) on Aug 01, 2002 at 02:16 UTC
    If no ~ get rid of the NFS if you can, that can be a big performance killer. Reformat your SQL queries, and change your "views" (simplify the final presentation to the user).

    Employ caching, unless you already have, but it sounds like you haven't. With the amount of SELECT's that go around here on perlmonks, the only reason it does as well as it does is due to caching.

    Seeing how most of the time, most of your stuff is static, a good caching mechanism will help a lot, but it would make sense to go the way of building static web pages, ala WebMake.

    If yes - keep the parts that are truly static static, and only the parts that really need to be dynamic, dynamic. I mentioned WebMake above, and I suggest you look into it, if only for the idea of what can be done.

    Another idea, although potentially complicated, is to mix the static and dynamic, via (i)Frame's and or (i)Layer's. Depending on your target audience, this can work out real well.

    Having no idea of what you're doing mod_perl side, and having only dealt with Apache::Registry, I suggest you invest into a mod_perl book, if you haven't already, so you can beef up your mod_perl stuff, and employ a smart caching scheme.

    As for what kind of caching scheme, I'm not really sure, not knowing anything about your database structure or said program, but you might wanna investigate DB_File (Building a Large-scale E-commerce Site with Apache and mod_perl) or Cache::Cache depending on your platform.

    I have made available a win32 ppm of BerkleyDB/DB_File with one of the latest BerkleyDB's(4 something) available on my win32 ppm repository, in case you want something fancy ;)

    ____________________________________________________
    ** The Third rule of perl club is a statement of fact: pod is sexy.

[reply]
      You might also try running EXPLAIN on your MySQL queries, then putting in appropriate indices. This will make selects much quicker, but inserts slightly slower. It sounds like you are doing quite a few more rows than you need. If you decide to use indices, be careful with LIKE clauses, as initial wildcards (LIKE %foo) won't use them.
[reply]
      At first - thanks for your reply.

      As for now NFS seems not to be a preformance killer - right now all the templates and other static files are on NFS server and it seems to work very well. I'm not sure how it will look like heavier traffic on NFS...
      Your idea with mixing dynamic and static content with Frames or Layers seems to be interesting and I already see some places where it can by deployed.
      Obviously I'll also take a look on some caching scheme befeore going completly to static

      Anyway, thanks for help and making me think a bit :-)
      Peter
[reply]
Re: Migrating to static structure?
by vladb (Vicar) on Aug 01, 2002 at 02:13 UTC
    There's a quick solution and that is in buying more actual hardware to boost your server performance. Things such as faster HD and larger (also faster) memory chips. Another option is to purchase another server and set up a 'round robin' system. In such a system, you'll have 2 main servers and one extra PC to serve as a load balancer. All request to your sites come through that load balancer. It's job is simply to monitor the loads on both supporting 'web' servers and direct traffic where it will be accomodated the best.

    Aside from this, you've got to have a close estimate of specific components involved in your system and their cost in terms of overall performance. mod_perl could generally be optimized pretty well to handle even 100 symultaneous sessions. Check out this mod_perl: Performance Tuning article for example. You should find quite a few things that you might of missed from your existing set up. Just add those ones and see your performance improve! ;-)

    _____________________ 
    # Under Construction

    [download]
[reply]
[d/l]
      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
[reply]
        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]

        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]
Re: Migrating to static structure?
by ignatz (Vicar) on Aug 01, 2002 at 02:54 UTC
    There have been some interesting discussions here about static websites: Two things that spring to mind while reading your post: The amout of time taken for your SQL is really scary. Perhaps a code/SQL review is in order? I'd seriously consider dumping this beast and moving over to a more mature publishing system.

    Also, your comment concerning your fears of modifying static content seems to indicate that you aren't using a staging server for development. If that's the case, working with one should help you to sleep better at night. 

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

Back to Seekers of Perl Wisdom