Planet MySQL

MySQL 8.0: if I should optimize only one query on my application, which one should it be ?

Answering this question is not easy. Like always, the best response is “it depends” !

But let’s try to give you all the necessary info the provide the most accurate answer. Also, may be fixing one single query is not enough and looking for that specific statement will lead in finding multiple problematic statements.

The most consuming one

The first candidate to be fixed is the query that consumes most of the execution time (latency). To identify it, we will use the sys schema and join it with events_statements_summary_by_digest from performance_schemato retrieve a real example of the query (see this post for more details).

Let’s take a look at what sys schema has to offer us related to our mission:

> show tables like 'statements_with%'; +---------------------------------------------+ | Tables_in_sys (statements_with%) | +---------------------------------------------+ | statements_with_errors_or_warnings | | statements_with_full_table_scans | | statements_with_runtimes_in_95th_percentile | | statements_with_sorting | | statements_with_temp_tables | +---------------------------------------------+

We will then use the statements_with_runtimes_in_95th_percentile to achieve our first task. However we will use the version of the view with raw data (not human readable formatted), to be able to sort the results as we want. The raw data version of sysschema views start with x$:

SELECT schema_name, format_time(total_latency) tot_lat, exec_count, format_time(total_latency/exec_count) latency_per_call, query_sample_text FROM sys.x$statements_with_runtimes_in_95th_percentile AS t1 JOIN performance_schema.events_statements_summary_by_digest AS t2 ON t2.digest=t1.digest WHERE schema_name NOT in ('performance_schema', 'sys') ORDER BY (total_latency/exec_count) desc LIMIT 1\G *************************** 1. row *************************** schema_name: library tot_lat: 857.29 ms exec_count: 1 latency_per_call: 857.29 ms query_sample_text: INSERT INTO `books` (`doc`) VALUES ('{\"_id\": \"00005d44289d000000000000007d\", \"title\": \"lucky luke, tome 27 : l alibi\", \"isbn10\": \"2884710086\", \"isbn13\": \"978-2884710084\", \"langue\": \"français\", \"relié\": \"48 pages\", \"authors\": [\"Guylouis (Auteur)\", \"Morris (Illustrations)\"], \"editeur\": \"lucky comics (21 décembre 1999)\", \"collection\": \"lucky luke\", \"couverture\": \" data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDABQODxIPDRQSEBIXFRQYHjIhHhwcHj0sLiQySUBMS0dARkVQWnNiUFVtVkVGZIhlbXd7gYKBTmCNl4x9lnN+gXz/2wBDARUXFx4aHjshITt8U0ZTfHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHx8fHz/wAARCAEfANwDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBA... 1 row in set (0.2838 sec)

This statement is complicated to optimize as it’s a simple insert, and it was run only once. Insert can be slower because of disk response time (I run in full durability of course). Having too many indexes may also increase the response time, this is why I invite you to have a look at these two sysschema tables:

  • schema_redundant_indexes
  • schema_unused_indexes

You will have to play with the limit of the query to find some valid candidates and then, thanks to the query_sample_text we have the possibility to run an EXPLAIN on the query without having to rewrite it !

Full table scans

Another query I would try to optimize is the one doing full table scans:

SELECT schema_name, sum_rows_examined, (sum_rows_examined/exec_count) avg_rows_call, format_time(total_latency) tot_lat, exec_count, format_time(total_latency/exec_count) AS latency_per_call, query_sample_text FROM sys.x$statements_with_full_table_scans AS t1 JOIN performance_schema.events_statements_summary_by_digest AS t2 ON t2.digest=t1.digest WHERE schema_name NOT in ('performance_schema', 'sys') ORDER BY (total_latency/exec_count) desc LIMIT 1\G *************************** 1. row *************************** schema_name: wp_lefred sum_rows_examined: 268075 avg_rows_call: 3277.0419 tot_lat: 31.31 s exec_count: 124 latency_per_call: 252.47 ms query_sample_text: SELECT count(*) as mytotal FROM wp_posts WHERE (post_content LIKE '%youtube.com/%' OR post_content LIKE '%youtu.be/%') AND post_status = 'publish' 1 row in set (0.0264 sec)

We can then see that this query was executed 124 times for a total execution time of 31.31 seconds which makes 252.47 milliseconds per call. We can also see that this query examined more than 268k rows which means that on average those full table scans are examining 3277 records per query.

This is a very good one for optimization.

Temp tables

Creating temporary tables is also sub optimal for your workload, if you have some slow ones you should have identified them already with the previous queries. But if you want to hunt those specifically, once again, sys schema helps you to catch them:

SELECT schema_name, format_time(total_latency) tot_lat, exec_count, format_time(total_latency/exec_count) latency_per_call, query_sample_text FROM sys.x$statements_with_temp_tables AS t1 JOIN performance_schema.events_statements_summary_by_digest AS t2 ON t2.digest=t1.digest WHERE schema_name NOT in ('performance_schema', 'sys') AND disk_tmp_tables=1 ORDER BY 2 desc,(total_latency/exec_count) desc LIMIT 1\G

Fortunately, I had none on my system.

Query optimization is not the most exciting part of the DBA job… but it has to be done ;-). You have now an easy method to find where to start, good luck ! And don’t forget that if you need any help, you can always joins the MySQL Community Slack channel !

Percona Server for MySQL 8.0.16-7 Is Now Available

Percona announces the release of Percona Server for MySQL 8.0.16-7 on August 15, 2019 (downloads are available here and from the Percona Software Repositories).

This release is based on MySQL 8.0.16. It includes all bug fixes in these releases. Percona Server for MySQL 8.0.16-7 is now the current GA release in the 8.0 series. All of Percona’s software is open-source and free.

Percona Server for MySQL 8.0.16 includes all the features available in MySQL 8.0.16 Community Edition in addition to enterprise-grade features developed by Percona. For a list of highlighted features from both MySQL 8.0 and Percona Server for MySQL 8.0, please see the GA release announcement.

Encryption Features General Availability (GA)

  • Temporary File Encryption (Temporary File Encryption)
  • InnoDB Undo Tablespace Encryption
  • InnoDB System Tablespace Encryption (InnoDB System Tablespace Encryption)
  • default_table_encryption  =OFF/ON (General Tablespace Encryption)
  • table_encryption_privilege_check =OFF/ON (Verifying the Encryption Settings)
  • InnoDB redo log encryption (for master key encryption only) (Redo Log Encryption)
  • InnoDB merge file encryption (Verifying the Encryption Setting)
  • Percona Parallel doublewrite buffer encryption (InnoDB Tablespace Encryption)

Known Issues

  • 5865: Percona Server 8.0.16 does not support encryption for the MyRocks storage engine. An attempt to move any table from InnoDB to MyRocks fails as MyRocks currently will see all InnoDB tables as being encrypted.

Bugs Fixed

  • Parallel doublewrite buffer writes must crash the server on an I/O error occurs. Bug fixed #5678.
  • After resetting the innodb_temp_tablespace_encrypt to OFF during runtime the subsequent file-per-table temporary tables continue to be encrypted. Bug fixed #5734.
  • Setting the encryption to ON for the system tablespace generates an encryption key and encrypts system temporary tablespace pages. Resetting the encryption to OFF, all subsequent pages are written to the temporary tablespace without encryption. To allow any encrypted tables to be decrypted, the generated keys are not erased. Modifying they innodb_temp_tablespace_encrypt does not affect file-per-table temporary tables. This type of table is encrypted if ENCRYPTION='Y' is set during table creation. Bug fixed #5736.
  • An instance started with the default values but setting the redo log to encrypt without specifying the keyring plugin parameters does not fail or throw an error. Bug fixed #5476.
  • The rocksdb_large_prefix allows index key prefixes up to 3072 bytes. The default value is changed to TRUE to match the behavior of the innodb_large_prefix. Bug fixed #5655.
  • On a server with two million or more tables, a shutdown may take a measurable length of time. Bug fixed #5639.
  • The changed page tracking uses the LOG flag during read operations. The redo log encryption may attempt to decrypt pages with a specific bit set and fail. This failure generates error messages. A NO_ENCRYPTION flag lets the read process safely disable decryption errors in this case. Bug fixed #5541.
  • If large pages are enabled on MySQL side, the maximum size for innodb_buffer_pool_chunk_size is effectively limited to 4GB. Bug fixed 5517 (upstream #94747 ).
  • The TokuDB hot backup library continually dumps TRACE information to the server error log. The user cannot enable or disable the dump of this information. Bug fixed #4850.

Other bugs fixed: #5688,
#5723, #5695, #5749, #5752, #5610, #5689, #5645, #5734, #5772, #5753, #5129, #5102, #5681, #5686, #5681, #5310, #5713, #5007, #5102, #5129, #5130, #5149, #5696, #3845, #5149, #5581, #5652, #5662, #5697, #5775, #5668, #5752, #5782, #5767, #5669, #5753, #5696, #5733, #5803, #5804, #5820, #5827, #5835, #5724, #5767, #5782, #5794, #5796, #5746, and #5748.

Note:

If you are upgrading from 5.7 to 8.0, please ensure that you read the upgrade guide and the document Changed in Percona Server for MySQL 8.0.

Find the release notes for Percona Server for MySQL 8.0.16-7 in our online documentation. Report bugs in the Jira bug tracker.

Automated Deployment of MySQL Galera Cluster to Amazon AWS with Puppet

Deployment and management your database environment can be a tedious task. It's very common nowadays to use tools for automating your deployment to make these tasks easier. Automation solutions such as Chef, Puppet, Ansible, or SaltStack are just some of the ways to achieve these goals.

This blog will show you how to use Puppet to deploy a Galera Cluster (specifically Percona XtraDB Cluster or PXC) utilizing ClusterControl Puppet Modules. This module makes the deployment, setup, and configuration easier than coding yourself from scratch. You may also want to check out one of our previous blogs about deploying a Galera Cluster using Chef,  “How to Automate Deployment of MySQL Galera Cluster Using S9S CLI and Chef.”

Our S9S CLI tools are designed to be used in the terminal (or console) and can be utilized to automatically deploy databases. In this blog, we'll show you how to do deploy a Percona XtraDB Cluster on AWS using Puppet, using ClusterControl and its s9s CLI tools to help automate the job.

Installation and Setup  For The Puppet Master and Agent Nodes

On this blog, I used Ubuntu 16.04 Xenial as the target Linux OS for this setup. It might be an old OS version for you, but we know it works with RHEL/CentOS and Debian/Ubuntu recent versions of the OS. I have two nodes that I used on this setup locally with the following host/IP:

Master Hosts:

     IP = 192.168.40.200

     Hostname = master.puppet.local

Agent Hosts:

     IP = 192.168.40.20

     Hostname = clustercontrol.puppet.local

Let's go over through the steps.

1) Setup the Master

## Install the packages required

wget https://apt.puppetlabs.com/puppet6-release-xenial.deb sudo dpkg -i puppet6-release-xenial.deb sudo apt update sudo apt install -y puppetserver

## Now, let's do some minor configuration for Puppet

sudo vi /etc/default/puppetserver

