Planet MySQL

MySQL Enterprise Edition Now in Docker Store!

I'm happy to announce that in addition to the official MySQL Community Edition Docker images, we now have official MySQL Enterprise Edition Docker images available in Docker Store! Now you can easily leverage the power of Docker with MySQL Community Edition or MySQL Enterprise Edition.  

  What Does All of This Mean?

First some context and general info to help you understand this news a bit more. Docker is a container platform which includes the following software components:

  • The Docker Engine, which is a client-server application consisting of:
    • The dockerd server process
    • A REST API that client programs can use to interact with dockerd
    • The docker command-line client
  • Optional orchestration tooling--Compose and Swarm--that help you manage more complex container based applications
  • The Docker SaaS platform that provides the underlying infrastructure for working with Docker containers--identity, repositories, versioning, etc. (this is the rough equivalent of what GitHub offers Git users). 

The usage of container images (pull and push) through docker by default utilize Docker's public container registries, and the access to those registries is managed through your user account that you create--your Docker ID. You then use that Docker ID to login and persist an authentication token on your client machine for accessing their public registries. Docker provides two separate public registries:

  • Docker Hub or "Docker Registry" is their registry for FOSS/Community containers. This is what the docker CLI will use by default. Example usage being: docker run -itd mysql/mysql-server
  • Docker Store or "Docker Trusted Registry" is their registry for "Enterprise" and Commercial containers.
So How Would I Use The MySQL EE Container Then?

You first setup authorized access to the container for your Docker ID within the Docker Store or "Docker Trusted Registry" by subscribing to the MySQL EE image. You do that simply by "proceeding to checkout" there since you don't have to pay up front (it's a BringYourOwnLicense model). Once you specify the required info, then registered/authorized access to the content and container will be associated with your Docker ID

Now that you have authorized access, you can use/run it with: docker run -itd store/oracle/mysql-enterprise-server:5.7 Finally, you can view all of your authorized/subscribed content in Docker Store at https://store.docker.com/profiles/{DockerID}/content and you can see what local images you have available on your host machine using the docker images command.

Summary

We're excited that you can now combine the power and flexibility of Docker and MySQL Enterprise! I hope that I've also helped you to understand exactly how you would go about getting started on that endeavor. If you have any questions or issues, please do let us know! Feel free to leave a comment here, reach out to me on twitter, file a bug, or file a support ticket in My Oracle Support.

As always, THANK YOU for using MySQL!

Percona Monitoring and Management 1.2.1 is Now Available

Percona announces the release of Percona Monitoring and Management 1.2.1 on August 16, 2017.

For install and upgrade instructions, see Deploying Percona Monitoring and Management.

This hotfix release improves memory consumption.

Changes in PMM Server

We’ve introduced the following changes in PMM Server 1.2.1:

Bug fixes
  • PMM-1280: PMM server affected by nGinx CVE-2017-7529. An integer overflow exploit could result in a DOS (Denial of Service) for the affected nginx service with the max_ranges directive not set. This problem is solved by setting the set max_ranges directive to 1 in the nGinx configuration.

Improvements
  • PMM-1232: Update the default value of the METRICS_MEMORY configuration setting. Previous versions of PMM Server used a different value for the METRICS_MEMORY configuration setting which allowed Prometheus to use up to 768MB of memory. PMM Server 1.2.0 used the storage.local.target-heap-size setting, its default value is 256MB. Unintentionally, this value reduced the amount of memory that Prometheus could use. As a result, the performance of Prometheus was affected. To improve the performance of Prometheus, the default setting of storage.local.target-heap-size has been set to 768 MB.

About Percona Monitoring and Management

Percona Monitoring and Management (PMM) is an open-source platform for managing and monitoring MySQL and MongoDB performance. Percona developed it in collaboration with experts in the field of managed database services, support and consulting.

PMM is a free and open-source solution that you can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.

A live demo of PMM is available at pmmdemo.percona.com.

We’re always happy to help! Please provide your feedback and questions on the PMM forum.

If you would like to report a bug or submit a feature request, please use the PMM project in JIRA.

The danger of no Primary Key when replicating in RBR (and a partial protection with MariaDB 10.1)

TL;DR: unless you know what you are doing, you should always have a primary key on your tables when replicating in RBR (and maybe even all the time). TL;DR2: MariaDB 10.1 has an interesting way to protect against missing a primary key (innodb_force_primary_key) but it could be improved. A few weeks ago, I was called off hours because replication delay on all the slaves from a replication chain

Upcoming Webinar Wednesday August 16: Lock, Stock and Backup – Data Guaranteed

Join Percona’s, Technical Services Manager, Jervin Real as he presents Lock, Stock and Backup: Data Guaranteed on Wednesday, August 16, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Reserve Your Spot

Backups are crucial in a world where data is digital and uptime is revenue. Environments are no longer bound to traditional data centers, and span multiple cloud providers and many heterogeneous environments. We need bulletproof backups and impeccable recovery processes. This talk aims to answer the question “How should I backup my MySQL databases?” by providing 3-2-1 backup designs, best practices and real-world solutions leveraging key technologies, automation techniques and major cloud provider services.

Register for the webinar here.

Jervin Real As Technical Services Manager, Jervin partners with Percona’s customers on building reliable and highly performant MySQL infrastructures while also doing other fun stuff like watching cat videos on the internet. Jervin joined Percona in April 2010. Starting as a PHP programmer, Jervin quickly learned the LAMP stack. He has worked on several high-traffic sites and a number of specialized web applications (such as mobile content distribution). Before joining Percona, Jervin also worked with several hosting companies, providing care for customer hosted services and data on both Linux and Windows.

MyRocks my.cnf changes from June 16 to Aug 15

There have been several recent performance improvements to MyRocks that I want to test. The last build I did was from June 16. With the my.cnf options that I use there is one new option and 3 have been removed. The new option is rocksdb_max_background_jobs. The removed options are rocksdb_base_background_compactions, rocksdb_max_background_compactions and rocksdb_max_background_flushes.

How ProxySQL deal with schema (and schemaname) Long story

This article is the spin-off of the article How ProxySQL deal with schema (and schemaname)

1 How ProxySQL differes from MySQL in managing explicit default schema declaration? does it respect the -D or USE

MySQL and ProxySQL will behave the same when passing the default schema, setting it as default.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 MySQL mysql -uuallworld -ptest -h192.168.1.107 -P 3306 -D test select database(); +------------+ | database() | +------------+ | test | +------------+   SHOW TABLES; +-----------------+ | Tables_in_test | +-----------------+ | rewrite_tbtest | | rewrite_tbtest1 | +-----------------+   ProxySQL mysql -uuallworld -ptest -h127.0.0.1 -P 6033 -D test select database(); +------------+ | database() | +------------+ | test | +------------+   SHOW TABLES; +-----------------+ | Tables_in_test | +-----------------+ | rewrite_tbtest | | rewrite_tbtest1 | +-----------------+  

 

Passing a different schema from the default one in ProxySQL will override the ProxySQL default:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 MySQL ./mysql-3306 -uuallworld -ptest -D mysql (uallworld@localhost) [mysql]>select database(); +------------+ | database() | +------------+ | mysql | +------------+ ProxySQL [root@rms2 server57S]# ./mysql-3306 -uuallworld -ptest -h192.168.1.50 -P6033 -D mysql   (uallworld@192.168.1.50) [mysql]>select database(); +------------+ | database() | +------------+ | mysql | +------------+  

 

Connecting without DEFAULT Schema

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 MySQL mysql -uuallworld -ptest -h192.168.1.107 -P 3306 mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+   ProxySQL mysql -uuallworld -ptest -h127.0.0.1 -P 6033 mysql> select database(); +------------+ | database() | +------------+ | test | +------------+  

 

The last is different between MySQL and ProxySQL.      

By default ProxySQL will connect the user to his default schema.     

Not only, if the default schema is not define:

1 2 3 4 5 6 7 8 9 10 11 12 13 +-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | uallworld | test | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |   ProxySQL will point the connection to information_schema. mysql> select database(); +--------------------+ | database() | +--------------------+ | information_schema | +--------------------+  

 

2 How proxy respect/follow security agains schema 1 2 3 4 5 6 7 8 9 10 11 MySQL mysql -uuworld -ptest -h192.168.1.107 -P 3306 -D test mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1044 (42000): Access denied for user 'uworld'@'%' to database 'test'   ProxySQL mysql -uuworld -ptest -h127.0.0.1 -P 6033 -D test mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 483902 Server version: 5.5.30 (ProxySQL)

 

And no warnings.     

Why?

Because while you directly connect to MySQL, when you connect to ProxySQL you do not actually touch the final server.      

You only open a connection to the ProxySQL, issue a query will open a connection and if the user do not have the right grants an error will be returned.

Let us see.

 just issuing the command:

 

1 2 3 mysql> select database(); ERROR 1044 (42000): Access denied for user 'uworld'@'%' to database 'test'  

 

 

 

As soon as you try to query the MySQL server you got an error.     

 

 

3 How schemaname filter acts in the query rules?

