Scratchy       

 

 

 
 
Home | About | Download | Docs | Screenshots | Links | Sourceforge |

SourceForge.net Logo Donate 
to this project

Choosing a SQL database

Scratchy currently requires either MySQL or SQLite databases. In addition to these packages, Scratchy also requires the respective Python module for the desired database.

MySQL Requirements SQLite Requirements
MySQL SQLite
Mysql-Python module PySQLite

Notes:

  • If you are downloading MySQL select the standard production release. Depending on which package you download, you may also need the client libraries and headers as well (for use with the MySQL Python module).

  • If you are downloading SQLite and PySQLite, please note that PySQLite v0.4.3 contains a bug at line 348. You will need to fix this before building pysqlite (you can do it after too, you will just need to re-install it). The bug is easy to fix, just edit the file pysqlite-0.4.3/sqlite/main.py. On line 348, move the right paren from after TupleType to the location following self.rowclass. When corrected the line should read:

    if type(self.rowclass) is TupleType:


    MySQL

    MySQL is a leading open source SQL server and offers excellent performance. Currently, Scratchy's parser module is optimized to use MySQL. Recent personal benchmarks of Scratchy's parsing module using MySQL runs at about 700 Apache log lines per second. For comparison purposes, using SQLite, these numbers drop to about 200 lines/second.

    Since MySQL is a database server, you will need to configure and administer the server as necessary. It is very important that you create a user for yourself that has been granted full access because Scratchy needs the ability to create databases and tables. If you cannot install a MySQL server and/or you cannot create MySQL users then you will not be able to use Scratchy with the MySQL database.

    Please refer to MySQL Guide to Adding Users

    Many Linux distributions have MySQL installed. However, you should make sure that you are using v4.0.13 or newer because there is a bug in earlier versions which results in table corruption (the bug relates to the fact that Scratchy disables and enables keys to optimize performance-- in versions prior to 4.0.13 MySQL failed to handle the re-enabling of the keys properly).

    Once you have MySQL setup, you will need to configure Scratchy for MySQL.

    SQLite

    SQLite is a database without the server. Therefor it is much easier to setup and lighter weight (since there is no additional server process). After installation of SQLite and PySQLite you will need to configure Scratchy for SQLite.

    Summary

  • If you have familiarity with MySQL then choose MySQL.
  • If you are comfortable installing/configuring servers: MySQL.
  • If you have a low traffic web site (less than 10k hits/month): SQLite.

    I've installed MySQL several times and it's pretty straight-forward. MySQL.com has solid documentation and there are several books available.

    In developing Scratchy I used 2 log files as benchmarks (one with 15k records, the other with 360k). Performance times were relatively the same for the small log file, however, MySQL was about 3.5x faster for the larger log file. MySQL allows for more areas of optimization than sqlite so I've been able to take advantage of some of these areas in developing Scratchy.

    Additionally, MySQL is more efficient than SQLite in terms of disk space. For the 360k line log file, SQLite occupied 196 MB of diskspace. MySQL stored the data in less than half (83 MB) the disk space of SQLite.