mozilla

Revision 60910 of Performance

  • Revision slug: Storage/Performance
  • Revision title: Performance
  • Revision id: 60910
  • Created:
  • Creator: BrettWilson
  • Is current revision? No
  • Comment /* Caching */

Revision Content

Introduction

mozStorage uses sqlite as the database backend. It has generally good performance for a small embedded database. However, many things case various database operations to be slow.

Queries

Many times careful reordering of the SQL statement or creating the proper indices can give much better performance. See the sqlite optimizer overview on the sqlite web site for information on how sqlite uses indices and executes statements.

You might also want to try to the "explain" feature on your statements to see if they are using the indices you expect. Type "explain" followed by your statement to see the plan. For example, explain select * from moz_history; The results are hard to understand, but you should be able to see whether it is using indices.

You can download the command line tool from the sqlite download page. Be sure you have the same version command line tool (or possibly newer) as what Mozilla is using. Currently (2006-04-10) Mozilla uses sqlite 3.3.4 but the latest precompiled version of the command line tools is not available for all platforms. This will lead to errors that say "database is encrypted" because the tool is not able to recognise the file format. You may want to check the SQLITE_VERSION definition in {{template.Source("db/sqlite3/src/sqlite3.h")}} for the current version if you are having problems.

Caching

Sqlite has a cache of database pages in memory. It keeps ones associated with the current transaction so it can roll them back, and it also keeps recently used ones so it can run faster.

By default, it only keeps the pages in memory during a transaction (if you don't explicitly open a transaction, one will be opened for you enclosing each individual statement). At the end of a transaction, the cache is flushed. If you subsequently begin a new transaction, the pages you need will be re-read from disk (or hopefully the OS cache). This makes even simple operations block on OS file reads, which can be prohibitive on some systems with bad disk caches or networked drives.

Disk writes

Revision Source

<h2 name="Introduction"> Introduction </h2>
<p>mozStorage uses sqlite as the database backend. It has generally good performance for a small embedded database. However, many things case various database operations to be slow.
</p>
<h2 name="Queries"> Queries </h2>
<p>Many times careful reordering of the SQL statement or creating the proper indices can give much better performance. See the <a class="external" href="http://www.sqlite.org/optoverview.html">sqlite optimizer overview</a> on the sqlite web site for information on how sqlite uses indices and executes statements.
</p><p>You might also want to try to the "explain" feature on your statements to see if they are using the indices you expect. Type "explain" followed by your statement to see the plan. For example, <code>explain select * from moz_history;</code> The results are hard to understand, but you should be able to see whether it is using indices.
</p><p>You can download the command line tool from the <a class="external" href="http://www.sqlite.org/download.html">sqlite download page</a>. Be sure you have the same version command line tool (or possibly newer) as what Mozilla is using. Currently (2006-04-10) Mozilla uses sqlite 3.3.4 but the latest precompiled version of the command line tools is not available for all platforms. This will lead to errors that say "database is encrypted" because the tool is not able to recognise the file format. You may want to check the SQLITE_VERSION definition in {{template.Source("db/sqlite3/src/sqlite3.h")}} for the current version if you are having problems.
</p>
<h2 name="Caching"> Caching </h2>
<p>Sqlite has a cache of database pages in memory. It keeps ones associated with the current transaction so it can roll them back, and it also keeps recently used ones so it can run faster.
</p><p>By default, it only keeps the pages in memory during a transaction (if you don't explicitly open a transaction, one will be opened for you enclosing each individual statement). At the end of a transaction, the cache is flushed. If you subsequently begin a new transaction, the pages you need will be re-read from disk (or hopefully the OS cache). This makes even simple operations block on OS file reads, which can be prohibitive on some systems with bad disk caches or networked drives.
</p>
<h2 name="Disk_writes"> Disk writes </h2>
Revert to this revision