in reply to mySQL hits or Storable retrieves?

We used the approach that you describe and it worked fine. In our case, we had a fully-normalized schema which required a six table join to get to useful data. The queries weren't terribly slow, but we were expecting lots of them.

If you want to be buzzword compliant, call your system a 'data warehouse.' Commercial database companies will sell you this same type of solution starting at six figure USD prices. I sat through a long sales pitch. As far as I could tell, your idea is the only database improvement involved in data warehousing.

I was able to get the mod_perl processes to share the memory used by the Storable data structure. This way the many http daemons didn't consume too much RAM. We had three data structures using a total of about 100M bytes of RAM.

You have to set the operating system process size limit large enough so that you don't run out of memory for the data structure.

One very minor gotcha we ran into was that the Storable data was OS dependent. It wasn't a network order problem; we just upgraded our web server OS and somehow it wouldn't read Storable data from the old OS any more. It shouldn't have had that problem, so there was probably a bug somewhere.

It should work perfectly the first time! - toma

Replies are listed 'Best First'.
Re: Re: mySQL hits or Storable retrieves?
by Hero Zzyzzx (Curate) on Oct 02, 2001 at 21:00 UTC

    OH MAN IS THAT SWEET!

    I just got the "stored array of arrays" thing working and it's so damn fast! My god!

    This is soo cool! Basically, I'm going to pre-create each of my accesslevel/category combinations and then store them on disk with Storable. Then on each invocation, I just need to retrieve the structure, do a little massaging, and VOILA! I've got my complex data structure, created with multiple DBI queries, off disk.

    My preliminary testing shows that it's at least 50% faster, and this is just when I'm doing 1 DBI query(the whole reason I needed to do this was because I'm going to need to do more than 1 query) and storing the structure on disk, not in memory.

    I'll still use DBI for selecting individual documents and lookups, but for the complicated, multi-query stuff I'm going Storable!

    -Any sufficiently advanced technology is
    indistinguishable from doubletalk.

[reply]
      One simple question: Why are you storing your structure on disk? Why not use a CLOB or BLOB (the datatype is called TEXT in MySQL I think, but I forget) and store it in a table?

      You may have to think some about how to physically structure the database doing this, but you wouldn't have to worry about file locking and other issues the database can abstract you away from.

      Just a thought ....

[reply]
        The common reason for using simple file storage for a cache rather than a denormalized RDBMS table is that the file storage is much faster and consumes fewer resources. There's no network transfer, no SQL parsing, and no logging. And with the Linux file system hot files are generally kept in RAM, making the file system a sort of shared memory cache. It may seem less elegant, but the speed can't be beat and CPAN modules make it really easy.
[reply]

In Section Seekers of Perl Wisdom