## edit from 

JAVA_ARGS="-Xms2g -Xmx2g -Djruby.logger.class=com.puppetlabs.jruby_utils.jruby.Slf4jLogger"

## to

JAVA_ARGS="-Xms512m -Xmx512m -Djruby.logger.class=com.puppetlabs.jruby_utils.jruby.Slf4jLogger"

## add alias hostnames in /etc/hosts

sudo vi /etc/hosts

## and add

192.168.40.10 client.puppet.local 192.168.40.200 server.puppet.local

## edit the config for server settings.

sudo vi /etc/puppetlabs/puppet/puppet.conf

## This can be depending on your setup so you might approach it differently than below. 

[master] vardir = /opt/puppetlabs/server/data/puppetserver logdir = /var/log/puppetlabs/puppetserver rundir = /var/run/puppetlabs/puppetserver pidfile = /var/run/puppetlabs/puppetserver/puppetserver.pid codedir = /etc/puppetlabs/code dns_alt_names = master.puppet.local,master [main] certname = master.puppet.local server = master.puppet.local environment = production runinterval = 15m

## Generate a root and intermediate signing CA for Puppet Server

sudo /opt/puppetlabs/bin/puppetserver ca setup

## start puppet server

sudo systemctl start puppetserver sudo systemctl enable puppetserver 2) Setup the Agent/Client Node

## Install the packages required

wget https://apt.puppetlabs.com/puppet6-release-xenial.deb sudo dpkg -i puppet6-release-xenial.deb sudo apt update sudo apt install -y puppet-agent

## Edit the config settings for puppet client

sudo vi /etc/puppetlabs/puppet/puppet.conf

And add the example configuration below,

[main] certname = clustercontrol.puppet.local server = master.puppet.local environment = production runinterval = 15m 3) Authenticating (or Signing the Certificate Request) for Master/Client Communication

## Go back to the master node and run the following to view the view outstanding requests.                 

sudo /opt/puppetlabs/bin/puppetserver ca list

## The Result

Requested Certificates: clustercontrol.puppet.local (SHA256) 0C:BA:9D:A8:55:75:30:27:31:05:6D:F1:8C:CD:EE:D7:1F:3C:0D:D8:BD:D3:68:F3:DA:84:F1:DE:FC:CD:9A:E1

## sign a request from agent/client

sudo /opt/puppetlabs/bin/puppetserver ca sign --certname clustercontrol.puppet.local

## The Result

Successfully signed certificate request for clustercontrol.puppet.local

## or you can also sign all request

sudo /opt/puppetlabs/bin/puppetserver ca sign --all

## in case you want to revoke, just do

sudo /opt/puppetlabs/bin/puppetserver ca revoke --certname <AGENT_NAME>

## to list all unsigned,

sudo /opt/puppetlabs/bin/puppetserver ca list --all

## Then verify or test in the client node,

## verify/test puppet agent

sudo /opt/puppetlabs/bin/puppet agent --test Scripting Your Puppet Manifests and Setting up the ClusterControl Puppet Module

Our ClusterControl Puppet module can be downloaded here https://github.com/severalnines/puppet. Otherwise, you can also easily grab the Puppet Module from Puppet-Forge. We're regularly updating and modifying the Puppet Module, so we suggest you grab the github copy to ensure the most up-to-date version of the script. 

You should also take into account that our Puppet Module is tested on CentOS/Ubuntu running with the most updated version of Puppet (6.7.x.). For this blog, the Puppet Module is tailored to work with the most recent release of ClusterControl (which as of this writing is 1.7.3). In case you missed it, you can check out our releases and patch releases over here.

1) Setup the ClusterControl Module in the Master Node

# Download from github and move the file to the module location of Puppet:

wget https://github.com/severalnines/puppet/archive/master.zip -O clustercontrol.zip; unzip -x clustercontrol.zip; mv puppet-master /etc/puppetlabs/code/environments/production/modules/clustercontrol 2) Create Your Manifest File and Add the Contents as Shown Below vi /etc/puppetlabs/code/environments/production/manifests/site.pp

Now, before we proceed, we need to discuss the manifest script and the command to be executed. First, we'll have to define the type of ClusterControl and its variables we need to provide. ClusterControl requires every setup to have token and SSH keys be specified and provided  accordingly. Hence, this can be done by running the following command below:

## Generate the key

bash /etc/puppetlabs/code/environments/production/modules/clustercontrol/files/s9s_helper.sh --generate-key

## Then, generate the token

bash /etc/puppetlabs/code/environments/production/modules/clustercontrol/files/s9s_helper.sh --generate-token

Now, let's discuss what we'll have to input within the manifest file one by one.

node 'clustercontrol.puppet.local' { # Applies only to mentioned node. If nothing mentioned, applies to all. class { 'clustercontrol': is_controller => true, ip_address => '<ip-address-of-your-cluster-control-hosts>', mysql_cmon_password => '<your-desired-cmon-password>', api_token => '<api-token-generated-earlier>' }

Now, we'll have to define the <ip-address-of-your-cluster-control-hosts> of your ClusterControl node where it's actually the clustercontrol.puppet.local in this example. Specify also the cmon password and then place the API token as generated by the command mentioned earlier.

Afterwards, we'll use ClusterControl RPC to send a POST request to create an AWS entry:

exec { 'add-aws-credentials': path => ['/usr/bin', '/usr/sbin', '/bin'], command => "echo '{\"operation\" : \"add_credentials\", \"provider\" : aws, \"name\" : \"<your-aws-credentials-name>\", \"comment\" : \"<optional-comment-about-credential-entry>\", \"credentials\":{\"access_key_id\":\"<aws-access-key-id>\",\"access_key_secret\" : \"<aws-key-secret>\",\"access_key_region\" : \"<aws-region>\"}}' | curl -sX POST -H\"Content-Type: application/json\" -d @- http://localhost:9500/0/cloud" }

The placeholder variables I set are self-explanatory. You need to provide the desired credential name for your AWS, provide a comment if you wanted to, provided the AWS access key id, your AWS key secret and AWS region where you'll be deploying the Galera nodes.

Lastly, we'll have to run the command using s9s CLI tools.

exec { 's9s': path => ['/usr/bin', '/usr/sbin', '/bin'], onlyif => "test -f $(/usr/bin/s9s cluster --list --cluster-format='%I' --cluster-name '<cluster-name>' 2> /dev/null) > 0 ", command => "/usr/bin/s9s cluster --create --cloud=aws --vendor percona --provider-version 5.7 --containers=<node1>,<node2>,<node3> --nodes=<node1>,<node2>,<node3> --cluster-name=<cluster-name> --cluster-type=<cluster-type> --image <aws-image> --template <aws-instance-type> --subnet-id <aws-subnet-id> --region <aws-region> --image-os-user=<image-os-user> --os-user=<os-user> --os-key-file <path-to-rsa-key-file> --vpc-id <aws-vpc-id> --firewalls <aws-firewall-id> --db-admin <db-user> --db-admin-passwd <db-password> --wait --log", timeout => 3600, logoutput => true }

Let’s look at the key-points of this command. First, the "onlyif" is defined by a conditional check to determine if such cluster name exists, then do not run since it's already added in the cluster. We'll proceed on running the command which utilizes the S9S CLI Tools. You'll need to specify the AWS IDs in the placeholder variables being set. Since the placeholder names are self-explanatory, its values will be taken from your AWS Console or by using the AWS CLI tools.

Now, let's check the succeeding steps remaining.

3) Prepare the Script for Your Manifest File

# Copy the example contents below (edit according to your desired values) and paste it to the manifest file, which is the site.pp.