In MySQL we can easily change the default schema with USE , this action is fully supported by ProxySQL.    
But it may have some side effects when using "schemaname" as filter in the query_rules.  
If you define a rule that include the default schemaname and the default schema is changed with USE, the rule will not apply, and unpredictable results may happen.  
To prevent that ProxySQL has another option in mysql_user "schema_locked" which will prevent the schema to be changed by USE.
This feature is present but not fully implemented, and after a brief conversation with Rene (https://github.com/sysown/proxysql/issues/1133).   
For now my advice is to DO NOT use schemaname as filter in the query_rules.  

Below the full test case:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 mysql> select active,hits,destination_hostgroup, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules where destination_hostgroup < 30 ORDER BY mysql_query_rules.rule_id; +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | active | hits | destination_hostgroup | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | 1 | 0 | 10 | 10 | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 | 0 | NULL | | 1 | 0 | 11 | 11 | ^SELECT | NULL | NULL | NULL | 1 | 0 | NULL | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ 2 rows in set (0.00 sec)     (uworld@192.168.1.50) [world]>Select * from world.City order by Population DESC limit 5 ; +------+-----------------+-------------+--------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------------+-------------+--------------+------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 206 | São Paulo | BRA | São Paulo | 9968485 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | +------+-----------------+-------------+--------------+------------+ 5 rows in set (0.01 sec)       mysql> select active,hits,destination_hostgroup, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules where destination_hostgroup < 30 ORDER BY mysql_query_rules.rule_id; +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | active | hits | destination_hostgroup | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | 1 | 0 | 10 | 10 | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 | 0 | NULL | | 1 | 1 | 11 | 11 | ^SELECT | NULL | NULL | NULL | 1 | 0 | NULL | <--- +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ 2 rows in set (0.00 sec)   mysql> select * from stats_mysql_query_digest; +-----------+------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | 10 | world | uworld | 0x594F2C744B698066 | select USER() | 1 | 1502789864 | 1502789864 | 0 | 0 | 0 | | 10 | world | umyworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502789849 | 1502789849 | 10264 | 10264 | 10264 | | 10 | world | uworld | 0x02033E45904D3DF0 | show databases | 1 | 1502789863 | 1502789863 | 1750 | 1750 | 1750 | | 10 | world | umyworld | 0x594F2C744B698066 | select USER() | 1 | 1502789745 | 1502789745 | 0 | 0 | 0 | | 10 | world | uworld | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1502789863 | 1502789863 | 0 | 0 | 0 | | 10 | world | uworld | 0x99531AEFF718C501 | show tables | 1 | 1502789863 | 1502789863 | 470 | 470 | 470 | | 10 | world | umyworld | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1502789745 | 1502789745 | 0 | 0 | 0 | | 10 | world | umyworld | 0x99531AEFF718C501 | show tables | 1 | 1502789745 | 1502789745 | 7427 | 7427 | 7427 | | 11 | world | uworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502789866 | 1502789866 | 3349 | 3349 | 3349 | <--- | 10 | world | umyworld | 0x02033E45904D3DF0 | show databases | 1 | 1502789745 | 1502789745 | 615 | 615 | 615 | +-----------+------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ 10 rows in set (0.00 sec)   (uworld@192.168.1.50) [world]>USE information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Database changed (uworld@192.168.1.50) [information_schema]>Select * from world.City order by Population DESC limit 5 ; +------+-----------------+-------------+--------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------------+-------------+--------------+------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 206 | São Paulo | BRA | São Paulo | 9968485 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | +------+-----------------+-------------+--------------+------------+ 5 rows in set (0.01 sec)         mysql> select * from stats_mysql_query_digest; +-----------+--------------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+--------------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | 10 | information_schema | uworld | 0x99531AEFF718C501 | show tables | 1 | 1502789929 | 1502789929 | 540 | 540 | 540 | | 10 | information_schema | uworld | 0x02033E45904D3DF0 | show databases | 1 | 1502789929 | 1502789929 | 897 | 897 | 897 | | 11 | world | uworld | 0x620B328FE9D6D71A | SELECT DATABASE() | 1 | 1502789929 | 1502789929 | 537 | 537 | 537 | | 10 | world | uworld | 0x594F2C744B698066 | select USER() | 1 | 1502789864 | 1502789864 | 0 | 0 | 0 | | 10 | world | umyworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502789849 | 1502789849 | 10264 | 10264 | 10264 | | 10 | world | uworld | 0x02033E45904D3DF0 | show databases | 1 | 1502789863 | 1502789863 | 1750 | 1750 | 1750 | | 10 | world | umyworld | 0x594F2C744B698066 | select USER() | 1 | 1502789745 | 1502789745 | 0 | 0 | 0 | | 10 | world | uworld | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1502789863 | 1502789863 | 0 | 0 | 0 | | 10 | world | uworld | 0x99531AEFF718C501 | show tables | 1 | 1502789863 | 1502789863 | 470 | 470 | 470 | | 10 | world | umyworld | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1502789745 | 1502789745 | 0 | 0 | 0 | | 10 | world | umyworld | 0x99531AEFF718C501 | show tables | 1 | 1502789745 | 1502789745 | 7427 | 7427 | 7427 | | 10 | information_schema | uworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502789933 | 1502789933 | 3707 | 3707 | 3707 | <--- | 11 | world | uworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502789866 | 1502789866 | 3349 | 3349 | 3349 | <--- | 10 | world | umyworld | 0x02033E45904D3DF0 | show databases | 1 | 1502789745 | 1502789745 | 615 | 615 | 615 | +-----------+--------------------+----------+--------------------+-----------------------------------------------------------+------------+------------+------------+----------+----------+----------+ 14 rows in set (0.01 sec)   mysql> select active,hits,destination_hostgroup, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules where destination_hostgroup < 30 ORDER BY mysql_query_rules.rule_id; +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | active | hits | destination_hostgroup | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | 1 | 0 | 10 | 10 | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 | 0 | NULL | | 1 | 2 | 11 | 11 | ^SELECT | NULL | NULL | NULL | 1 | 0 | NULL | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ 2 rows in set (0.00 sec)  

 

4 how can I transparently redirect using schema name?

This is not a Schema feature, more one of the things that in ProxySQL are quite easy to set, while close to be impossible if done in plain MySQL.
When connecting directly with MySQL there is no option for you to "play" with GRANTS and schema such that you will transparently allow a user to do an action on a schema/server pair and another on a different schema/server pair.
When using ProxySQL to filter by schemaname is quite trivial.

For instance assuming we have 3 users one is admin of the platform which include 2 separate schemas (or more), each user can access one schema for write (but that can be table as well), and a final slave with reporting information, where all the users that needs to read from other schema except their own can read cross schemas.
While all the select not cross schema mus still got to the Master.
This is not so uncommon, actually with few variant is exactly what one of the customer I spoke last week needs to do.

Let see what we need and how to do it:

  1. user(s) uworld & umyworld need to go to their default schema on Master for Writes.        
  2. user(s) uworld & umyworld should go to their default schema on  master for direct reads
  3. user(s) uworld & umyworld should go to the slave for reads when the other schema is used

To do this we will need the following rules:

 

1 2 3 4 5 6 7 8 9 10 insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) values(10,'uworld',10,1,3,'^SELECT.*FOR UPDATE',1); insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(11,'uworld',10,1,3,'^SELECT ',0,50,0); insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(12,'uworld',11,1,3,'\smyworld.',1,50,50);     insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply) values(13,'umyworld',10,1,3,'^SELECT.*FOR UPDATE',1); insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(14,'umyworld',10,1,3,'^SELECT ',0,50,0); insert into mysql_query_rules (rule_id,username,destination_hostgroup,active,retries,match_digest,apply,FlagOUT,FlagIN) values(15,'umyworld',11,1,3,'\sworld.',1,50,50);   LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;

 

 

 

To check the behaviour:

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select database(); update world.City set Population=10500000 where ID=1024; update world.Country set LifeExpectancy=62.5 where Code='IND'; update myworld.CityM set Population=10500001 where ID=1024; update myworld.CountryM set LifeExpectancy=0 where Code='IND';   select database(); Select * from world.City order by Population DESC limit 5 ; Select * from myworld.CityM order by Population DESC limit 5 ;   Select City.*, Country.name, Country.LifeExpectancy from world.City as City join world.Country as Country on City.CountryCode=Country.code order by Population DESC limit 5; Select City.*, Country.name, Country.LifeExpectancy from myworld.CityM as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5;   Select City.*, Country.name, Country.LifeExpectancy from world.City as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5;  

 

Once I run the test (queries above):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 mysql> select active,hits,destination_hostgroup, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules where destination_hostgroup < 30 ORDER BY mysql_query_rules.rule_id; +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | active | hits | destination_hostgroup | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply | flagIN | flagOUT | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ | 1 | 0 | 10 | 10 | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 | 0 | NULL | | 1 | 5 | 10 | 11 | ^SELECT | NULL | NULL | NULL | 0 | 0 | 50 | <-- 5 selects in total OK | 1 | 3 | 11 | 12 | myworld. | NULL | NULL | NULL | 1 | 50 | 50 | <-- 3 contains term myworld OK | 1 | 0 | 10 | 13 | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 | 0 | NULL | | 1 | 0 | 10 | 14 | ^SELECT | NULL | NULL | NULL | 0 | 0 | 50 | | 1 | 0 | 11 | 15 | world. | NULL | NULL | NULL | 1 | 50 | 50 | +--------+------+-----------------------+---------+---------------------+---------------+-----------------+-----------+-------+--------+---------+ 6 rows in set (0.00 sec)       mysql> select * from stats_mysql_query_digest; +-----------+------------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+------------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+ | 11 | world | uworld | 0x921512ADAF79D0FF | Select City.*, Country.name, Country.LifeExpectancy from world.City as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit ? | 1 | 1502718358 | 1502718358 | 6531 | 6531 | 6531 | | 11 | world | uworld | 0xE9D26001526F2618 | Select City.*, Country.name, Country.LifeExpectancy from myworld.CityM as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit ? | 1 | 1502718358 | 1502718358 | 6573 | 6573 | 6573 | | 10 | world | uworld | 0xE846287B5A6B3945 | Select City.*, Country.name, Country.LifeExpectancy from world.City as City join world.Country as Country on City.CountryCode=Country.code order by Population DESC limit ? | 1 | 1502718358 | 1502718358 | 3181 | 3181 | 3181 | | 11 | world | uworld | 0x55FFF888F4642D3A | Select * from myworld.CityM order by Population DESC limit ? | 1 | 1502718358 | 1502718358 | 7753 | 7753 | 7753 | | 10 | world | uworld | 0x26DB674419D1E979 | update myworld.CountryM set LifeExpectancy=? where Code=? | 1 | 1502718358 | 1502718358 | 257 | 257 | 257 | | 10 | world | uworld | 0x056615DE2CFD8C8E | update myworld.CityM set Population=? where ID=? | 1 | 1502718358 | 1502718358 | 235 | 235 | 235 | | 10 | world | uworld | 0x134B0F1E837A0B8B | Select * from world.City order by Population DESC limit ? | 1 | 1502718358 | 1502718358 | 3262 | 3262 | 3262 | | 10 | world | uworld | 0x7A15CC342D54452D | update world.Country set LifeExpectancy=?.? where Code=? | 1 | 1502718358 | 1502718358 | 319 | 319 | 319 | | 10 | world | uworld | 0x500E6F01B02078B6 | update world.City set Population=? where ID=? | 1 | 1502718358 | 1502718358 | 970 | 970 | 970 | +-----------+------------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+  

 

