Re: Should prepare_cached() manage its total size or memory usage?
On Mon, Apr 04, 2005 at 12:47:56AM -0400, Steven Lembark wrote: -- Mark Stosberg [EMAIL PROTECTED] Hello, I have a database application that selects about 50,000 rows one by one, does some process in Perl, and then executes a SELECT statement for each one, with slight variations in the SQL and parameters. I was using prepare_cached() on this repeatedly called SELECT statement. Depends on how you're using the caching. If it looks something like: my $sth = prepare_cached( 'select foo from bar where ( bletch = ?)' ); Then this should generate a single query and re-cycle it for later use. If you are using ANYthing hardcoded specific to the query then caching will just leave a huge number of row-specific queries lying around; which is probably not what you want. Q: Are the placeholders in the existing query(s) or do they use hard-coded values? Thanks for the response. From reviewing the code, there area small number of hard-coded values, but they never change from query to query. Example: active = 't' . I think there could be a dozen or two bind variables, though. There are some IN clauses which might have a dozen bind variables each. The good news for me has been that switching back to use prepare() has adequate performance. In fact, for the same run with 50,000 rows to process, the overall time is about the same. Mark -- . . . . . . . . . . . . . . . . . . . . . . . . . . . Mark StosbergPrincipal Developer [EMAIL PROTECTED] Summersault, LLC 765-939-9301 ext 202 database driven websites . . . . . http://www.summersault.com/ . . . . . . . .
Re: Should prepare_cached() manage its total size or memory usage?
-- Mark Stosberg [EMAIL PROTECTED] Hello, I have a database application that selects about 50,000 rows one by one, does some process in Perl, and then executes a SELECT statement for each one, with slight variations in the SQL and parameters. I was using prepare_cached() on this repeatedly called SELECT statement. Depends on how you're using the caching. If it looks something like: my $sth = prepare_cached( 'select foo from bar where ( bletch = ?)' ); Then this should generate a single query and re-cycle it for later use. If you are using ANYthing hardcoded specific to the query then caching will just leave a huge number of row-specific queries lying around; which is probably not what you want. Q: Are the placeholders in the existing query(s) or do they use hard-coded values? If you have a small number of placeholder queries then this is odd behavior; if not then all you've done is generate the non-recycled-cache-from-hell and the behavior is normal. -- Steven Lembark 85-09 90th Street Workhorse ComputingWoodhaven, NY 11421 [EMAIL PROTECTED] 1 888 359 3508
Should prepare_cached() manage its total size or memory usage?
Hello, I have a database application that selects about 50,000 rows one by one, does some process in Perl, and then executes a SELECT statement for each one, with slight variations in the SQL and parameters. I was using prepare_cached() on this repeatedly called SELECT statement. Recently, as the number of SELECTs crept upwards, the script started to die near the need with out of memory errors, like: Out of memory during request for 8864 bytes, total sbrk() is 415438848 bytes! Oddly, MRTG graphs of the machines memory usage during the run didn't show a significant fluctuation. (The machine has 2 Gigs of RAM, plus swap). Switching to use just a 'prepare()' resolved this issue, after some hours of tracking down the issue. I believe I understand what was happening-- the logic kept caching new unique handles in the cache, until the total memory usage was too much. Would it be reasonable to expect DBI to perform more gracefully in this case? (Or maybe this is more of a driver issue? I use DBD::Pg). My preference would be that I could still use prepare_cached(), but that there would be a maximum cache size, and the oldest unused handles would be auto-expired. A related question: Would the server-side-prepare feature of PostgreSQL 8.0 help with this? Thanks! Mark
Re: Should prepare_cached() manage its total size or memory usage?
Mark, I was using prepare_cached() on this repeatedly called SELECT statement. Recently, as the number of SELECTs crept upwards, the script started to die near the need with out of memory errors, like: Horrible thing, should not happen. I believe I understand what was happening-- the logic kept caching new unique handles in the cache, until the total memory usage was too much. If you are using bind variables, you should have only quite few distinct SQL statements. If these still generate unique handles, that sounds like a serious bug in the driver. If you are not using bind variables, you should not prepare_cached 50.000 statements. The cache is only useful anyway if you re-use the cached entry. From what you tell, it looks like you have 50.000 distinct handles (each used just once) so you gain nothing from using prepare_cached (only waste memory, and a lot of it apparently). I am not sure if prepare_cached() should manage its total size. It is, after all, an advanced feature, so the DBI user should be forced to think about when to use it. If it had an auto-flush feature, you would not have noticed the problem with your program at all. Thilo
