[vwnc] Store /postgres speed optimization ?

Hello all,
I have a question about optimizing the store performance.
My configuration is fairly simple - Store(in the postgres)  resides on
  Red Hat Linux.  There are few clients attaching to it mostly from
windows clients.  As time progresses the publish or reconcile and even
browsing the existing packages taking longer and longer.

I also observed that when I connect to the cincom's Public store I can
reconcile or compare packages faster then on mine.

Apparently there are some store optimization tricks which I am missing.

Does anybody have a list of optimizations I can try to speed things
along, like database indexes, memory allocation for the DB, etc.

TIA,

Mark Pirogovsky
_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

At 08:33 AM 5/28/2008, Mark Pirogovsky wrote:

Hello all,
I have a question about optimizing the store performance.
My configuration is fairly simple - Store(in the postgres)  resides on
  Red Hat Linux.  There are few clients attaching to it mostly from
windows clients.  As time progresses the publish or reconcile and even
browsing the existing packages taking longer and longer.

I also observed that when I connect to the cincom's Public store I can
reconcile or compare packages faster then on mine.

Apparently there are some store optimization tricks which I am missing.

Does anybody have a list of optimizations I can try to speed things
along, like database indexes, memory allocation for the DB, etc.

Make sure you auto-vacuum/analyze. In newer versions I think that defaults to on, but check.

The other thing is that the default Store installation has no indexes (yes, we're going to fix that, but it's not fixed in 7.6). If you load StoreForGlorpVWUI from the distribution you'll get a Store menu item called "Store Workbook". At the very bottom of that there's an expression to create a number of indexes in the database. This may be helpful. Note that extreme robustness of the expression, indicated by the "self halt" if there's an exception. You may take this as an indication that it would be good to make sure your database backup procedures are working properly before trying it :-) I've done this on a number of databases with my own code in them, it shouldn't have a problem, but it's always better to back everything up before modifying databases.



_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Hi Mark!

Mark Pirogovsky wrote:
Not really a store optimization trick, but logging long running
queries by activating the feature in postgresql and analysing
the logs after a week of regular work by the team, we installed
some indexes for the most heavily used columns. You can either
analyse yourself of use the following (rather old) piece of sql
to create some indexes in your database:

CREATE INDEX tw_methods_packageref_index ON tw_methods (packageref);
CREATE INDEX tw_method_classname_index ON tw_method (classname);
CREATE INDEX tw_classrecord_name_index ON tw_classrecord (name);
CREATE INDEX tw_classrecord_definitionid_ind ON tw_classrecord (definitionid);
CREATE INDEX tw_method_sourcecodeid_index ON tw_method (sourcecodeid);
CREATE INDEX tw_package_name_index ON tw_package(name);
CREATE INDEX tw_bundle_name_index ON tw_bundle(name);
CREATE INDEX tw_pkgclasses_packageref_index ON tw_pkgclasses(packageref);
CREATE INDEX tw_pkgclasses_metaclassref_index ON tw_pkgclasses(metaclassref);

If your postgresql is pre version 8, I recommend upgrading,
since store makes extensive use of IN clauses, which were quite
slow before the 8 series of postgresql.

You also might find some advice in the archives of the mailing list.

Regards
  Jan

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Mark Pirogovsky wrote:
> Does anybody have a list of optimizations I can try to speed things
> along, like database indexes, memory allocation for the DB, etc.
>  

In the open repository there is the package 'Store-Speed patches',
I don't think this contains a new schema definition though.


I looked at what else we have here and decided to replicate the latest
couple of versions to the open repository as
bundle StorePatchesBundle.

The latest version should be compatible with 7.6, the others with 7.5.

This bundle also includes several alterations of the UI which you may
dislike, you'll have to review to see what you want yourself - no
documentation other than the code :-/
I think this bundle still does not contain any schema changes (extra
indexes etc) though.


HTH,

Reinout
-------



--
*********************************************************************

Dit e-mailbericht is alleen bestemd voor de geadresseerde(n).

Gebruik door anderen is niet toegestaan. Indien u niet degeadresseerde(n) bent wordt u verzocht de verzender hiervan op de hoogte te stellen en het bericht te verwijderen. Door de elektronische verzending kunnen aan de inhoud van dit bericht geen rechten worden ontleend.

Soops B.V. is gevestigd te Amsterdam, Nederland, en is geregistreerd bij de Kamer van Koophandel onder nummer 33240368.
Soops B.V. levert volgens de Fenit voorwaarden, gedeponeerd te Den Haag op 8 december 1994 onder nummer 1994/189.
**********************************************************************

This e-mail message is intended to be exclusively for the addressee.

If you are not the intended recipient you are kindly requested not to make any use whatsoever of the contents and to notify the sender immediately by returning this e-mail message. No rights can be derived from this message.

Soops B.V. is a private limited liability company and has its seat at Amsterdam, The Netherlands and is registered with the Trade Registry of the Chamber of Commerce and Industry under number 33240368.
Soops B.V. delivers according to the General Terms and Conditions of Business of Fenit, registered at The Hague, The Netherlands on December 8th, 1994, under number 1994/189
**********************************************************************