As we can see with this simple set of rules my uworld user was able to perform exactly as expected and able to access the schema from the designated HG.       

All the selects with schema "myworld" were redirect to HG 11.

Results details:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 (uworld@192.168.1.50) [(none)]>update world.City set Population=10500000 where ID=1024; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0   (uworld@192.168.1.50) [(none)]>update world.Country set LifeExpectancy=62.5 where Code='IND'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0   (uworld@192.168.1.50) [(none)]>update myworld.CityM set Population=10500001 where ID=1024; <--- I am NOT managing this but I could if needed ERROR 1142 (42000): UPDATE command denied to user 'uworld'@'192.168.1.50' for table 'CityM' (uworld@192.168.1.50) [(none)]>update myworld.CountryM set LifeExpectancy=0 where Code='IND'; <--- I am NOT managing this but I could if needed ERROR 1142 (42000): UPDATE command denied to user 'uworld'@'192.168.1.50' for table 'CountryM' (uworld@192.168.1.50) [(none)]>   (uworld@192.168.1.50) [(none)]>Select * from world.City order by Population DESC limit 5 ; +------+-----------------+-------------+--------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------------+-------------+--------------+------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 206 | São Paulo | BRA | São Paulo | 9968485 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | +------+-----------------+-------------+--------------+------------+ 5 rows in set (0.00 sec)   (uworld@192.168.1.50) [(none)]>Select * from myworld.CityM order by Population DESC limit 5 ; +------+-----------------+-------------+--------------+------------+ | ID | Name | CountryCode | District | Population | +------+-----------------+-------------+--------------+------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500001 | | 2331 | Seoul | KOR | Seoul | 9981619 | | 206 | São Paulo | BRA | São Paulo | 9968485 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | +------+-----------------+-------------+--------------+------------+ 5 rows in set (0.01 sec)   (uworld@192.168.1.50) [(none)]> (uworld@192.168.1.50) [(none)]>Select City.*, Country.name, Country.LifeExpectancy from world.City as City join world.Country as Country on City.CountryCode=Country.code order by Population DESC limit 5; +------+-----------------+-------------+--------------+------------+-------------+----------------+ | ID | Name | CountryCode | District | Population | name | LifeExpectancy | +------+-----------------+-------------+--------------+------------+-------------+----------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | India | 62.5 | | 2331 | Seoul | KOR | Seoul | 9981619 | South Korea | 74.4 | | 206 | São Paulo | BRA | São Paulo | 9968485 | Brazil | 62.9 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | China | 71.4 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | Indonesia | 68.0 | +------+-----------------+-------------+--------------+------------+-------------+----------------+ 5 rows in set (0.01 sec)   (uworld@192.168.1.50) [(none)]>Select City.*, Country.name, Country.LifeExpectancy from myworld.CityM as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5; +------+-----------------+-------------+--------------+------------+-------------+----------------+ | ID | Name | CountryCode | District | Population | name | LifeExpectancy | +------+-----------------+-------------+--------------+------------+-------------+----------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500001 | India | 0.0 | | 2331 | Seoul | KOR | Seoul | 9981619 | South Korea | 74.4 | | 206 | São Paulo | BRA | São Paulo | 9968485 | Brazil | 62.9 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | China | 71.4 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | Indonesia | 68.0 | +------+-----------------+-------------+--------------+------------+-------------+----------------+ 5 rows in set (0.00 sec)   (uworld@192.168.1.50) [(none)]> (uworld@192.168.1.50) [(none)]>Select City.*, Country.name, Country.LifeExpectancy from world.City as City join myworld.CountryM as Country on City.CountryCode=Country.code order by Population DESC limit 5; +------+-----------------+-------------+--------------+------------+-------------+----------------+ | ID | Name | CountryCode | District | Population | name | LifeExpectancy | +------+-----------------+-------------+--------------+------------+-------------+----------------+ | 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 | India | 0.0 | | 2331 | Seoul | KOR | Seoul | 9981619 | South Korea | 74.4 | | 206 | São Paulo | BRA | São Paulo | 9968485 | Brazil | 62.9 | | 1890 | Shanghai | CHN | Shanghai | 9696300 | China | 71.4 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | Indonesia | 68.0 | +------+-----------------+-------------+--------------+------------+-------------+----------------+ 5 rows in set (0.00 sec)  

 

Obviosuly doing it on the other user/schema work exacty the same.

 

Back to main article

LDAP with auth_pam and PHP to authenticate against MySQL

In the quest to secure MySQL as well as ease the number of complicated passwords to remember, many organizations are looking into external authentication, especially using LDAP. For free and open source, Percona’s PAM authentication plugin is the standard option.

tl;dr is I go through how to compile php-cli for use with auth_pam plugin.

Background
There are two plugins that can be used. From the documentation, the two plugins are:
  • Full PAM plugin called auth_pam. This plugin uses dialog.so. It fully supports the PAM protocol with arbitrary communication between client and server.
  • Oracle-compatible PAM called auth_pam_compat. This plugin uses mysql_clear_password which is a part of Oracle MySQL client. It also has some limitations, such as, it supports only one password input. You must use -p option in order to pass the password to auth_pam_compat.

Percona’s MySQL client supports both plugins natively. That is, you can use auth_pam or auth_pam_compat and use the “mysql” tool (or “mysqldump”, or mysql_upgrade, etc.) and you are good to go. Given the choice, we would all use auth_pam, under which clients DO NOT use mysql_clear_password.

Not all clients support auth_pam, which is the main problem. Workarounds have called for using auth_pam_compat over SSL, which is a perfectly reasonable way to handle the risk of cleartext passwords – encrypt the connection.

However, what if you want to use auth_pam?

The problem with auth_pam

Back in 2013, Percona posted about how to install and configure auth_pam and auth_pam_compat. I will not rehash that setup, except to say that most organizations no longer use /etc/shadow, so the setup involves getting the correct /etc/pam.d/mysqld in place on the server.

That article has this gem:

As of now, only Percona Server’s mysql client and an older version of HeidiSQL(version 7), a GUI MySQL client for Windows, are able to authenticate over PAM via the auth_pam plugin by default.

So, if you try to connect to MySQL using Perl, PHP, Ruby, Python and the like, you will receive this error: “Client does not support authentication protocol requested by server; consider upgrading MySQL client.”

Fast forward 4 years, to now, and this is still an issue. Happily, the article goes on to explain how to recompile clients to get them to work:

The good news is that if the client uses libmysqlclient library to connect via MySQL, you can recompile the client’s source code to use the libmysqlclient library of Percona Server to make it compatible. This involves installing Percona Server development library, compiler tools, and development libraries followed by compiling and installing the client’s source code.

And, it helpfully goes step by step on how to recompile perl-DBD-mysql to get it working with LDAP authentication (as well as without – it still works for users who do not use LDAP).

But what if you are using PHP to connect to MySQL?

PHP and auth_pam


If you try to connect, you get this error:
SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

So let us try to mirror the perl recompilation process in PHP.

Step 1

“Install Percona yum repository and Percona Server development library.” This is not a problem, do what you need to do to install Percona-Server-devel for your version.

Step 2

Install a package manager so you can build a package – optional, but useful, if you ever want to have this new client without having to recompile. As in the example, I chose the RPM package manager, so I installed rpm-build.

Step 3

Download and install the source RPM for the client package. This is where I started running into trouble. What I did not realize was that PHP does not divide out its packages like Perl does. Well, it does, but php-mysqlnd is compiled as part of the core, even though it is a separate package.

Downloading the main PHP package
So I downloaded the source RPM for PHP at https://rpms.remirepo.net/SRPMS/, and installed it into the sources directory:
cd SRPMS
wget https://rpms.remirepo.net/SRPMS/php-7.0.22-2.remi.src.rpm
cd ../SOURCES
rpm -Uvh ../SRPMS/php-7.0.22-2.remi.src.rpm

This unpacks a main file, php-7.0.22.tar.xz, plus a bunch of supplemental files (like patches, etc).

What it does NOT contain is a spec file, which is critical for building the packages.

Getting a spec file
I searched around and found one at https://github.com/iuscommunity-pkg/php70u/blob/master/SPECS/php70u.spec – this is for 7.0.21, so beware of using different versions of spec files and source code. Once that was done, I changed the mysql lines to /usr/bin/mysql_config as per Choosing a MySQL library. Note that I went with the “not recommended” library, but in this case, we WANT to compile with libmysqlclient.

Compiling php-cli, not php-mysqlnd
In addition, I discovered that compiling php-mysqlnd with the new libraries did not work. Perhaps it was something I did wrong, as at that point I was still compiling the whole PHP package and every module in it.

