Использование Pinba в Badoo: то, чего вы еще не знаете

, ! , – PHP- Badoo, , Pinba. , , , . , , .

Pinba , . , Badoo, : , open source . , Pinba IT-, - : « Pinba» « Pinba», .

: -, Pinba, -, , . , , Pinba Badoo. !

, «Pinba – , MySQL ...» «», , . Pinba, , MySQL-, . , , .

tony2001 , . , MySQL- SQL- . Pinba , MySQL- . ? , . « ». «» , , requests – - .

. SQL-. ( «» ) , ( JOIN . .) Pinba « » .

, , SQL, «» , . . , , , - , requests tags. , . Pinba – .

, . , , , CREATE TABLE



, , . «», . : - , , «» . Pinba , : – .

, : . MySQL Pinba , , :

CREATE TABLE `tag_report_perf` (
`script_name` varchar(128) NOT NULL DEFAULT '',
`tag_value` varchar(64) DEFAULT NULL,
`req_count` int(11) DEFAULT NULL,
`req_per_sec` float DEFAULT NULL,
`hit_count` int(11) DEFAULT NULL,
`hit_per_sec` float DEFAULT NULL,
`timer_value` float DEFAULT NULL,
`timer_median` float DEFAULT NULL,
`ru_utime_value` float DEFAULT NULL,
`ru_stime_value` float DEFAULT NULL,
`index_value` varchar(256) DEFAULT NULL,
`p75` float DEFAULT NULL,
`p95` float DEFAULT NULL,
`p99` float DEFAULT NULL,
`p100` float DEFAULT NULL,
KEY `script_name` (`script_name`)
) ENGINE=PINBA DEFAULT CHARSET=latin1
COMMENT='tag_report:perf::75,95,99,100'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

tag_report



; , , perf



, – – . , , . , , tag1_value, tag2_value



, .

(request’s tags)

, Pinba , ( ) . ( , ) , .

? Badoo – . 350 , . : – «» , , “” — , .

HighLoad++ . «» , c , – , -. 98%.

, , , , 96% . . Pinba. PHP ( -), Pinba nginx.

– nginx , . , . , . :

• rtag_info
  
  
  
  – ,
• rtagN_info
  
  
  
  – ,
• rtag_report
  
  
  
  – ,
• rtagN_report
  
  
  
  – .

:

CREATE TABLE `photoscache_report_hitrate` (
`hostname` varchar(64) NOT NULL DEFAULT '',
`tag1_value` varchar(64) DEFAULT NULL,
`tag2_value` varchar(64) DEFAULT NULL,
`tag3_value` varchar(64) DEFAULT NULL,
`req_count` int(11) DEFAULT NULL,
`req_per_sec` float DEFAULT NULL,
`req_time_total` float DEFAULT NULL,
`req_time_percent` float DEFAULT NULL,
`req_time_per_sec` float DEFAULT NULL,
`ru_utime_total` float DEFAULT NULL,
`ru_utime_percent` float DEFAULT NULL,
`ru_utime_per_sec` float DEFAULT NULL,
`ru_stime_total` float DEFAULT NULL,
`ru_stime_percent` float DEFAULT NULL,
`ru_stime_per_sec` float DEFAULT NULL,
`traffic_total` float DEFAULT NULL,
`traffic_percent` float DEFAULT NULL,
`traffic_per_sec` float DEFAULT NULL,
`memory_footprint_total` float DEFAULT NULL,
`memory_footprint_percent` float DEFAULT NULL,
`req_time_median` float DEFAULT NULL,
`index_value` varchar(256) DEFAULT NULL,
KEY `hostname` (`hostname`)
) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='rtagN_report:served_by,build,img_size'

CREATE TABLE `photoscache_top_size` (
`geo` varchar(64) DEFAULT NULL,
`req_count` int(11) DEFAULT NULL,
`req_per_sec` float DEFAULT NULL,
`req_time_total` float DEFAULT NULL,
`req_time_percent` float DEFAULT NULL,
`req_time_per_sec` float DEFAULT NULL,
`ru_utime_total` float DEFAULT NULL,
`ru_utime_percent` float DEFAULT NULL,
`ru_utime_per_sec` float DEFAULT NULL,
`ru_stime_total` float DEFAULT NULL,
`ru_stime_percent` float DEFAULT NULL,
`ru_stime_per_sec` float DEFAULT NULL,
`traffic_total` float DEFAULT NULL,
`traffic_percent` float DEFAULT NULL,
`traffic_per_sec` float DEFAULT NULL,
`memory_footprint_total` float DEFAULT NULL,
`memory_footprint_percent` float DEFAULT NULL,
`req_time_median` float DEFAULT NULL,
`index_value` varchar(256) DEFAULT NULL,
`p95` float DEFAULT NULL,
`p99` float DEFAULT NULL
) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='rtag_info:geo:tag.img_size=top:95,99'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

