MariaDB Ab (Oct 2015 - eBay)
Summary
- A lagre number of MariaDB developers and engineers (including Monty)
- Big MariaDB gathering at the Booking.com this week
- I've never attended a meetup before with 16 presentation in one evening ;)
- Lots of interesting developments, the introduction of histograms was particularly interesting
Notes from various presentors
1) Rasmus
MariaDB, MariaDB Galeria, MaxScale << key products with multiple releases per year
mariadb.org/jira << nearly 10,000 issues
Weekly sprint ... finish every Tues at 4pm (Swedish Time) / 3 pm Nederlands ... weekly call
2) Daniel Batholomew ... release manager
MariaDB tree on github
Buildbot ... test and re-test
Sergei Golubchik (Chef Architect) raises JIRA for actual releases
Push to mirrors into US (Origean) and Europe (Netherlands)
Anouncement to world via normal geek social networks: google+ group ...
3) Sergei Golubchik (Chef Architect)
Data Encryption at Rest
Encrypting keys?
What is encrypted: tablespace, logs, temp/swap?
encryption plugins - file_key_management plugins, simple APIs?
Apropos ... XtraDB/InnoDB 'page compression' and 'data scrubbing'
Benchmark ... ro (order of 1% slower), rw/filesort (order of 10% slower), bin log encryption (< 4%)
temp files (ie sorts) are only encrypted if they are written to disc
5) Sergei Pertunia
optimizing troubleshooting
- EXPLAIN you query .. are the stats true, where is the time spent
- slow query log ... rows_examined
- PERFORMANCE_SCHEMA .. table_io_waits_summary_by_table
- ANALYZE ... optimize the query and trace the execution
rows (estimate) vs r_rows (real)
filters (estimate) vs r_filter (real) e.g. do we expect to 50% or 5% or rows to match
- EXPLAIN/ANALYZE FORMAT=JSON ... both look hard to read compared to
r_total_time_ms, r_buffer_size
r_indexes
6) Colin Charles
- SHOW PLUGINS
- INFORMATION.ALL_PLUGINS
- AUTH_SOCKET
- PAM AUTHENTICATION (Google authenication on phone?)
- Password validation << cracklib_password
- Server_audit << required by regulator
- query_cache_info <<
- information_schema.query_response_time << distro reponse time bar/count chart
7) Otto Kekalainen
- passwordless authentication
- pain with automation
- /etc/mysql/debian.cnf << clear text root password !?
- unix_socket to the rescue
- ONLY debian/ubuntu NOT ON Centos/RPM
8) Alexander Barkov
- REGEX
- old library - Henr Spencer?
- modern PCRE (MCDEV-4424) ... google summer or code?
- several new REGEX functions: REGEX_INSTR, REGEX_REPLACE
- performance questions? the new library is generally faster
9) Jan Lindstrom
- InnoDB in MariaDB 10.1 (both XtraDB from Percona + InnoDB Oracle)
- Galera integration
- wsrep_on=1 and speciy library
- Page compesssion for SSD
- innodb-file-format=Barracuda
- punch whole
- create table t1(...) page_compressed=1;
- zip, lz4, lzo, bzip2, snappy
-Defragement
- remove duplicate are removed from page
- too empty pages are merged
- does not return totally empty pages
10) Massimiliano Pinto - What is MaxScale?
Classic proxy between db server and the client
Key Parts: Router, Montior, Protocol, Authentication, Filters
Bin Log Router via MaxScale:
offload network load from master
easier to promote a new master
the remaining slaves are unaffected as they are still interfacing with maxscale
blr_distrubute_binlog_record
events written to local binlog before distributing << 'this is important' (although not sure why?)
Enhancements/bugs/projects recorded under : https://mariadb.atlassian.net/projects/MXS
Semi-sync to be added to MaxScale (in the future)
11) ??? MariaBD java connector - failover handling
driver initialization to include failover details
transparent to application ie query execution
how it works depends on : (a) galera cluster or (b) classic master-slaves
examples of connection details for (a) classic java and (b) spring framework
fail slave
- database ping
- blacklist ... don't use slace
fail master
- database ping
- blacklist
- connect to another master (if possible)
- if unsure if the query has been excuted then raise an Exception
connection pool:
- initialise
- validate
12) Daniel (no slide)
- community member
- system-d patches
- in 10.1 proper system-d support, with notify handling
- in theory don't need mysqld-safe anymore
- galeria and system-d ... specifying initial primary at startup?
- socket activation ... provide the ability over multiple interfaces
13) Axel Schwenke - Performance Engineer
- Using Compiler Profiling
- modern CPU's use a pipeline
- CPUs very than memory access
- optimized for linear exeecution pattern (not branching)
- superscaler architecture, branch prediction, speculative execution
- attributes/hints for the compiler: this is LIKELY branch or UNLIKELY branch
- gcc -fprofile-generate << record how often a function is calls >> when you have an instrumented binary
- gcc -fprofile-use << recompile the biary using collected profiles
- this two phase compile with instrumented binary can be automated
- pain points:
- does your workload match the enduser workloads
- good results with standard benchmark tools (10% to 15%) .. less good with enduser tests (? %)
- in most case the error checking branches don get executed so don't need to be in the linear
- in theory it can performance worse (e.g. lots of errors in code / error paths) ... could performance worse but they never seen this
14) MariaDB in Docker (Kolbe@mariadb.com)
- existing images issues : root password as an environment variable
- linked containers have access
- container metadata
- 3 volumes
- data directory on faster storage
- unix socket
- load data
- helper scripts
- UID and GID of mysql user ... easy chmod
15) Sergiu Petrunia
- Engine Independent Table Statistics (EITS)
- Traditonal Mysql: (1) #row, (2) #rows in index range, (3) 'index statistic' (imprecise)
some issues
- index statistic (imprecise)
- not enought stats
- joins needs column statistics (join order on customer on supplier with moderate complicate where rules)
- Histograms (CBO)
- Engineer Independent ... beyond the scope of talk
- mysql.column_stats
set histogram_200; set use_stat_tables='preferably'; analyze table lineitem, orders
- hieght and width bal
16) Monty Q&A
- instance upgrades for mariadb?
- atomic writes standard for modern SSD (not just fusionIO)
- one disc seek is now 100 execution of the
- 10.2 tuning optimizing paremeter for hardware like gather system stats on oracle
- pluigns are potentially ... could they be sansitized by using a seperate namespace
- hybrid storage, for example : 0.5 T RAM, 100T of SSD and the rest on Spinning Disk
zfs has this sort of storage model built in?
working on a prototype with ScanDisc
memory mapping can handle 'atomic writes'?
no easy APIs exist at the moment for this sort of call ... Monty is working with vendors
- ScaleDB good for parallel execution... MariaDB looking to leverage this sort of technology
