One place for hosting & domains

      Optimize

      How To Optimize a WordPress Installation Before Troubleshooting



      Part of the Series:
      Common WordPress Errors

      This tutorial series explains how to troubleshoot and fix common errors that you may encounter when deploying, maintaining, and updating your WordPress installation.

      Each tutorial in this series includes descriptions of common deployment, maintenance, or update errors, and explores ways to fix and optimize your installation to scale.

      Introduction

      WordPress is a robust Content Management System (CMS) that provides blog and site infrastructure, creation, and publishing tools. While WordPress is a well-maintained, open source CMS, you may sometimes encounter issues or errors that will prevent you from having access to common functionalities.

      In this tutorial, you’ll learn how to perform steps to ward against common WordPress errors in a way that also optimizes your site to prevent those errors from occurring in the future.

      Step 1 — Creating a Backup of Your Site

      Before you begin any troubleshooting process, it’s wise to back up your site. Creating backups of your site, whether manually or by using a plugin, allows you to restore your WordPress installation in the event of an error. A backup also serves to protect your WordPress site against security threats, data loss, and more.

      Learn How To Back Up Your WordPress Site to Object Storage with our tutorial, or explore WordPress Backup Plugins to automate the backup process.

      Step 2 — Making Sure your Cache is Clear

      A cache is a temporary storage space that allows browsers and programs to take a snapshot of your site and store temporary files and data, to load your site faster and increase performance.

      While caching does generally give a performance boost to site load times and improves user experience, sometimes errors with visual elements may be a result of a cached version of your site being served. Clearing your browser cache as well as your WordPress cache typically solves issues with older or cached versions of your site being shown.

      This list shares step-by-step information to clear your browser cache on any browser, and there are a number of site caching plugins available that allow you to clear and maintain WordPress cache size, in order to optimize your site’s appearance and performance.

      Step 3 — Auditing Your Plugins

      Plugins are third-party software added to WordPress installations to extend functionality. Even though these extensions provide helpful and convenient features, they can sometimes conflict with each other and cause your site to experience issues in performance, speed, and security.

      To audit your plugins and potentially troubleshoot an error, start by ensuring that each of your plugins are updated to its most recent version. Next, you can also deactivate all plugins and reactivate them one-by-one.

      While inside of your WordPress site’s admin panel, click Plugins, then All Plugins. Within your list of plugins, click the box to Select All, then click Deactivate from the dropdown above the checkbox. Click Apply to deactivate all plugins.Then, select one plugin at a time and click Activate to reactivate each of them while monitoring your website to identify any issues .

      Conclusion

      This tutorial highlighted three steps that you can take to prevent your WordPress site from experiencing common errors, and to maintain the health of your WordPress installation.

      For more information on optimizing your WordPress installation on Ubuntu, visit our tutorial, How to Optimize WordPress on Ubuntu 20.04.



      Source link

      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