Databases Reference
In-Depth Information
Tuning Your Application and Database Driver to Minimize Memory Use
Here are some general guidelines to minimize memory use:
•
Reduce the number of open connections and prepared statements
—Open
connections use memory on the client and on the database server. Make sure
that your application closes connections immediately after it's finished with
them. If your application uses connection pooling and the database server
(or application server) starts to experience memory problems, try tuning the
connection pool to reduce the number of connections in the pool.
Alternatively, if your database system and database driver supports reau-
thentication, you may be able to use it to minimize the number of connec-
tions required to service your application.
Using statement pooling with connection pooling complicates the memory
situation exponentially. On the database client, client resources that correlate
to each pooled statement are stored in memory. On the database server, each
pooled connection has a statement pool associated with it that's also main-
tained in memory. For example, if your application uses 5 pooled connec-
tions along with 20 prepared statements, each statement pool associated
with those 5 connections may potentially contain all 20 prepared state-
ments. That's 5 connections
20 prepared statements = 100 prepared state-
ments, all maintained in memory on the database server. If you use
statement pooling and the client or database server starts to experience
memory problems, try tuning the statement pool to reduce the number of
prepared statements in the pool. See “Using Statement Pooling with
Connection Pooling,” page 238, for more information.
×
•
Do not leave transactions active for too long
—The database must write
every modification made by a transaction to a log that is stored in memory
on the database server. If your application uses transactions that update large
amounts of data without committing modifications at regular intervals, the
application can consume a substantial amount of database memory.
Committing a transaction flushes the contents of the log and releases mem-
ory used by the database server. See “Managing Commits in Transactions,”
page 22, for guidelines on committing active transactions.
•
Avoid retrieving large amounts of data from the database server
—When
the database driver retrieves data from the database server, it typically stores
that data in a result set that is maintained in memory on the client. If your
application executes queries that retrieve millions of rows, memory can be
used up quickly. Always formulate your SQL queries to retrieve only the data
you need.