node 'clustercontrol.puppet.local' { # Applies only to mentioned node. If nothing mentioned, applies to all. class { 'clustercontrol': is_controller => true, ip_address => '192.168.40.20', mysql_cmon_password => 'R00tP@55', mysql_server_addresses => '192.168.40.30,192.168.40.40', api_token => '0997472ab7de9bbf89c1183f960ba141b3deb37c' } exec { 'add-aws-credentials': path => ['/usr/bin', '/usr/sbin', '/bin'], command => "echo '{\"operation\" : \"add_credentials\", \"provider\" : aws, \"name\" : \"paul-aws-sg\", \"comment\" : \"my SG AWS Connection\", \"credentials\":{\"access_key_id\":\"XXXXXXXXXXX\",\"access_key_secret\" : \"XXXXXXXXXXXXXXX\",\"access_key_region\" : \"ap-southeast-1\"}}' | curl -sX POST -H\"Content-Type: application/json\" -d @- http://localhost:9500/0/cloud" } exec { 's9s': path => ['/usr/bin', '/usr/sbin', '/bin'], onlyif => "test -f $(/usr/bin/s9s cluster --list --cluster-format='%I' --cluster-name 'cli-aws-repl' 2> /dev/null) > 0 ", command => "/usr/bin/s9s cluster --create --cloud=aws --vendor percona --provider-version 5.7 --containers=db1,db2,db3 --nodes=db1,db2,db3 --cluster-name=cli-aws-repl --cluster-type=galera --image ubuntu18.04 --template t2.small --subnet-id subnet-xxxxxxxxx --region ap-southeast-1 --image-os-user=s9s --os-user=s9s --os-key-file /home/vagrant/.ssh/id_rsa --vpc-id vpc-xxxxxxx --firewalls sg-xxxxxxxxx --db-admin root --db-admin-passwd R00tP@55 --wait --log", timeout => 3600, logoutput => true } } Let's Do the Test and Run Within the Agent Node /opt/puppetlabs/bin/puppet agent --test The End Product

Now, let's have a look once the agent is being ran. Once you have this running, visiting the URL http://<cluster-control-host>/clustercontrol, you'll be asked by ClusterControl to register first. 

Now, you wonder where's the result after we had run the RPC request with resource name 'add-aws-credentials' in our manifest file, it'll be found in the Integrations section within the ClusterControl.  Let's see how it looks like after the Puppet perform the runbook.

You can modify this in accordance to your like through the UI but you can also modify this by using our RPC API. 

Now, let's check the cluster,

From the UI view, it shows that it has been able to create the cluster, display the cluster in the dashboard, and also shows the job activities that were performed in the background.

Lastly, our AWS nodes are already present now in our AWS Console. Let's check that out,

All nodes are running healthy and are expected to its designated names and region.

Conclusion

In this blog, we are able to deploy a Galera/Percona Xtradb Cluster using automation with Puppet. We did not create the code from scratch, nor did we use any external tools that would have complicated the task. Instead, we used the CusterControl Module and the S9S CLI tool to build and deploy a highly available Galera Cluster.

Tags:  MySQL galera cluster percona xtradb cluster percona amazon AWS cloud database automation Puppet

MySQL 8.0 Memory Consumption on Small Devices

Recently, PeterZ pointed a huge difference in memory usage of MySQL 8.0 compare to MySQL 5.7. This can be an issue for small instances if the same configuration for buffers like the buffer pool are not changed.

As explained in Peter’s article, this can lead to the awakening of the so feared OOM Killer !

MorganT, pointed accurately in his comment what is the source of such difference and how this was then caused by the new instrumentation added in MySQL 8.0.

Nothing is free, even as a beer. There is always a cost for more features.

This is a small non exhaustive list relating some additions in Performance_Schema:

However, if you plan to use MySQL 8.0 on small devices and still benefit of Performance_Schema, you can reduce its memory consumption.

Here is an example of the memory consumption of a fresh installed MySQL 8.0.17 with Peter’s config (adapted for native MySQL 8.0):

+---------------------------+---------------+ | code_area | current_alloc | +---------------------------+---------------+ | memory/innodb | 319.66 MiB | | memory/performance_schema | 268.40 MiB | <-- | memory/mysys | 8.58 MiB | | memory/sql | 3.59 MiB | | memory/temptable | 1.00 MiB | | memory/mysqld_openssl | 134.50 KiB | | memory/mysqlx | 3.44 KiB | | memory/vio | 912 bytes | | memory/myisam | 696 bytes | | memory/csv | 88 bytes | | memory/blackhole | 88 bytes | +---------------------------+---------------+

The total memory consumption is the following:

MySQL 8.0> select * from sys.memory_global_total; +-----------------+ | total_allocated | +-----------------+ | 615.54 MiB | +-----------------+

Now let’s adapt the Performance_Schema configuration to reduce it’s memory consumption and see the result:

+---------------------------+---------------+ | code_area | current_alloc | +---------------------------+---------------+ | memory/innodb | 319.66 MiB | | memory/performance_schema | 89.88 MiB | <-- | memory/mysys | 8.58 MiB | | memory/sql | 3.59 MiB | | memory/temptable | 1.00 MiB | | memory/mysqld_openssl | 134.50 KiB | | memory/mysqlx | 3.44 KiB | | memory/vio | 912 bytes | | memory/myisam | 696 bytes | | memory/csv | 88 bytes | | memory/blackhole | 88 bytes | +---------------------------+---------------+

We can see the total memory used:

MySQL 8.0> select * from sys.memory_global_total; +-----------------+ | total_allocated | +-----------------+ | 437.39 MiB | +-----------------+

These are the changes performed:

MySQL 8.0> SELECT t1.VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.variables_info t1 JOIN performance_schema.global_variables t2 ON t2.VARIABLE_NAME=t1.VARIABLE_NAME WHERE t1.VARIABLE_SOURCE = 'PERSISTED'; +----------------------------------------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +----------------------------------------------------------+----------------+ | performance_schema_digests_size | 1000 | | performance_schema_error_size | 1 | | performance_schema_events_stages_history_long_size | 1000 | | performance_schema_events_statements_history_long_size | 1000 | | performance_schema_events_transactions_history_long_size | 1000 | | performance_schema_events_waits_history_long_size | 1000 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_digest_length | 512 | | performance_schema_max_mutex_classes | 210 | | performance_schema_max_rwlock_classes | 50 | | performance_schema_max_sql_text_length | 512 | | performance_schema_max_stage_classes | 150 | | performance_schema_max_thread_classes | 50 | +----------------------------------------------------------+----------------+

So, indeed it’s the default instrumentation settings may not fit small instances but it’s not very complicated to modify them. This is how I modified them using the new SET PERSIST statement:

set persist_only performance_schema_events_waits_history_long_size=1000;

If you are interested in this new statement, please check these articles:

Problem Queries are Killing Your Database Performance

Author: Robert Agar

Tuning database performance is a complicated task that can be a thorn in the side of the database team. There are many interconnected components and environmental aspects that come under consideration when attempting to optimize the performance of your database systems. A DBA can be hard-pressed to determine where to begin their optimization efforts.

An initial investigation may concentrate on the network and hardware on which the database is running. These inquiries may uncover issues that can be easily identified and addressed. It may be a simple matter of adding some disk space or upgrading memory on the database’s server. You might be able to move the database to a less-used network segment to improve the response time when satisfying user requests. Maybe there are conflicting applications or processes on the same server that are impacting the availability of resources to power the database.

Hopefully, some of these steps allow you to achieve the performance gains that you seek. But what if they don’t? Numerous attempts at modifying system parameters and juggling components can leave you right where you started. Your database is still underperforming and pressure is building to resolve the problem. It’s time to dig into the programming logic that provides functionality to the database’s users. Don’t be frightened, but you need to go under the hood and take a look at your SQL queries and optimize the ones that are negatively impacting the system.

Methods of Improving SQL Query Performance

Multiple factors can influence the speed at which SQL queries are executed. This also means that there are a variety of ways to address SQL queries that are causing deficient database performance. If you are responsible for MySQL databases, here are some methods that may improve the execution speed of your SQL queries.

  • Use the MySQL full-text search facility. Searching your database with wildcards can lead to significantly slower response time. Here’s some sample code that illustrates adding this feature to a query. Making this type of change can drastically speed up your queries by narrowing the query and eliminating unnecessary scanning for data.

mysql>Alter table students ADD FULLTEXT (first_name, last_name);

mysql>Select * from students where match(first_name, last_name) AGAINST (‘Ade’);

  • Fine-tune your Like statements. You should stay away from Like expressions that contain leading wildcards. A leading wildcard in a query inhibits MySQL from utilizing indexes and forces it to perform a full table scan. This is probably not the most efficient way to query your database, and eliminating the wildcard will lead to improved response time.

  • Another way to improve Like statement performance is by using the Union clause. Using a Union rather than the ‘or’ operator can help reduce the potential that the MySQL optimizer will retrieve records via a full table scan.

  • Indexing columns where it is appropriate can lead to performance gains. An index can increase the speed at which MySQL server returns query results. Adding indexes to columns that employ ‘where’ and ‘group by’ clauses can help you achieve better query performance.

  • Care must be taken when adding indexes to your database tables. Incorrect indexing can impact the overall well-being of your system and lead to degraded performance. Before haphazardly adding indexes to your database tables you should perform two checks. These steps are to verify the database’s existing structure and to confirm the size of the table. This information can help you determine if the index you intend to add is already present in some form and help you estimate the time impact of adding the index. 

Finding the Right Queries to Optimize

Having techniques that allow you to optimize a database’s SQL queries certainly presents a way to improve the system’s performance. However, you may face some difficulty in establishing which queries deserve your attention. SQL Diagnostic Manager for MySQL furnishes a DBA with a comprehensive performance monitor that can help identify the SQL queries that will provide the greatest benefits by being optimized.

SQL Diagnostic Manager for MySQL provides a host of features that will help you optimize the performance of your MySQL and MariaDB systems. It supplies real-time details of slow or locked queries and allows you to kill them if necessary. You can easily identify the top 10 queries based on execution time, which gives you a great starting point for query optimization. The tool works on databases located on-premises as well as those residing in the cloud. It’s an excellent way to find and address the problem SQL queries that may be dragging down your database performance.

The post Problem Queries are Killing Your Database Performance appeared first on Monyog Blog.

A Guide to Automated Cloud Database Deployments

Complex, inflexible architectures, redundancy and out-of-date technology, are common problems for companies facing data to cloud migration. 

We look to the “clouds,” hoping that we will find there a magic solution to improve operational speed and performance, better workload and scalability, less prone and less complicated architectures. We hope to make our database administrator's life more comfortable. But is it really always a case? 

As more enterprises are moving to the cloud, the hybrid model is actually becoming more popular. The hybrid model is seen as a safe model for many businesses. 

In fact, it's challenging to do a heart transplant and port everything over immediately. Many companies are doing a slow migration that usually takes a year or even maybe forever until everything is migrated. The move should be made in an acceptable peace.

Unfortunately, hybrid means another puzzle piece that not necessary to reduce complexity. Perhaps as many others walking this road before you, you will find out that some of the applications will actually not move. 

Or you will find out that the other project team just decided to use yet another cloud provider. 

For instance, it is free, and relatively easy, to move any amount of data into an AWS EC2 instance, but you'll have to pay to transfer data out of AWS. The database services on Amazon are only available on Amazon. Vendor lock-in is there and should not be ignored.

Along the same lines, ClusterControl offers a suite of database automation and management functions to give you full control of your database infrastructure. On-prem, in the cloud and multiple vendors, support.

With ClusterControl, you can monitor, deploy, manage,  and scale your databases, securely and with ease through our point-and-click interface.

Utilizing the cloud enables your company and applications to profit from the cost-savings and versatility that originate with cloud computing.

Supported Cloud Platforms

ClusterControl allows you to run multiple databases on the top of the most popular cloud providers without being locked-in to any vendor. It has offered the ability to deploy databases (and backup databases) in the cloud since ClusterControl 1.6. 

The supported cloud platforms are Amazon AWS, Microsoft Azure and Google Cloud. It is possible to launch new instances and deploy MySQL, MariaDB, MongoDB, and PostgreSQL directly from the ClusterControl user interface. 

The recent ClusterControl version (1.7.4) added support for the MySQL Replication 8.0, PostgreSQL and TimescaleDB from Amazon AWS, Google Cloud Platform, and Microsoft Azure.

  Cloud Providers Configuration

Before we jump into our first deployment we need to connect ClusterControl with our cloud provider.
It’s done in the Integrations panel. 

The tool will walk you through the Cloud integration with the straightforward wizard. As we can see in the below screenshot first, we start with one of the three big players Amazon Web Services (AWS), Google Cloud and Microsoft Azure.

In the next section, we need to provide the necessary credentials.

When all is set and ClusterControl can talk with your cloud provider we can go to the deployment section.

Cloud Deployment Process

In this part, you want to select the supported cluster type, MySQL Galera Cluster, MongoDB Replica Set, or PostgreSQL Streaming Replication, TimescaleDB, MySQL Replication. 

The next move is to pick the supported vendor for the selected cluster type. At the moment, the following vendors and versions are:

  • MySQL Galera Cluster - Percona XtraDB Cluster 5.7, MariaDB 10.2, MariaDB 10.3

  • MySQL Replication Cluster - Percona Server 8.0, MariaDB Server 10.3, Oracle MySQL Server 8.0

  • MongoDB Replica Set - Percona Server for MongoDB 3.6, MongoDB 3.6, MongoDB 4.0

  • PostgreSQL Cluster - PostgreSQL 11.0

  • TimescaleDB 11.0

The deployment procedure is aware of the functionality and flexibility of the cloud environments, like the type of VM's dynamic IP and hostname allocation, NAT-ed public IP address, virtual private cloud network or storage.

In the following dialog:

Most of the settings in this step are dynamically populated from the cloud provider by the chosen credentials. You can configure the operating system, instance size, VPC setting, storage type, and size and also specify the SSH key location on the ClusterControl host. You can also let ClusterControl generate a new key specifically for these instances.

When all is set you will see your configuration. At this stage, you can also pick up additional subnet.

 Verify if everything is correct and hit the "Deploy Cluster" button to start the deployment.

You can then monitor the progress by clicking on the Activity -> Jobs -> Create Cluster -> Full Job Details:

Depending on the cluster size, it could take 10 to 20 minutes to complete. Once done, you will see a new database cluster listed under the ClusterControl dashboard.

Under the hood, the deployment process did the following:

  • Create SSH key
  • Create cloud VM instances
  • Configure security groups and networking (firewalls, subnets)
  • Verify the SSH connectivity from ClusterControl to all created instances
  • Prepare VM’s for a specific type of cluster (VM node configuration like package installation, kernel configuration, etc)
  • Deploy a database on every instance
  • Configure the clustering or replication links
  • Register the deployment into ClusterControl

 After the deployment, you can review the process and see what exactly was executed. With the extended logging, you can see each command. You can see who triggered the job and what was the outcome.
If at any point you want to extend your cluster, you can use the scaling which is also integrated with your cloud provider.

The process is simple. In the first phase, you choose the desired VM type.

Finally, you can  choose the master node and remaining settings which depends on your cluster type:

Conclusion

We showed you how to set up your database MySQL Replication environment on Microsoft Azure, it only took a couple of clicks to build virtual machines, network, and finally a reliable master/slave replication cluster. With new scaling in the cloud functionality, you can also easily expand cluster whenever needed. 

This is just a first step if you want to see what to do next check out our other blogs where we talk about auto-recovery, backups, security and many other aspects of day to day administration with ClusterControl. Want to try it by yourself? Give it a try.

Tags:  cloud deployment AWS Google Cloud azure microsoft azure Amazon EC2 MySQL MariaDB PostgreSQL

How to solve SELinux and MySQL log rotation issue

Disable SELinux? Think again!

SELinux is always a complicated topic. If you search this on the web, most people will advise just disabling it, but that may not be acceptable from a security point of view in your organization. In this case, we are going to see how to solve an issue with SELinux and MySQL log rotation

We had configured log rotation as per this post. The scripts seemed to work perfectly when running manually. However, when running under cron they would fail to run. Since there were no other errors in the logs, eventually I tracked that down to SELinux. What I found is that SELinux default policies prevent logrotate daemon from making the changes to files outside of /var/log. In this case, MySQL logs were living on /var/lib/mysql so that was clearly the problem.

Figuring out SELinux

The first thing to do when debugging a SELinux issue, is to set permissive mode on a test server.

# setenforce 0

This mode will have the effect of letting processes make any changes they need, and SELinux will record any operations that would be blocked if we were running in enforcing mode.

Now, we set up our logrotate cron with the script, and wait for the next run to happen. After the next run, we can check /var/log/audit/audit.log. After filtering the info we need, we can display the error in a human-readable format by running the following command:

# grep logrotate /var/log/audit/audit.log | audit2allow -w -a

This will generate one or more messages like the following:

type=AVC msg=audit(1557805022.012:2213614): avc: denied { setattr } for pid=4645 comm="logrotate" name="mysql-error.log" dev="dm-1" ino=4891395 scontext=system_u:system_r:logrotate_t:s0-s0:c0.c1023 tcontext=system_u:object_r:mysqld_db_t:s0 tclass=file Was caused by: Missing type enforcement (TE) allow rule. You can use audit2allow to generate a loadable module to allow this access.

Now we have some more details about the problem. If the above command doesn’t work for you, it’s likely you will need to install some helper packages e.g.

# yum -y install policycoreutils-python Generating custom policy files

Once we have the information, we can go ahead and build a custom SELinux policy that will (hopefully) solve the issue. The first step is to generate a .te policy file, which is in human-readable format:

# grep logrotate /var/log/audit/audit.log | audit2allow -m mypol > mypol.te # cat mypol.te module mypol 1.0; require { type mysqld_db_t; type logrotate_t; class file setattr; } #============= logrotate_t ============== allow logrotate_t mysqld_db_t:file setattr;

Now we have to convert the .te file to a policy module:

# checkmodule -M -m -o mypol.mod mypol.te

and also compile the policy by running:

# semodule_package -o mypol.pp -m mypol.mod

Finally, we can install the generated .pp file by doing:

semodule -i mypol.pp

Now we can check the list of installed policies to validate it is present:

# semodule -l ... mrtg 1.9.0 mta 2.7.3 munin 1.9.1 mypol 1.0 ...

Now everything should work, right? Think again! In my testing, I found that sometimes I needed to repeat the above process several times to gather all the necessary rules, as some messages appeared in audit log only after I fixed the previous error. The good thing is you can combine all rules that are displayed by audit2allow in a single policy file.

Even more fun!

Another thing that can complicate debugging is that some policies have a way to bypass auditing which means that even if there is a denied operation, you won’t see it in audit.log. You can rebuild existing policies without the “don’t audit” rules to get every single message to display as follows:

semodule -DB

After you do your debugging, you can build it back with “don’t audit” rules included again:

semodule -B Closing thoughts

I am sure by now you love SELinux as much as I do… To sum up, the policy that worked for this case is:

module pol 1.0; require { type mysqld_db_t; type logrotate_t; class dir { add_name read remove_name write }; class file { create ioctl open read rename getattr setattr unlink write }; } #============= logrotate_t ============== allow logrotate_t mysqld_db_t:dir { add_name read remove_name write }; allow logrotate_t mysqld_db_t:file { create ioctl open read rename getattr setattr unlink write };

Note that with this policy, logrotate can also make changes to any mysqld_db_t file, which means datafiles and/or redo log files for example. Ideally, one would create a new file type just for log files, but this will be subject of another blog post. Happy SELinuxing.

Improved MySQL Query Performance With InnoDB Mutli Value Indexes

Multi-Valued Indexes are going to change the way you think about using JSON data and the way you architect your data. Before MySQL 8.0.17 you could store data in JSON arrays but trying to search on that data in those embedded arrays was tricky and usually required a full table scan.  But now it is easy and very quick to search and to access the data in JSON arrays.
Multi-Valued IndexesA Multi-Valued Index (MVI) is a secondary index defined on a column made up of an array of values.  We are all used to traditional indexes where you have one value per index entry, a 1:1 ratio.  A MVI can have multiple records for each index record.  So you can have multiple postal codes, phone numbers, or other attributes from one JSON document indexed for quick access. See Multi-Valued Indexes for details.

For a very simple example, we will create a table. Note the casting of the $.nbr key/values as an unsigned array.

mysql> CREATE TABLE s (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> name CHAR(20) NOT NULL,
    -> j JSON,
    -> INDEX nbrs( (CAST(j->'$.nbr' AS UNSIGNED ARRAY)))
    -> );
Query OK, 0 rows affected (0.11 sec)

Then add in some data. The goal is to have a set of multiple values available under the 'nbr' key where each number in the array represents some enumerated attribute.
mysql> SELECT * FROM s; +----+-------+---------------------+ | id | name  | j                   | +----+-------+---------------------+ |  1 | Moe   | {"nbr": [1, 7, 45]} | |  2 | Larry | {"nbr": [2, 7, 55]} | |  3 | Curly | {"nbr": [5, 8, 45]} | |  4 | Shemp | {"nbr": [3, 6, 51]} | +----+-------+---------------------+ 4 rows in set (0.00 sec)
So we want to search on one of the values in the 'nbr' arrays.  Before 8.0.17, you could probably manage with a very elaborate JSON_CONTAINS() or JSON_EXTRACT() calls that have to handle multiple positions in that array.  But with MySQL 8.0.17 you can check to see if a desired value is a member of the array very easily, And there is another new function, MEMBER OF() that can take advantage of MVIs.
mysql>  SELECT * FROM s WHERE 7 MEMBER OF (j->"$.nbr"); +----+-------+---------------------+ | id | name  | j                   | +----+-------+---------------------+ |  1 | Moe   | {"nbr": [1, 7, 45]} | |  2 | Larry | {"nbr": [2, 7, 55]} | +----+-------+---------------------+ 2 rows in set (0.00 sec)
So we had two records with the number 7 in the array.  Think abut how many times you have multiple uses of things like postcodes, phone numbers, credit cards , or email addresses tied to a master record. Now you can keep all that within one JSON document and not have to make multiple dives into the data to retrieve that information. Imagine you have a 'build sheet' of a complex product, say a car, and you wanted to be able to quickly find the ones with certain attributes (GPS, tinted windows, and red leather seats).  A MVI give you a way to quickly and efficiently search for these attributes.

And for those curious about the query plan:
mysql> EXPLAIN SELECT * FROM s WHERE 7 MEMBER OF (j->"$.nbr")\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: s    partitions: NULL          type: ref possible_keys: nbrs           key: nbrs       key_len: 9           ref: const          rows: 1      filtered: 100.00         Extra: Using where 1 row in set, 1 warning (0.00 sec)
And yes the optimizer handles the new indexes easily. There are some implementation notes below that you will want to familiarize yourself with to make sure you know all the fine points of using MVIs at the end of this blog entry.
A Bigger ExampleLets create a table with one million rows with randomly created data inside a JSON array. Let us use a very simple table with a primary key and a JSON column that will supply the JSON array for the secondary index.

mysql>desc a1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| data  | json             | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

I wrote a quick PHP script to generate data on STDOUT to a temporary file. And that temporary file was fed in using the MySQL source command.  It is my personal preference to load data this way and probably a bit of a personality quirk but it does allow me to truncate or drop table definitions and re-use the same data.

<?php

for ($x=1; $x < 1000000; $x++) {
$i = rand(1,10000000);
$j = rand(1,10000000);
$k = rand(1,10000000);
echo "INSERT into a1 (id,data) VALUES (NULL,'{\"nbr\":[$i,$j,$k]}');\n";
}
?>

An example line from the file looks like this:


INSERT into a1 (id,data) VALUES (NULL,'{"nbr":[8526189,5951170,68]}');

The  entries in the array should have a pretty large cardinality with ranges between 1 and 10,000,000, especially considering there are only 1,000,000 rows.

Array subscripts in JSON start with a 0 (zero). And remember that the way to get to the third item in the array would be SELECT data->>"$.nbr[2]" for future reference. And is we wanted to check $.nbr[0] to $.nbr[N] we would have to explicitly check each one. Not pretty and expensive to perform.
My benchmark system is an older laptop with an i5 processor with 8k of ram filled with Ubuntu goodness.  So hopefully this would be a worst case scenario for hardware as nobody would run such old & slow gear in production, right (nobody runs gear slow than me, wink-wink nudge-nudge)?  The reason for such antiquated system usage is that comparisons would (or should) so similar gains on a percentage basis.

So lets us start by looking for a $.nbr[0] = 99999.  I added one record with all three elements in the array as five nines to make for a simple example.


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9718585
     filtered: 100
        Extra: Using where
1 row in set, 1 warning (0.0004 sec)
Note (code 1003): /* select#1 */ select `test`.`a1`.`id` AS `id`,json_unquote(json_extract(`test`.`a1`.`data`,'$.nbr')) AS `data->>"$.nbr"` from `test`.`a1` where (json_unquote(json_extract(`test`.`a1`.`data`,'$.nbr[0]')) = 99999)

And there are no indexes available to be used and it is a full table scan, as indicated in the type: ALL above.  The query runs in about 0.61 seconds.

In the previous example we created the index with the table but this time it is created after the able. And I could have used ALTER TABLE too.

CREATE INDEX data__nbr_idx ON a1( (CAST(data->'$.nbr' AS UNSIGNED ARRAY)) );

So first trial query:

SELECT id, data->>"$.nbr" 
FROM a 
WHERE data->>"$.nbr[2]" = 99999

We have to pick a specific entry in the array as we can not search each item of the array (at least until we can use MVIs). The query runs in about 0.62 seconds, or a fraction slower but close enough for me to say they are the same time.  And EXPLAIN shows this is a full table scan and it does not take advantage of that index just created. So how do we access this new index and take advantage of the MVIs?


New Functions To The RescueThere are new functions that can take advantage of MVIs when used to the right of the WHERE clause in a query with InnoDB tables. One of those functions is MEMBER OF().

SELECT _id, data->>"$.nbr" 
FROM a1
WHERE 99999 MEMBER OF (data->"$.nbr");

This query runs in 0.001 seconds which is much faster than the previous time of 0.61!  And we are searching all the data in the array not just one slot in the array. So if we do not know if the data we want is in $.nbr[0] or $.nbr[N], we can search all of the array entries easily.  So we are actually looking at more data and at a much faster rate. 

We can also use JSON_CONTAINS() and JSON_OVERLAPS() see Three New JSON Functions in MySQL 8.0.17 fro details.  These three functions are designed to take full advantage of Multi-Value indexes.

SELECT id, data->>"$.nbr" 
FROM a1 
WHERE JSON_CONTAINS(data->'$.nbr',cast('[99999,99999]' as JSON) );

+---------+-----------------------+
| id      | data->>"$.nbr"        |
+---------+-----------------------+
| 1000000 | [99999, 99999, 99999] |
+---------+-----------------------+
1 row in set (0.0013 sec)


SELECT id, data->>"$.nbr"  FROM a1  WHERE JSON_OVERLAPS(data->'$.nbr',cast('[99999,99999]' as JSON) );
+---------+-----------------------+
| id      | data->>"$.nbr"        |
+---------+-----------------------+
| 1000000 | [99999, 99999, 99999] |
+---------+-----------------------+
1 row in set (0.0012 sec)


Fine Points You can create MVIs with CREATE TABLE, ALTER TABLE, or CREATE INDEX statements, just like any other index.The values are cast as a same-type scalar in a SQL array, A virtual column is transparently generated with all the values of the array and then a functional index is created on the virtual column.
Only one MVI can be used in a composite index. 
You can use MEMBER OF(), JSON_CONTAINS(), or JSON_OVERLAPS() in the WHERE clause to take advantage of MVIs. But once again you can you those three functions on non MVI JSON Data too.
DML for MVIs work like other DMLs for Indexes but you may have more than one insert/updates for a single clustered index record.
Empty arrays are not added to the index so do not try to search for empty values via the index.
MVIs do not support ordering of values so do not use them for primary keys! And no ASC or DSC either!!
And you are limited to 644,335 keys and 10,000 bytes by InnoDB for a single record.  The limit is a single InnoDB undo log page size so you should get up to 1250 integer values.
MVIs can not be used in a foreign key specification.

And check the cardinality of you data.  Having a very narrow range of numbers indexed will not really gain extra performance.





Minimalist Tooling for MySQL/MariaDB DBAs

In my roles as a DBA at various companies, I generally found the tooling to be quite lacking. Everything from metrics collection, alerting, backup management; they were either missing, incomplete or implemented poorly. DBA-Tools was born from a desire to build backup tools that supported my needs in smaller/non-cloud environments. As BASH is easily the most common shell available out there on systems running MySQL® or MariaDB®, it was an easy choice.

How DBA-Tools came to be

While rebuilding my home-lab two years ago, I decided I wanted some simple tools for my database environment. Being a fan of NOT re-inventing the wheel, I thought I would peruse GitHub and Gitlab to see what others have put together. Nothing I saw looked quite like what I wanted. They all hit one or more of the checkboxes I wanted, but never all of them.

My checklist when searching for tools included the following features:

  • Extendable
  • Configurable
  • User Friendly
  • Easy-to-Read

The majority of scripts I found were contained within a single file and not easy to extend. They were universally easy-to-use. My subjective requirement for code quality simply was not met. When I considered what kits were already available to me against the goal I had in mind, I came to the only reasonable conclusion I could muster:

I would build my own tools!

A trip down release lane and publicity

DBA-Tools was designed to be simple, extendible and configurable. I wanted my kit to have very few external dependencies. BASH was the shell I chose for implementation and I grew my vision from there. At the most fundamental level, I enjoy simplicity. I consider procedural programming to be just that – simple. This, thus far, remains my guiding philosophy with these tools.

My first public release was on July 7th, 2019. The scripts only did single full backups and most of the secondary scripts only worked with MariaDB. I posted about it in one of the MySQL Slack groups. The tools were written for my lab use and, while I hoped others would find my offering useful, the lack of noticeable response did not bother me.

The second release, 22 days later, marked full incremental support and ensured all the secondary scripts supported MySQL and MariaDB. I decided to call this one 2.0.0 and posted it again. I received my first “support” email that day, which spurred me to create better documentation.

Later, I found out that Peter Zaitsev posted about the tools I wrote on his Twitter and LinkedIn pages on August 11th 2019. I can’t say thank you enough – I didn’t expect these tools to be used much beyond a small niche of home-lab engineers that might stumble across them.

What’s next?

As of this writing, I’m working on adding extensible, easy-to-use alerting facilities to these tools. I’m always ready to accept PRs and help from anyone that would like to add their own features.

Now, I just need to get significantly better with git.

In any case, check them out at http://gitlab.com/gwinans/dba-tools or read the Wiki at https://gitlab.com/gwinans/dba-tools/wikis/home


Photo by Iker Urteaga on Unsplash The content in this blog is provided in good faith by members of the open source community. Percona has not edited or tested the technical content. Views expressed are the authors’ own. When using the advice from this or any other online resource test ideas before applying them to your production systems, and always secure a working back up.

The post Minimalist Tooling for MySQL/MariaDB DBAs appeared first on Percona Community Blog.

MySQL 8 and MySQL 5.7 Memory Consumption on Small Devices

While we often run MySQL on larger scale systems in Production for Test and Dev, sometimes we want to run MySQL on the tiniest cloud instances possible or just run it on our laptops. In these cases, MySQL 8 and MySQL 5.7 memory consumption is quite important.

In comparing MySQL 8 vs MySQL 5.7, you should know that MySQL 8 uses more memory. Basic tests on a 1GB VM with MySQL 8 and MySQL 5.7 (actually they’re Percona Server versions) running the same light workload, I see the following vmstat output:

MySQL 5.7 vmstat output

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 4 0 65280 71608 58352 245108 0 0 2582 3611 1798 8918 18 9 11 33 30 4 0 65280 68288 58500 247512 0 0 2094 2662 1769 8508 19 9 13 30 29 3 1 65280 67780 58636 249656 0 0 2562 3924 1883 9323 20 9 7 37 27 4 1 65280 66196 58720 251072 0 0 1936 3949 1587 7731 15 7 11 36 31

MySQL 8.0 vmstat output

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 9 1 275356 62280 60832 204736 0 0 2197 5245 2638 13453 24 13 2 34 27 3 0 275356 60548 60996 206340 0 0 1031 3449 2446 12895 25 11 10 28 26 7 1 275356 78188 59564 190632 0 1 2448 5082 2677 13661 26 13 6 30 25 4 1 275356 76516 59708 192096 0 0 2247 3750 2401 12210 22 12 4 38 24

As you can see, MySQL 8 uses some 200MB more swap and also uses less OS cache, signaling more memory being allocated and at least “committed.” If we look at the “top” output we see:

MySQL 5.7

MySQL 8.0

This also shows more Resident memory and virtual memory used by MySQL8.   Virtual Memory, in particular, is “scary” as it is well in excess of the 1GB of physical memory available on these VMs.  Of course, Virtual Memory usage (VSZ) is a poor indicator of actual memory needs for modern applications, but it does corroborate the higher memory needs story.

In reality, though, as we know from the “vmstat” output, neither MySQL 8 nor MySQL 5.7 is swapping with this light load, even though there isn’t much “room” left. If you have more than a handful of connections or wish to run some applications on the same VM, you would get swapping (or OOM killer if you have not enabled swap).

It would be an interesting project to see how low I can drive MySQL 5.7 and MySQL 8 memory consumption, but I will leave it to another project. Here are the settings I used for this test:

[mysqld] innodb_buffer_pool_size=256M innodb_buffer_pool_instances=1 innodb_log_file_size=1G innodb_flush_method=O_DIRECT innodb_numa_interleave=1 innodb_flush_neighbors=0 log_bin server_id=1 expire_logs_days=1 log_output=file slow_query_log=ON long_query_time=0 log_slow_rate_limit=1 log_slow_rate_type=query log_slow_verbosity=full log_slow_admin_statements=ON log_slow_slave_statements=ON slow_query_log_always_write_time=1 slow_query_log_use_global_control=all innodb_monitor_enable=all userstat=1

Summary:  When moving to MySQL 8 in a development environment, keep in mind it will require more memory than MySQL 5.7 with the same settings.

Shell/Bash Commands Execution from MySQL Client or Stored Procedure/function


Today I am going to explain ways to execute shell or bash commands from mysql clients or stored procedure and function. There are basically 2 method to do so: Method 1: Use MySQL Client inbuilt feature  To run single command: \! command or system command. eg \! uptime or system command  To get terminal \! bash or \! sh
Method 2: Deploy external plugin (lib_mysqludf_sys) Step 1: Download lib_mysqludf_sys from github:
git clone https://github.com/mysqludf/lib_mysqludf_sys.git Step 2: Install libmysqlclient15-dev, for Ubuntu you can use:
apt-get install libmysqlclient15-dev Step 3: Note down o/p of:
mysql -uroot -pxxxx -e "select @@plugin_dir;" Step 4: Change directory to git clone dir.
cd lib_mysqludf_sys/ Step 5: Compile and put plugin in plugin dir
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o {value from step 3}lib_mysqludf_sys.so -fPIC Step 6: Create necessary functions for lib_mysqludf_sys:
mysql -uroot -pxxx mysql -e "source lib_mysqludf_sys.sql" Step 7: library lib_mysqludf_sys contains a number of functions that allows one to interact with the operating system.
sys_eval - executes an arbitrary command, and returns it's output eg: select sys_eval("df -h") sys_exec - executes an arbitrary command, and returns it's exit code eg: select sys_exec("df -h") sys_get - gets the value of an environment variable. sys_set - create or update an environment variable. lib_mysqludf_sys_info - information about the currently installed version of lib_mysqludf_sys.

Shinguz: FromDual Ops Center for MariaDB and MySQL 0.9.2 has been released

FromDual has the pleasure to announce the release of the new version 0.9.2 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center for MariaDB and MySQL (focmm) can be downloaded from here. How to install and use focmm is documented in the Ops Center User Guide.

In the inconceivable case that you find a bug in the FromDual Ops Center for MariaDB and MySQL please report it to the FromDual bug tracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Installation of Ops Center 0.9.2

A complete guide on how to install FromDual Ops Center you can find in the Ops Center User Guide.

Upgrade from 0.9.x to 0.9.2

Upgrade from 0.9.x to 0.9.2 should happen automatically. Please do a backup of your Ops Center Instance before you upgrade! Please also check Upgrading.

Changes in Ops Center 0.9.2 Instance
  • Replication: Mariadb 10.4 problem with mysql.user table fixed.
  • Restore: Clean-up work done.
  • Restore: Point-in-Time-Recovery is implemented.
  • Operations: Binary Log state added.
Security
  • Potential XSS hole fixed.
  • Bootstrap CSS updated to 4.3.1
General
  • Newest MyEnv Library added.
  • Function array_pop replaced by array_shift.
  • Tests against MariaDB 10.4 were successful. MariaDB 10.4 is officially supported now.
Build and Packaging
  • Dependency bug in Debian control file fixed.
Taxonomy upgrade extras:  Operations release Backup failover Restore FromDual Ops Center ops center

4th Mydbops Database Meetup

Partnering to participate knowledge sharing for the community of Database Administrators.

In continuation to the community contribution with knowledge gained out of experience and networking, Mydbops IT Solutions, conducted its 4th Database Meetup on Saturday, 3rd of August, 2019. Here was the first time, that we had changed our venue. With the like-minded sponsor in M/s.Zenefits Technologies India Pvt. Ltd. They played a role of a perfect host for all of us as attendees. Their venue was a perfectly equipped to suit the knowledge sharing exercise.

Key note by Mr.Ramesh Aithal, Head of Engineering at Zenefits was build on statistics, of how the Industry is taking shape and the galore of opportunities in the coming days. The attendees from multiple companies were a great booster for this community meetup.

The presence of knowledge seekers and knowledge providers made this event a great platform for knowledge sharing. 94% of the participants felt that the topics were relevant and useful for their knowledge prowess. On an average, all the speakers have got a “Take-away rate” of 65% which ensured the meetup objectives being achieved. Topic of MongoDb Sharding has got the high retention rate of 70%. Galera 4 has got the second topic with high retention rate at 66%. The topics of ZFS and Galera 4 had the most number of “To-be-known” curiosity topics.

MySQL Topic has got highest number of Questions being clarified. Every Session had good interactive Q&A discussions, among the participants and the speakers. Best Q&A session had been of MongoDb Sharded Cluster, as the speaker displayed his in-depth knowledge on the topic.

The Speakers being :

  1. Using ZFS files with MySQL – Mr.Bajrang Panigrahi, Zenefits Technologies India Private Ltd.
  2. MongoDB Sharded Cluster – How to design your Topology – Mr. Vinodh Krishnaswamy, Percona.
  3. InnoDB scalability improvements in MySQL 8.0 – Mr. Karthik P R, Mydbops IT Solutions
  4. What is new is Galera 4? – Mr. Aakash Muthuramalingam,Mydbops IT Solutions.

We also had a special invite extended by Ms. Revathi Rangachary of Oracle India for one of their upcoming event.  Then the event concluded with a Pizza grab at 01:00 PM. The whole gathering takes lots of pride by singing our National Anthem in one voice as a prelude for our 73rd Independence Day Celebrations. This was an emotional moment for all of the participants.

The speakers were felicitated with mementos and beautiful bouquets by the founders of Mydbops IT Solutions, Mr.Karthik P.R., and Mr.Vinod Khanna at the end of event.  Group photo of all the happy and enlightened participants was taken as a tradition continues.

Special thanks to Mr. Vinay, Mr. Ramesh Aithal, Mr. Sagar Birla and Mr. Bajrang of Zenefits Technologies Pvt. Ltd., and Mr. Selva Venkatesh, Mr. Benedict Henry and Mr.Manthiramoorthy (Mandy) of Mydbops IT Solutions. for seamlessly and smoothly organizing the event.

The Next event is tentatively scheduled for October 2019. Follow us on LinkedIn (https://www.linkedin.com/company/7775692/) to know the exact date, time and venue of 5th Mydbops Database Meetup.

Presentations of speakers for your reference :

InnoDB Scalability improvements in MySQL 8.0 from Mydbops

Using ZFS file system with MySQL from Mydbops

What is new in Galera 4 ? from Mydbops

MongoDB sharded cluster. How to design your topology ? from Mydbops Networking happening during the session break.. “A TRUE SPIRIT OF MEETUP”Felicitation to all the contributors, including, involved participants.one’s who took the dais!!! The Enthusiasts, who made the event, fun and knowledge sharing platform !!! 4th Mydbops Database Meetup : At-a-Glance

SET PERSIST in MySQL: A Small Thing for Setting System Variable Values

To set correct system variable values is the essential step to get the correct server behavior against the workload.
In MySQL, we have many System variables that can be changed at runtime, and most of them can be set at the session or global level.

To change the value of a system variable at the global level in the past, users needed to have SUPER privileges. Once the system variable value is modified as global, the server will change this behavior for the session, and obviously as global scope.

For instance, one of the most commonly adjusted variables is probably max_connections.

If you have max_connection=100 in your my.cnf or as the default value, and during the day as DBA you notice that it is not enough, it is easy just to add new connections on the fly with the command:

SET GLOBAL MAX_CONNECTIONS=500;

This will do the work.

But here is the issue. We had changed a GLOBAL value, that applies to the whole server, but this change is ephemeral and if the server restarts, the setting is lost. In the past, I have seen millions of times servers with different configurations between my.cnf and Current Server settings. To prevent this, or at least keep it under control, good DBAs had developed scripts to check if and where the differences exist and fix them. The main issue is that very often, we forget to update the configuration file while doing the changes, or we do it on purpose to do “Fine-tuning first” and forgot afterward.

What’s new in MySQL8 about that?

Well, we have a couple of small changes. First of all the privileges, as for MySQL8 users can have SYSTEM_VARIABLES_ADMIN or SUPER to modify the GLOBAL system variables. Also, the ability to have GLOBAL changes to variable to PERSIST on disk and finally, to know who did it and when.

The new option for SET command is PERSIST

So, if I have:

(root@localhost) [(none)]>show global variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1500 | +-----------------+-------+

and I want to change the value of max_connection and be sure this value is reloaded at the restart, I will do this:

(root@localhost) [(none)]>set PERSIST max_connections=150; (root@localhost) [(none)]>show global variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 150 | +-----------------+-------+

With the usage of PERSIST, MySQL will write the information related to:

– key (variable name)
– value
– timestamp (including microseconds)
– user
– host

A new file in the data directory: mysqld-auto.cnf contains the information. The file is in Json format and will have the following:

{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "150" , "Metadata" : { "Timestamp" : 1565363808318848 , "User" : "root" , "Host" : "localhost" } } } }

