I think I haven't explained the memory issue correctly :
The example Kern gave is :
"SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
"LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
"WHERE Job.JobId IS NULL LIMIT 300000";
and it only fails if I remove the "LIMIT 3000000".
The problem you mention is that you select 300,000 records from the database.
What happens then is that your SQL CLIENT process will allocate all these
records in memory (except if you use a cursor, but that's not the question).
As a consequence, if you remove the LIMIT code, the client will have to store
millions of records in memory (for you to use them). The server process won't
have to.
There is no such code in the batch inserts :
In the batch DB insert code, there is no SELECT statement (at least no SELECT
statement retrieving rows). It means no data is sent from the SQL server
process to the SQL client process.
The only memory allocated will be by the SQL backend. This memory is already
limited by administrative limits put by the DBA. Even if the DBA hasn't put
these limits, they are in place, as there are default (small) values put
there by the installer...
The memory allocated by the SQL server process is used for sorting, hashing,
small buffers to send data to the SQL client process. These resources are
controlled (and better be, or a single query could easily kill a database,
using a cross join for instance).
Bigger sorts go to disk, in chunks, in temp files...
On Wednesday 21 March 2007 15:25, Alan Brown wrote:
> On Wed, 21 Mar 2007, David Boyes wrote:
> >> - First, have a default limit of the number of records that will be
> >> inserted
> >> in any one batch request. This should guarantee that an out of memory
> >> problem will not normally occur.
> >
> > Can we calculate this based on available memory at execution time?
> > General tuning wisdom for the commercial databases (DB/2, Oracle, etc)
> > target about 60% of real memory as the goal for this kind of
> > segmentation. That allows some query optimization overhead, and a little
> > wiggle room if the optimizer guesses wrong.
>
> In MySQL: /etc/my.cnf
>
> ====
> # If your system supports the memlock() function call, you might want to
> # enable this option while running MySQL to keep it locked in memory and
> # to avoid potential swapping out in case of high memory pressure. Good
> # for performance.
> memlock
> ====
>
> Comment this out. If MySQL runs out of RAM it will hit swap and slow down,
> but it will not crash.
>
>
> I did send Kern some updated query forms some time back for dbcheck which
> reduced the load and sped things up a LOT, but I can't find them now.
>
> (Basically, SELECT COUNT(*) instead of SELECT, etc)
>
> No need to bring out the rows and count them when the SQL database can do
> it for you.....
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
