     

   

      !  "     %    # $  

&  '  

 !  "(   #)) * "  +  , -


.

     %)        

   /001 
2/ 3 34 )) $*  5 6) 7  $ )   "   # )5, 6# "   5 89  (   
" +6 :    * / " 

 

                  !"# "$ ;        "$ )   %&"$ 
 "    

!  "$&   $  ,  $; $<=%  >$    <  &!  "$&    ) ; , #,  ( 2  # + "  $  
  
'  6 $() $ 2; / 6-"    
$  

.

'   1   4<   ?
2   ;     +) @ 

7

%  (          )    ))        )

        9)   % $4"      )4)  )  2  ! /  *   %    #;  groongastorage engine

Brazil, Inc.
Tasuku SUENAGA
a.k.a. gunyarakun
1

What I talk about        2

MySQL fulltext index• Phrase search is slow.
• Updating index is slow.
• Cannot combine full text searchindex with other indexes(like BTree).

Our prior product Tritonn solves.
3Tritonn• Tritonn =groonga +patches for MyISAM

   
4

MySQL v.s. Tritonn

Target dataset : Wikipedia English 458,713 record 1088MB

MySQL(5.0)ʼsFulltext Index
Index size
Phrase search for‘united states’
Indexing after
inserting recs

Inserting recs
after idx. creation

Where MATCH
AGAINST and order byprimary key
Where MATCH
AGAINST and primary
key > 200000

Tritonn

109 MB1028 MB44.91 sec

0.40 sec

1,474 sec

1,808 sec

28,182 sec

1,839 sec

20.33 sec

0.89 sec

6.55 sec

0.32 sec
5

So Tritonn provides …

• Fast phrase search• Fast index update (realtime)• Works well with other indexes.

But some problems remain.
6

Remaining problems• MyISAM based‒ Table lock

• when updating table, read accesses areblocked.

• Patch based‒ Patch maintainance and building
patched MySQL is too messy.

Need for a new solution.
7

New solution is• groonga storage engine‒ Use column store of groonga insteadof MyISAM.
‒ Not patch but storage engine.



Tritonn (old)  

     

groonga storageengine(new)8Advantage• Table lock free

‒ Column store of groonga is lock-free.

• Only access columns required‒ Not row-based.

• Easy to build and develop

And some optimization
for typical queries
9Optimization(1)• COUNT(*) optimization.
‒ For queries like below.

SELECT COUNT(*) FROM table
WHERE MATCH(col)AGAINST (ʻqueryʼ);

10Optimization(2)• ORDER BY score and LIMIT
optimization.
‒ For queries like below.

SELECT * FROM table
WHERE MATCH(col)AGAINST (ʻqueryʼ)
ORDER BY MATCH(col)AGAINST (ʻqueryʼ)
LIMIT 10;
11

Conclusion of my part• groonga storage engine provides• Fast phrase search• Fast index update (realtime)• Inserting records doesnʼt block
reading records

12The combination of Groonga and Spider

Kentoku SHIBA
kentokushiba at gmail dot com

The combination of Groonga and Spider
In this time ...

What is Spider Storage Engine
3.Response

1.Request
AP
2.Just connect to spider

SPIDER
tbl_atbl_b

tbl_c

DB1

DB2

DB3Spider Storage Engine is a storage engine
for database sharding transparently.

The combination of Groonga and Spider

You can get following power by combinationof Groonga and Spider.
- The optimization for the fulltext searching with sorting by score.
- The optimization for the sorting by range partition key column.
- The optimization for the fulltext searching with filtering bypartition key column.

The optimization for the fulltext searchingwith sorting by score(The case of scanning all partitions)Sorting by score
SELECT * FROM t1
WHERE MATCH(c2) AGAINST('hoge')
ORDER BY _score LIMIT 100;

1t1
DB1Parallel searching is comming soon.

222SELECT * FROM t1
WHERE MATCH(c2) AGAINST('hoge')
ORDER BY _score LIMIT 100;

t1

t1

t1

DB2

DB3

DB4- Parallel searching- 2 step limitation

The optimization for the sortingby range partition key column(coming soon)

The sorting by range partition key column
SELECT * FROM t1
WHERE MATCH(c2) AGAINST('hoge')
ORDER BY c1 LIMIT 100;

1t1
DB1
234SELECT * FROM t1
WHERE MATCH(c2) AGAINST('hoge')
ORDER BY c1 LIMIT 100;
(LIMIT value is decreasing gradually)

t1

t1

t1

DB2

DB3

DB4

c1 < 50c1 < 100c1 >= 100

- Sort optimization with range partition

The optimization for the fulltext searching withfiltering by partition key column

The filtering by partition key column
SELECT * FROM t1
WHERE MATCH(c2) AGAINST('hoge')
AND c1 = 60
ORDER BY _score LIMIT 100;

1t1
DB1

2

SELECT * FROM t1
WHERE MATCH(c2) AGAINST('hoge')
AND c1 = 60
ORDER BY _score LIMIT 100;

t1

t1

t1

DB2

DB3DB4c1 < 50c1 < 100c1 >= 100

- Partition pruning

End of the session

Source code and binary

If you want to try introduced Spiderfeatures,
you can download from here and try.
source code
http://groonga.org/pkg/mysql-5.5.8-spider-2.24h-vp-0.13-hs-1.0.src.tgz
binary (Linux x86_64 glibc2.3)
http://groonga.org/pkg/mysql-5.5.8-spider-2.24h-vp-0.13-hs-1.0.bin.tgz
initialize SQL
http://groonga.org/pkg/spider-init-2.24-for-5.5.8.tgz

Contact us

If you have some questions, comments orsuggestions, please contact us from here.
http://bit.ly/fSs5vx

Thank you for
taking your time!!

Daijiro MORI (morita at razil dot jp)
Tasuku SUENAGA (a at razil dot jp)
Kentoku SHIBA (kentokushiba at gmail dot com)