🖍️ 🤚 🐨 Использование 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.

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

(request’s tags)

MySQL is not a bottleneck anymore

More articles: