SourceForge.net Logo
Main Overview Wiki Issues Forum Build Fisheye

Welcome, Guest
Guest Settings
Help

Compass Support Forums » Compass » Compass Users

Thread: Mysql backend: Weird performance profile

This question is answered. Helpful answers available: 0. Correct answers available: 1.


Permlink Replies: 5 - Pages: 1 - Last Post: Jan 22, 2009 9:27 AM Last Post By: Shay Banon
uwe schaefer

Posts: 3
Registered: 1/22/09
Mysql backend: Weird performance profile
Posted: Jan 22, 2009 6:25 AM
 
  Click to reply to this thread Reply
hi

i started using compass and i liked to have the index avail to two nodes, so i need a distributed index. i tried the following config:
<jdbc>
   <dataSourceProvider>
     <driverManager url="jdbc:mysql://localhost/compasstestindex?emulateLocators=true" username="root" password="root"
					driverClass="com.mysql.jdbc.Driver" />
  </dataSourceProvider>
</jdbc>


i have 20000 entities with a small text and faced the following performance problem:
Index RT 230sec
Query RT 854sec

where Index is a complete gps.index(); and Query is a simple query for a word with 3000 hits.

this is not exactly exciting :)

i also tried the simple file directory and the terracotta flavour, which both process the query in about 200-400 msec.

Is there anything i could to or anywhere to look for a working mysql/compass/jpa setup with reasonable performance?

thanks. uwe

Edited by: uwe schaefer on Jan 22, 2009 6:25 AM
Shay Banon

Posts: 4,028
Registered: 9/6/05
Re: Mysql backend: Weird performance profile
Posted: Jan 22, 2009 6:37 AM   in response to: uwe schaefer in response to: uwe schaefer
Helpful
  Click to reply to this thread Reply
Try and configure Compass with a file based local cache. It should speed things up.
uwe schaefer

Posts: 3
Registered: 1/22/09
Re: Mysql backend: Weird performance profile
Posted: Jan 22, 2009 6:52 AM   in response to: Shay Banon in response to: Shay Banon
 
  Click to reply to this thread Reply
Thank you for taking the time to answer.

i tried...

<localCache subIndex="__default__" connection="memory://" />
 
lead to 
 
java.lang.ArrayIndexOutOfBoundsException: 685
     at org.compass.core.lucene.engine.store.localcache.MemoryDirectoryCache$WrappedIndexInput.readByte(MemoryDirectoryCache.java:330)


while

<localCache subIndex="__default__" connection="file://target/cache" />


brought the query time to as low as: 280*secs* for the first query, and 280msec for the second (which is exactly the performance expected from a file index). Still, this is far from acceptable. is there anything i can do to improve the jdbc-dir throughput? i´ve seem an incredible number of queries fired against the DB when initially caching the index. maybe some more buffers here and there? already tried with FileBuffers, but with no significant effect, but a OOM when being too generous :)

Edited by: uwe schaefer on Jan 22, 2009 6:53 AM
Shay Banon

Posts: 4,028
Registered: 9/6/05
Re: Mysql backend: Weird performance profile
Posted: Jan 22, 2009 6:58 AM   in response to: uwe schaefer in response to: uwe schaefer
Helpful
  Click to reply to this thread Reply
In the file based local cache, the first query will need to download (most of the) index to the file system. Then, only delta changes are applied to the database, and search / reads only occur against the local one.

In any case, there is nothing much you can do with blobs and database, this is how it works.... . You can try and increase the buffer size here: http://www.compass-project.org/docs/2.1.1/reference/html/core-connection.html#core-connection-jdbc.
uwe schaefer

Posts: 3
Registered: 1/22/09
Re: Mysql backend: Weird performance profile
Posted: Jan 22, 2009 8:54 AM   in response to: Shay Banon in response to: Shay Banon
 
  Click to reply to this thread Reply
Ok. I did some benchmarking. Nothing general, just my usecase, but maybe someone is interested in the results anyway:

i got the best performance using this config:

<jdbc>
                <dataSourceProvider>
                    <driverManager url="jdbc:mysql://localhost/compasstestindex?emulateLocators=true" username="root" password="root"
                     driverClass="com.mysql.jdbc.Driver" autoCommit="external"
                    />
                </dataSourceProvider>
		        <fileEntries>
		            <fileEntry name="__default__">
                		<indexInput bufferSize="4096000" />
                		<indexOutput bufferSize="4096000" />
            		</fileEntry>
        		</fileEntries>
</jdbc>
<localCache subIndex="__default__" connection="file://target/cache" />


Synchronization on first use is in acceptable Range (from one sec to 20 secs depending on the actual change to be synced of course), and query performance as fasst as simple file directory. Inserting is acceptable as well, just reindexing is still expensive, but that is probably inevitable.

The above FileEntry setting improved throughput by roughly 200%.
Again: right for ME, not necessarily for anyone else, considering different usage patterns, memory requirements etc.

Another option promising a good performance for my usecase seems to be a file-directory on a Distributed Block Device. Is there a good reason to not do that?

Thanks to Shay for helping out.

PS: apart from thisdifficulty, i love compass for its ease of use and (almost) proper documentation. Kudos to Shay!
Shay Banon

Posts: 4,028
Registered: 9/6/05
Re: Mysql backend: Weird performance profile
Posted: Jan 22, 2009 9:27 AM   in response to: uwe schaefer in response to: uwe schaefer
 
  Click to reply to this thread Reply
Thanks for sharing the result!. I have not tried D , so can't comment on that. If you are looking for open source solution, Terracotta might come in handy.
Legend
Guru: 2001 + pts
Expert: 501 - 2000 pts
Super-star: 101 - 500 pts
Assistant: 51 - 100 pts
Participant: 0 - 50 pts
Helpful Answer (5 pts)
Correct Answer (10 pts)

Point your RSS reader here for a feed of the latest messages in all forums