However, what I *did* discover is that if I recompiled the php-cli package with libmysqlclient, I was able to get a connection via PHP using LDAP authentication, via a tool written by someone else – with no changes to the tool.

Final spec file
So here is the spec file I eventually came up with. I welcome any optimizations to be made! Step 4

“Install compilers and dependencies”.
On my host I had to do a bunch of installations to get the requirements installed (your mileage may vary), including the Percona Server package for the /usr/lib64/mysql/plugin/dialog.so file:
yum install Percona-Server-server-55-5.5.55-rel38.8.el6.x86_64 libtool systemtap-sdt-devel unixODBC-devel

Step 5

“Build the RPM file”. Such an easy step, but it took about a week of back and forth with building the RPM file (which configures, tests and packages up everything), so I went between this step and updating the spec file a lot.

cd rpmbuild/SPECS/
rpmbuild -bb rpmbuild/SPECS/php-cli.spec

Then I installed my PHP file and tested it, and it worked!
# rpm -e php-cli –nodeps
# rpm -Uvh /root/rpmbuild/RPMS/x86_64/php70u-cli-7.0.22-2.ius.el6.x86_64.rpm –nodeps
Preparing… ########################################### [100%]
1:php70u-cli ########################################### [100%]

I hope you have similar success, and if you have updates to the spec files and lists of packages to install, please let me know!

What’s New With MySQL Replication in MySQL 8.0

Replication in MySQL has been around for a long time, and has been steadily improving over the years. It has been more like evolution rather than revolution. This is perfectly understandable, as replication is an important feature that many depend on - it has to work.

In the last MySQL versions, we’ve seen improvements in replication performance through support for applying transactions in parallel. In MySQL 5.6, parallelization was done on schema level - all transactions which have been executed in separate schemas could be executed at once. This was a nice improvement for those workloads that had multiple schemas on a single server, and the load was distributed more or less evenly across the schemas.

In MySQL 5.7, another parallelization method was added, so called “logical clock”. It allowed to get some level of concurrency on a slave, even if all your data has been stored in a single schema. It was based, in short, on the fact that some transactions would commit together because of a latency added by hardware. You could even add that latency manually, to achieve better parallelization on the slaves using binlog_group_commit_sync_delay.

This solution was really nice but not without drawbacks. Every delay in committing a transaction could eventually affect user-facing parts of the application. Sure, you can set delays within a range of several milliseconds, but even then, it’s additional latency which slows down the app.

Replication performance improvements in MySQL 8.0 Related resources  ClusterControl for MySQL Replication  MySQL Replication Blueprint Whitepaper  Webinar: Nine Tips for Building a Stable Replication Environment

MySQL 8.0, which as of now (August 2017) is still in beta state, brings some nice improvements to replication. Originally, it was developed for Group Replication (GR), but as GR uses regular replication under the hood, “normal” MySQL replication benefited from it. The improvement we mentioned is dependency tracking information stored in the binary log. What happens is that MySQL 8.0 now has a way to store information about which rows were affected by a given transaction (so called writeset), and it compares writesets from different transactions. This makes it possible to identify those transactions which did not work on the same subset of rows and, therefore, these may be applied in parallel. This may allow to increase the parallelization level by several times compared to the implementation from MySQL 5.7. What you need to keep in mind is that, eventually, a slave will see a different view of the data, one that never appeared on the master. This is because transactions may be applied in a different order than on the master. This should not be a problem though. The current implementation of multithreaded replication in MySQL 5.7 may also cause this issue unless you explicitly enable slave-preserve-commit-order.

To control this new behavior, a variable binlog_transaction_dependency_tracking has been introduced. It can take three values:

  • COMMIT_ORDER: this is the default one, it uses the default mechanism available in MySQL 5.7.
  • WRITESET: It enables better parallelization and the master starts to store writeset data in binary log.
  • WRITESET_SESSION: This ensures that transactions will be executed on the slave in order and the issue with a slave that sees a state of database which never was seen on the master is eliminated. It reduces parallelization but it still can provide better throughput than the default settings.
Benchmark

In July, on mysqlhighavailability.com, Vitor Oliveira wrote a post where he tried to measure the performance of new modes. He used the best case scenario - no durability whatsoever, to showcase the difference between old and new modes. We decided to use the same approach, this time in a more real-world setup: binary log enabled with log_slave_updates. Durability settings were left to default (so, sync_binlog=1 - that’s new default in MySQL 8.0, doublewrite buffer enabled, InnoDB checksums enabled etc.) Only exception in durability was innodb_flush_log_at_trx_commit set to 2.

We used m4.2xl instances, 32G, 8 cores (so slave_parallel_workers was set to 8). We also used sysbench, oltp_read_write.lua script. 16 million rows in 32 tables were stored on 1000GB gp2 volume (that’s 3000 IOPS). We tested the performance of all of the modes for 1, 2, 4, 8, 16 and 32 concurrent sysbench connections. Process was as follows: stop slave, execute 100k transactions, start slave and calculate how long it takes to clear the slave lag.

First of all, we don’t really know what happened when sysbench was executed using 1 thread only. Each test was executed five times after a warmup run. This particular configuration was tested two times - results are stable: single-threaded workload was the fastest. We will be looking into it further to understand what happened.

Other than that, the rest of the results are in line with what we expected. COMMIT_ORDER is the slowest one, especially for low traffic, 2-8 threads. WRITESET_SESSION performs typically better than COMMIT_ORDER but it’s slower than WRITESET for low-concurrent traffic.

How it can help me?

The first advantage is obvious: if your workload is on the slow side yet your slaves have tendency to fall back in replication, they can benefit from improved replication performance as soon as the master will be upgraded to 8.0. Two notes here: first - this feature is backward compatible and 5.7 slaves can also benefit from it. Second - a reminder that 8.0 is still in beta state, we don’t encourage you to use beta software on production, although in dire need, this is an option to test. This feature can help you not only when your slaves are lagging. They may be fully caught up but when you create a new slave or reprovision existing one, that slave will be lagging. Having the ability to use “WRITESET” mode will make the process of provisioning a new host much faster.

All in all, this feature will have much bigger impact that you may think. Given all of the benchmarks showing regressions in performance when MySQL handles traffic of low concurrency, anything which can help to speed up the replication in such environments is a huge improvement.

If you use intermediate masters, this is also a feature to look for. Any intermediate master adds some serialization into how transactions are handled and executed - in real world, the workload on an intermediate master will almost always be less parallel than on the master. Utilizing writesets to allow better parallelization not only improves parallelization on the intermediate master but it also can improve parallelization on all of its slaves. It is even possible (although it would require serious testing to verify all pieces will fit correctly) to use an 8.0 intermediate master to improve replication performance of your slaves (please keep in mind that MySQL 5.7 slave can understand writeset data and use it even though it cannot generate it on its own). Of course, replicating from 8.0 to 5.7 sounds quite tricky (and it’s not only because 8.0 is still beta). Under some circumstances, this may work and can speed up CPU utilization on your 5.7 slaves.

Other changes in MySQL replication

Introducing writesets, while it is the most interesting, it is not the only change that happened to MySQL replication in MySQL 8.0. Let’s go through some other, also important changes. If you happen to use a master older than MySQL 5.0, 8.0 won’t support its binary log format. We don’t expect to see many such setups, but if you use some very old MySQL with replication, it’s definitely a time to upgrade.

Default values have changed to make sure that replication is as crash-safe as possible: master_info_repository and relay_log_info_repository are set to TABLE. Expire_log_days has also been changed - now the default value is 30. In addition to expire_log_days, a new variable has been added, binlog_expire_log_seconds, which allows for more fine-grained binlog rotation policy. Some additional timestamps have been added to the binary log to improve observability of replication lag, introducing microsecond granularity.

By all means, this is not a full list of changes and features related to MySQL replication. If you’d like to learn more, you can check the MySQL changelogs. Make sure you reviewed all of them - so far, features have been added in all 8.0 versions.

As you can see, MySQL replication is still changing and becoming better. As we said at the beginning, it has to be a slow-paced process but it’s really great to see what is ahead. It’s also nice to see the work for Group Replication trickling down and reused in the “regular” MySQL replication.

Tags:  MySQL replication high availability

Docker Secrets and MySQL Password Management

In this posting we will look at currently recommended ways of managing passwords in MySQL Docker containers and explore whether the recently introduced concept of Docker Secrets could play a role in this area. Managing runtime secrets in Docker has traditionally been hard to do securely. The MySQL Docker images have typically offered various ways […]

More Details about InnoDB Compression Levels (innodb_compression_level)

In one of my previous posts, I shared InnoDB table compression statistics for a read-only dataset using the default value of innodb_compression_level (6).  In it, I claimed, without giving much detail, that using the maximum value for the compression level (9) would not make a big difference.  In this post, I will share more details about this claim. TL;DR: tuning innodb_compression_level is not

More on Studying MySQL Hashes in gdb, and How P_S Code May Help

I have to get back to the topic of checking user variables in gdb to clarify few more details. In his comment Shane Bester kindly noted that calling functions defined in MySQL code is not going to work when core dump is studied. So, I ended up with a need to check what does the my_hash_element() function I've used really do, to be ready to repeat that step by step manually. Surely I could skip that and use Python and Shane himself did, but structures of HASH type are widely used in MySQL, so I'd better know how to investigate them manually than blindly use existing code.