_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

I did some research on the indexes and found that the query like
select * from pg_indexes where tablename like 'tw_%' and indexName not
like '%pkey' order by tablename

NOTE: I did exclude all of the primary key indexes in the query

does return 43 indexes in the public store, while the same query returns
only 39 in mine.

and it also obvious to me that some of my tables do not have indexes
while some others do have more then in the public store.

So I wonder would it be safe for me to drop all of mine indexes and
rebuilt them using the create statements form the public store.

I did attach the scripts for the indexes in the public store


Jan Weerts wrote:
CREATE INDEX tw_blessing_pkgid_idx ON tw_blessing USING btree (pkgid)
CREATE INDEX tw_bundle_name_idx ON tw_bundle USING btree (name)
CREATE INDEX tw_bundle_timestamp_idx ON tw_bundle USING btree ("timestamp")
CREATE INDEX tw_bundle_version_idx ON tw_bundle USING btree (version)
CREATE INDEX tw_bundle_propertiesid_idx ON tw_bundle USING btree (propertiesid)
CREATE INDEX tw_bundles_bundleref_idx ON tw_bundles USING btree (bundleref, subbundleref)
CREATE INDEX tw_bundles_subbundleref_idx ON tw_bundles USING btree (subbundleref)
CREATE INDEX tw_classrecord_name_idx ON tw_classrecord USING btree (name)
CREATE INDEX tw_classrecord_timestamp_idx ON tw_classrecord USING btree ("timestamp")
CREATE INDEX tw_classrecord_environmentstrx ON tw_classrecord USING btree (environmentstring)
CREATE INDEX tw_data_packageref_idx ON tw_data USING btree (packageref, dataref)
CREATE INDEX tw_dataelement_name_idx ON tw_dataelement USING btree (name)
CREATE INDEX tw_dataelement_timestamp_idx ON tw_dataelement USING btree ("timestamp")
CREATE INDEX tw_filerecord_name_idx ON tw_filerecord USING btree (name)
CREATE INDEX tw_filerecord_timestamp_idx ON tw_filerecord USING btree ("timestamp")
CREATE INDEX tw_files_fileref_idx ON tw_files USING btree (fileref)
CREATE INDEX tt_methodretrievalindex ON tw_method USING btree (name, classname)
CREATE INDEX tw_method_name_idx ON tw_method USING btree (name)
CREATE INDEX tw_method_timestamp_idx ON tw_method USING btree ("timestamp")
CREATE INDEX tw_method_classname_idx ON tw_method USING btree (classname)
CREATE INDEX tw_methoddocs_methodref_idx ON tw_methoddocs USING btree (methodref, documentref)
CREATE INDEX tw_methods_packageref_idx ON tw_methods USING btree (packageref, methodref)
CREATE INDEX tw_methods_methodref_idx ON tw_methods USING btree (methodref)
CREATE INDEX tw_namespacere_name_idx ON tw_namespacerecord USING btree (name)
CREATE INDEX tw_namespacere_timestamp_idx ON tw_namespacerecord USING btree ("timestamp")
CREATE INDEX tw_package_name_idx ON tw_package USING btree (name)
CREATE INDEX tw_package_timestamp_idx ON tw_package USING btree ("timestamp")
CREATE INDEX tw_package_version_idx ON tw_package USING btree (version)
CREATE INDEX tw_packages_bundleref_idx ON tw_packages USING btree (bundleref, packageref)
CREATE INDEX tw_packages_packageref_idx ON tw_packages USING btree (packageref)
CREATE INDEX tw_parcelrecor_name_idx ON tw_parcelrecord USING btree (name)
CREATE INDEX tw_parcelrecor_timestamp_idx ON tw_parcelrecord USING btree ("timestamp")
CREATE INDEX tw_parcelrecor_version_idx ON tw_parcelrecord USING btree (version)
CREATE INDEX tw_pkgclasses_packageref_idx ON tw_pkgclasses USING btree (packageref, metaclassref)
CREATE INDEX tw_pkgclasses_metaclassref_idx ON tw_pkgclasses USING btree (metaclassref)
CREATE INDEX tw_pkgnamespac_packageref_idx ON tw_pkgnamespaces USING btree (packageref, namespaceref)
CREATE INDEX tw_pkgnamespac_namespaceref_idx ON tw_pkgnamespaces USING btree (namespaceref)
CREATE INDEX tw_properties_propertyref_idx ON tw_properties USING btree (propertyref)
CREATE INDEX tw_properties_pundleref_idx ON tw_properties USING btree (pundleref, propertyref)
CREATE INDEX tw_propertyrec_name_idx ON tw_propertyrecord USING btree (name)
CREATE INDEX tw_propertyrec_timestamp_idx ON tw_propertyrecord USING btree ("timestamp")
CREATE INDEX tw_propertyrec_definitionid_idx ON tw_propertyrecord USING btree (definitionid)
CREATE INDEX tw_propertyrec_searchstring_idx ON tw_propertyrecord USING btree (searchstring)

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc