Re: mod_perl memory consumption
At 2:55 PM +1000 8/31/01, Jeremy Howard wrote:
>Paul DuBois wrote:
>> mysql_store_result/mysql_use_result determine whether or not the server
>> sends all the rows to the client at once, or one at a time as the client
>> asks for them. mysql_store_result is therefore more memory intensive
>> for the client (which must hold the entire result set).
>> mysql_use_result is less memory intensive for the client, which needs
>> memory only for one row at a time, but can cause other processes trying
>> to use the server to block, because the tables from which you're
>> retrieving rows are read-locked for the duration of the query (that is,
>> until the client actually fetches the last row).
>>
>For more information see Paul's great book 'MySQL' (New Riders), p257-259.
>If you're not lucky enough to have the book, there's a brief description
>here:
>http://perl.apache.org/guide/databases.html#mysql_use_result_vs_mysql_store
Note that the documentation at this link uses the notation
mysql_store_result() and mysql_use_result(), as though these
things are functions. They aren't. They're statement handle
attributes, typically accessed like this:
$sth->{mysql_store_result} = 1 or 0
$sth->{mysql_use_result} = 1 or 0
Re: mod_perl memory consumption
Paul DuBois wrote: > mysql_store_result/mysql_use_result determine whether or not the server > sends all the rows to the client at once, or one at a time as the client > asks for them. mysql_store_result is therefore more memory intensive > for the client (which must hold the entire result set). > mysql_use_result is less memory intensive for the client, which needs > memory only for one row at a time, but can cause other processes trying > to use the server to block, because the tables from which you're > retrieving rows are read-locked for the duration of the query (that is, > until the client actually fetches the last row). > For more information see Paul's great book 'MySQL' (New Riders), p257-259. If you're not lucky enough to have the book, there's a brief description here: http://perl.apache.org/guide/databases.html#mysql_use_result_vs_mysql_store
Re: mod_perl memory consumption
>[EMAIL PROTECTED] (Perrin Harkins) wrote: >>> I guess that DBI gets all records if you use fetchall_array|hashref >>> and that it is else OK. >> >>No, the behavior that I saw was when fetching a row at a time. Behind the >>scenes, DBI would fetch ALL the rows into RAM and then iterate over them. >>This may have been changed since then. It was a couple of years ago. > >It's still true, mysql will fetch all records at once, whether you ask >for them or not. You can control it with mysql_(use|store)_result, >which is documented as a feature so I think it's not going away anytime >soon. mysql_store_result/mysql_use_result determine whether or not the server sends all the rows to the client at once, or one at a time as the client asks for them. mysql_store_result is therefore more memory intensive for the client (which must hold the entire result set). mysql_use_result is less memory intensive for the client, which needs memory only for one row at a time, but can cause other processes trying to use the server to block, because the tables from which you're retrieving rows are read-locked for the duration of the query (that is, until the client actually fetches the last row). > > > ------ > Ken Williams Last Bastion of Euclidity > [EMAIL PROTECTED]The Math Forum
Re: mod_perl memory consumption
[EMAIL PROTECTED] (Perrin Harkins) wrote: >> I guess that DBI gets all records if you use fetchall_array|hashref >> and that it is else OK. > >No, the behavior that I saw was when fetching a row at a time. Behind the >scenes, DBI would fetch ALL the rows into RAM and then iterate over them. >This may have been changed since then. It was a couple of years ago. It's still true, mysql will fetch all records at once, whether you ask for them or not. You can control it with mysql_(use|store)_result, which is documented as a feature so I think it's not going away anytime soon. ------ Ken Williams Last Bastion of Euclidity [EMAIL PROTECTED]The Math Forum
Re: mod_perl memory consumption
> I use a single object (lightweight) which I construct and then just call > its method in a loop, once for each message. If I don't use globals, use > strict, undef $SomeVar I'm doing the right thing. All the "my" variables > should dissapear once the method call is over. It sounds like you're doing it right. The lexical variables will actually hold onto their memory if you don't undef them, but if you're re-using them they should never get bigger than the largest value you put in them. > But nevertheless memory goes > on and on. Maybe this is because I tweaked vpopmail.pm (xs code) to include > information about user quotas which wasn't present there (perl hash) ? Could be. You could try commenting things out until it stops growing. > > It sounds like you have some data structure which you are adding to and > > never clearing out. One thing to be careful of is the MySQL DBI driver. > > The last time I used it, it fetched all returned rows into memory. With a > > large result set, that could take up tons of RAM. > > I guess that DBI gets all records if you use fetchall_array|hashref and that > it is else OK. No, the behavior that I saw was when fetching a row at a time. Behind the scenes, DBI would fetch ALL the rows into RAM and then iterate over them. This may have been changed since then. It was a couple of years ago. - Perrin
Re: mod_perl memory consumption
> > Why is the memory consumption so high ? According to my calculations it > > should take less than 100 kilobytes. > > What are you basing that on? I use a single object (lightweight) which I construct and then just call its method in a loop, once for each message. If I don't use globals, use strict, undef $SomeVar I'm doing the right thing. All the "my" variables should dissapear once the method call is over. But nevertheless memory goes on and on. Maybe this is because I tweaked vpopmail.pm (xs code) to include information about user quotas which wasn't present there (perl hash) ? > It sounds like you have some data structure which you are adding to and > never clearing out. One thing to be careful of is the MySQL DBI driver. > The last time I used it, it fetched all returned rows into memory. With a > large result set, that could take up tons of RAM. I guess that DBI gets all records if you use fetchall_array|hashref and that it is else OK. tnx. for reply mire
Re: mod_perl memory consumption
[EMAIL PROTECTED] (Perrin Harkins) wrote: >It sounds like you have some data structure which you are adding to and >never clearing out. One thing to be careful of is the MySQL DBI driver. >The last time I used it, it fetched all returned rows into memory. With a >large result set, that could take up tons of RAM. Right, that sounds like the culprit. Miroslav, see the 'mysql_use_result' parameter in 'perldoc DBD::mysql', which will let you store the result in a mysql server table rather than in client memory. ------ Ken Williams Last Bastion of Euclidity [EMAIL PROTECTED]The Math Forum
Re: mod_perl memory consumption
> I wrote a program that converts 5 gigs of emails stored in mysql to > phisical messages on disk (resulting in approximately 10 gigs). The program > consumes way to much memory although I've wrote it in a very clean way (use > strict, no globals, use of udef $var; to help free memory). I start it > telneting to :80 and through time process takes from 15 megs to more than > 100 megs and only about 20% of messages are converted so far. The program > uses vpopmail.pm and should run approximately one day. Maybe it would be > faster if I used Apache::FakeRequest or something similar eleminating use of > mod_perl (plain cgi) ? I'm using HTML::Mason. It will definitely not be faster if you use CGI. It may take care of your memory problems, but only by avoiding them. You should be able to fix the problem in your code and use mod_perl for this. > Why is the memory consumption so high ? According to my calculations it > should take less than 100 kilobytes. What are you basing that on? It sounds like you have some data structure which you are adding to and never clearing out. One thing to be careful of is the MySQL DBI driver. The last time I used it, it fetched all returned rows into memory. With a large result set, that could take up tons of RAM. - Perrin
