MySQL Partitioning: The Spider Solution - Percona

MySQL Partitioning: The Spider Solution Frédéric Descamps

Percona Live London 2011

Who am I ? Frédéric Descamps @lefred http://about.be/lefred Managing mysql since 3.23 (as far as I remember) devops believer

www.percona.com

Thank You to Our Sponsors Platinum Sponsor

Gold Sponsor

Silver Sponsors

www.percona.com

Percona Live London Sponsors Exhibitor Sponsors

Friends of Percona Sponsors

Media Sponsors

www.percona.com

Description of the problem Currently the application is using one huge sbtest\", user \"root\" ' partition by range ( id ) ( partition sbtest1 values less than (1000001) comment 'host \"fred2\", port \"3306\"', partition sbtest2 values less than (2000001) comment 'host \"fred3\", port \"3306\"', partition sbtest3 values less than (3000001) comment 'host \"fred4\", port \"3306\"',partition sbtest4 values less than (MAXVALUE) comment 'host \"fred5\", port \"3306\"' )" oltptestmode=complex prepare

(*) modified sysbench to support spider

www.percona.com

sysbench prepare (inserts) seconds

600

500

400

single server spider

300

200

100

0 1

2

3

4

Million rows inserted (on spider 1M/shard)

www.percona.com

Updates Selecting rows from all the shards is slower And updating them ? Test with using an update statement with a range covering 1 to 4 shards update traces set speed=speed 1,type=3 where customer_id=123 and tr_timestamp between '201001 01' and '20101231';

www.percona.com

Updates : results updates 40

35

30

seconds

25 single spider

20

15

10

5

0 0

5000

10000

15000

20000

25000

rows

www.percona.com

Conclusion When the data is spread all over the servers it's slower to retrieve it but faster to update it. ● You need then to select very carefully how you partition your tables ● Check with developers and try to profile your queries ●

www.percona.com

Other numbers ?

www.percona.com

54 www.percona.com

54 files The source code of spider mysql engine is composed of 54 files

www.percona.com

51652 www.percona.com

51652

51,652 lines of code :-)

www.percona.com

8 www.percona.com

8 BUGS There are 8 bugs in launchpad: 2 critical (fix released)

●

2 high (fix released)

●

2 medium (1 fix released, 1 fix committed)

●

2 low (1 fix releases, 1 fix committed)

●

www.percona.com

92 www.percona.com

Spider engine server variables

Spider engine add a large amount (currently 92) of global variables that change the behavior and help tuning the system.

Example: - spider_auto_increment_mode -1 : The table parameter is adopted. 0 : Normal mode.

(Use a count that Spider get from the remote server with exclusive lock for auto increment value.)

Slow. Switch to quick mode if you use Spider table with table partitioning feature and auto increment column is first column of index. 1 : Quick mode.

2 : Set 0 value. (Auto increment value is given by the remote server.) Set 0 value even if you set a value to the auto increment column. Set 0 value after choosing a inserted partition if you use a table with table partitioning feature. 3 : Auto increment value is given by the remote server, if you set null value to auto increment column. Auto increment value is given by the local server, if you set 0 value to auto increment column. You should better to use "spider_reset_auto_increment=2or3" if you want to use an auto increment column at remote server.

(Use a count that Spider has internal for auto increment value.)

Fast but you get duplicate entry if you update same table from multiple Spider and self tables. www.percona.com

86 www.percona.com

Spider engine table variables

Spider engine provides a large amount of table parameters (86) Most of them are the same as the global server parameters.

connect_timeout(cto)

Wait timeout of connecting to remote server. This timeout applies only to TCP/IP connections.

0 or more : Seconds of timeout.

The default value is 6

error_read_mode(erm)

Return 0 record at net read error.

That is given to priority when server parameter spider_error_read_mode is set.

0 : Return error at net read error.

1 : Return 0 record at net read error.

The default value is 0 www.percona.com

5 www.percona.com

Spider User Defined Functions Spider provides also 5 udf's: • spider_direct_sql : execute the SQL string at remote server. • spider_bg_direct_sql : background execution of spider_direct_sql •spider_ping_table • spider_flush_table_mon_cache : this function is used for refreshing monitoring server information •spider_copy_tables www.percona.com

29 www.percona.com

Spider UDF parameters

