Edit Rename Changes History Upload Download Back to Top

Store Schema Optimization

This comes from the same folks who put up the Store Schema Diagram.
See also wiki.cs.uiuc.edu/VisualWorks/Store+speedup+tips.
See also Store Schema Optimization (Views)

I was recently faced with the task of doing a considerable amount of Store garbage collection. However, the speed of the garbage collection, or lack thereof, was making the task virtually impossible to complete. So I decided to see if there was something that could be done to improve the performance. After analyzing the garbage collection methods it became apparent that (a) garbage collection is quite a complex process that only a Store expert should attempt to modify, and (b) garbage collection requires the execution of numerous SQL queries.

So I decided to focus on the second issue and see, if perchance, there was some way to tune the database server to execute the queries more efficiently. I was astonished to find that the Store installation defines only one index. It does define primary keys and most databases will automatically create indexes for primary keys. However, in practice most queries, such as selecting all the packages belonging to bundle or all the versions of a package of a given name, will not benefit at all from the primary key indexes. Ideally, the foreign key columns and columns such as "name" that are referenced in the where clauses of SQL statements should be indexed.

So I defined new indexes for all the tables in the Store database for all the foreign keys and "searchable" columns and created inverse unique indexes for all the associative tables (those used to create many to many relationships). Then I ran a test garbage collection on a single package both before adding the indexes and again afterwards. The results:

Time required to garbage collect the package with no indexes: 7 minutes and 49 seconds. Time required to garbage collect the package with indexes: 0 minutes and 6 seconds.

This test was performed on a local repostory (Personal Oracle on my Dell Laptop PIII). Your mileage may vary depending on a number of factor such as:

1) LAN/WAN communication. This test had none so as to isolate the affect of the indexes on the database server itself. In practice using a shared repository the improvement will not be as significant because the SQL queries and corresponding results, of which there are many, must travel accross the LAN/WAN.

(2) Database Server. Issues such as the effectiveness of the server's query optimizer, disk I/O speed, RAM etc will all have an affect.

I've installed these indexes on our team's shared respository. We have not seen any negative consequences of doing so although in theory this will increase the size of the databases and may slow down inserts and updates because of the need to also update the indexes. However, it should improve the performance of all selects, not just those associated with garbage collection. The overwhelming majority of queries are probably simple selects used to populate lists and such for the Store UI. We have seen noticable improvements in other things such as "Switch Database".

So I offer these indexes (see attachments) for any who would like to install them. And for those who would like to, it would be useful if you would share with the vw-dev subscribers some before and after feedback to see what operations and configurations benefit from them and which ones don't.

DROPNEWSTOREINDEXES.sql

NEWSTOREINDEXES.sql

NEWSTOREINDEXESWTS.sql

I've included a generic SQL script for use with any SQL database that will create the indexes. In addition, there is a version that includes a tablespace definition (for Oracle) that places the indexes in the Store tablespace. (A database optimization that is sometimes employed by some is to create a separate tablespace just for indexes and to place the files of that tablespace in a directory that is served by a different disk controller than the one serving the table data. That way index I/O and table data I/O are not competing with each other. If you want to do something like that feel free to alter the scripts as needed.) Finally, I've included a script to remove the indexes in case you don't find them beneficial.

The SQL Scripts:

Comments

I can vouch for these being highly useful; I had avoided using Store Garbage Collection for over two years before getting this enhancement!

PostgreSQL doesn't allow for ascending/descending indexes, so for that DB the 'ASC' keyword must be removed prior to defining the indexes. Pete Hatch

SQL Server
I don't really know what I'm doing, so applying this to Microsoft SQL Server took a little while. In case anybody else is in the same boat, here's what I found - experts please correct!

each SQL statement is:
CREATE INDEX Index_Name ON Table_Name ( columnName );
You need to make some changes to the default SQL script provided above 'for other DB'. Email me (stevek@metacase.com) for the file I used, or follow these instructions (might help others on non-Oracle dbs):

Edit Rename Changes History Upload Download Back to Top