Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re: algorithm help for determining efficient db data retrieval

by tilly (Archbishop)
on Apr 06, 2004 at 02:14 UTC ( [id://342808]=note: print w/replies, xml ) Need Help??


in reply to algorithm help for determining efficient db data retrieval

Without extensive analysis and knowledge of your particular usage parameters, no solution is best. There are solutions that are good, but situations can be found where one outperforms another, and vice versa.

The usual heuristic for this kind of problem is to fetch back well defined and simple sets of properties every time you fetch an object (typically "all") and then cache results (eg with a strategy similar to Memoize) so that you can decide on the fly what objects you don't need to fetch back again.

If you're fetching lots of things for differing reasons then trying to be really clever about being minimal in what you fetch generally loses to having big simple categories. Plus a KISS strategy takes less work.

A widely used module that attempts to address this problem is Class::DBI. See the section on Lazy Population.

  • Comment on Re: algorithm help for determining efficient db data retrieval

Replies are listed 'Best First'.
Re: Re: algorithm help for determining efficient db data retrieval
by AidanLee (Chaplain) on Apr 06, 2004 at 02:42 UTC

    The Lazy Population section of the Class::DBI docs was enlightening. Ideally i'd like to be able to hang onto the top x most frequently used objects in memory, but at the moment I live in a cgi world. That will be a possibility when I migrate the platform to mod_perl, but for now I'm stuck with limited-to-no caching between requests.

      Not true. Your RDBMS will cache all the most recently accessed data for you in memory without you lifting a finger. If you feed it more memory it will cache more. Here is a sample my.cnf that we use on servers with 2GB of RAM running squid and apache as well. Compared to the default settings throughput is about tripled for the way we use it.

      [root@devel3 root]# cat /etc/my.cnf [client] socket=/tmp/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/tmp/mysql.sock #set-variable=wait_timeout=3600 set-variable=key_buffer=640M set-variable=max_allowed_packet=32M set-variable=table_cache=512 set-variable=sort_buffer_size=32M set-variable=record_buffer=32M set-variable=read_buffer_size=32M set-variable=myisam_sort_buffer_size=64M set-variable=thread_cache=8 set-variable=query_cache_size=32M set-variable=tmp_table_size=32M [snip]

      cheers

      tachyon

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://342808]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others about the Monastery: (2)
As of 2024-04-26 05:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found