The information is also in Performance Schema:

select a.VARIABLE_NAME,b.VARIABLE_value ,SET_TIME,SET_USER,SET_HOST from performance_schema.variables_info a join performance_schema.global_variables b on a.VARIABLE_NAME=b.VARIABLE_NAME where b.VARIABLE_NAME like 'max_connections'\G *************************** 1. row *************************** VARIABLE_NAME: max_connections VARIABLE_value: 150 SET_TIME: 2019-08-09 11:16:48.318989 SET_USER: root SET_HOST: localhost

As you see, it reports who did the change, from where, when, and the value. Unfortunately, there is no history here, but this can be easily implemented.

To clear the PERSIST settings, run RESET PERSIST and all the Persistent setting will be removed.

If you have:

{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565367524946371 , "User" : "root" , "Host" : "localhost" } } , "wait_timeout" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565368154036275 , "User" : "root" , "Host" : "localhost" } } } }

RESET PERSIST will do:

{ "Version" : 1 , "mysql_server" : { }

Which is removing ALL THE SETTINGS, not just one.

Anyhow, why is this a good thing to have?

First, because we have no excuse now when we change a variable, as we have all the tools needed to make sure we will have it up at startup if this is the intention of the change.

Second, it is good because storing the information in a file, and not only showing it from PS, allows us to include such information in any automation tool we have. This is in case we decide to take action or just to keep track of it, like comparison with my.cnf and fixing the discrepancies automatically also at service down or when cloning. On this let me say that WHILE you can change the value in the file mysqld-auto.cnf, have the server at restart use that value as the valid one.

This is not recommended, instead please fix my.cnf and remove the information related to PERSIST.

To touch that file is also dangerous because if you do stupid things like removing a double quote or in any way affecting the Json format, the server will not start, but there will be NO error in the log.

{ "Version" : 1 , "mysql_server" : { "wait_timeout": { "Value : "150" , "Metadata" : { "Timestamp" : 1565455891278414, "User" : "root" , "Host" : "localhost" } } } } ^^^ missing double quote

tusa@tusa-dev:/opt/mysql_templates/mysql-8.0.17futex$ ps aux|grep 8113 tusa 8119 0.0 0.0 14224 896 pts/1 S+ 12:54 0:00 grep --color=auto 8113 [1]+ Exit 1 bin/mysqld --defaults-file=./my.cnf

I have opened a bug for this (https://bugs.mysql.com/bug.php?id=96501).

A short deep dive in the code (you can jump it if you don’t care)

The new feature is handled in the files <source>/sql/persisted_variable.(h/cc). The new structure dealing with the PERSIST actions is:

struct st_persist_var { std::string key; std::string value; ulonglong timestamp; std::string user; std::string host; bool is_null; st_persist_var(); st_persist_var(THD *thd); st_persist_var(const std::string key, const std::string value, const ulonglong timestamp, const std::string user, const std::string host, const bool is_null); };

And the main steps are in the constructors st_persist_var. It should be noted that when creating the timestamp, the code is generating a value that is NOT fully compatible with the MySQL functions FROM_UNIXTIME.

The code assigning the timestamp value pass/assign also passes the microseconds from the timeval (tv) structure:

st_persist_var::st_persist_var(THD *thd) { timeval tv = thd->query_start_timeval_trunc(DATETIME_MAX_DECIMALS); timestamp = tv.tv_sec * 1000000ULL + tv.tv_usec; user = thd->security_context()->user().str; host = thd->security_context()->host().str; is_null = false; }

Where:

tv.tv_sec = 1565267482 tv.tc_usec = 692276

will generate:
timestamp = 1565267482692276

This TIMESTAMP is not valid in MySQL and cannot be read from the time functions, while the segment related to tv.tv_sec = 1565267482 works perfectly.

(root@localhost) [(none)]>select FROM_UNIXTIME(1565267482); +---------------------------+ | FROM_UNIXTIME(1565267482) | +---------------------------+ | 2019-08-08 08:31:22 | +---------------------------+ (root@localhost) [(none)]>select FROM_UNIXTIME(1565267482692276); +---------------------------------+ | FROM_UNIXTIME(1565267482692276) | +---------------------------------+ | NULL | +---------------------------------+

This because the timestamp with microseconds is formatted differently in MySQL :
PERSIST_code = 1565267482692276
MySQL = 1565267482.692276

If I run: select FROM_UNIXTIME(1565267482.692276);

I get the right result:

+----------------------------------+ | FROM_UNIXTIME(1565267482.692276) | +----------------------------------+ | 2019-08-08 08:31:22.692276 | +----------------------------------+

And of course, I can use the trick:

select FROM_UNIXTIME(1565267482692276/1000000); +-----------------------------------------+ | FROM_UNIXTIME(1565267482692276/1000000) | +-----------------------------------------+ | 2019-08-08 08:31:22.6922 | +-----------------------------------------+

Well, that’s all for the behind the scene info.  Keep this in mind if you want to deal with the value coming from the Json file.

SET PERSIST Conclusion

Sometimes the small things can be better than the HUGE shiny things. Many times I saw DBAs in trouble because they do not have this simple feature in MySQL, and many MySQL fails to start or doesn’t behave as expected. Given that, I welcome SET PERSIST and I am sure that the people who manage thousands of servers, with different workloads and automation in place, will see this as a good thing as well.

References:

https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestamp
https://lefred.be/content/where-does-my-mysql-configuration-variable-value-come-from/
https://lefred.be/content/what-configuration-settings-did-i-change-on-my-mysql-server/

How to limit the SQL query result set to Top-N rows only

Introduction In this article, we are going to see how we can limit the SQL query result set to the Top-N rows only. Limiting the SQL result set is very important when the underlying query could end up fetching a very large number of records, which can have a significant impact on application performance. Why limit the number of rows of a SQL query? Fetching more data than necessary is the number one cause of data access performance issues. When a given business use case is developed, the amount of data available... Read More

The post How to limit the SQL query result set to Top-N rows only appeared first on Vlad Mihalcea.

ProxySQL 2.0.5 and proxysql-admin tool Now Available

ProxySQL 2.0.5, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.5 which fixes many bugs and introduces a number of features and enhancements. The proxysql-admin tool has been enhanced to support the following new options:

  • The --add-query-rule option creates query rules for synced MySQL users. This option is only applicable for the singlewrite mode and works together with the --syncusers and --sync-multi-cluster-users options.
  • The --force option skips existing configuration checks in mysql_servers, mysql_users and mysql_galera_hostgroups tables. This option will only work together with the –enable option: proxysql-admin --enable --force.
  • The --update-mysql-version option updates the mysql-server_version variable in ProxySQL with the version from a node in Percona XtraDB Cluster.

The ProxySQL 2.0.5 source and binary packages available from the Percona download page for ProxySQL include proxysql-admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.5 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

ProxySQL 2.0.5 Improvements
  • PSQLADM-49: Create rules for –syncusers. When running with --syncusers or --sync-multi-cluster-users, the --add-query-rule option can now be specified to add the singlewriter query rules for the new users.
  • PSQLADM-51: Update mysql-server_version variable. The --update-mysql-version command has been added to set the mysql-server_version__ global variable in ProxySQL.  This will take the version from a node in the cluster and set it in ProxySQL.
Bugs Fixed
  • PSQLADM-190: The --remove-all-servers option did not work on enable. When running with proxysql-cluster, the galera hostgroups information was not replicated which could result in failing to run --enable on a different ProxySQL node.  The --force option was added for --enable to be able to ignore any errors and always configure the cluster.
  • PSQLADM-199: query-rules removed during proxysql-cluster creation with PXC operator. When using the PXC operator for Kubernetes and creating a proxysql-cluster, the query rules could be removed.  The code was modified to merge the query rules (rather than deleting and recreating).  If the --force option was specified, then a warning was issued in case any existing rules were found; otherwise an error was issued. The --disable-updates option was added to ensure that ProxySQL cluster updates did not interfere with the current command.
  • PSQLADM-200: users were not created for –-syncusers with PXC operator. When using the PXC operator for Kubernetes, the --syncusers command was run but the mysql_users table was not updated.  The fix for PSQLADM-199 that suggested to use --disable-updates also applies here.

ProxySQL is available under Open Source license GPLv3.

SQL Check Constraint Example | Check Constraint In SQL Tutorial

SQL Check Constraint Example | Check Constraint In SQL Tutorial is today’s topic. Check constraint is used for specifying the predicate that every tuple must satisfy in a relation i.e. it is used for limiting the values that a column can hold in a relation. It is used for giving a condition to check the value to be entered into a record. If the condition results in false then that value will not be added to the record.

SQL Check Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Some key points of SQL Check Constraint are following.

  1. Check constraint cannot be defined inside the SQL views.
  2. Check constraint only refers to the column of that table in which it is created not in the other table.
  3. A subquery is not allowed in check constraint.
  4. Check constraint is not only defined inside the CREATE TABLE statement but also in an ALTER TABLE statement.
#Syntax: (Create Statement) CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL], column2 datatype [ NULL | NOT NULL], ... CONSTRAINT constraint_name CHECK [ NOT FOR REPLICATION] (column_name condition) ); #Parameters
  1. Table_name: Name of the table in which constraint is to be specified.
  2. Constraint_name: Name of the Constraint. It can be anything.
  3. Column_name: Name of the column in which constraint is to be specified.
  4. Condition: Condition that must be met by the constraint.

Let’s look at the example to understand the above concept.

#EXAMPLE: 1

See the following query.

CREATE TABLE Employee ( ID INT NOT NULL, Name VARCHAR (30) NOT NULL, Age INT, GENDER VARCHAR (9), check (GENDER in ('Male', 'Female')) );

So, here we have created a table named Employee which contains ID, Name, Age and its gender. Now, when we start entering the values inside the table check constraint in the above SQL command will restrict the gender value up to two categories.

If any other tuple is inserted with gender value other than of these two, then the database operation will be aborted.

#EXAMPLE: 2 CREATE TABLE Student ( ID INT NOT NULL, Name VARCHAR (30) NOT NULL, Age INT NOT NULL, GENDER VARCHAR (9), check (Age >= 18) );

So, here we have created a table called Student which will contain the data of all students present in the university. The constraint specified here is Age which will not allow the entry of that student whose age is less than 18.

If, however, the following SQL statement is used.

INSERT INTO Student (ID, Name, Age, Gender) VALUES (1, 'Roger', 16, 'Male');

This tuple will not be updated in the Student database as age is 16 which is less than 18.

#SYNTAX: (Alter Statement) ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name condition); #PARAMETERS
  1. Table_name: Name of the table. ‘
  2. Constraint_name: Name of the Constraint. It can be anything.
  3. Column_name: Name of the column in which constraint is to be specified.
  4. Condition: Condition that must be met by the constraint.
#EXAMPLE ALTER TABLE Student ADD CONSTRAINT check_gender CHECK (Gender IN ('Male', 'Female'));

So, in our example, we have created a constraint named check_gender which will restrict the entry of tuples which does not contain the categories of gender as mentioned above.

#SYNTAX: (Disable Check Constraint)

This is mainly used in Transact-SQL.

ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name; #PARAMETERS
  • Table_name: Name of the table. ‘
  • Constraint_name: Name of the Constraint. It can be anything.
#EXAMPLE ALTER TABLE Student NOCHECK CONSTRAINT check_Gender;

So, the above query will disable the Check constraint.

#SYNTAX: (Enable Check Constraint)

This is mainly used in Transact-SQL.

ALTER TABLE table_name WITH CHECK CHECK CONSTRAINT constraint_name; #PARAMETERS
  1. Table_name: Name of the table. ‘
  2. Constraint_name: Name of the Constraint. It can be anything.
#EXAMPLE ALTER TABLE Student WITH CHECK CHECK CONSTRAINT check_Gender;

So, here in the above query, we have enabled check_gender which was previously disabled as done above.

#SYNTAX: (DROPPING A CHECK CONSTRAINT) ALTER TABLE table_name DROP CONSTRAINT constraint_name; #PARAMETERS
  1. Table_name: Name of the table. ‘
  2. Constraint_name: Name of the Constraint. It can be anything.
#EXAMPLE ALTER TABLE Student DROP CONSTRAINT check_gender;

So, in the above query, we have deleted the constraint named check_gender.

Finally, SQL Check Constraint Example | Check Constraint In SQL Tutorial is over.

The post SQL Check Constraint Example | Check Constraint In SQL Tutorial appeared first on AppDividend.

SQL String Functions Example | String Functions In SQL

SQL String Functions Example | String Functions In SQL is today’s topic. String functions are used for manipulating the strings. Here, in SQL, we provide the string as input and then after applying string functions desired manipulated string is obtained. Let’s discuss all the string functions with proper explanation and examples.

SQL String Functions

See the following String Functions.

#ASCII()

The ASCII function is used for finding the ascii value of the character i.e. the numeric value. If more than one character is inserted within the function then the leftmost character value will be displayed. Here, we are using SQL SELECT Statement.

#Syntax SELECT ASCII('ab'); #Output 97 #CHAR_LENGTH()

The char_length() function is used for finding the length of a word.

#Syntax SELECT char_length('Shubh'); #Output 5 #CHARACTER_LENGTH()

The character_length() function is used for returning the length of an entire string.

#Syntax SELECT CHARACTER_LENGTH ('God Is Great'); #Output 12 #CONCAT()

The concat() function is used for adding two words or strings. It may have one or more arguments, if all arguments are non-binary strings, then the result is a non-binary string.

#SYNTAX SELECT CONCAT ('My', 'S', 'QL'); #OUTPUT: MySQL #CONCAT_WS()

The concat_ws() is used for adding two words or strings with a symbol as concatenating symbol.

#Syntax SELECT CONCAT_WS ('_', 'God', 'is', 'Great'); #Output God_is_Great #FIND_IN_SET()

The find_in_set() function is used for finding a symbol from a set of symbols. This mainly returns the position in which the symbol is present.

#Syntax SELECT FIND_IN_SET ('b', 'a, b, c, d, e, f'); #Output 2 #FORMAT() 

The format() function is used for displaying a number in the given format.

#Syntax SELECT Format ("0.981", "Percent"); #Output ‘98.10%’ #INSERT()

The insert() function is used for inserting the data into a database.

#SYNTAX: INSERT INTO database (Id, Name) VALUES (1, 'Shubh'); #INSTR()

The instr() function is used for finding the occurrence of an alphabet. It generally displays the first occurrence of the string.

#Syntax SELECT INSTR ('God is Great', 'e'); #Output 11 #LCASE(): 

The lcase() function is used for converting the given string into lower case.

#Syntax: SELECT LCASE ("Welcome to Appdividend”); #Output Welcome to appdividend #LEFT()

The left() function is used for selecting a substring from the left of given size or characters.

#Syntax SELECT LEFT ('Appdividend.com', 5); #Output Appdi #LENGTH()

The length() function is used for finding the length of a word.

#Syntax: SELECT LENGTH('Shubh'); #Output 13 #LOCATE()

The locate() function is used for finding the nth position of a given word in the string.

#Syntax SELECT LOCATE ('is', 'GodisGreat', 1); #Output 4 #Lower()

The lower() function is used for converting the upper-case string into lower case.

#Syntax SELECT LOWER ('APPDIVIDEND.COM'); #Output appdividend.com #LPAD()

The lpad() function is used for returning the string argument, left-padded with the specified string.

#Syntax SELECT LPAD ('Shubh', 8, '0'); #Output 000Shubh #LTRIM()

The ltrim() function is used for cutting the given substring from the original string.

#Syntax SELECT LTRIM ('44App', '44'); #Output App #MID()

The mid() function is used for returning a substring starting from the specified position.

#Syntax Select Mid ("LoveisLife", 6, 2); #Output sLi #POSITION()

The position() function is used for finding position of the first occurrence of a given alphabet.

#Syntax SELECT POSITION ('e' IN 'AppDividend'); #Output 9 #REPEAT() 

The repeat() function is used for writing the given string again and again till the number of times mentioned.

#Syntax SELECT REPEAT ('SQL', 2); #Output SQLSQL #REPLACE()

The replace() function is used for cutting the given string in a function by removing the given substring.

#Syntax SELECT REPLACE ('123App123', '123'); #Output App #REVERSE()

The reverse() function is used for reversing a string specified inside a function.

#Syntax SELECT REVERSE('Shubh'); #Output hbuhs #RIGHT()

The right() function is used for selecting a substring from the right end of the given size.

#Syntax SELECT RIGHT ('AppDividend.com', 4); #Output .com #RPAD()

The rpad() function is used for making a given string as long as the given size by adding the given symbol on the right.

#Syntax SELECT RPAD ('Shubh', 8, '0'); #Output Shubh000 #RTRIM()

The rtrim() function is used for cutting a part of the string from the original string.

#Syntax SELECT RTRIM ('Shubhxyz', 'xyz'); #Output Shubh #SPACE() 

The space() function is used to writing a given number of spaces.

#Syntax SELECT SPACE(7); #Output

‘       ‘

#STRCMP() 

The strcmp() function is used to compare the two strings.

    1. If string1 and string2 are same, then the STRCMP function will return 0.
    2. If string1 is smaller than string2, then the STRCMP function will return -1.
    3. If string1 is larger than string2, then the STRCMP function will return 1.
#Syntax SELECT STRCMP ('google.com', 'AppDividend.com'); #Output 1 #SUBSTR()

The substr() function is used for finding a substring from the given string and its position specified within the function.

#Syntax SELECT SUBSTR ('GodisGreat', 1, 5); #Output Godis #SUBSTRING()

The substring() function is used for finding an alphabet from the mentioned size and the given string.

#Syntax SELECT SUBSTRING ('AppDividend.com', 9, 1); #Output e #SUBSTRING_INDEX()

The substring_index() function is used for finding a substring before the given symbol specified within a function.

#Syntax SELECT SUBSTRING_INDEX ('www.AppDivivdend.com', '.', 1); #Output www #SOUNDEX (str):

The soundex() function is used for returning the Soundex string. A Soundex string is four characters long, but the function returns an arbitrarily long string. We can use SUBSTRING () function on the result to get a standard Soundex string.

All non-alphabetic characters in str are ignored, and all international alphabetic characters outside the A-Z range are treated as vowels.

#SYNTAX SELECT SOUNDEX('Hello'); #OUTPUT H400 #TRIM() 

The trim() function is used for cutting the given symbol from the string mentioned.

#Syntax SELECT TRIM (LEADING '0' FROM '000123'); #Output 123 #UCASE() 

The ucase() function is used for making the given string into upper case.

#Syntax SELECT UCASE ("Appdividend"); #Output APPDIVIDEND #UNHEX (str)

It is used for converting given hexadecimal characters to digits or ascii characters.

#SYNTAX SELECT UNHEX('4D7953514C'); #OUTPUT SQL

Finally, SQL String Functions Example | String Functions In SQL is over.

The post SQL String Functions Example | String Functions In SQL appeared first on AppDividend.

A small thing that can save a lot of mess, meet: SET PERSIST

To set correct system variable values is the essential step to get the correct server behavior against the workload.  

In MySQL we have many System variables that can be changed at runtime, most of them can be set at session or at global scope. 

To change the value of a system variable at global level in the past user need to have SUPER privileges. Once the system variable value is modified as global, the server will change his behavior for the session, and obviously as global scope. For instance, one of the most commonly adjusted variables is probably max_connections. If you have max_connection=100 in your my.cnf or as default value, and during the day as DBA you notice that the number of them is not enough, it is easy just to add new connections on the fly, the command:

SET GLOBAL MAX_CONNECTIONS=500;

Will do the work. But here is the issue. We had changed a GLOBAL value, that apply to the whole server, but this change is ephemeral and if the server restarts the setting is lost. In the past I have seen many times servers with different configurations between my.cnf and current Server settings. To prevent this or at least keep it under control good DBAs had develop scripts to checks if and where the differences exists and fix it. The main issue is that very often, we forget to update the configuration file while doing the changes, or we do on purpose to do "Fine tuning first” and forgot after.

What's new in MySQL8 about that? Well we have a couple of small changes. First of all, the privileges, as for MySQL8 user can have SYSTEM_VARIABLES_ADMIN or SUPER to modify the GLOBAL system variables. The other news is related to the ability to have GLOBAL changes to variable to PERSIST on disk and finally to know who did it and when. The new option for SET command is PERSIST. So, if I have:

(root@localhost) [(none)]>show global variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1500 | +-----------------+-------+

and I want to change the value of max_connection and be sure this value is reloaded at restart, I will do:

(root@localhost) [(none)]>set PERSIST max_connections=150; (root@localhost) [(none)]>show global variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 150 | +-----------------+-------+

With the usage of PERSIST, MySQL will write the information related to: - key (variable name) - value - timestamp (including microseconds) - user - host A new file in the data directory: mysqld-auto.cnf contains the information The file is in Json format and will have the following:

{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "150" , "Metadata" : { "Timestamp" : 1565363808318848 , "User" : "root" , "Host" : "localhost" } } } }

The information is also in Performance Schema:

select a.VARIABLE_NAME,b.VARIABLE_value ,SET_TIME,SET_USER,SET_HOST from performance_schema.variables_info a join performance_schema.global_variables b on a.VARIABLE_NAME=b.VARIABLE_NAME where b.VARIABLE_NAME like 'max_connections'\G *************************** 1. row *************************** VARIABLE_NAME: max_connections VARIABLE_value: 150 SET_TIME: 2019-08-09 11:16:48.318989 SET_USER: root SET_HOST: localhost

As you see the information present, report who did the change, from where, when, and the value. Unfortunately, there is no history here, but this can be easily implemented. To clear the PERSIST settings, run RESET PERSIST and all the Persistent setting will be removed. To be clear if you have:

{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565367524946371 , "User" : "root" , "Host" : "localhost" } } , "wait_timeout" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565368154036275 , "User" : "root" , "Host" : "localhost" } } } }

