One place for hosting & domains

      Optimize

      How to Optimize WordPress on Ubuntu 20.04


      Introduction

      Optimizing WordPress installations gives the clients and individuals who use your sites the performance, speed, and flexibility they’ve come to expect with WordPress. Whether you’re managing a personal site or a suite of installations for various clients, taking the time to optimize your WordPress installations increases efficiency and performance.

      In this tutorial, you’ll explore how to optimize WordPress installations in a way that’s built for scale, including guidance on configuration, speed, and overall performance.

      Prerequisites

      This is a conceptual article sharing different ways to approach optimization of a WordPress installation on Ubuntu 20.04. While this tutorial references the use of a managed solution via our WordPress 1-Click App, there are many different starting points, including:

      Whichever you choose, this tutorial will start with the assumption that you have or are prepared to install a fully-working WordPress installation configured with an administrative user on Ubuntu 20.04.

      Step 1 — Consider Your Installation

      During the installation and creation of your WordPress installation there are a few variables to take into account, including the location of your potential users, the scope of your WordPress site or suite of sites, and the maintenance and security preferences set that allow your site to be continually optimized. Taking the time to dive into each thoughtfully before building out your site will save time and benefit your WordPress installation as it grows.

      Considering Your Site’s Potential

      The first step in optimizing your WordPress site is to have a deep understanding of how you intend to use and grow your site. Will it be one site, or a network of sites? Is your site a static or dynamic website? Answering these questions before setting up your installation can inform some of your initial decisions regarding hosting, storage size, and performance.

      For example, if you’d like to build a personal blog, caching and optimizing images and visual content is important to consider. If you intend to create a community or ecommerce site with concurrent visitors and frequently changing data, considerations for server resources should be made. Being thoughtful about your intention for your WordPress installation from the start will guide the usefulness of security and performance tweaks made to your site, and lend to an overall more efficient installation.

      Optimizing Installation Preferences

      There are a few preferences that are important to consider while installing WordPress that can reduce latency and increase performance on your site.

      Hosting and Included Software

      First, select a hosting provider that provides the latest WordPress, Apache, MySQL, and PHP software with firewall and SSL certificate capabilities. A reliable and modern hosting provider will give you the best start for your LAMP stack installation. With shared hosting, be aware of server usage and customers per server to ensure the best performance for your site. Choosing the right hosting provider for your needs will help you prevent downtime and performance errors.

      Location and Latency

      Be aware of the location of your servers or datacenters when starting a new WordPress installation, and choose the location that best suits the need of your site and general location of your visitors and users. Latency, the time it takes for data to be transmitted between your site and users, fluctuates based on location. The WordPress documentation on site analytic tools explains how to track visitor location data, as well as the number of visits to your site. Having an idea from the start about where your visitors are from can help determine where to host your site and provide them with a faster browsing experience.

      Step 2 — Consider Your Theme

      There are a wide range of available themes that can be used or customized for WordPress. Many themes can be configured with user-friendly drag and drop interfaces, integrated with custom plugins and more. When setting up your WordPress site, it’s a good idea to initially consider only the essential features that you’ll use for the lifecycle of your site, adding more as you grow.

      Optimizing Theme Configuration

      Starting with a lightweight theme can help your installation to load more efficiently. A theme will require fewer database calls and by keeping your site free of unnecessary code, your users will have fewer delays in site speed and performance.

      For any theme selected, be sure to turn off or disable any features offered with the theme that you won’t need or use. These can be preferences offered in the Appearance section of the WordPress dashboard, typically under Theme Editor or Customize. Turning off features you don’t use reduces the number of requests and calls happening to query for data in the background.

      While there are a number of free and paid options for WordPress themes available online, many use page builders that add excess shortcode and unused code that will affect the performance of your site. Consider your use case when deciding whether or not to use a page builder, as they typically include a lot of extra processes that will have an impact on your site’s speed.

      Considering Plugin Use

      WordPress plugins offer extended functionality for WordPress installations through added code that allows users to customize their installations to suit their specific needs. There are over 56,000 currently available plugins, making them an appealing way to add additional features to a WordPress site.

      While plugins can increase the efficiency of your site, care should be taken in selecting quality plugins that are maintained and updated regularly. Because many plugins not only add code to your site but entries to your WordPress installation’s database, using too many plugins may cause site speed issues over time.

      Step 3 — Optimize for Security and Performance

      Once you have installed all of the plugins, widgets, and additional features you’d like to add to your WordPress installation, there are a few more optimization options to try within the WordPress dashboard that could positively impact your site’s speed and performance.

      Tweaking WordPress Settings

      First, be sure to change your site’s login address. Because most WordPress administrative login pages end in /wp-admin, this page is often prone to attacks. There are a number of tools available that enable you to change your login URL — be sure to select the one that works best for your use case.

      Next, consider the Site Health tool, located in the Tools section of your WordPress dashboard:

      picture of WordPress Site Health page

      Consider the results shown, and follow the instructions found in each dropdown on the Status tab to improve security or performance as mentioned within the tabs.

      Using the built-in configuration offered in the WordPress dashboard ensures that you’ve covered all of the readily available configuration tweaks for your installation.

      Caching for Site Speed

      Caching can also help improve your WordPress site’s performance and speed. Caching, a core design feature of the HTTP protocol meant to minimize network traffic while improving the perceived responsiveness of the system as a whole, can be used to help minimize load times when implemented on your site. WordPress offers a number of caching plugins that are helpful in maintaining a snapshot of your site to serve static HTML elements, reducing the amount of PHP calls and improving page load speed.

      Conclusion

      In this tutorial you explored a number of different techniques that you can use to make your WordPress installation on Ubuntu 20.04 faster and more efficient. Following the suggestions in this tutorial will help ensure that your site’s performance isn’t an issue as you grow in users and content on your site.

      To learn more about some of the security practices and WordPress optimization tips that are mentioned in this guide, visit our tutorial, “How To Configure Secure Updates and Installations in WordPress on Ubuntu 20.04”.



      Source link

      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.

      Introduction

      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.

      Prerequisites

      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 https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb

      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:

      Output

      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 127.0.0.1 -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:

      • LOAD MYSQL VARIABLES TO RUNTIME;

      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:

      • SAVE MYSQL VARIABLES TO DISK;

      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, '127.0.0.1', 3306);

      To apply the changes, run LOAD and SAVE again:

      • LOAD MYSQL SERVERS TO RUNTIME;
      • SAVE MYSQL SERVERS TO DISK;

      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:

      • LOAD MYSQL USERS TO RUNTIME;
      • SAVE MYSQL USERS TO DISK;

      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:

      Output

      +----------------------------+ | 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 https://github.com/datacharmer/test_db.git

      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:

      Output

      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:

      Output

      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 127.0.0.1 -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:

      Output

      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:

      Output

      +------------+-------------+-----------+--------------------+------------------------------------------+ | 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:

      • LOAD MYSQL QUERY RULES TO RUNTIME;
      • SAVE MYSQL QUERY RULES TO DISK;
      • 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:

      Output

      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.

      Conclusion

      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.

      Introduction

      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.

      Prerequisites

      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:

      Output

      +------------------+-------+ | 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:

      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;

      Output

      Query OK, 1 row affected (0.00 sec)

      Then switch to the database:

      Output

      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;

      Output

      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');

      Output

      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:

      Output

      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:

      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:

      Output

      +----------+------------+-------------------------+ | 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:

      /etc/mysql/my.cnf

      ...
      [mysqld]
      query_cache_type=1
      query_cache_size = 10M
      query_cache_limit=256K
      

      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:

      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:

      Output

      +----------+------------+-------------------------+ | 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:

      • SHOW PROFILE FOR QUERY 1;

      Output

      +--------------------------------+----------+ | 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:

      • SHOW PROFILE FOR QUERY 2;

      Output

      +--------------------------------+----------+ | 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.

      Conclusion

      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