Hi all,
Since the performance of mooshup.com <http://mooshup.com> is going down
everyday, I examined a dump of registry database mooshup is using.
As per the dump, it has following tables with specified number of records.
ARTIFACTS -379
CHILDREN -0
COMMENTS -3
DEPENDENCY - 144889
LOGS - 2235
PROPERTIES -128
RATINGS - 46
TAGS - 63
VERSIONS - 7761
Out of these 'VERSIONS' table is special,as it contains a BLOB field.
Structure of the table is as follows :
AID (INTEGER(10)
VN (INTEGER(10)
CONTENT (BLOB)
AUTHOR (VARCHAR)
UPDATED_TIME (TIMESTAMP)
Due to BLOB field, doing a SELECT * FROM VERSION is a very costly
operation.
In the registry code base, DAO class
https://wso2.org/repos/wso2/trunk/registry/modules/core/src/main/java/org/wso2/registry/jdbc/dao/VersionedResourceDAO.java
<https://wso2.org/repos/wso2/trunk/registry/modules/core/src/main/java/org/wso2/registry/jdbc/dao/VersionedResourceDAO>
has 4 locations where 'SELECT * FROM VERSION WHERE ...' is used. This
might be a reason for performance degradation. And it's a very bad practice
when it comes to writing SQL statements too. You should only select the
columns needed for subsequent program execution.
Following are 4 methods mentioned above.
public ResourceImpl get(String path, long versionNumber, Connection conn)
public ResourceImpl getResourceByID(long artifactID, long versionNumber,
Connection conn)
public InputStream getResourceContentStream(long resourceID, long
versionNumber, Connection conn)
public ResourceImpl getLatestVersion(String path, Connection conn)
out of these, only getResourceContentStream() method really needs the
value of 'CONTENT' field.
So I suggest we change other 3 sql statements to return only required
fields & see how registry on mooshup performs.
This will definitely create a positive impact, as 'get' method is
heavily used.
Additionally we can create indexes to support frequently used SELECT
queries too.
/sumedha
_______________________________________________
Mashup-dev mailing list
[email protected]
http://www.wso2.org/cgi-bin/mailman/listinfo/mashup-dev