( geo



img_size



).

nginx :

location ~ '.....' {
...
pinba_tag fit_size '500x500';
pinba_tag is_fit 1;
pinba_tag img_size '920';
…
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

– :

. – , Pinba:

• PHP-;
• PHP-;
• ( C, Go, Memcached, MySQL . .);
• nginx- nginx ;
• .

- . , , . :

• – , -, ;
• : ( MySQL, , , Darner);
• : .

- , . , - PHP-, ( PHP): , – , , -, . ? , . . , , , Pinba. :

CREATE TABLE `tag_info_measure_cpq_consumer` (
`type` varchar(64) DEFAULT NULL,
`consumer` varchar(64) DEFAULT NULL,
`timer` varchar(64) DEFAULT NULL,
....
) ENGINE=PINBA DEFAULT CHARSET=latin1 COMMENT='tagN_info:type,consumer,timer'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

timer



– , consumer



– , type



– ( ( – , ).

:

. , . , , , Badoo , , , , (, ).

( , Pinba) Jinba. Jinba – , Pinba; JavaScript is not a bottleneck anymore. Jinba .

PHP-, Pinba. , Jinba . – , .

Pinba , , 512 , , . , PHP-, nginx, . . ? , 1000 - , , 1000 . ? : « », – - , , . . , , , : 50- ( ) – , 50% , .

: 95-, 99- 100- ( ). ? :

, , (50- ) , 95- . , , , , 5% . , 95- , , - , .

MySQL is not a bottleneck anymore

, , , . Badoo MySQL. , Slowlog + Zabbix. . Slowlog , , . .

, , , ( , ), , .

DBA (, : , ) . performance_schema



, Python, Elastic, Kibana. , , , , MySQL, Elasticsearch.

, Badoo . Pinba. : Pinba SQL-? , , «» , , 1000 Select * from table where field = field_value



, field_value , Pinba Select * from table where field = #placeholder#



. , SQL , . , Pinba. , « – ». Pinba :

CREATE TABLE `minba_query_details` (
`tag_value` varchar(64) DEFAULT NULL,
...
`p95` float DEFAULT NULL,
`p99` float DEFAULT NULL
) ENGINE=PINBA DEFAULT CHARSET=latin1
COMMENT='tag_info:query::95,99'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

PHP- :

$tags = [
    ‘query’ => $query_hash,
    ‘dest_host’ => ‘dbs1.mlan’,
    ‘src_host’ => ‘www1.mlan’,
    ‘dest_cluster’ => ‘dbs.mlan’,
    ‘sql_op’ => ‘select’,
    ‘script_name’ => ‘demoScript.php’,
];
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , , -; mysql_query



mysqli_query



, , .

, , Pinba.

$config = [‘host’ => ‘pinbamysql.mlan’, ‘pinba_port’ => 30002];
$PinbaClient = new \PinbaClient($config);
$timer_value = /*Execute query and get execution time */
$PinbaClient→addTimer($tags, $timer_value);
/* Some logic */
$PinbaClient->send();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

\PinbaClient



. PHP Pinba, « » ( PHP, ). , , Pinba, , . . php.ini



Pinba, . \PinbaClient



. Pinba . , Pinba. Pinba , « – SQL-» .

, Pinba . . . , , , . .

Pinba – . Memcached, , . , , . , (slabs). – , . , 152 , – 192 . , 152 192 . , (chunks), . , , , , , , . , . , , Pinba.

MySQL – “key_family”:%s_%s



, ( ) , , messages_cnt:13589



( 13589) personal_messages_cnt:13589_4569



( 13589 4569).

, , :

$tags = [
    ‘key’ =>’uc’,
    ‘cluster’ => ‘wwwbma’,
    ‘hit’ => 1,
    ‘mchost’ => ‘memcache1.mlan’,
    ‘cmd’ => ‘get’,
];
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

key



– , cluster



– , , hit



– , mchost



– , cmd



– , .

Pinba :

tag_info_key_hit_mchost | CREATE TABLE `tag_info_key_hit_mchost` (
`key` varchar(190) DEFAULT NULL,
`hit` tinyint(1) DEFAULT NULL,
`mchost` varchar(40) DEFAULT NULL,
...
) ENGINE=PINBA DEFAULT CHARSET=latin1
COMMENT='tagN_info:key,hit,mchost',
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

MySQL Pinba.

C , PHP-, , Pinba . – Pinba « » . – ? «». , , . .

-, , Pinba – . Pinba , , , – .

-, «» , .

-, . , Pinba .

P. S. , Pinba, , . PHP.