One place for hosting & domains


      How To Optimize MySQL Queries with ProxySQL Caching on Ubuntu 16.04

      The author selected the Free Software Foundation to receive a donation as part of the Write for DOnations program.


      ProxySQL is a SQL-aware proxy server that can be positioned between your application and your database. It offers many features, such as load-balancing between multiple MySQL servers and serving as a caching layer for queries. This tutorial will focus on ProxySQL’s caching feature, and how it can optimize queries for your MySQL database.

      MySQL caching occurs when the result of a query is stored so that, when that query is repeated, the result can be returned without needing to sort through the database. This can significantly increase the speed of common queries. But in many caching methods, developers must modify the code of their application, which could introduce a bug into the codebase. To avoid this error-prone practice, ProxySQL allows you to set up transparent caching.

      In transparent caching, only database administrators need to change the ProxySQL configuration to enable caching for the most common queries, and these changes can be done through the ProxySQL admin interface. All the developer needs to do is connect to the protocol-aware proxy, and the proxy will decide if the query can be served from the cache without hitting the back-end server.

      In this tutorial, you will use ProxySQL to set up transparent caching for a MySQL server on Ubuntu 16.04. You will then test its performance using mysqlslap with and without caching to demonstrate the effect of caching and how much time it can save when executing many similar queries.


      Before you begin this guide you’ll need the following:

      Step 1 — Installing and Setting Up the MySQL Server

      First, you will install MySQL server and configure it to be used by ProxySQL as a back-end server for serving client queries.

      On Ubuntu 16.04, mysql-server can be installed using this command:

      • sudo apt-get install mysql-server

      Press Y to confirm the installation.

      You will then be prompted for your MySQL root user password. Enter a strong password and save it for later use.

      Now that you have your MySQL server ready, you will configure it for ProxySQL to work correctly. You need to add a monitor user for ProxySQL to monitor the MySQL server, since ProxySQL listens to the back-end server via the SQL protocol, rather than using a TCP connection or HTTP GET requests to make sure that the backend is running. monitor will use a dummy SQL connection to determine if the server is alive or not.

      First, log in to the MySQL shell:

      -uroot logs you in using the MySQL root user, and -p prompts for the root user’s password. This root user is different from your server’s root user, and the password is the one you entered when installing the mysql-server package.

      Enter the root password and press ENTER.

      Now you will create two users, one named monitor for ProxySQL and another that you will use to execute client queries and grant them the right privileges. This tutorial will name this user sammy.

      Create the monitor user:

      • CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';

      The CREATE USER query is used to create a new user that can connect from specific IPs. Using % denotes that the user can connect from any IP address. IDENTIFIED BY sets the password for the new user; enter whatever password you like, but make sure to remember it for later use.

      With the user monitor created, next make the sammy user:

      • CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';

      Next, grant privileges to your new users. Run the following command to configure monitor:

      • GRANT SELECT ON sys.* TO 'monitor'@'%';

      The GRANT query is used to give privileges to users. Here you granted only SELECT on all tables in the sys database to the monitor user; it only needs this privilege to listen to the back-end server.

      Now grant all privileges to all databases to the user sammy:

      • GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';

      This will allow sammy to make the necessary queries to test your database later.

      Apply the privilege changes by running the following:

      Finally, exit the mysql shell:

      You’ve now installed mysql-server and created a user to be used by ProxySQL to monitor your MySQL server, and another one to execute client queries. Next you will install and configure ProxySQL.

      Step 2 — Installing and Configuring ProxySQL Server

      Now you can install ProxySQL server, which will be used as a caching layer for your queries. A caching layer exists as a stop between your application servers and database back-end servers; it is used to connect to the database and to save the results of some queries in its memory for fast access later.

      The ProxySQL releases Github page offers installation files for common Linux distributions. For this tutorial, you will use wget to download the ProxySQL version 2.0.4 Debian installation file:

      • wget

      Next, install the package using dpkg:

      • sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb

      Once it is installed, start ProxySQL with this command:

      • sudo systemctl start proxysql

      You can check if ProxySQL started correctly with this command:

      • sudo systemctl status proxysql

      You will get an output similar to this:


      root@ubuntu-s-1vcpu-2gb-sgp1-01:~# systemctl status proxysql ● proxysql.service - LSB: High Performance Advanced Proxy for MySQL Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled) Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago Docs: man:systemd-sysv-generator(8) Tasks: 0 Memory: 0B CPU: 0

      Now it is time to connect your ProxySQL server to the MySQL server. For this purpose, use the ProxySQL admin SQL interface, which by default listens to port 6032 on localhost and has admin as its username and password.

      Connect to the interface by running the following:

      • mysql -uadmin -p -h -P6032

      Enter admin when prompted for the password.

      -uadmin sets the username as admin, and the -h flag specifies the host as localhost. The port is 6032, specified using the -P flag.

      Here you had to specify the host and port explicitly because, by default, the MySQL client connects using a local sockets file and port 3306.

      Now that you are logged into the mysql shell as admin, configure the monitor user so that ProxySQL can use it. First, use standard SQL queries to set the values of two global variables:

      • UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
      • UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

      The variable mysql-monitor_username specifies the MySQL username that will be used to check if the back-end server is alive or not. The variable mysql-monitor_password points to the password that will be used when connecting to the back-end server. Use the password you created for the monitor username.

      Every time you create a change in the ProxySQL admin interface, you need to use the right LOAD command to apply changes to the running ProxySQL instance. You changed MySQL global variables, so load them to RUNTIME to apply changes:


      Next, SAVE the changes to the on-disk database to persist changes between restarts. ProxySQL uses its own SQLite local database to store its own tables and variables:


      Now, you will tell ProxySQL about the back-end server. The table mysql_servers holds information about each back-end server where ProxySQL can connect and execute queries, so add a new record using a standard SQL INSERT statement with the following values for hostgroup_id, hostname, and port:

      • INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '', 3306);

      To apply the changes, run LOAD and SAVE again:


      Finally, you will tell ProxySQL which user will connect to the back-end server; set sammy as the user, and replace sammy_password with the password you created earlier:

      • INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);

      The table mysql_users holds information about users used to connect to the back-end servers; you specified the username, password, and default_hostgroup.

      LOAD and SAVE the changes:


      Then exit the mysql shell:

      To test that you can connect to your back-end server using ProxySQL, execute the following test query:

      • mysql -usammy -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"

      In this command, you used the -e flag to execute a query and close the connection. The query prints the hostname of the back-end server.

      Note: ProxySQL uses port 6033 by default for listening to incoming connections.

      The output will look like this, with your_hostname replaced by your hostname:


      +----------------------------+ | hostname | +----------------------------+ | your_hostname | +----------------------------+

      To learn more about ProxySQL configuration, see Step 3 of How To Use ProxySQL as a Load Balancer for MySQL on Ubuntu 16.04.

      So far, you configured ProxySQL to use your MySQL server as a backend and connected to the backend using ProxySQL. Now, you are ready to use mysqlslap to benchmark the query performance without caching.

      Step 3 — Testing Using mysqlslap Without Caching

      In this step, you will download a test database so you can execute queries against it with mysqlslap to test the latency without caching, setting a benchmark for the speed of your queries. You will also explore how ProxySQL keeps records of queries in the stats_mysql_query_digest table.

      mysqlslap is a load emulation client that is used as a load testing tool for MySQL. It can test a MySQL server with auto-generated queries or with some custom queries executed on a database. It comes installed with the MySQL client package, so you do not need to install it; instead, you will download a database for testing purposes only, on which you can use mysqlslap.

      In this tutorial, you will use a sample employee database. You will be using this employee database because it features a large data set that can illustrate differences in query optimization. The database has six tables, but the data it contains has more than 300,000 employee records. This will help you emulate a large-scale production workload.

      To download the database, first clone the Github repository using this command:

      • git clone

      Then enter the test_db directory and load the database into the MySQL server using these commands:

      • cd test_db
      • mysql -uroot -p < employees.sql

      This command uses shell redirection to read the SQL queries in employees.sql file and execute them on the MySQL server to create the database structure.

      You will see output like this:


      INFO CREATING DATABASE STRUCTURE INFO storage engine: InnoDB INFO LOADING departments INFO LOADING employees INFO LOADING dept_emp INFO LOADING dept_manager INFO LOADING titles INFO LOADING salaries data_load_time_diff 00:00:32

      Once the database is loaded into your MySQL server, test that mysqlslap is working with the following query:

      • mysqlslap -usammy -p -P6033 -h127.0.0.1 --auto-generate-sql --verbose

      mysqlslap has similar flags to the mysql client; here are the ones used in this command:

      • -u specifies the user used to connect to the server.
      • -p prompts for the user’s password.
      • -P connects using the specified port.
      • -h connects to the specified host.
      • --auto-generate-sql lets MySQL perform load testing using its own generated queries.
      • --verbose makes the output show more information.

      You will get output similar to the following:


      Benchmark Average number of seconds to run all queries: 0.015 seconds Minimum number of seconds to run all queries: 0.015 seconds Maximum number of seconds to run all queries: 0.015 seconds Number of clients running queries: 1 Average number of queries per client: 0

      In this output, you can see the average, minimum, and maximum number of seconds spent to execute all queries. This gives you an indication about the amount of time needed to execute the queries by a number of clients. In this output, only one client was used to execute queries.

      Next, find out what queries mysqlslap executed in the last command by looking at ProxySQL’s stats_mysql_query_digest. This will give us information like the digest of the queries, which is a normalized form of the SQL statement that can be referenced later to enable caching.

      Enter the ProxySQL admin interface with this command:

      • mysql -uadmin -p -h -P6032

      Then execute this query to find information in the stats_mysql_query_digest table:

      • SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

      You will see output similar to the following:

      | count_star | sum_time | hostgroup | digest             | digest_text                      |
      | 1          | 598      | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname    |
      | 1          | 0        | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
      2 rows in set (0.01 sec)

      The previous query selects data from the stats_mysql_query_digest table, which contains information about all executed queries in ProxySQL. Here you have five columns selected:

      • count_star: The number of times this query was executed.
      • sum_time: Total time in milliseconds that this query took to execute.
      • hostgroup: The hostgroup used to execute the query.
      • digest: A digest of the executed query.
      • digest_text: The actual query. In this tutorial’s example, the second query is parameterized using ? marks in place of variable parameters. select @@version_comment limit 1 and select @@version_comment limit 2, therefore, are grouped together as the same query with the same digest.

      Now that you know how to check query data in the stats_mysql_query_digest table, exit the mysql shell:

      The database you downloaded contains some tables with demo data. You will now test queries on the dept_emp table by selecting any records whose from_date is greater than 2000-04-20 and recording the average execution time.

      Use this command to run the test:

      • mysqlslap -usammy -P6033 -p -h127.0.0.1 --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

      Here you are using some new flags:

      • --concurrency=100: This sets the number of users to simulate, in this case 100.
      • --iterations=20: This causes the test to run 20 times and calculate results from all of them.
      • --create-schema=employees: Here you selected the employees database.
      • --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'": Here you specified the query executed in the test.

      The test will take a few minutes. After it is done, you will get results similar to the following:


      Benchmark Average number of seconds to run all queries: 18.117 seconds Minimum number of seconds to run all queries: 8.726 seconds Maximum number of seconds to run all queries: 22.697 seconds Number of clients running queries: 100 Average number of queries per client: 1

      Your numbers could be a little different. Keep these numbers somewhere in order to compare them with the results from after you enable caching.

      After testing ProxySQL without caching, it is time to run the same test again, but this time with caching enabled.

      Step 4 — Testing Using mysqlslap With Caching

      In this step, caching will help us to decrease latency when executing similar queries. Here, you will identify the queries executed, take their digests from ProxySQL’s stats_mysql_query_digest table, and use them to enable caching. Then, you will test again to check the difference.

      To enable caching, you need to know the digests of the queries that will be cached. Log in to the ProxySQL admin interface using this command:

      • mysql -uadmin -p -h127.0.0.1 -P6032

      Then execute this query again to get a list of queries executed and their digests:

      • SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

      You will get a result similar to this:


      +------------+-------------+-----------+--------------------+------------------------------------------+ | count_star | sum_time | hostgroup | digest | digest_text | +------------+-------------+-----------+--------------------+------------------------------------------+ | 2000 | 33727110501 | 1 | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date>? | | 1 | 601 | 1 | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname | | 1 | 0 | 1 | 0x226CD90D52A2BA0B | select @@version_comment limit ? | +------------+-------------+-----------+--------------------+------------------------------------------+ 3 rows in set (0.00 sec)

      Look at the first row. It is about a query that was executed 2000 times. This is the benchmarked query executed previously. Take its digest and save it to be used in adding a query rule for caching.

      The next few queries will add a new query rule to ProxySQL that will match the digest of the previous query and put a cache_ttl value for it. cache_ttl is the number of milliseconds that the result will be cached in memory:

      • INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);

      In this command you are adding a new record to the mysql_query_rules table; this table holds all the rules applied before executing a query. In this example, you are adding a value for the cache_ttl column that will cause the matched query by the given digest to be cached for a number of milliseconds specified in this column. You put 1 in the apply column to make sure that the rule is applied to queries.

      LOAD and SAVE these changes, then exit the mysql shell:

      • exit;

      Now that caching is enabled, re-run the test again to check the result:

      • mysqlslap -usammy -P6033 -p -h127.0.0.1 --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

      This will give output similar to the following:


      Benchmark Average number of seconds to run all queries: 7.020 seconds Minimum number of seconds to run all queries: 0.274 seconds Maximum number of seconds to run all queries: 23.014 seconds Number of clients running queries: 100 Average number of queries per client: 1

      Here you can see the big difference in average execution time: it dropped from 18.117 seconds to 7.020.


      In this article, you set up transparent caching with ProxySQL to cache database query results. You also tested the query speed with and without caching to see the difference that caching can make.

      You’ve used one level of caching in this tutorial. You could also try, web caching, which sits in front of a web server and caches the responses to similar requests, sending the response back to the client without hitting the back-end servers. This is very similar to ProxySQL caching but at a different level. To learn more about web caching, check out our Web Caching Basics: Terminology, HTTP Headers, and Caching Strategies primer.

      MySQL server also has its own query cache; you can learn more about it in our How To Optimize MySQL with Query Cache on Ubuntu 18.04 tutorial.

      Source link

      How To Optimize MySQL with Query Cache on Ubuntu 18.04

      The author selected the Apache Software Foundation to receive a donation as part of the Write for DOnations program.


      Query cache is a prominent MySQL feature that speeds up data retrieval from a database. It achieves this by storing MySQL SELECT statements together with the retrieved record set in memory, then if a client requests identical queries it can serve the data faster without executing commands again from the database.

      Compared to data read from disk, cached data from RAM (Random Access Memory) has a shorter access time, which reduces latency and improves input/output (I/O) operations. As an example, for a WordPress site or an e-commerce portal with high read calls and infrequent data changes, query cache can drastically boost the performance of the database server and make it more scalable.

      In this tutorial, you will first configure MySQL without query cache and run queries to see how quickly they are executed. Then you’ll set up query cache and test your MySQL server with it enabled to show the difference in performance.

      Note: Although query cache is deprecated as of MySQL 5.7.20, and removed in MySQL 8.0, it is still a powerful tool if you’re using supported versions of MySQL. However, if you are using newer versions of MySQL, you may adopt alternative third-party tools like ProxySQL to optimize performance on your MySQL database.


      Before you begin, you will need the following:

      Step 1 — Checking the Availability of Query Cache

      Before you set up query cache, you’ll check whether your version of MySQL supports this feature. First, ssh into your Ubuntu 18.04 server:

      • ssh user_name@your_server_ip

      Then, run the following command to log in to the MySQL server as the root user:

      Enter your MySQL server root password when prompted and then press ENTER to continue.

      Use the following command to check if query cache is supported:

      • show variables like 'have_query_cache';

      You should get an output similar to the following:


      +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 1 row in set (0.01 sec)

      You can see the value of have_query_cache is set to YES and this means query cache is supported. If you receive an output showing that your version does not support query cache, please see the note in the Introduction section for more information.

      Now that you have checked and confirmed that your version of MySQL supports query cache, you will move on to examining the variables that control this feature on your database server.

      Step 2 — Checking the Default Query Cache Variables

      In MySQL, a number of variables control query cache. In this step, you'll check the default values that ship with MySQL and understand what each variable controls.

      You can examine these variables using the following command:

      • show variables like 'query_cache_%' ;

      You will see the variables listed in your output:


      +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 5 rows in set (0.00 sec)

      The query_cache_limit value determines the maximum size of individual query results that can be cached. The default value is 1,048,576 bytes and this is equivalent to 1MB.

      MySQL does not handle cached data in one big chunk; instead it is handled in blocks. The minimum amount of memory allocated to each block is determined by the query_cache_min_res_unit variable. The default value is 4096 bytes or 4KB.

      query_cache_size controls the total amount of memory allocated to the query cache. If the value is set to zero, it means query cache is disabled. In most cases, the default value may be set to 16,777,216 (around 16MB). Also, keep in mind that query_cache_size needs at least 40KB to allocate its structures. The value allocated here is aligned to the nearest 1024 byte block. This means the reported value may be slightly different from what you set.

      MySQL determines the queries to cache by examining the query_cache_type variable. Setting this value to 0 or OFF prevents caching or retrieval of cached queries. You can also set it to 1 to enable caching for all queries except for ones beginning with the SELECT SQL_NO_CACHE statement. A value of 2 tells MySQL to only cache queries that begin with SELECT SQL_CACHE command.

      The variable query_cache_wlock_invalidate controls whether MySQL should retrieve results from the cache if the table used on the query is locked. The default value is OFF.

      Note: The query_cache_wlock_invalidate variable is deprecated as of MySQL version 5.7.20. As a result, you may not see this in your output depending on the MySQL version you're using.

      Having reviewed the system variables that control the MySQL query cache, you'll now test how MySQL performs without first enabling the feature.

      Step 3 — Testing Your MySQL Server Without Query Cache

      The goal of this tutorial is to optimize your MySQL server by using the query cache feature. To see the difference in speed, you're going to run queries and see their performance before and after implementing the feature.

      In this step you're going to create a sample database and insert some data to see how MySQL performs without query cache.

      While still logged in to your MySQL server, create a database and name it sample_db by running the following command:

      • Create database sample_db;


      Query OK, 1 row affected (0.00 sec)

      Then switch to the database:


      Database changed

      Create a table with two fields (customer_id and customer_name) and name it customers:

      • Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;


      Query OK, 0 rows affected (0.01 sec)

      Then, run the following commands to insert some sample data:

      • Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
      • Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');
      • Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');
      • Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');
      • Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');
      • Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');
      • Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');
      • Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');
      • Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');
      • Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');


      Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) ...

      The next step is starting the MySQL profiler, which is an analysis service for monitoring the performance of MySQL queries. To turn the profile on for the current session, run the following command, setting it to 1, which is on:


      Query OK, 0 rows affected, 1 warning (0.00 sec)

      Then, run the following query to retrieve all customers:

      You'll receive the following output:


      +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | JANE DOE | | 2 | JANIE DOE | | 3 | JOHN ROE | | 4 | MARY ROE | | 5 | RICHARD ROE | | 6 | JOHNNY DOE | | 7 | JOHN SMITH | | 8 | JOE BLOGGS | | 9 | JANE POE | | 10 | MARK MOE | +-------------+---------------+ 10 rows in set (0.00 sec)

      Then, run the SHOW PROFILES command to retrieve performance information about the SELECT query you just ran:

      You will get output similar to the following:


      +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 1 | 0.00044075 | Select * from customers | +----------+------------+-------------------------+ 1 row in set, 1 warning (0.00 sec)

      The output shows the total time spent by MySQL when retrieving records from the database. You are going to compare this data in the next steps when query cache is enabled, so keep note of your Duration. You can ignore the warning within the output since this simply indicates that SHOW PROFILES command will be removed in a future MySQL release and replaced with Performance Schema.

      Next, exit from the MySQL Command Line Interface.

      You have ran a query with MySQL before enabling query cache and noted down the Duration or time spent to retrieve records. Next, you will enable query cache and see if there is a performance boost when running the same query.

      Step 4 — Setting Up Query Cache

      In the previous step, you created sample data and ran a SELECT statement before you enabled query cache. In this step, you'll enable query cache by editing the MySQL configuration file.

      Use nano to edit the file:

      • sudo nano /etc/mysql/my.cnf

      Add the following information to the end of your file:


      query_cache_size = 10M

      Here you've enabled query cache by setting the query_cache_type to 1. You've also set up the individual query limit size to 256K and instructed MySQL to allocate 10 megabytes to query cache by setting the value of query_cache_size to 10M.

      Save and close the file by pressing CTRL + X, Y, then ENTER. Then, restart your MySQL server to implement the changes:

      • sudo systemctl restart mysql

      You have now enabled query cache.

      Once you have configured query cache and restarted MySQL to apply the changes, you will go ahead and test the performance of MySQL with the feature enabled.

      Step 5 — Testing Your MySQL Server with Query Cache Enabled

      In this step, you'll run the same query you ran in Step 3 one more time to check how query cache has optimized the performance of your MySQL server.

      First, connect to your MySQL server as the root user:

      Enter your root password for the database server and hit ENTER to continue.

      Now confirm your configuration set in the previous step to ensure you enabled query cache:

      • show variables like 'query_cache_%' ;

      You'll see the following output:


      +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 262144 | | query_cache_min_res_unit | 4096 | | query_cache_size | 10485760 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 5 rows in set (0.01 sec)

      The variable query_cache_type is set to ON; this confirms that you enabled query cache with the parameters defined in the previous step.

      Switch to the sample_db database that you created earlier.

      Start the MySQL profiler:

      Then, run the query to retrieve all customers at least two times in order to generate enough profiling information.

      Remember, once you've run the first query, MySQL will create a cache of the results and therefore, you must run the query twice to trigger the cache:

      • Select * from customers;
      • Select * from customers;

      Then, list the profiles information:

      You'll receive an output similar to the following:


      +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 1 | 0.00049250 | Select * from customers | | 2 | 0.00026000 | Select * from customers | +----------+------------+-------------------------+ 2 rows in set, 1 warning (0.00 sec)

      As you can see the time taken to run the query has drastically reduced from 0.00044075 (without query cache in Step 3) to 0.00026000 (the second query) in this step.

      You can see the optimization from enabling the query cache feature by profiling the first query in detail:



      +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000025 | | Waiting for query cache lock | 0.000004 | | starting | 0.000003 | | checking query cache for query | 0.000045 | | checking permissions | 0.000008 | | Opening tables | 0.000014 | | init | 0.000018 | | System lock | 0.000008 | | Waiting for query cache lock | 0.000002 | | System lock | 0.000018 | | optimizing | 0.000003 | | statistics | 0.000013 | | preparing | 0.000010 | | executing | 0.000003 | | Sending data | 0.000048 | | end | 0.000004 | | query end | 0.000006 | | closing tables | 0.000006 | | freeing items | 0.000006 | | Waiting for query cache lock | 0.000003 | | freeing items | 0.000213 | | Waiting for query cache lock | 0.000019 | | freeing items | 0.000002 | | storing result in query cache | 0.000003 | | cleaning up | 0.000012 | +--------------------------------+----------+ 25 rows in set, 1 warning (0.00 sec)

      Run the following command to show profile information for the second query, which is cached:



      +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000024 | | Waiting for query cache lock | 0.000003 | | starting | 0.000002 | | checking query cache for query | 0.000006 | | checking privileges on cached | 0.000003 | | checking permissions | 0.000027 | | sending cached result to clien | 0.000187 | | cleaning up | 0.000008 | +--------------------------------+----------+ 8 rows in set, 1 warning (0.00 sec)

      The outputs from the profiler show that MySQL took less time on the second query because it was able to retrieve data from the query cache instead of reading it from the disk. You can compare the two sets of output for each of the queries. If you look at the profile information on QUERY 2, the status of sending cached result to client shows that data was read from the cache and no tables were opened since the Opening tables status is missing.

      With the MySQL query cache feature enabled on your server, you'll now experience improved read speeds.


      You have set up query cache to speed up your MySQL server on Ubuntu 18.04. Using features like MySQL's query cache can enhance the speed of your website or web application. Caching reduces unnecessary execution for SQL statements and is a highly recommended and popular method for optimizing your database. For more on speeding up your MySQL server, try the How To Set Up a Remote Database to Optimize Site Performance with MySQL on Ubuntu 18.04 tutorial.

      Source link

      How To Optimize Docker Images for Production

      The author selected to receive a donation as part of the Write for DOnations program.


      In a production environment, Docker makes it easy to create, deploy, and run applications inside of containers. Containers let developers gather applications and all their core necessities and dependencies into a single package that you can turn into a Docker image and replicate. Docker images are built from Dockerfiles. The Dockerfile is a file where you define what the image will look like, what base operating system it will have, and which commands will run inside of it.

      Large Docker images can lengthen the time it takes to build and send images between clusters and cloud providers. If, for example, you have a gigabyte-sized image to push every time one of your developers triggers a build, the throughput you create on your network will add up during the CI/CD process, making your application sluggish and ultimately costing you resources. Because of this, Docker images suited for production should only have the bare necessities installed.

      There are several ways to decrease the size of Docker images to optimize for production. First off, these images don’t usually need build tools to run their applications, and so there’s no need to add them at all. By using a multi-stage build process, you can use intermediate images to compile and build the code, install dependencies, and package everything into the smallest size possible, then copy over the final version of your application to an empty image without build tools. Additionally, you can use an image with a tiny base, like Alpine Linux. Alpine is a suitable Linux distribution for production because it only has the bare necessities that your application needs to run.

      In this tutorial, you’ll optimize Docker images in a few simple steps, making them smaller, faster, and better suited for production. You’ll build images for a sample Go API in several different Docker containers, starting with Ubuntu and language-specific images, then moving on to the Alpine distribution. You will also use multi-stage builds to optimize your images for production. The end goal of this tutorial is to show the size difference between using default Ubuntu images and optimized counterparts, and to show the advantage of multi-stage builds. After reading through this tutorial, you’ll be able to apply these techniques to your own projects and CI/CD pipelines.

      Note: This tutorial uses an API written in Go as an example. This simple API will give you a clear understanding of how you would approach optimizing Go microservices with Docker images. Even though this tutorial uses a Go API, you can apply this process to almost any programming language.


      Before you start you will need:

      Step 1 — Downloading the Sample Go API

      Before optimizing your Docker image, you must first download the sample API that you will build your Docker images from. Using a simple Go API will showcase all the key steps of building and running an application inside a Docker container. This tutorial uses Go because it’s a compiled language like C++ or Java, but unlike them, has a very small footprint.

      On your server, begin by cloning the sample Go API:

      • git clone

      Once you have cloned the project, you will have a directory named mux-go-api on your server. Move into this directory with cd:

      This will be the home directory for your project. You will build your Docker images from this directory. Inside, you will find the source code for an API written in Go in the api.go file. Although this API is minimal and has only a few endpoints, it will be appropriate for simulating a production-ready API for the purposes of this tutorial.

      Now that you have downloaded the sample Go API, you are ready to build a base Ubuntu Docker image, against which you can compare the later, optimized Docker images.

      Step 2 — Building a Base Ubuntu Image

      For your first Docker image, it will be useful to see what it looks like when you start out with a base Ubuntu image. This will package your sample API in an environment similar to the software you're already running on your Ubuntu server. Inside the image, you will install the various packages and modules you need to run your application. You will find, however, that this process creates a rather heavy Ubuntu image that will affect build time and the code readability of your Dockerfile.

      Start by writing a Dockerfile that instructs Docker to create an Ubuntu image, install Go, and run the sample API. Make sure to create the Dockerfile in the directory of the cloned repo. If you cloned to the home directory it should be $HOME/mux-go-api.

      Make a new file called Dockerfile.ubuntu. Open it up in nano or your favorite text editor:

      • nano ~/mux-go-api/Dockerfile.ubuntu

      In this Dockerfile, you'll define an Ubuntu image and install Golang. Then you'll proceed to install the needed dependencies and build the binary. Add the following contents to Dockerfile.ubuntu:


      FROM ubuntu:18.04
      RUN apt-get update -y 
        && apt-get install -y git gcc make golang-1.10
      ENV GOROOT /usr/lib/go-1.10
      ENV GOPATH /root/go
      ENV APIPATH /root/go/src/api
      COPY . .
        go get -d -v 
        && go install -v 
        && go build
      EXPOSE 3000
      CMD ["./api"]

      Starting from the top, the FROM command specifies which base operating system the image will have. Then the RUN command installs the Go language during the creation of the image. ENV sets the specific environment variables the Go compiler needs in order to work properly. WORKDIR specifies the directory where we want to copy over the code, and the COPY command takes the code from the directory where Dockerfile.ubuntu is and copies it over into the image. The final RUN command installs Go dependencies needed for the source code to compile and run the API.

      Note: Using the && operators to string together RUN commands is important in optimizing Dockerfiles, because every RUN command will create a new layer, and every new layer increases the size of the final image.

      Save and exit the file. Now you can run the build command to create a Docker image from the Dockerfile you just made:

      • docker build -f Dockerfile.ubuntu -t ubuntu .

      The build command builds an image from a Dockerfile. The -f flag specifies that you want to build from the Dockerfile.ubuntu file, while -t stands for tag, meaning you're tagging it with the name ubuntu. The final dot represents the current context where Dockerfile.ubuntu is located.

      This will take a while, so feel free to take a break. Once the build is done, you'll have an Ubuntu image ready to run your API. But the final size of the image might not be ideal; anything above a few hundred MB for this API would be considered an overly large image.

      Run the following command to list all Docker images and find the size of your Ubuntu image:

      You'll see output showing the image you just created:


      REPOSITORY TAG IMAGE ID CREATED SIZE ubuntu latest 61b2096f6871 33 seconds ago 636MB . . .

      As is highlighted in the output, this image has a size of 636MB for a basic Golang API, a number that may vary slightly from machine to machine. Over multiple builds, this large size will significantly affect deployment times and network throughput.

      In this section, you built an Ubuntu image with all the needed Go tools and dependencies to run the API you cloned in Step 1. In the next section, you'll use a pre-built, language-specific Docker image to simplify your Dockerfile and streamline the build process.

      Step 3 — Building a Language-Specific Base Image

      Pre-built images are ordinary base images that users have modified to include situation-specific tools. Users can then push these images to the Docker Hub image repository, allowing other users to use the shared image instead of having to write their own individual Dockerfiles. This is a common process in production situations, and you can find various pre-built images on Docker Hub for almost any use case. In this step, you'll build your sample API using a Go-specific image that already has the compiler and dependencies installed.

      With pre-built base images already containing the tools you need to build and run your app, you can cut down the build time significantly. Because you're starting with a base that has all needed tools pre-installed, you can skip adding these to your Dockerfile, making it look a lot cleaner and ultimately decreasing the build time.

      Go ahead and create another Dockerfile and name it Dockerfile.golang. Open it up in your text editor:

      • nano ~/mux-go-api/Dockerfile.golang

      This file will be significantly more concise than the previous one because it has all the Go-specific dependencies, tools, and compiler pre-installed.

      Now, add the following lines:


      FROM golang:1.10
      WORKDIR /go/src/api
      COPY . .
          go get -d -v 
          && go install -v 
          && go build
      EXPOSE 3000
      CMD ["./api"]

      Starting from the top, you'll find that the FROM statement is now golang:1.10. This means Docker will fetch a pre-built Go image from Docker Hub that has all the needed Go tools already installed.

      Now, once again, build the Docker image with:

      • docker build -f Dockerfile.golang -t golang .

      Check the final size of the image with the following command:

      This will yield output similar to the following:


      REPOSITORY TAG IMAGE ID CREATED SIZE golang latest eaee5f524da2 40 seconds ago 744MB . . .

      Even though the Dockerfile itself is more efficient and the build time is shorter, the total image size actually increased. The pre-built Golang image is around 744MB, a significant amount.

      This is the preferred way to build Docker images. It gives you a base image which the community has approved as the standard to use for the specified language, in this case Go. However, to make an image ready for production, you need to cut away parts that the running application does not need.

      Keep in mind that using these heavy images is fine when you are unsure about your needs. Feel free to use them both as throwaway containers as well as the base for building other images. For development or testing purposes, where you don't need to think about sending images through the network, it's perfectly fine to use heavy images. But if you want to optimize deployments, then you need to try your best to make your images as tiny as possible.

      Now that you have tested a language-specific image, you can move on to the next step, in which you will use the lightweight Alpine Linux distribution as a base image to make your Docker image lighter.

      Step 4 — Building Base Alpine Images

      One of the easiest steps to optimize your Docker images is to use smaller base images. Alpine is a lightweight Linux distribution designed for security and resource efficiency. The Alpine Docker image uses musl libc and BusyBox to stay compact, requiring no more than 8MB in a container to run. The tiny size is due to binary packages being thinned out and split, giving you more control over what you install, which keeps the environment as small and efficient as possible.

      The process of creating an Alpine image is similar to how you created the Ubuntu image in Step 2. First, create a new file called Dockerfile.alpine:

      • nano ~/mux-go-api/Dockerfile.alpine

      Now add this snippet:


      FROM alpine:3.8
      RUN apk add --no-cache 
      ENV GOPATH /go
      ENV PATH $GOPATH/bin:/usr/local/go/bin:$PATH
      ENV APIPATH $GOPATH/src/api
      RUN mkdir -p "$GOPATH/src" "$GOPATH/bin" "$APIPATH" && chmod -R 777 "$GOPATH"
      COPY . .
          go get -d -v 
          && go install -v 
          && go build
      EXPOSE 3000
      CMD ["./api"]

      Here you're adding the apk add command to use Alpine's package manager to install Go and all libraries it requires. As with the Ubuntu image, you need to set the environment variables as well.

      Go ahead and build the image:

      • docker build -f Dockerfile.alpine -t alpine .

      Once again, check the image size:

      You will receive output similar to the following:


      REPOSITORY TAG IMAGE ID CREATED SIZE alpine latest ee35a601158d 30 seconds ago 426MB . . .

      The size has gone down to around 426MB.

      The small size of the Alpine base image has reduced the final image size, but there are a few more things you can do to make it even smaller.

      Next, try using a pre-built Alpine image for Go. This will make the Dockerfile shorter, and will also cut down the size of the final image. Because the pre-built Alpine image for Go is built with Go compiled from source, its footprint is significantly smaller.

      Start by creating a new file called Dockerfile.golang-alpine:

      • nano ~/mux-go-api/Dockerfile.golang-alpine

      Add the following contents to the file:


      FROM golang:1.10-alpine3.8
      RUN apk add --no-cache --update git
      WORKDIR /go/src/api
      COPY . .
      RUN go get -d -v 
        && go install -v 
        && go build
      EXPOSE 3000
      CMD ["./api"]

      The only differences between Dockerfile.golang-alpine and Dockerfile.alpine are the FROM command and the first RUN command. Now, the FROM command specifies a golang image with the 1.10-alpine3.8 tag, and RUN only has a command for installing Git. You need Git for the go get command to work in the second RUN command at the bottom of Dockerfile.golang-alpine.

      Build the image with the following command:

      • docker build -f Dockerfile.golang-alpine -t golang-alpine .

      Retrieve your list of images:

      You will receive the following output:


      REPOSITORY TAG IMAGE ID CREATED SIZE golang-alpine latest 97103a8b912b 49 seconds ago 288MB

      Now the image size is down to around 288MB.

      Even though you've managed to cut down the size a lot, there's one last thing you can do to get the image ready for production. It's called a multi-stage build. By using multi-stage builds, you can use one image to build the application while using another, lighter image to package the compiled application for production, a process you will run through in the next step.

      Ideally, images that you run in production shouldn't have any build tools installed or dependencies that are redundant for the production application to run. You can remove these from the final Docker image by using multi-stage builds. This works by building the binary, or in other terms, the compiled Go application, in an intermediate container, then copying it over to an empty container that doesn't have any unnecessary dependencies.

      Start by creating another file called Dockerfile.multistage:

      • nano ~/mux-go-api/Dockerfile.multistage

      What you'll add here will be familiar. Start out by adding the exact same code as with Dockerfile.golang-alpine. But this time, also add a second image where you'll copy the binary from the first image.


      FROM golang:1.10-alpine3.8 AS multistage
      RUN apk add --no-cache --update git
      WORKDIR /go/src/api
      COPY . .
      RUN go get -d -v 
        && go install -v 
        && go build
      FROM alpine:3.8
      COPY --from=multistage /go/bin/api /go/bin/
      EXPOSE 3000
      CMD ["/go/bin/api"]

      Save and close the file. Here you have two FROM commands. The first is identical to Dockerfile.golang-alpine, except for having an additional AS multistage in the FROM command. This will give it a name of multistage, which you will then reference in the bottom part of the Dockerfile.multistage file. In the second FROM command, you'll take a base alpine image and COPY over the compiled Go application from the multistage image into it. This process will further cut down the size of the final image, making it ready for production.

      Run the build with the following command:

      • docker build -f Dockerfile.multistage -t prod .

      Check the image size now, after using a multi-stage build.

      You will find two new images instead of only one:


      REPOSITORY TAG IMAGE ID CREATED SIZE prod latest 82fc005abc40 38 seconds ago 11.3MB <none> <none> d7855c8f8280 38 seconds ago 294MB . . .

      The <none> image is the multistage image built with the FROM golang:1.10-alpine3.8 AS multistage command. It's only an intermediary used to build and compile the Go application, while the prod image in this context is the final image which only contains the compiled Go application.

      From an initial 744MB, you've now shaved down the image size to around 11.3MB. Keeping track of a tiny image like this and sending it over the network to your production servers will be much easier than with an image of over 700MB, and will save you significant resources in the long run.


      In this tutorial, you optimized Docker images for production using different base Docker images and an intermediate image to compile and build the code. This way, you have packaged your sample API into the smallest size possible. You can use these techniques to improve build and deployment speed of your Docker applications and any CI/CD pipeline you may have.

      If you are interested in learning more about building applications with Docker, check out our How To Build a Node.js Application with Docker tutorial. For more conceptual information on optimizing containers, see Building Optimized Containers for Kubernetes.

      Source link