RESET PERSISTE will do:

{ "Version" : 1 , "mysql_server" : { }

Which is removing ALL THE SETTINGS not just one.

Anyhow why is this a good thing to have?

First because we have no excuse now, when we change a variable, we have all the tools needed to make sure we will have it up at startup if this is the intention of the change.

Second is good because storing the information in a file, and not only showing it from PS, allow us to include such information in any automation tool we have.
This in the case we decide to take action or just to keep track of it, like comparison with my.cnf and fixing the discrepancies automatically also at service down or when cloning.


On this let me say that WHILE you can change the value in the file mysqld-auto.cnf, and have the server at restart use that value as the valid one.
This is not recommended, instead please fix the my.cnf and remove the information related to PERSIST.
To touch that file is also dangerous because if you do stupid things like removing a double quote or in any way affecting the Json format, the server will not start, but there will be NO error in the log.

{ "Version" : 1 , "mysql_server" : { "wait_timeout": { "Value : "150" , "Metadata" : { "Timestamp" : 1565455891278414, "User" : "root" , "Host" : "localhost" } } } } ^^^ missing double quote tusa@tusa-dev:/opt/mysql_templates/mysql-8.0.17futex$ ps aux|grep 8113 tusa 8119 0.0 0.0 14224 896 pts/1 S+ 12:54 0:00 grep --color=auto 8113 [1]+ Exit 1 bin/mysqld --defaults-file=./my.cnf

I have opened a bug for this (https://bugs.mysql.com/bug.php?id=96501).

A short deep dive in the code (you can jump it if you don't care)

The new feature is handled in the files <source>/sql/persisted_variable.(h/cc) The new structure dealing with the PERSIST actions is:

struct st_persist_var { std::string key; std::string value; ulonglong timestamp; std::string user; std::string host; bool is_null; st_persist_var(); st_persist_var(THD *thd); st_persist_var(const std::string key, const std::string value, const ulonglong timestamp, const std::string user, const std::string host, const bool is_null); };

And the main steps are in the constructors st_persist_var. About that, should be noted that when creating the timestamp the code is generating a value that is NOT fully compatible with the MySQL functions FROM_UNIXTIME. The code assigning the timestamp value pass/assign also the microseconds passing them from the timeval (tv) structure:

st_persist_var::st_persist_var(THD *thd) { timeval tv = thd->query_start_timeval_trunc(DATETIME_MAX_DECIMALS); timestamp = tv.tv_sec * 1000000ULL + tv.tv_usec; user = thd->security_context()->user().str; host = thd->security_context()->host().str; is_null = false; }

Where:

tv.tv_sec = 1565267482 tv.tc_usec = 692276

this will generate: timestamp = 1565267482692276 this TIMESTAMP is not valid in MySQL and cannot be read from the time functions, while the segment related to tv.tv_sec = 1565267482 works perfectly.

(root@localhost) [(none)]>select FROM_UNIXTIME(1565267482); +---------------------------+ | FROM_UNIXTIME(1565267482) | +---------------------------+ | 2019-08-08 08:31:22 | +---------------------------+ (root@localhost) [(none)]>select FROM_UNIXTIME(1565267482692276); +---------------------------------+ | FROM_UNIXTIME(1565267482692276) | +---------------------------------+ | NULL | +---------------------------------+

this because the timestamp with microsecond is formatted differently in MySQL : PERSIST_code = 1565267482692276 MySQL = 1565267482.692276 If I run: select FROM_UNIXTIME(1565267482.692276); I get the right result:

+----------------------------------+ | FROM_UNIXTIME(1565267482.692276) | +----------------------------------+ | 2019-08-08 08:31:22.692276 | +----------------------------------+

of course, I can use the trick:

select FROM_UNIXTIME(1565267482692276/1000000); +-----------------------------------------+ | FROM_UNIXTIME(1565267482692276/1000000) | +-----------------------------------------+ | 2019-08-08 08:31:22.6922 | +-----------------------------------------+

Well that's all for the behind the scene info, keep in mind if you want to deal with the value coming from the Json file.

Conclusion

Sometimes the small things can be better than the HUGE shining things. I saw many times DBAs in trouble because they do not have this simple feature in MySQL, and many MySQL failing to start, or behave not as expected. Given that, I welcome PERSIST and I am sure that the people who manage thousands of servers, have different workloads and automation in place, will see this as a good thing as well.

References:

https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestamp
https://lefred.be/content/where-does-my-mysql-configuration-variable-value-come-from/
https://lefred.be/content/what-configuration-settings-did-i-change-on-my-mysql-server/

 

Pages