Some udf have a large number of extra optional parameters, from 6 to 23 !

www.percona.com

Spider This is a very promising engine still in beta but ready for some tests and benchmarks

Please enjoy using Spider and growing your business!*

(*) Kentoku Shiba

www.percona.com

Annual MySQL Users Conference Presented by Percona Live The Hyatt Regency Hotel, Santa Clara, CA April 10th-12th, 2012 Featured Speakers Mark Callaghan, Facebook Jeremy Zawodny, Craigslist Marten Mickos, Eucalyptus Systems Sarah Novotny, Blue Gecko Peter Zaitsev, Percona Baron Schwartz, Percona

The Call for Papers is Now Open! Visit www.percona.com/live/mysql-conference-2012/ www.percona.com

[email protected] @lefred

We're Hiring! www.percona.com/about-us/careers/

Questions ?

Thank you ! 74

sm D é F 1 0 cv2 lu d p e h :T g n rtio a P L Q S y M

()v 2 3 yq g M u lft:/b s@ iD d é F I? m h W m a n rco e .p w

v d liG P s S tO u Y k h T m a n rco e .p w

fM tF b h x E s S d iv L P m a n rco e .p w

vI(x,z) S d tyg u C lm b itfh s D m a n rco e .p w

3 2 m a n rco e .p w

9 4 D j _ t : 7 A G 0 q y m 1 w r 5 | S i * l 3 . 2 h u d # ls b T ig B m a n rco e .p w