Quick search with grep for my_hash_element shows:
[root@centos mysql-server]# grep -rn my_hash_element *
include/hash.h:94:uchar *my_hash_element(HASH *hash, ulong idx);
mysys/hash.c:734:uchar *my_hash_element(HASH *hash, ulong idx)plugin/keyring/hash_to_buffer_serializer.cc:34:      if(store_key_in_buffer(reinterpret_cast<const IKey *>(my_hash_element(keys_hash, i)),
plugin/version_token/version_token.cc:135:  while ((token_obj= (version_token_st *) my_hash_element(&version_tokens_hash, i)))
plugin/version_token/version_token.cc:879:    while ((token_obj= (version_token_st *) my_hash_element(&version_tokens_hash, i)))
sql/sql_base.cc:1051:    TABLE_SHARE *share= (TABLE_SHARE *)my_hash_element(&table_def_cache, idx);
sql/sql_base.cc:1262:        share= (TABLE_SHARE*) my_hash_element(&table_def_cache, idx);
sql/sql_udf.cc:277:    udf_func *udf=(udf_func*) my_hash_element(&udf_hash,idx);
...
sql/table_cache.cc:180:      (Table_cache_element*) my_hash_element(&m_cache, idx);
sql/rpl_gtid.h:2123:        Node *node= (Node *)my_hash_element(hash, i);
sql/rpl_gtid.h:2274:          node= (Node *)my_hash_element(hash, node_index);
sql/rpl_tblmap.cc:168:    entry *e= (entry *)my_hash_element(&m_table_ids, i);
sql/rpl_master.cc:238:    SLAVE_INFO* si = (SLAVE_INFO*) my_hash_element(&slave_list, i);
sql-common/client.c:3245:        LEX_STRING *attr= (LEX_STRING *) my_hash_element(attrs, idx);
storage/perfschema/table_uvar_by_thread.cc:76:    sql_uvar= reinterpret_cast<user_var_entry*> (my_hash_element(& thd->user_vars, index));storage/ndb/include/util/HashMap.hpp:155:    Entry* entry = (Entry*)my_hash_element(&m_hash, (ulong)i);
storage/ndb/include/util/HashMap.hpp:169:    Entry* entry = (Entry*)my_hash_element((HASH*)&m_hash, (ulong)i);
[root@centos mysql-server]#That is, HASH structure is used everywhere in MySQL, from keyring to UDFs and table cache, to replication and NDB Cluster, with everything in between. If I can navigate to each HASH element and dump/print it, I can better understand a lot of code, if needed. If anyone cares, HASH is defined in a very simple way in include/hash.h:
typedef struct st_hash {
  size_t key_offset,key_length;         /* Length of key if const length */
  size_t blength;
  ulong records;
  uint flags;
  DYNAMIC_ARRAY array;                          /* Place for hash_keys */
  my_hash_get_key get_key;
  void (*free)(void *);
  CHARSET_INFO *charset;
  my_hash_function hash_function;
  PSI_memory_key m_psi_key;
} HASH;It relies on DYNAMIC_ARRAY to store keys.

The code of the my_hash_element function in mysys/hash.c is very simple:
uchar *my_hash_element(HASH *hash, ulong idx)
{
  if (idx < hash->records)
    return dynamic_element(&hash->array,idx,HASH_LINK*)->data;
  return 0;
}Quick search for dynamic_element shows that it's actually a macro:
[root@centos mysql-server]# grep -rn dynamic_element *
client/mysqldump.c:1608:    my_err= dynamic_element(&ignore_error, i, uint *);
extra/comp_err.c:471:      tmp= dynamic_element(&tmp_error->msg, i, struct message*);
extra/comp_err.c:692:    tmp= dynamic_element(&err->msg, i, struct message*);
extra/comp_err.c:803:  first= dynamic_element(&err->msg, 0, struct message*);
include/my_sys.h:769:#define dynamic_element(array,array_index,type) \mysys/hash.c:126:  HASH_LINK *data= dynamic_element(&hash->array, 0, HASH_LINK*);
...that is defined in include/my_sys.h as follows:
#define dynamic_element(array,array_index,type) \
  ((type)((array)->buffer) +(array_index))So, now it's clear what to do in gdb, having in mind what array do we use. Let me start the session, find a thread I am interested in and try to check elements one by one:
(gdb) thread 2
[Switching to thread 2 (Thread 0x7fc3a037b700 (LWP 3061))]#0  0x00007fc3d2cb3383 in poll () from /lib64/libc.so.6
(gdb) p do_command::thd->m_thread_id
$1 = 5
(gdb) p do_command::thd->user_vars
$2 = {key_offset = 0, key_length = 0, blength = 4, records = 3, flags = 0,
  array = {buffer = 0x7fc3ba2b3560 "\377\377\377\377", elements = 3,
    max_element = 16, alloc_increment = 32, size_of_element = 16,
    m_psi_key = 38},
  get_key = 0xc63630 <get_var_key(user_var_entry*, size_t*, my_bool)>,
  free = 0xc636c0 <free_user_var(user_var_entry*)>, charset = 0x1ded740,
  hash_function = 0xeb6990 <cset_hash_sort_adapter>, m_psi_key = 38}
(gdb) set $uvars=&(do_command::thd->user_vars)
(gdb) p $uvars
$3 = (HASH *) 0x7fc3b9fad280
...
(gdb) p &($uvars->array)
$5 = (DYNAMIC_ARRAY *) 0x7fc3b9fad2a8
(gdb) p ((HASH_LINK*)((&($uvars->array))->buffer) + (0))
$6 = (HASH_LINK *) 0x7fc3ba2b3560
(gdb) p ((HASH_LINK*)((&($uvars->array))->buffer) + (0))->data
$7 = (uchar *) 0x7fc3b9fc80e0 "H\201\374\271\303\177"
(gdb) p (user_var_entry *)(((HASH_LINK*)((&($uvars->array))->buffer) + (0))->data)
$8 = (user_var_entry *) 0x7fc3b9fc80e0
(gdb) p *(user_var_entry *)(((HASH_LINK*)((&($uvars->array))->buffer) + (0))->data)
$9 = {static extra_size = 8, m_ptr = 0x7fc3b9fc8148 "bbb", m_length = 3,
  m_type = STRING_RESULT, m_owner = 0x7fc3b9fad000, m_catalog = {
    str = 0x100000000 <Address 0x100000000 out of bounds>,
    length = 416611827727}, entry_name = {m_str = 0x7fc3b9fc8150 "a",
    m_length = 1}, collation = {collation = 0x1ded740,
    derivation = DERIVATION_IMPLICIT, repertoire = 3}, update_query_id = 25,
  used_query_id = 25, unsigned_flag = false}
...
(gdb) p *(user_var_entry *)(((HASH_LINK*)((&($uvars->array))->buffer) + (2))->data)
$11 = {static extra_size = 8, m_ptr = 0x7fc3b9e6e220 "\002", m_length = 64,
  m_type = DECIMAL_RESULT, m_owner = 0x7fc3b9fad000, m_catalog = {str = 0x0,
    length = 0}, entry_name = {m_str = 0x7fc3b9fc8290 "c", m_length = 1},
  collation = {collation = 0x1ded740, derivation = DERIVATION_IMPLICIT,
    repertoire = 3}, update_query_id = 25, used_query_id = 25,
  unsigned_flag = false}
(gdb)I tried to highlight important details above. With gdb variables it's a matter of proper type casts and dereferencing. In general, I was printing content of item (user variable in this case) with index N as *(user_var_entry *)(((HASH_LINK*)((&($uvars->array))->buffer) + (N))->data).

Now, back to printing the variables.  Let's see how this is done in performance_schema, in storage/perfschema/table_uvar_by_thread.cc:
     74   for (;;)
     75   {
     76     sql_uvar= reinterpret_cast<user_var_entry*> (my_hash_element(& thd->user_vars, index));
     77     if (sql_uvar == NULL)
     78       break;
...
     98     /* Copy VARIABLE_NAME */
     99     const char *name= sql_uvar->entry_name.ptr();
    100     size_t name_length= sql_uvar->entry_name.length();
    101     DBUG_ASSERT(name_length <= sizeof(pfs_uvar.m_name));
    102     pfs_uvar.m_name.make_row(name, name_length);
    103
    104     /* Copy VARIABLE_VALUE */
    105     my_bool null_value;
    106     String *str_value;
    107     String str_buffer;
    108     uint decimals= 0;
    109     str_value= sql_uvar->val_str(& null_value, & str_buffer, decimals);    110     if (str_value != NULL)
    111     {
    112       pfs_uvar.m_value.make_row(str_value->ptr(), str_value->length());
    113     }
    114     else
    115     {
    116       pfs_uvar.m_value.make_row(NULL, 0);
    117     }
    118
    119     index++;    120   }
So, there we check elements by index until there is no element with such index, and apply the val_str() function of the class. While debugging live server we can do the same, but if we care to see how it works step by step, here is the code from sql/item_func.cc:
String *user_var_entry::val_str(my_bool *null_value, String *str,
                                uint decimals) const
{
  if ((*null_value= (m_ptr == 0)))
    return (String*) 0;

  switch (m_type) {
  case REAL_RESULT:
    str->set_real(*(double*) m_ptr, decimals, collation.collation);    break;
  case INT_RESULT:
    if (!unsigned_flag)
      str->set(*(longlong*) m_ptr, collation.collation);    else
      str->set(*(ulonglong*) m_ptr, collation.collation);    break;
  case DECIMAL_RESULT:
    str_set_decimal((my_decimal *) m_ptr, str, collation.collation);
    break;
  case STRING_RESULT:
    if (str->copy(m_ptr, m_length, collation.collation))
      str= 0;                                   // EOM error
  case ROW_RESULT:
    DBUG_ASSERT(1);                             // Impossible
    break;
  }
  return(str);
} For INT_RESULT and REAL_RESULT it's all clear, and Shane did essentially the same in his Python code. For strings we have to copy proper items into a zero terminated string or use methods of String class if we debug on a live server to get the entire string data. For DECIMAL_RESULT I checked the implementation of str_set_decimal() that relies on decimal2string() eventually that... looks somewhat complicated (check yourself in strings/decimal.c). So, I'd better to just print my_decimal structure in gdb, for any practical purposes, instead of re-implementing this function in Python.

To summarize, HASH structure is widely used in MySQL and it is easy to dump any of these hashes in gdb, item by item, in the same way as, for example, Performance Schema in MySQL 5.7 does this of user variables. Getting string representation of my_decimal "manually" is complicated.

Extending the Power of MariaDB ColumnStore with User Defined Functions

Extending the Power of MariaDB ColumnStore with User Defined Functions david_thompson_g Fri, 08/11/2017 - 18:40

 

Introduction

MariaDB ColumnStore 1.0 supports User Defined Functions (UDF) for query extensibility. This allows you to create custom filters and transformations to suit any need. This blog outlines adding support for distributed JSON query filtering. 

An important MariaDB ColumnStore concept to grasp is that there are distributed and non-distributed functions. Distributed functions are executed at the PM nodes supporting query execution scale out. Non distributed functions are MariaDB Server functions that are executed within the UM node. As a result, MariaDB ColumnStore requires two distinct implementations of any function.

The next release, MariaDB ColumnStore 1.1, will bring support for User Defined Aggregate Functions and User Defined Window Functions.

Getting Started

To develop a User Defined Function requires familiarity with C, C++ and Linux.  At a high level the process involves:

  • Setting up a development environment to build MariaDB ColumnStore from source
  • Implementing the MariaDB Server UDF interface
  • Implementing the MariaDB ColumnStore Distributed UDF interface

You can find more information in the MariaDB knowledge base: User Defined Functions.

Example - JSON Pointer Query

In this example, I will create a simple UDF for JSON pointer queries using the RapidJSON library from Tencent. It will have the following syntax:

json_ptr(, )

The first argument is the JSON string. The second argument is the JSON pointer string.

Build Files

RapidJSON is a C++ header only library, so it must be included with the source code. I’ve chosen to add the RapidJSON source code as a submodule in the mariadb-columnstore-engine source code repository:

git submodule add https://github.com/miloyip/rapidjson utils/udfsdk/rapidjson

This will create a submodule rapidjson under the utils/udfsdk directory where the UDF SDK code is defined. 

The rapidjson include directory is added to the cmake file utils/udfsdk/CMakeLists.txt to make it available for use (last line below)

include_directories( ${ENGINE_COMMON_INCLUDES}                      ../../dbcon/mysql                      rapidjson/include)
MariaDB UDF SDK 

To implement the MariaDB Server UDF SDK, three procedures must be implemented in utils/udfsdk/udfmysql.cpp:

  • _init  : initialization / parameter validation
  • : function implementation.
  • _deinit : any clean up needed, for example freeing of allocated memory

The init function, in this example, will validate the argument count:

my_bool json_ptr_init(UDF_INIT* initid, UDF_ARGS* args, char* message) {     if (args->arg_count != 2)     {         strcpy(message,"json_ptr() requires two arguments: expression, path");         return 1;     }     return 0; }

The function implementation will take the JSON string, parse this into an object structure, execute the JSON pointer path and return a string representation of the results:

1 string json_ptr(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) 2 { 3     string json = cvtArgToString(args->arg_type[0], args->args[0]); 4     string path = cvtArgToString(args->arg_type[1], args->args[1]); 5     Document d; 6     d.Parse(json.c_str()); 7    if (Value *v = Pointer(path.c_str()).Get(d)) { 8         rapidjson::StringBuffer sb; 9         Writer<:stringbuffer> writer(sb); 10         v->Accept(writer); 11         return string(sb.GetString()); 12     } 13     else { 14         return string(); 15     } 16 }

Here is a brief explanation of the code:

  • Lines 3-4 : read the 2 arguments and convert to string.
  • Lines 5-6 : Parse the JSON string into a RapidJSON Document object .
  • Line 7 : Execute the JSON pointer path against the JSON document into the Value object.
  • Lines 8-11 : If the Value object is not null then serialize Value to a string. The Value class also offers strongly typed accessors that may also be used. Note that string values will be serialized with surrounding double quotes.
  • Line 14: If the Value object is null then return the empty string.

The following RapidJSON includes are required at the top of the file:

#include "rapidjson/document.h" #include "rapidjson/pointer.h" #include "rapidjson/stringbuffer.h" #include "rapidjson/writer.h" using namespace rapidjson;


The json_ptr_deinit function must be declared, but it is empty – there is nothing we need to do for clean up.

void json_ptr_deinit(UDF_INIT* initid) { } MariaDB ColumnStore Distributed UDF SDK

The MariaDB ColumnStore distributed UDF SDK requires defining a class instance and registering an instance for it to be usable.

First the class must be declared in utils/udfsdk/udfsdk.h. The simplest approach is to clone one of the existing reference implementations:

class json_ptr : public funcexp::Func {   public:     json_ptr() : Func("json_ptr") {}     virtual ~json_ptr() {}     execplan::CalpontSystemCatalog::ColType operationType(                 funcexp::FunctionParm& fp,        execplan::CalpontSystemCatalog::ColType& resultType);     virtual int64_t getIntVal(       rowgroup::Row& row,       funcexp::FunctionParm& fp,       bool& isNull,       execplan::CalpontSystemCatalog::ColType& op_ct);     virtual double getDoubleVal(       rowgroup::Row& row,       funcexp::FunctionParm& fp,       bool& isNull,       execplan::CalpontSystemCatalog::ColType& op_ct);      virtual float getFloatVal(        rowgroup::Row& row,        funcexp::FunctionParm& fp,        bool& isNull,        execplan::CalpontSystemCatalog::ColType& op_ct);      virtual std::string getStrVal(        rowgroup::Row& row,        funcexp::FunctionParm& fp,        bool& isNull,        execplan::CalpontSystemCatalog::ColType& op_ct);      virtual bool getBoolVal(        rowgroup::Row& row,        funcexp::FunctionParm& fp,        bool& isNull,        execplan::CalpontSystemCatalog::ColType& op_ct);       virtual execplan::IDB_Decimal getDecimalVal(         rowgroup::Row& row,         funcexp::FunctionParm& fp,         bool& isNull,                                                             execplan::CalpontSystemCatalog::ColType& op_ct);           virtual int32_t getDateIntVal(         rowgroup::Row& row,         funcexp::FunctionParm& fp,         bool& isNull,         execplan::CalpontSystemCatalog::ColType& op_ct);       virtual int64_t getDatetimeIntVal(         rowgroup::Row& row,         funcexp::FunctionParm& fp,         bool& isNull,         execplan::CalpontSystemCatalog::ColType& op_ct);   private:     void log_debug(std::string arg1, std::string arg2);   }; }

It can be seen that the following methods are defined:

  • operationType which is used to indicate the return type of the function, which could be dynamic.
  • A number of getVal methods, which perform the operation for a given return type.
  • An optional private log_debug method, which could be used for debug logs in your implementation.

Next the class is implemented in utils/udfsdk/udfsdk.cpp.  First an entry must added to register the class by name (in lower class). This is added to the UDFMap function:

FuncMap UDFSDK::UDFMap() const {     FuncMap fm;     fm["mcs_add"] = new MCS_add();     fm["mcs_isnull"] = new MCS_isnull();     fm["json_ptr"] = new json_ptr(); -- new entry for json_ptr function     return fm; }

The class is implemented, for brevity only a subset of methods is shown below:

    CalpontSystemCatalog::ColType json_ptr::operationType (       FunctionParm& fp,                                                                  CalpontSystemCatalog::ColType& resultType) {         assert (fp.size() == 2);         return fp[0]->data()->resultType();     }     void json_ptr::log_debug(string arg1, string arg2) {         logging::LoggingID lid(28); // 28 = primproc         logging::MessageLog ml(lid);         logging::Message::Args args;         logging::Message message(2);         args.add(arg1);         args.add(arg2);         message.format( args );         ml.logDebugMessage( message );     }     string json_ptr::getStrVal(Row& row,                                FunctionParm& parm,                                bool& isNull,                                CalpontSystemCatalog::ColType& op_ct) {         string json = parm[0]->data()->getStrVal(row, isNull);         string path = parm[1]->data()->getStrVal(row, isNull);         Document d;         d.Parse(json.c_str());         if (Value *v = Pointer(path.c_str()).Get(d)) {             StringBuffer sb;             Writer writer(sb);             v->Accept(writer);             return string(sb.GetString());         }         else {             return string();         }     }     double json_ptr::getDoubleVal(Row& row,                                  FunctionParm& parm,                                  bool& isNull,                                  CalpontSystemCatalog::ColType& op_ct)     {         throw logic_error("Invalid API called json_ptr::getDoubleVal");     }


The following methods are implemented:

  • operationType which validates there are exactly 2 arguments and specifies the return type as as string.
  • Optional log_debug method which illustrates how to log to the debug log.
  • getStrVal which performs the JSON evaluation. This is similar to the MariaDB UDF implementation with the exception of being strongly typed and the arguments are retrieved differently.
  • getDoubleVal illustrates throwing an error stating that this is not a supported operation. The method could be implemented of course but for simplicity this was not done.

The complete set of changes can be seen in github.

Building and Using the Example

This example can be built from a branch created from the 1.0.10 code. Once you have cloned the mariadb-columnstore-engine source tree, use the following instructions to build and install on the same server:

git checkout json_ptr_udf git submodule update --init cmake . make -j4 cd utils/udfsdk/ sudo cp libudf_mysql.so.1.0.0 libudfsdk.so.1.0.0 /usr/local/mariadb/columnstore/lib

For a multi server setup, the library files should be copied to the same location on each server. After this, restart the MariaDB ColumnStore instance to start using the functions.

mcsadmin restartSystem

The function is registered using the create function syntax:

create function json_ptr returns string soname 'libudf_mysql.so';

Now a simple example will show the user defined function in action:

create table animal( id int not null,  creature varchar(30) not null,  name varchar(30),  age decimal(18) , attributes varchar(1000) ) engine=columnstore; insert into animal(id, creature, name, age, attributes)  values (1, 'tiger', 'roger', 10, '{"fierce": true, "colors": ["white", "orange", "black"]}'),         (2, 'tiger', 'sally', 2, '{"fierce": true, "colors": ["white", "orange", "black"]}'),         (3, 'lion', 'michael', 56, '{"fierce": false, "colors": ["grey"], "weight" : 9000}'); select name, json_ptr(attributes, '/fierce') is_fierce  from animal  where json_ptr(attributes, '/weight') = '9000'; +---------+-----------+ | name    | is_fierce | +---------+-----------+ | michael | false     | +---------+-----------+ 1 row in set (0.05 sec)

In the example, you can see that the where clause uses the json_ptr function to filter on the weight element in the attributes JSON column and then the fierce element is retrieved in the select clause.

I hope this inspires you to further enhance this example or come up with your own user defined functions to extend the query capabilities of MariaDB ColumnStore.

Getting started? Download MariaDB ColumnStore today and learn how you can get started with MariaDB ColumnStore in 10 minutes.
 

MariaDB ColumnStore 1.0 supports User Defined Functions (UDF) for query extensibility. This allows you to create custom filters and transformations to suit any need. This blog will outline adding support for distributed querying of JSON data. 

Login or Register to post comments

Learning MySQL 5.7: Q & A

In this post I’ll answer questions I received in my Wednesday, July 19, 2017, webinar Learning MySQL 5.7!

First, thank you all who attended the webinar. The link to the slides and the webinar recording can be found here.

I received a number of interesting questions in the webinar that I’ve followed up with below.

Would there be a big difference on passing from 5.1 to 5.6 before going to 5.7 or, at this point, would it be roughly the same?

The biggest risk of jumping between versions, in this case 5.1 to 5.6, is reverting in case of problems. Rollbacks don’t happen often, but they do happen and you have to make sure you have the infrastructure in place whenever you decide to execute. These upgrade steps are not officially supported by Oracle nor even recommended here at Percona. Having said that, as long as your tests (checksums, pt-upgrade) and rollback plan works, this shouldn’t be a problem.

One unforgettable issue I have personally encountered is an upgrade from 5.1 via dump and reload to 5.6. The 5.6 version ran with ROW binlog format preventing replication back to 5.1 because of the limitation with the TIMESTAMP columns. Similarly, downgrading without replication means you have to deal with changes to the MySQL system schema, which obviously require some form of downtime.

Additionally, replication from 5.7 to 5.5 will not work because of the additional metadata information that 5.7 creates (i.e., GTID even when GTID is disabled).

After in-place upgrade a Percona XtraDB Cluster from 5.5 to 5.7 (through 5.6), innodb_file_per_table is enabled by default and the database is now almost twice the size. It was a 40 GB DB now it’s 80 GB due to every table has its own file but ibdata1 is still 40 GB. Is there any solution for this (that doesn’t involve mysqldump and drop tables) and how can this be avoided in future upgrades?

The reason this might be the case is that after upgrading, a number (or possibly all) of tables were [re]created. This would obviously create separate tablespaces for each. One way I can think of reclaiming that disk space is through a familiar upgrade path:

  1. Detach one of the nodes and make is an async replica of the remaining nodes in the cluster
  2. Dump and reload data from this node, then resume replication
  3. Join the other nodes from the cluster as additional nodes of a new cluster using the async replica
  4. Once there is only one node remaining in the original cluster, you can switch to the new cluster for production
  5. Rejoin the last node from the original cluster into the new cluster to complete the process

Depending on the semantics of your switch, it may or may not involve a downtime. For example, if you use ProxySQL this should be a transparent operation.

One way to avoid this problem is by testing. Testing the upgrade process in a lab will expose this kind of information even before deploying the new version into production, allowing you to adjust your process accordingly.

What is a possible impact on upgrades going from the old table format to Barracuda?

So far I am not aware of any negative impact – except if you upgrade and need to downgrade but have since created indexes with prefixes larger than what was supported on the previous version (see large_index_prefix and Barracuda documentation).

Upgrading to Barracuda and one of the supported row formats specifically allows memory constrained systems to save a little more. With BLOB/TEXT column stored off the page, they will not fill the buffer pool unless they are needed.

How do you run mysql_upgrade in parallel?

Good question, I actually wrote about it here.

Can you elaborate on ALTER progress features, and is it also applicable to “Optimization ” query?

I was not able to get more details on the “Optimization” part of this question. I can only assume this too was meant to be table rebuild via OPTIMIZE TABLE. First I would like to point out that OPTIMIZE has been an online DDL operation from 5.6 (with few limitations). As such, there is almost no point in monitoring. Also, for the cases where the online DDL does not apply to OPTIMIZE, under the hood, this is ALTER TABLE .. FORCE – a full table rebuild.

Now, for the actual ALTER process doing a table copy/rebuild, MySQL 5.7 provides some form of progress indication as to how much work has been done. However, it does not necessarily provide an estimate of the actual time it would take to complete. Each ALTER process has different phases which can vary under different conditions. Alternatively, you can also employ other ways of monitoring progress as described in the post.

We are migrated from 5.7.11 to 5.7.17 Percona Server and facing “Column 1 of table 'x.x' cannot be converted from type 'varchar(100)' to type 'varchar(100)'”.

This is interesting – what we have seen so far are errors with different datatypes or sizes, which most likely means inconsistency from the table structures if the error is coming from replication. We will need more information on what steps were taken during the upgrade to tell what happened here. Our forums would be the best place to continue this conversation. To begin with, perhaps slave_type_conversions might help if the table structures in replication are the same.

Is the Boost Geometry almost on par with Postgres GIS functions?

I cannot answer this with authority or certainty. I’ve used GIS functions in MySQL, but not developed code for it. Although Boost::Geometry was chosen because of its well-designed API, rapid development and license compatibility, it does not necessarily mean it is more mature than PostGIS (which is widely adopted).

What is the best bulk insert method for MySQL 5.7?

The best option can be different in many situations, so we have to put context here. For this reason, let me give some example scenarios and what might work best:

  • On an upgrade process where you are doing a full dump and reload, parallelizing the process by using mydumper/myloader or mysqlpump will save a lot of time depending the hardware resource available.
  • Bulk INSERT from your application that happens at regular intervals – multi-row inserts are always ideal to reduce disk writes per insert. LOAD DATA INFILE is also a popular option if you can.

Again, thank you for attending the webinar – if you have additional questions head on out to the Percona Forums!

A complete MySQL monitoring system with early-warning mechanisms: Liam Terblanche, CTO, Airvantage

Airvantage is a Value Added Service Provider for mobile telecommunication networks. Airvantage offers various gaming platforms, and subscriber retention initiatives like emergency airtime, mobile money integration, etc.

A simple solution for staying compliant

Airvantage host each customer’s database on its server (virtual) as they are bound to do so from a legislative perspective. They are presently monitoring several production servers that are spread over various countries in Africa and the Caribbean. Looking at many tools, including ManageEngine and some open source alternatives, Airvantage opted for Monyog for its simplicity, ease of config, and relatively affordable price-tag.

Liam described what he needed was “a complete MySQL monitoring system with early-warning mechanisms which allow him to sleep much better”.  With Monyog, Liam instantly started relying on the 600+ monitors and alerts to become better at proactively monitoring MySQL.

The straightforward and intuitive interface of Monyog along with the default set of alerts made the user experience truly delightful for Liam and his team. From all the benefits Monyog offers, he reckons comparing Server Config feature is the most useful ensuring all the servers are similarly configured for their particular system requirements.

Agentless Monitoring

With the agentless architecture of Monyog, Airvantage could further stay compliant, and Liam is looking forward to considering the similar approach with other solutions as well. He mentioned that “to simply connect to any remote OS/ MySQL and start monitoring, is the way it should always have been.”

Customer Satisfaction

Liam had a particular use-case where he needed an alert if the checksum of a specific table changed.  When asked about the sales and support interaction experience, he said that “the guys sat with me and assisted me until it was working to my satisfaction. The salesperson and subsequent technical support were first-class.”

You can download Monyog free trial here.

 

The post A complete MySQL monitoring system with early-warning mechanisms: Liam Terblanche, CTO, Airvantage appeared first on Webyog Blog.

MariaDB 10.1.26 now available

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.1.26. See the release notes and changelogs for details. Download MariaDB 10.1.26 Release Notes Changelog What is MariaDB 10.1? MariaDB APT and YUM Repository Configuration Generator Thanks, and enjoy MariaDB!

The post MariaDB 10.1.26 now available appeared first on MariaDB.org.

Handy JSON to MySQL Loading Script

JSON in Flat File to MySQL DatabaseSo how do you load that JSON data file into MySQL. Recently I had this question presented to me and I thought I would share a handy script I use to do such work. For this example I will use the US Zip (postal) codes from JSONAR. Download and unzip the file. The data file is named zips.json and it can not be bread directly into MySQL using the SOURCE command. It needs to have the information wrapped in a more palatable fashion.

head zips.json
{ "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA", "_id" : "01001" }
{ "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA", "_id" : "01002" }
{ "city" : "BARRE", "loc" : [ -72.10835400000001, 42.409698 ], "pop" : 4546, "state" : "MA", "_id" : "01005" }
{ "city" : "BELCHERTOWN", "loc" : [ -72.41095300000001, 42.275103 ], "pop" : 10579, "state" : "MA", "_id" : "01007" }
{ "city" : "BLANDFORD", "loc" : [ -72.936114, 42.182949 ], "pop" : 1240, "state" : "MA", "_id" : "01008" }
{ "city" : "BRIMFIELD", "loc" : [ -72.188455, 42.116543 ], "pop" : 3706, "state" : "MA", "_id" : "01010" }
{ "city" : "CHESTER", "loc" : [ -72.988761, 42.279421 ], "pop" : 1688, "state" : "MA", "_id" : "01011" }
{ "city" : "CHESTERFIELD", "loc" : [ -72.833309, 42.38167 ], "pop" : 177, "state" : "MA", "_id" : "01012" }
{ "city" : "CHICOPEE", "loc" : [ -72.607962, 42.162046 ], "pop" : 23396, "state" : "MA", "_id" : "01013" }
{ "city" : "CHICOPEE", "loc" : [ -72.576142, 42.176443 ], "pop" : 31495, "state" : "MA", "_id" : "01020" }
Follow the Document Store ExampleThe MySQL Document Store is designed for storing JSON data and this example will follow its practices by having a two column table -- a JSON column, and another column for a primary key (remember InnoDB wants so badly to have a primary key on each table that it will create one for you but it is better practice to make it yourself; besides we want to search on the zipcode which is labeled as _id in the data. So we use a stored generated column that uses JSON_UNQUOTE(JSON_EXTRACT(doc,"$_id")) and saves that info in a column named zip.

So a simple table is created and it looks like this:

mysql> desc zipcode\g
+-------------+-------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------------------+
| doc | json | YES | | NULL | |
| zip | char(5) | NO | PRI | NULL | STORED GENERATED |
+-------------+-------------+------+-----+---------+-------------------+
2 rows in set (0.00 sec) Handy ScriptSo now we have the data, we have the table, and now we need to convert the data into something MySQL can use to laod the data.

Bash is one of those shells with so many rich built-in tools that is hard to remember them all. But it does have a hand read line feature that can be used for the task.


#!/bin/bash
file="/home/dstokes/Downloads/zips.json"
while IFS= read line
do
echo "INSERT INTO zipcode (doc) VALUES ('$line');"
done Run the script and output the data to a file named foo, ./loader.sh > foo. The output shows how the data is wrapped: $head foo
INSERT INTO zipcode (doc) VALUES ('{ "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA", "_id" : "01001" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA", "_id" : "01002" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BARRE", "loc" : [ -72.10835400000001, 42.409698 ], "pop" : 4546, "state" : "MA", "_id" : "01005" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BELCHERTOWN", "loc" : [ -72.41095300000001, 42.275103 ], "pop" : 10579, "state" : "MA", "_id" : "01007" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BLANDFORD", "loc" : [ -72.936114, 42.182949 ], "pop" : 1240, "state" : "MA", "_id" : "01008" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "BRIMFIELD", "loc" : [ -72.188455, 42.116543 ], "pop" : 3706, "state" : "MA", "_id" : "01010" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHESTER", "loc" : [ -72.988761, 42.279421 ], "pop" : 1688, "state" : "MA", "_id" : "01011" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHESTERFIELD", "loc" : [ -72.833309, 42.38167 ], "pop" : 177, "state" : "MA", "_id" : "01012" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHICOPEE", "loc" : [ -72.607962, 42.162046 ], "pop" : 23396, "state" : "MA", "_id" : "01013" }');
INSERT INTO zipcode (doc) VALUES ('{ "city" : "CHICOPEE", "loc" : [ -72.576142, 42.176443 ], "pop" : 31495, "state" : "MA", "_id" : "01020" }');

So now the data can be loaded with mysql -u itisme test .

This Week in Data: Thoughts from Percona Chief Evangelist Colin Charles

Welcome to a new weekly column on the Percona blog. My name is Colin Charles, Percona Chief Evangelist, and I have been involved in MySQL, MariaDB Server and the open source community for over a decade. Now I am at Percona, and this is my weekly column.

When you start a column, you have ask yourself what you’ll be writing about. Keeping the focus on the reader is what’s crucial. With this in mind, I plan to cover happenings, pointers and maybe even musings in this column. It’s August, and while many are away on summer vacations, there’s still plenty happening in the database world. So maybe this will be a little like the now-defunct, Weekly MySQL News. It will be broader than just MySQL, however, and focus on open source databases (after all, Percona’s mission is to to champion unbiased open source database solutions).

So let’s get started! I look forward to feedback/tips via comments, or you can email me directly at colin.charles@percona.com. Feel free to socialize with me! I’m @bytebot on Twitter.

Percona Live Europe Dublin

Percona Live Open Source Database Conference Europe 2017 started in London, moved to Amsterdam (where last year it sold out at 400+ attendees) and is now docking itself in Dublin. Dublin, famous for Guinness, is also now famous a European tech hub. With our expanded conference focus beyond just the MySQL ecosystem, Percona Live Europe also includes MongoDB, PostgreSQL and other open source databases.

Where are we at with the event? The sneak peak schedule is out, and we aim to have a more or less full conference schedule by the second week of August. The conference committee is at its most diverse, with two MongoDB Masters to ensure tighter content around MongoDB, and also two whom are prominent in the DevOps world.

Naturally, evolution is good because you are now getting the “best of the best” talks, as there are less slots to compete when it comes to topics! Registration is open, and you’ll want to sign up as soon as possible to lock in the best available rates.

Percona Live Europe in Dublin is also a great place to be a sponsor as a smaller, intimate event helps ensure that people pop by your expo hall booths. This is great for promoting your products, hiring new folks and so on. Find out more about sponsorship here.

Releases Link List

In coming posts, I expect to cover upcoming events that I’m participating in, and also thoughts about one’s that I’ve been to. See you soon!

Why we still need MyISAM (for read-only tables)

TL;DR: we still need MyISAM and myisampack because it uses less space on disk (half of compressed InnoDB) ! In the previous post, I shared my experience with InnoDB table compression on a read-only dataset.  In it, I claimed, without giving much detail, that using MyISAM and myisampack would result is a more compact storage on disk.  In this post, I will share more details about this claim.

How to Configure Aurora RDS Parameters

In this blog post, we’ll look at some tips on how to configure Aurora RDS parameters.

I was recently deploying a few Aurora RDS instances, a process very similar to configuring a regular RDS instance. I noticed a few minor differences in the way you configure Aurora RDS parameters, and very few articles on how the commands should be structured (for RDS as well as Aurora). The only real literature available is the official Amazon RDS documentation.

This blog provides a concise “how-to” guide to quickly change Aurora RDS parameters using the AWS CLI. Aurora retains the parameter group model introduced with RDS, with new instances having the default read only parameter groups. For a new instance, you need to create and allocate a new parameter group (this requires a DB reboot). After that, you can apply changes to dynamic variables immediately. In other words, the first time you add the DB parameter group you’ll need to reboot even if the variable you are configuring is dynamic. It’s best to create a new DB parameter group when initializing your clusters. Nothing stops you from adding more than one host to the same DB Parameter Group rather than creating one per instance.

In addition to the DB Parameter Group, each instance is also allocated a DB Cluster Parameter Group. The DB Parameter Group is used for instance-level parameters, while the DB Cluster Parameter Group is used for cluster-level parameters (and applies to all instances in a cluster). You’ll find some of the MySQL engine variables can only be found in the DB Cluster Parameter Group. Here you will find a handy reference of all the DB cluster and DB instance parameters that are viewable or configurable for Aurora instances.

To run these commands, you’ll need to have the “aws” cli tool installed and configured. Note that the force-failover option used for RDS instances doesn’t apply to Aurora. You should perform either a controlled failover or let Aurora handle this. Also, the group family to use for Aurora is “oscar5.6”. The commands to set this up are as follows:

aws rds create-db-parameter-group --db-parameter-group-name percona-opt --db-parameter-group-family oscar5.6 --description "Percona Optimizations" aws rds modify-db-parameter-group --db-parameter-group-name percona-opt --parameters "ParameterName=max_connections,ParameterValue=5000,ApplyMethod=immediate" # For each instance-name: aws rds modify-db-instance --db-instance-identifier <instance-name> --db-parameter-group-name=percona-opt aws rds reboot-db-instance --db-instance-identifier <instance-name>

Once you create the initial DB parameter group, configure the variables as follows:

aws rds modify-db-parameter-group --db-parameter-group-name <instance-name> --parameters "ParameterName=max_connect_errors,ParameterValue=999999,ApplyMethod=immediate" aws rds modify-db-parameter-group --db-parameter-group-name <instance-name> --parameters "ParameterName=max_connect_errors,ParameterValue=999999,ApplyMethod=immediate" ## Verifying change: aws rds describe-db-parameters --db-parameter-group-name aurora-instance-1 | grep -B7 -A2 'max_connect_errors'

Please keep in mind, it can take a few seconds to propagate changes to nodes. Give it a moment before checking the values with “show global variables”. You can configure the DB Cluster Parameter group similarly, for example:

# Create a new db cluster parameter group aws rds create-db-cluster-parameter-group --db-cluster-parameter-group-name percona-cluster --db-parameter-group-family oscar5.6 --description "new cluster group" # Tune a variable on the db cluster parameter group aws rds modify-db-cluster-parameter-group --db-cluster-parameter-group-name percona-cluster --parameters "ParameterName=innodb_flush_log_at_trx_commit,ParameterValue=2,ApplyMethod=immediate" # Allocate the new db cluster parameter to your cluster aws rds modify-db-cluster --db-cluster-identifier <cluster_identifier> --db-cluster-parameter-group-name=percona-cluster # And of course, for viewing the cluster parameters aws rds describe-db-cluster-parameters --db-cluster-parameter-group-name=percona-cluster

I hope you find this article useful, please make sure to share with the community!

Pages