0 6 9 G M 7 8 . 5 1 4 3 f z _ l t x d i D w r S E L A | )+ (2 ls b T ig B m a n rco e .p w

! ,'fW k)O t(vylg h u T ● ? d itb Is m a n rco e .p w

! m d v Ih m a n rco e .p w

! b (f)B q tlg u S :● d y m is h T m a n rco e .p w

'fyk u ,b d h R tvW N O T C L P A lit? Is m a n rco e .p w

s! -T b vS E g tiyP C ● ? ld tu h W m a n rco e .p w

t'd L g v is D m a n rco e .p w

-y g rn a q fS vu E zlg tb A ? d tis h W m a n rco e .p w


x,zv u kB L yQ lfM b stg id S m a n rco e .p w

x E _ /q K b zvIT D lh B tg L Q M ● s ftu y :k id S m a n rco e .p w

y tu A tG s id S m a n rco e .p w

f N u d Q yS 6 E U L V X A M 5 4 3 9 8 0 2 < Y ti1 lP b T ? t's h W m a n rco e .p w

; ' = w m r f * t fl N u d Q yS 6 E U L V X A M 5 4 3 9 8 0 < Y ti1 lP b )T (2 ? t's h W m a n rco e .p w

; ' = w m r f * t fl N u d Q yS 6 E U L V X A M 5 4 9 8 0 2 < Y ti1 lP b )T (3 ? t's h W m a n rco e .p w

: V h H b _ G M f k l w , U F B O N Y R D I P v A X s T m C t r u i E | + g L Q y M id S m a n rco e .p w

t v g f k ) % r ( m i _ l T | + f-xR yh q u tb id S Itls m a n rco e .p w

T L Q S yM ,kfvh tb g id A Itls m a n rco e .p w

u ,B g d vb L Q yS iktfM h )T (2 Itls m a n rco e .p w

4 m a n rco e .p w

lm b tu k B m a n rco e .p w

; 9 5 8 7 4 = G x Y K ' 6 F D l m , M I U O N ) 2 i ( 3 j _ r t ` L B T A R C 0 vy1 -E g u W tlib C ● d s th O m a n rco e .p w

tl W ● ; V X 5 8 . 7 4 9 w " S = G x Y K ' y 6 F D 1 u l m , M I U O N ) 0 2 g i ( 3 b j _ r t ` L B T A E R C v id s th O m a n rco e .p w

; U L V X A M 5 8 6 . 7 0 2 4 9 h v 1 ) ( g y w x m s u , 3 j _ " l b ' t C r d i S = I G N E ! m Z m a n rco e .p w

' B )U (X ivlytfg d h Y ● s u k tb A m a n rco e .p w

lyu b vh titg s T A X m a n rco e .p w


yfv! Q ; 3 2 ' _ b 7 = D I x m f * l ) 0 . ( t i w r 9 8 5 4 :1 d u th W ? v itm s D m a n rco e .p w

yv Q 8 9 ; 1 ' _ b 7 = D I x m f * l ) 0 . ( t i w r 2 4 3 5 :6 sd u ith W m a n rco e .p w

ylv Q ; 4 3 ' _ b 9 = D I x m f * l ) 5 . 2 ( t i w r 0 7 6 :1 sd u ith W m a n rco e .p w

yv Q 3 ; 6 5 1 ' _ 8 0 b D I x m f * l ) 4 . ( t i w r 7 2 :9 sd u ith W m a n rco e .p w


I X *M N P F D U A "_ C L xfQ T R v6 yq :/-0 klg ,K h 9 t1 !O u t)s (b 7 2 id S m a n rco e .p w

6 5 8 4 0 vf,x1 tg !T u t)s (b 7 2 id S m a n rco e .p w

d n co se

d p frsS 7 w e N 1 8 6 4 .2 xi0 a tm n u co

tv !U u t)s (b 7 2 id S m a n rco e .p w

c se

i. S 9 3 s2 rw 6 7 5 8 4 ,0 f1 o te a d p u

R D P 6 8 . 9 4 i w 3 1 5 0 | + ; r f ) * ( u t > l q y m L G N )I 2 C (E ls x m S m a n rco e .p w

it ) ( , g 4 U N k A b L M : . G \ 3 1 0 ' 8 7 6 = d _ u h w f * t r i > l q y m R D I )P 2 C (E ls x m S m a n rco e .p w

it 3 g 8 7 4 U N k A , b L M : . G \ 1 9 0 ' 6 = d _ u h w f * t r i > l q y m R D I )P 2 C (E ls x m S m a n rco e .p w

it 6 ) ( g 7 U N k A 9 , b L M : . G \ 1 8 0 ' 5 4 3 = d _ u h w f * t r i > l q y m R D I )P 2 C (E ls x m S m a n rco e .p w

it ) ( g U N k A 8 , 6 b L M : . G \ 1 3 5 ' 0 9 4 = d _ u h w f * t r i > l q y m R D I )P 2 C (E ls x m S m a n rco e .p w

) 2 C (E ls x m S m a n rco e .p w

s w ro

s r+ d g in p m lctxa e S 5 9 1 6 3 4 0 7 2

x 0 4 z m i r l = t u vq kIO T ? tlfg d (it)H h b y s m a n rco e .p w

*ft x 5 U L V X A M 6 3 2 f 1 ) ( , \ ' I G N E " . / 0 4 z k u g q m v i r d l = t (it) h b y s m a n rco e .p w

/ 1 ltd (it)M h b y s m a n rco e .p w d rvp le g sin 6 5 0 4 3 2 1

; 0 ' b 2 i _ m w 3 y , = r t 4 v1 T ? u A fh ltig S ts d U m a n rco e .p w

l :u ts d U m a n rco e .p w

d n co se

w lro g sin te a d p u 4 3 2 1 5 0

ykq fY v'b td h W i● s lu C m a n rco e .p w

? s b m u th O m a n rco e .p w


g yq d u h T fils 4 5 m a n rco e .p w

2 6 1 5 m a n rco e .p w

:-) ,lisfd 2 6 1 5 m a n rco e .p w

8 m a n rco e .p w

, t(fx)1 2 :● sild g u b h T S G U B 8 m a n rco e .p w


"= ,Y 3 A F Q kq U N 0 T 1 x:-_ E )fh 2 t(y9 lu b v s g id S m a n rco e .p w


1 w R : 0 I / P C y l T . r f W ) ( m i _ t h )M 6 tf(8 u s lv tb g id S m a n rco e .p w

5 m a n rco e .p w

ky b g L Q xh tq ':•_ tvl5 u fF D s U id S m a n rco e .p w


! 3 2 xt,6 b vlg fh u ts m F D U id S m a n rco e .p w

j!*()K fkP u tlb svyg h T id S m a n rco e .p w

/q !V zO G B N E kJZ F -2 0 ,1 g R H h T iv b td fP C s U L Q S y lM u A m a n rco e .p w

[email protected] @lefred

We're Hiring! www.percona.com/about-us/careers/

! y k a h 74T ? n tio s e u Q