{"id":69640,"date":"2023-10-24T03:17:59","date_gmt":"2023-10-24T09:17:59","guid":{"rendered":"https:\/\/www.scalahosting.com\/blog\/?p=69640"},"modified":"2026-01-22T02:15:41","modified_gmt":"2026-01-22T08:15:41","slug":"optimizing-database-performance-on-managed-vps-hosting","status":"publish","type":"post","link":"https:\/\/www.scalahosting.com\/blog\/optimizing-database-performance-on-managed-vps-hosting\/","title":{"rendered":"Optimizing Database Performance on Managed VPS Hosting"},"content":{"rendered":"\n<p><em>How do you improve your website performance? <\/em>One obvious solution would be to upgrade your hosting account. Your site will have more hardware resources to play with, so it can process more requests at once and deliver the data more quickly.<\/p>\n\n\n\n<p>That&#8217;s what people assume, anyway. The reality is, as always, different.<\/p>\n\n\n\n<p>Modern websites are complex systems with <strong>numerous components that must work in unison<\/strong>. If one of them is acting up &#8211; the entire project will<strong> slow to a crawl<\/strong>, no matter how powerful the hosting server is. And one of the most important components is <strong>the <\/strong><a href=\"https:\/\/www.scalahosting.com\/kb\/how-to-create-a-database-in-mysql\/\"><strong>database<\/strong><\/a>.<\/p>\n\n\n\n<p>Today, we&#8217;ll take an in-depth look into how databases work, what slows them down, and how you can optimize them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>The Importance of Database Performance Optimization<\/strong><\/h2>\n\n\n\n<p>In the early days of the<strong> <\/strong><a href=\"https:\/\/en.wikipedia.org\/wiki\/World_Wide_Web\"><strong>World Wide Web<\/strong><\/a>, websites used to consist almost <strong>exclusively of HTML<\/strong> and media files. After a user entered the site&#8217;s URL, the web server would deliver the required files for the browser to render them into visible text and pictures. Every visitor would see the same content, and all they could do with it was click <strong>hyperlinks to browse different pages and images<\/strong>.<\/p>\n\n\n\n<p><em>Things are a whole lot different now.<\/em><\/p>\n\n\n\n<p>We often have <strong>multiple scripts and applications that run simultaneously <\/strong>to turn even the simplest page into an interactive website. Such solutions can enable simple features like comments under blog posts, but they can also be used for more complicated things like<strong> payments, orders, subscriptions, etc. <\/strong>All this would be impossible without a database.<\/p>\n\n\n\n<p>Every time a user interacts with your site, your web application sends requests (or queries) to your database, asking it to either store a new data record or serve an existing one. The speed with which these queries are processed is critical for the site&#8217;s overall performance.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>But how exactly do you tell whether your database is quick or not?<\/strong><\/h3>\n\n\n\n<p>With so many variables, answering this question is fairly tricky. There are two main types of databases and dozens of different database management systems (DBMSs), all with their individual data handling mechanisms.&nbsp;<\/p>\n\n\n\n<p>Each has its own<strong> speed monitoring and measurement tools<\/strong> that can help you track your performance. Nevertheless, the aspects of the database&#8217;s operation that fall under scrutiny are usually the same.&nbsp;<\/p>\n\n\n\n<p><strong>Here they are:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Response time &#8211; <\/strong>the response time measures <strong>how long the database takes to process a query<\/strong> and return the results to the user. You could argue that it&#8217;s not the most accurate metric out there. No two queries are the same, and the<strong> average response times<\/strong> you get at any given time depend on many different factors.<\/li>\n<\/ul>\n\n\n\n<p>However, this is the metric that users notice the most, and it&#8217;s definitely something you want to pay attention to. If the average response time increases, the<strong> <\/strong><a href=\"https:\/\/www.scalahosting.com\/blog\/optimize-web-site\/\"><strong>site&#8217;s overall performance<\/strong><\/a><strong> <\/strong>deteriorates, so it&#8217;s time to do something about it.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Query performance &#8211; <\/strong>the<strong> query&#8217;s structure<\/strong> plays an enormous part in how long the database takes to deliver the required data. By <strong>monitoring the performance of individual queries<\/strong>, database administrators can single out slow ones and take the necessary precautions to optimize them.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Query throughput &#8211; <\/strong>throughput refers to <strong>the volume of information a database can process at once<\/strong>. On the one hand, admins must know what this volume is in order to scale the hosting environment accordingly. However, they must also be aware of how the data is delivered.<br><br><strong>It could be processed in a small number of large querie<\/strong>s, or it could be the result of many smaller ones. This information helps admins draw a plan and determine what optimization techniques they&#8217;ll use.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Database events &#8211; <\/strong>a database event is an event that revolves around <strong>storing, modifying, or deleting data in the database<\/strong>.&nbsp; Both users and applications can cause a db event.<br><br>For example, a visitor setting up a new account or an<strong> application automatically cleaning error logs<\/strong>. Too many events at one time could slow the database down, especially if they happen during busy hours. It&#8217;s the job of the system administrator to avoid such scenarios.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Open database connections &#8211; <\/strong>the database connection facilitates <strong>the communication between users of software applications on one side and the database on the other<\/strong>. The more open connections you have at any given point, the more bandwidth you use. However, it&#8217;s the new connection requests that have an even bigger impact on<strong> both the performance and resource usage.<\/strong><\/li>\n<\/ul>\n\n\n\n<p>That&#8217;s why the concept of connection pooling is gathering more and more speed. A <strong>connection pool is a cache of open database connections<\/strong> that can be shared among users and software applications. With it, visitors won&#8217;t need to send new requests all the time and will instead use one of the already opened connections. This reduces latency and improves the overall performance.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Database errors &#8211; <\/strong>a database error often results in a complete website breakdown. The pages become inaccessible, and all visitors see is a <strong>white background and an error message<\/strong> that may or may not make sense to the average Joe.<\/li>\n<\/ul>\n\n\n\n<p>The user will likely click away and never return to your site again, which is not ideal for your business. That&#8217;s why it&#8217;<strong>s crucial to identify and resolve any database errors<\/strong> as quickly as possible.<\/p>\n\n\n\n<p>However, before you can <strong>monitor and optimize your database<\/strong>, you need to know what sort of technology it uses.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Choosing the Right Database and DBMS<\/strong><\/h2>\n\n\n\n<p>Choosing the <strong>right database management system<\/strong> is crucial not just for your website performance but for the future of the entire project. Before we show you how to do it, however, <strong>we need to clarify a few concepts.<\/strong><\/p>\n\n\n\n<p><strong>First, we need to discuss the two main types of databases:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Relational databases &#8211; <\/strong>relational databases are also known as <strong>SQL databases<\/strong> because they work with the <strong>Structured Query Language<\/strong>. Every bit of information you store in such a format is placed in a table. Your <strong>DBMS builds relations between the different tables <\/strong>so data can be retrieved and served more quickly and efficiently.<\/li>\n<\/ul>\n\n\n\n<p>Relational databases take a more<strong> simplistic approach to data storage<\/strong>. The <strong>SQL syntax is easy to understand<\/strong>, and the structure eliminates the chance of storing duplicate entries.<\/p>\n\n\n\n<p>Conversely, relational databases are designed to work on a single machine, so they <strong>can&#8217;t be scaled horizontally<\/strong>. This could be an issue if your project grows to the point where a single server is no longer powerful enough to support&nbsp; it.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Non-relational databases &#8211; <\/strong>non-relational databases are also called <strong>NoSQL (as in Not Only SQL)<\/strong>. NoSQL databases use several storage methods for saving information. Some keep it in files, some use columns and tables (<em>with no relations between them<\/em>), and some rely on complicated networks of nodes.<\/li>\n<\/ul>\n\n\n\n<p>Non-relational databases can store <strong>a more diverse range of data structures<\/strong>, and because they can be <strong>horizontally scaled across multiple servers<\/strong> &#8211; they can also work great for large-scale websites with enormous volumes of data. However, for a regular website where you&#8217;ll handle standardized data sets, using a<strong> non-relational database<\/strong> could be an unnecessary complication.<\/p>\n\n\n\n<p>Whether you&#8217;ll use a relational or a non-relational database depends mainly on the type of site you&#8217;re trying to build. Most content management systems and website-building applications like <a href=\"https:\/\/www.scalahosting.com\/blog\/what-is-wordpress-how-does-it-work\/\"><strong>WordPress<\/strong><\/a><strong>, Joomla, Drupal, Magento, etc., use SQL databases<\/strong>, and there are a couple of reasons for this.<\/p>\n\n\n\n<p>First, many of these platforms can trace their roots back to the early 2000s when the management of non-relational databases was even more complicated than it is today. The second and more crucial reason is that the data generated by most <strong>web applications <\/strong>tends to be predictable. There are limited data types, and the structure rarely changes.<\/p>\n\n\n\n<p>Ultimately, the type of database you use is determined by your <strong>project&#8217;s requirements.<\/strong> The same is also true for the DBMS. The <strong>DBMS sits between the user or application and the database<\/strong>. It&#8217;s responsible for handling the queries between the client and the server.&nbsp;<\/p>\n\n\n\n<p><strong>There are plenty of options out there. If you use an SQL database, you&#8217;ll choose between some of the following:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>MySQL<\/li>\n\n\n\n<li>MariaDB<\/li>\n\n\n\n<li>Microsoft SQL Server<\/li>\n\n\n\n<li>PostgreSQL<\/li>\n\n\n\n<li>SQLite<\/li>\n<\/ul>\n\n\n\n<p>The choice is more limited for NoSQL, with the most popular options being <strong>MongoDB <\/strong>and <strong>Redis<\/strong>. Certain proprietary products like the Oracle DBMS work with both relational and non-relational databases.<\/p>\n\n\n\n<p>Once again, the primary consideration is to see which DBMSs are supported by the software you&#8217;ll use to build your site. Some apps are designed to work with only one particular database technology, while others support multiple options.<\/p>\n\n\n\n<p>Which one you choose depends on many factors, including <strong>the size of your project, the potential for growth, the tools you use to build it, <\/strong>and <strong>your budget<\/strong>. Consider them all individually and drop the unsuitable options until you&#8217;re down to a few candidates.&nbsp;<\/p>\n\n\n\n<p>With the choice narrowed down, you&#8217;ll need to compare the <strong>different DBMSs and their features side-by-side<\/strong>. That&#8217;s when you&#8217;ll notice that certain differences between the individual systems may directly affect your database performance.<\/p>\n\n\n\n<p>For example, if you compare <strong>MySQL and MariaDB<\/strong> head-to-head, you&#8217;ll find that the latter has more storage engine and speed optimization options. If performance is a major consideration, it should be obvious where the scales are tipped.<\/p>\n\n\n\n<p>In the end, choosing the right DBMS comes down to an in-depth analysis and a careful arrangement of the priorities.<\/p>\n\n\n\n<p>Most managed hosting accounts come with a <strong>stack of technologies that includes a database management system<\/strong>. Because most websites work with relational databases, the typical setup usually has an<strong> SQL DBMS.<\/strong><\/p>\n\n\n\n<p>Overall, <strong>relational databases are much more popular<\/strong>, so the optimization techniques we&#8217;ll show you today revolve primarily around them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Database Indexing and Query Optimization<\/strong><\/h2>\n\n\n\n<figure class=\"wp-block-image size-full mpg-gallery\"><img decoding=\"async\" width=\"1140\" height=\"513\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Indexing-1140x513-1.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, Database Indexing and Query Optimization\" class=\"wp-image-69643\" srcset=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Indexing-1140x513-1.webp 1140w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Indexing-1140x513-1-300x135.webp 300w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Indexing-1140x513-1-768x346.webp 768w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/figure>\n\n\n\n<p>The faster your database queries, the quicker the website. The speed of query execution depends on numerous different factors, including the<strong> <\/strong><a href=\"https:\/\/www.scalahosting.com\/blog\/key-server-performance-metrics-for-actionable-monitoring\/\"><strong>server&#8217;s performance<\/strong><\/a>, the number of simultaneous connections, the <strong>buffer and memory caching configuration<\/strong>, etc.<\/p>\n\n\n\n<p>However, one of the simplest ways of ensuring data is delivered quickly is to organize it better. And if you use a relational database, one of the easiest things to do is to implement <strong>an indexing strategy<\/strong>. Let&#8217;s look at a hypothetical example to demonstrate how indexing works.<\/p>\n\n\n\n<p><em>Imagine that you have a database table with 230,000 rows \u2013 roughly the number of entries in a modern English dictionary. The <\/em><strong><em>first column contains IDs, the second for words or phrases<\/em><\/strong><em>, and the third for their respective definitions. The entries are NOT arranged alphabetically.<\/em><\/p>\n\n\n\n<p><em>A user wants to find the definition of the word &#8220;database,&#8221; located on row number 200,000. What the DBMS has to do is load the table into memory, start from the very top, and <\/em><strong><em>run through every single row until it finds the correct one<\/em><\/strong><em>. In other words, to execute a single query, your database needs to read 199,999 rows. This is hardly the most efficient approach, and it&#8217;s the reason why indexes exist.<\/em><\/p>\n\n\n\n<p>An index is <strong>a separate data structure within the database<\/strong>. It contains a copy of one or more columns from a database table, as well as pointers to where the relevant data is.<\/p>\n\n\n\n<p>In our case, we have an index with a copy of the column containing the words and pointers telling the DBMS where each entry is located on the main table. Because this is a<strong> separate data structure<\/strong>, we can arrange the words and phrases alphabetically without causing any other damage (<em>like messing up the IDs, for example<\/em>). The benefits of such an index are two-fold.<\/p>\n\n\n\n<p>First, the DBMS uses the alphabetical order to quickly locate the word on the index. Instead of going through every one of the 230,000 words and phrases, it&#8217;ll check the 115,000<sup>th<\/sup> entry (the halfway point) in the index and figure out whether the word &#8220;database&#8221; is before or after it alphabetically. We&#8217;ll assume that under the 115,000<sup>th<\/sup> entry, we have the word &#8220;microscope,&#8221; so &#8220;database&#8221; is definitely above it.<\/p>\n\n\n\n<p><strong>&#8220;Microscope,&#8221;<\/strong> along with all the entries below it, will be eliminated from the search. The DBMS will repeat the same process several times<strong> until it locates the &#8220;database&#8221; entry within the index<\/strong>. This is much more efficient than reading through every row in a database.<\/p>\n\n\n\n<p>Then, having found the entry in the index, the database has the coordinates of the data block that contains the relevant data. As a result, it can<strong> retrieve the definition without loading the entire database table into memory. <\/strong>This makes it both <strong>faster and more efficient<\/strong>.<\/p>\n\n\n\n<p>Any downsides? Well, being a separate structure, an index inevitably takes up some additional storage. Furthermore, you need to carefully pick and combine the columns you&#8217;ll add to your indexes. Ideally, you want to index data that is frequently searched and used.<\/p>\n\n\n\n<p>However, the enormous performance benefits far outweigh any disadvantages.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>But how do you create a database index?<\/strong><\/h3>\n\n\n\n<p>One option would be to use the <strong><em>CREATE INDEX<\/em><\/strong><strong> SQL <\/strong>statement. It&#8217;s <a href=\"https:\/\/www.w3schools.com\/sql\/sql_create_index.asp\">well-documented<\/a> and shouldn&#8217;t present too much of a challenge for anyone with some <strong>SQL experience<\/strong>. Those who don&#8217;t feel all that comfortable working with the command line will prefer to use a<strong> GUI-based tool<\/strong> like <strong>phpMyAdmin<\/strong>.<\/p>\n\n\n\n<p><strong>The steps are as follows:<\/strong><\/p>\n\n\n\n<p>1. Open phpMyAdmin via your control panel. If you use SPanel, you can find the icon under the <strong>Databases <\/strong>section on the User Interface&#8217;s homepage.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full mpg-gallery\"><img decoding=\"async\" width=\"847\" height=\"178\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, But how do you create a database index?\" class=\"wp-image-69644\" srcset=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin.webp 847w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin-300x63.webp 300w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin-768x161.webp 768w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/figure>\n\n\n\n<p>2. Select the database you&#8217;d like to modify from the menu on the left.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full mpg-gallery\"><img decoding=\"async\" width=\"238\" height=\"277\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin2.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, But how do you create a database index? 2\" class=\"wp-image-69645\"\/><\/figure>\n\n\n\n<p>3. The main screen will now showcase all the tables in the selected database. Click on the one you&#8217;d like to index.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large mpg-gallery\"><img decoding=\"async\" width=\"1140\" height=\"548\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin3-1140x548.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, But how do you create a database index? 3\" class=\"wp-image-69646\" srcset=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin3-1140x548.webp 1140w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin3-300x144.webp 300w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin3-768x369.webp 768w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin3.webp 1165w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/figure>\n\n\n\n<p>4. Go to the <strong>Structure <\/strong>tab. You&#8217;ll see all the columns in the table. Use the checkboxes to select the ones you want to add to the index and click the <strong>Index <\/strong>button below the list.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full mpg-gallery\"><img decoding=\"async\" width=\"1070\" height=\"856\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin4.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, But how do you create a database index? 4\" class=\"wp-image-69647\" srcset=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin4.webp 1070w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin4-300x240.webp 300w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/phpmyadmin4-768x614.webp 768w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/figure>\n\n\n\n<p>If the data is not corrupted, <strong>phpMyAdmin<\/strong> should display a message saying the operation is successful.<\/p>\n\n\n\n<p class=\"alert-orange\"><strong><em>NOTE:<\/em><\/strong> <em>Remember that you&#8217;re making changes to the database \u2013 one of your website&#8217;s main components. Before you try to create an index, it may be a good idea to create a full backup of your site and database in case something goes wrong.<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Memory Management and Buffering<\/strong><\/h2>\n\n\n\n<figure class=\"wp-block-image size-full mpg-gallery\"><img decoding=\"async\" width=\"1140\" height=\"513\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Memory-Management-1140x513-1.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, Memory Management and Buffering\" class=\"wp-image-69648\" srcset=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Memory-Management-1140x513-1.webp 1140w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Memory-Management-1140x513-1-300x135.webp 300w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Memory-Management-1140x513-1-768x346.webp 768w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/figure>\n\n\n\n<p>Buffering is all about efficiently utilizing the available hardware resources. More specifically, it shows how your database management system uses your <strong>server&#8217;s RAM to speed up query execution.<\/strong><\/p>\n\n\n\n<p>Whenever the DBMS has to read or write information from or to the database, it saves a copy of the record into the server&#8217;s memory. Then, it receives a request for the same bit of data, and it <strong>fetches the copy from the RAM.<\/strong><\/p>\n\n\n\n<p><em>Why does it do that?<\/em><\/p>\n\n\n\n<p>It&#8217;s all because <strong>serving data from memory is much faster than doing it from the disk<\/strong>. The speed difference depends on numerous factors, but even when you compare<strong> cutting-edge NVMe SSDs to standard-issue RAM<\/strong>, we&#8217;re talking about <strong>several times faster performance<\/strong> when the data is read from the memory.&nbsp; As you may have guessed, this all has a profound effect on the overall loading speeds and user experience.<\/p>\n\n\n\n<p>Obviously, the <strong>server memory is not unlimited<\/strong>, so the database can&#8217;t store an infinite volume of data in it. In fact, your database has an <strong>allocated volume of memory<\/strong> specifically reserved for caching data. It&#8217;s called a<strong> buffer<\/strong>, and if your site uses the InnoDB storage engine, its <strong>default capacity is 128MB.<\/strong><\/p>\n\n\n\n<p>This doesn&#8217;t sound like a lot, and some of you will be happy to hear you can change it. However, remember that increasing the buffer size won&#8217;t necessarily solve all your issues.&nbsp;<\/p>\n\n\n\n<p>This is partly because allocating more RAM to the buffer can leave other components<strong> crucial for your site&#8217;s performance deprived of the resources<\/strong> they need to run well. It&#8217;s also not guaranteed that the size of the buffer is the main performance hindrance.<\/p>\n\n\n\n<p>That&#8217;s why, if there is an issue with the database&#8217;s buffer, you&#8217;ll want to approach the matter more meticulously.<\/p>\n\n\n\n<p><strong>Here are some indicators you may want to pay close attention to:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>The buffer pool hit ratio &#8211; <\/strong><em>this metric shows you what percentage of all queries are responded to by the buffer<\/em> and what goes through to the disk. By default, the <strong>DBMS will process as many queries as possible <\/strong>via the buffer. However, if the request is for a page that isn&#8217;t stored in memory, it will be relayed to the disk, and its processing will be much slower.<br><br>That&#8217;s why keeping the buffer pool hit ratio as high as p\u0431ossible is essential. Experts recommend configuring your database so around 90% of all queries are handled by the buffer.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Page life expectancy &#8211; <\/strong>every copy of a record stored in the buffer is deleted after a period of time. The busier the database, the shorter this period is. Monitoring how frequently data records are removed from the buffer will give you the <strong>average page life expectancy<\/strong> \u2013 a metric that can help you improve your <strong>database&#8217;s performance.<\/strong><br><br>It&#8217;s traditionally accepted that a reasonable page life expectancy hovers around 300 seconds. However, that conclusion was reached back when servers were less powerful. Nowadays, it&#8217;s considered normal to have an<strong> average page life expectancy of 300 seconds for every 4GB of RAM.<\/strong><strong><br><\/strong><strong><br><\/strong>The busier the server, the more frequently pages must be deleted from the buffer. This will <strong>push the page life expectancy down and increase the number of requests<\/strong> that can&#8217;t be processed by the buffer. Because the disk is so much slower, the overall website performance will inevitably be affected.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Page reads\/sec. &#8211; <\/strong>this metric indicates how much data is read from the disk every second. You want to keep it as low as possible because, no matter<strong> <\/strong><a href=\"https:\/\/www.scalahosting.com\/blog\/10-reasons-need-ssd-cloud-server\/\"><strong>how quick modern SSDs are<\/strong><\/a><strong>,<\/strong> queries handled by the server&#8217;s disk are still comparatively slow.<br><br>There are many approaches to minimizing the<strong> role of the disk in database query execution<\/strong>. You can do it by <strong>providing an adequately sized buffer<\/strong> that caches frequently searched data, but you can also ensure the information is properly indexed so the DBMS can find it more quickly.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Managing your database buffer size<\/strong><\/h3>\n\n\n\n<p>There are many ways to monitor the health of your database&#8217;s buffer pool. For example,<strong> MySQL Workbench<\/strong>, a free GUI-based database management tool, has a performance dashboard showing all kinds of statistics, including the<strong> buffer pool usage, the read\/write requests, the disk reads per second, etc.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large mpg-gallery\"><img decoding=\"async\" width=\"1140\" height=\"694\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench-1140x694.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, Managing your database buffer size\" class=\"wp-image-69649\" srcset=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench-1140x694.webp 1140w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench-300x183.webp 300w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench-768x468.webp 768w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench-1536x936.webp 1536w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench.webp 1630w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/figure>\n\n\n\n<p>Changing the buffer&#8217;s size is more complicated. It requires modifying MySQL&#8217;s primary configuration file \u2013 <em>my.cnf<\/em>. Its precise location depends on the operating system. Most <a href=\"https:\/\/www.scalahosting.com\/blog\/which-is-the-best-operating-system-centos-vs-ubuntu-vs-debian-vs-opensuse-vs-windows\/\"><strong>Linux distributions<\/strong><\/a> based on Red Hat store it in the <em>\/etc\/<\/em> folder. The variable you need to add or modify depends on the storage engine. If you use InnoDB &#8211; it&#8217;s <strong><em>innodb_buffer_pool_size<\/em><\/strong><strong>.<\/strong> For<strong> MyISAM-powered databases<\/strong>, it&#8217;s <em>key_buffer_size<\/em>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Utilizing Caching Mechanisms<\/strong><\/h2>\n\n\n\n<figure class=\"wp-block-image size-full mpg-gallery\"><img decoding=\"async\" width=\"1140\" height=\"513\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Utilizing-Caching-Mechanisms-1140x513-1.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, Utilizing Caching Mechanisms\" class=\"wp-image-69650\" srcset=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Utilizing-Caching-Mechanisms-1140x513-1.webp 1140w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Utilizing-Caching-Mechanisms-1140x513-1-300x135.webp 300w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Utilizing-Caching-Mechanisms-1140x513-1-768x346.webp 768w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/figure>\n\n\n\n<p>As we discovered in the previous section, your relational database has a <strong>built-in mechanism to store information in memory for faster retrieva<\/strong>l. In other words, it effectively has a caching system out of the box. In light of this, you may think implementing other caching solutions would be unnecessary.<\/p>\n\n\n\n<p>This is not quite the case.<\/p>\n\n\n\n<p>Most <strong>caching solutions focus on static data like images, CSS stylesheets, and JavaScript files<\/strong>, but some advanced systems can also store database-generated data in the server&#8217;s RAM to speed up delivery.&nbsp;<\/p>\n\n\n\n<p><strong>They outperform the default buffer pool in many key areas, including:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>The cache storage method &#8211; <\/strong>your database&#8217;s buffer contains pages of information with multiple table rows and records. Caches built by dedicated solutions tend to be much better organized.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>The data retrieval mechanism &#8211; <\/strong>caching solutions rely on complex key\/value systems to locate the required data, resulting in much quicker delivery.<\/li>\n\n\n\n<li><strong>Scalability &#8211; dedicated caching solutions<\/strong> are built with projects of all shapes and sizes in mind. As a result, they can support your project and ensure excellent performance even as your site grows.<\/li>\n<\/ul>\n\n\n\n<p>There are dozens of caching systems, all with their advantages and disadvantages. When it comes to database caching, however, two solutions stand out from the crowd. Let&#8217;s have a look at them.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Memcached<\/strong><\/h3>\n\n\n\n<p>Memcached was first released more than two decades ago, and throughout the years, it has become an <strong>integral part of many development stacks<\/strong>. It&#8217;s popular with website owners because of its advanced versatility. It supports the <strong>most popular programming languages<\/strong> and has an<strong> easy-to-use API<\/strong>, so configuring your application to work with it should be no problem. A <strong>special plugin for MySQL databases <\/strong>makes the setup quick and easy.<\/p>\n\n\n\n<p>Memcached also supports <strong>distributed architecture<\/strong>, meaning cached data can be stored on multiple nodes simultaneously. This sounds rather expensive, but thanks to containerization and virtualization solutions like <strong>Docker and Kubernetes<\/strong>, it doesn&#8217;t need to be.<\/p>\n\n\n\n<p>At the same time, using multiple nodes will not only distribute the load and improve performance but also minimize potential downtime. This is why, in the past, even large social networks like Twitter used Memcached.<\/p>\n\n\n\n<p>Now, though, they&#8217;ve also implemented another caching system.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Redis<\/strong><\/h3>\n\n\n\n<p>Initially released in 2009, <strong>Redis is similar to Memcached<\/strong> in that it&#8217;s open-source and supported by most web hosting providers. Like Memcached, it stores data in the server&#8217;s RAM to deliver it more quickly.<\/p>\n\n\n\n<p>Unlike its market competitor, <strong>the data Redis writes to memory is persistent<\/strong> \u2013 snapshot files and logs restore the information upon startup. This, coupled with its<strong> key-value data retrieval system,<\/strong> means that Redis can be used as a fully-featured <strong>Non-relational database management system<\/strong>.<\/p>\n\n\n\n<p>If you use an SQL database, Redis can be implemented as a caching solution. It offers support for data types, something Memcached lacks, and the range of languages and applications Redis works with is even more impressive.<\/p>\n\n\n\n<p>Overall, several features make Redis more powerful and flexible than Memcached. However, <strong>both solutions can significantly speed up your database<\/strong>, so considering them is a good call.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Disk I\/O Optimization<\/strong><\/h3>\n\n\n\n<p>Pretty much all optimization techniques are ultimately aimed at <strong>reducing the number of read and write operations the disk needs to perform (disk I\/O)<\/strong>.<\/p>\n\n\n\n<p>Thanks to indexes, the <strong>DBMS doesn&#8217;t need to scan an entire table every time a piece of data<\/strong> is required, and with the help of buffering, many of the queries are handled by the server&#8217;s random access memory rather than the much slower disk.<\/p>\n\n\n\n<p>However, if the size of your database grows sufficiently, the volume of the data will be too big, and the searches will be too random for the optimization methods above to be effective. The disk I\/O will rise despite your best efforts.<\/p>\n\n\n\n<p>Unfortunately, dealing with the issue is not as easy as you think. This is partly because diagnosing the problem requires some technical knowledge and partly because the different DBMSs and storage engines have different aspects of their <strong>operations that may affect I\/O.<\/strong><\/p>\n\n\n\n<p>There is no universally supported solution that can lower the number of input and output operations on your disk. However, there are a few techniques that should work in most cases:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Optimizing your tables and indexes<\/strong><\/h3>\n\n\n\n<p>We&#8217;ll start with the most basic method for lowering disk overhead. An active database has to deal with thousands of requests that<strong> insert, modify, and remove information stored in tables<\/strong>. The more of these requests, the more disorganized the data becomes.<\/p>\n\n\n\n<p>Most DBMSs have <strong>built-in mechanisms for automatically rearranging the information<\/strong> to speed up the scans and deliver the correct record more quickly. In the case of <strong>MariaDB and MySQL<\/strong>, for example, it&#8217;s run with the OPTIMIZE TABLE statement. In the case of <strong>PostgreSQL, it&#8217;s VACUUM.<\/strong><\/p>\n\n\n\n<p>It&#8217;s recommended to use these utilities periodically as a part of the database&#8217;s regular maintenance. If you&#8217;re not comfortable working with the command line, tools like phpMyAdmin can offer the functionality through a graphic interface.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Improving your indexes<\/strong><\/h3>\n\n\n\n<p>An index will only boost a database&#8217;s performance if it&#8217;s properly configured. <strong>Extra care won&#8217;t go amiss when indexing a large database<\/strong>.<br><br>If you include a column in your index that is frequently updated, the DBMS will need to perform more write operations on the disk. This is especially true if you have a <strong>database with thousands<\/strong> (or even hundreds of thousands) of rows.<\/p>\n\n\n\n<p>So, as your database grows, make sure you monitor its performance. If it slows down, check the indexes and see if any frequently modified data is being indexed. If it is, it may be a good idea to disable some indexes and see if this will affect the speed.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Partitioning<\/strong><\/h3>\n\n\n\n<p>Partitioning means <strong>splitting large database tables into smaller, easier-to-manage parts<\/strong>. The individual partitions can be hosted on different filesystems, meaning the load is more evenly spread, and more resources are available to guarantee quick loading speeds. Furthermore, <strong>breaking down the enormous table into smaller chunks<\/strong> enables the DBMS to find the required information more quickly and improves the<strong> database&#8217;s overall performance<\/strong>.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Setting the correct checkpoints<\/strong><\/h4>\n\n\n\n<p>A checkpoint tells the DBMS to copy any modified data from the memory and save it for permanent storage on the <strong>server&#8217;s HDD or SSD<\/strong>. Having regular checkpoints ensures that the modifications you make to your database will be saved, and no data will be lost.<\/p>\n\n\n\n<p>However, <strong>every checkpoint inevitably generates activity on the disk<\/strong>. There are ways to moderate this activity.<\/p>\n\n\n\n<p>You can set your checkpoints during less busy periods for your site to<strong> avoid unwanted I\/O spikes<\/strong>. Furthermore, checkpoints have a timeout feature that aborts the write operation if it&#8217;s taking too long. This can relieve some of the pressure on your <strong>server&#8217;s primary storage device during heavy load.<\/strong><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Database Schema Design<\/strong><\/h2>\n\n\n\n<p>The schema defines the crucial elements in your database <em>(tables, rows, columns, indexes, and data entries<\/em>) and the relations between them. If you liken the<strong> database to a machine<\/strong>, the schema will be the blueprint showing the server how the individual components were assembled and how they work.<\/p>\n\n\n\n<p>Needless to say, if the schema is poorly designed, it will be difficult to be understood by both database administrators and software applications. Crucially, it will <strong>make data retrieval trickier and slow down query execution times<\/strong>. So, the design of your schema is essential.&nbsp;<\/p>\n\n\n\n<p><em>But how do you pick the right one?<\/em><\/p>\n\n\n\n<p>First, you start with an <strong>entity relationship diagram (or ERD)<\/strong>. An ERD is a flowchart showing you how elements are related to each other within a system. In our context, the elements are the tables, columns, rows, and entries, and the system is the database.<br>It&#8217;s not technically a part of the database. It&#8217;s <strong>a visual representation used by database administrators to determine how their data will be organized<\/strong>. Below, you can see an ERD of a typical WordPress database.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full mpg-gallery\"><img decoding=\"async\" width=\"1140\" height=\"513\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Database-Schema-Design-1140x513-1.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, Database Schema Design\" class=\"wp-image-69651\" srcset=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Database-Schema-Design-1140x513-1.webp 1140w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Database-Schema-Design-1140x513-1-300x135.webp 300w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/Optimizing-Database-Performance-Database-Schema-Design-1140x513-1-768x346.webp 768w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/figure>\n\n\n\n<p>When you<strong> start drawing your ERD<\/strong>, you&#8217;ll find dozens of models applicable to different types of databases.<\/p>\n\n\n\n<p>For example, <strong>the hierarchical model<\/strong> is a tree-like structure with clear parent-child relations between individual entries. If data entries have a <strong>one-to-many relationship<\/strong>, this is the model for you.<\/p>\n\n\n\n<p>The <strong>network model<\/strong> introduces the notion of sets. It doesn&#8217;t stick to a rigid parent-child structure and is suitable if your data organization is based on <strong>many-to-many relationships<\/strong>. The downside is that implementing and maintaining the network model is trickier.<\/p>\n\n\n\n<p><strong>The choice of a model for your ERD and schema depends on numerous factors, including:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <strong>volume of the data <\/strong>stored in the database.<\/li>\n\n\n\n<li>The <strong>volume of data being queried<\/strong> at any given time.<\/li>\n\n\n\n<li>The <strong>frequency <\/strong>with which data is modified.<\/li>\n\n\n\n<li>The<strong> type of data<\/strong> you need to process and store.<\/li>\n\n\n\n<li>The<strong> performance benchmarks<\/strong> you&#8217;ve set yourself.<\/li>\n\n\n\n<li>The <strong>scalability options <\/strong>you want to have.<\/li>\n<\/ul>\n\n\n\n<p>With the model chosen and the schema design clear, it&#8217;s time to create it. There are multiple ways to set up a new schema. Traditionalists will prefer to write the<strong> required statements in an SQL file<\/strong>, upload it to the server, and run it through the command line.<br>For those looking for a more novice-friendly option, doing it via a tool like <strong>MySQL Workbench<\/strong> will probably be a better choice. After <strong>establishing a connection to the database server<\/strong>, you&#8217;ll see the button in the main toolbar.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full mpg-gallery\"><img decoding=\"async\" width=\"465\" height=\"118\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench2.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, Database Schema Design 2\" class=\"wp-image-69652\" srcset=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench2.webp 465w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench2-300x76.webp 300w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/figure>\n\n\n\n<p>You&#8217;ll be asked to enter the name of the new schema and the default character set and collation. After you click <strong>Apply<\/strong>,<strong> MySQL Workbench will open the SQL editor<\/strong>, where you can enter the required statements.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full mpg-gallery\"><img decoding=\"async\" width=\"784\" height=\"592\" src=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench3.webp\" alt=\"Optimizing Database Performance on Managed VPS Hosting, Database Schema Design 3\" class=\"wp-image-69653\" srcset=\"https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench3.webp 784w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench3-300x227.webp 300w, https:\/\/www.scalahosting.com\/blog\/wp-content\/uploads\/2023\/10\/mysql-workbench3-768x580.webp 768w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/figure>\n\n\n\n<p><strong>While designing your database, you should focus on several things that can result in faster speeds and easier management. Here are some of them:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Easy-to-understand naming conventions &#8211; <\/strong>there are a few rules of thumb that you want to follow here.<strong> When naming tables and columns, use full words instead of acronyms<\/strong> and separate them with underscores. Shorten table and column names whenever possible, and make sure you don&#8217;t use reserved words if you don&#8217;t want to get syntax errors.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Use the correct data type &#8211; <\/strong>your relational database can store all sorts of information. However, if you want it to be as efficient as possible, you must set the <strong>correct data type for every table and every record you save. <\/strong><strong><br><\/strong><br>For example, a column that will store users&#8217; dates of birth will need to use the <strong>DATE type<\/strong>. If it&#8217;s reserved for the exact date and time when they created their accounts, it&#8217;ll have to use the <strong>DATETIME type.<\/strong><\/li>\n<\/ul>\n\n\n\n<p>If you don&#8217;t set the correct data types, your database will use too much disk space, and query execution will be comparatively slow.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Stick to normalization best practices &#8211; <\/strong>normalization is a database design principle that <em>eliminates data redundancy, streamlines your db structure, and slashes significant chunks<\/em> of your query execution times. There are no fewer than seven different normalization forms that can help you with that. They&#8217;re rarely used at the same time, but researching through all of them is worth the effort.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Scaling Database Resources<\/strong><\/h2>\n\n\n\n<p>Scaling means <strong>adding more hardware resources to your hosting infrastructure to speed up your website<\/strong>.&nbsp;<\/p>\n\n\n\n<p>The <strong>additional memory, processing power, and storage<\/strong> can indeed eliminate quite a few bottlenecks, but upgrading your account shouldn&#8217;t be your first port of call. In fact, before you resort to it, you have to ensure you have a fully optimized database with all the<strong> speed-enhancing techniques<\/strong> already implemented. Failing to do that can affect your project in not one but two ways.<\/p>\n\n\n\n<p>First, it&#8217;s the <strong>question of whether you need a hardware upgrade at all<\/strong>. An optimized database can perform well and execute more queries on less powerful hardware than a poorly organized one running on <strong>top-notch machinery<\/strong>. Furthermore, as your database grows bigger and bigger, the lack of any optimization will become more apparent, and even the scaled-up resources won&#8217;t be good enough to guarantee quick loading speeds.<\/p>\n\n\n\n<p>Second, the most effective scaling techniques usually <strong>make the database setup more convoluted and trickier to maintain<\/strong>. You don&#8217;t want to deal with the extra complexity unless you really need to.<\/p>\n\n\n\n<p>So, scaling isn&#8217;t a shortcut that eliminates the need to <strong>improve your database design and operation<\/strong>. However, it&#8217;s often the best way to ensure queries are executed quickly and efficiently.<\/p>\n\n\n\n<p>There are two types of database scaling \u2013 <strong>vertical and horizontal<\/strong>. Let&#8217;s explore the concepts in a bit more detail.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Vertical database scaling &#8211; <\/strong>vertical scaling basically means <strong>upgrading your server&#8217;s hardware<\/strong>. How you&#8217;re going to approach this depends on the type of hosting service you use. If you&#8217;re using a <a href=\"https:\/\/www.scalahosting.com\/blog\/what-does-dedicated-server-hosting-mean\/\"><strong>dedicated server<\/strong><\/a>, you have to either shut down the <strong>machine and swap the physical components <\/strong>or migrate the entire website to a more powerful server.<br><br>No such thing is required if you use a <a href=\"https:\/\/www.scalahosting.com\/managed-cloud-hosting.html\"><strong>cloud VPS<\/strong><\/a>. Thanks to virtualization, your <strong>virtual server is infinitely scalable<\/strong>. You can add more <strong>CPU cores, upgrade the RAM<\/strong>, or expand the storage capacity with a few clicks. The upgrade rarely needs anything more than a quick reboot, so it won&#8217;t affect your uptime stats too badly.<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Horizontal database scaling &#8211; <\/strong>horizontal database scaling is also referred to as <strong>sharding<\/strong> and is reserved mainly for large databases with tables with thousands of rows. It involves <strong>splitting a large table into two or more smaller ones and storing them on separate nodes<\/strong>. These nodes could be individual machines, or they could be<strong> virtual instances of Docker containers.<\/strong><\/li>\n<\/ul>\n\n\n\n<p><em>But how does doing that affect your database performance?<\/em><\/p>\n\n\n\n<p>First, when the <strong>DBMS has to find a particular record<\/strong>, instead of going through an enormous table with thousands upon thousands of rows and columns, it will scan a much smaller subset of information. This reduces the load on the server, which helps with loading speeds<strong>. Disk I\/O also shrinks, so data is delivered more quickly.<\/strong><\/p>\n\n\n\n<p>Data availability has improved, as well. If one of the nodes goes down, users can still access the information stored in the parts of the sharded table that remain unaffected.<\/p>\n\n\n\n<p><em>But how is a table split into two?<\/em><\/p>\n\n\n\n<p>Once again, there are two approaches \u2013 <strong>horizontal and vertical<\/strong>.<\/p>\n\n\n\n<p><strong>To illustrate the difference, we&#8217;ll take a simple table with four rows and five columns. It contains, let&#8217;s say, the details of registered users, and it looks like this:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular green-rows\"><table><thead><tr><th><strong>ID<\/strong><\/th><th><strong>First name<\/strong><\/th><th><strong>Last name<\/strong><\/th><th><strong>Date of birth<\/strong><\/th><th><strong>Place of birth<\/strong><\/th><\/tr><\/thead><tbody><tr><td>001<\/td><td>John<\/td><td>Williams<\/td><td>25.09.1990<\/td><td>New York<\/td><\/tr><tr><td>002<\/td><td>Jane<\/td><td>Smith<\/td><td>30.04.1989<\/td><td>San Diego<\/td><\/tr><tr><td>003<\/td><td>William<\/td><td>Jones<\/td><td>05.06.1994<\/td><td>Dallas<\/td><\/tr><tr><td>004<\/td><td>Sam<\/td><td>Turner<\/td><td>04.03.1992<\/td><td>Boston<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>Splitting the page horizontally literally means drawing a horizontal line through it. We end up with two tables that look like this:<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular green-rows\"><table><thead><tr><th><strong>ID<\/strong><\/th><th><strong>First name<\/strong><\/th><th><strong>Last name<\/strong><\/th><th><strong>Date of birth<\/strong><\/th><th><strong>Place of birth<\/strong><\/th><\/tr><\/thead><tbody><tr><td>001<\/td><td>John<\/td><td>Williams<\/td><td>25.09.1990<\/td><td>New York<\/td><\/tr><tr><td>002<\/td><td>Jane<\/td><td>Smith<\/td><td>30.04.1989<\/td><td>San Diego<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>And this:<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular green-rows\"><table><thead><tr><th><strong>ID<\/strong><\/th><th><strong>First name<\/strong><\/th><th><strong>Last name<\/strong><\/th><th><strong>Date of birth<\/strong><\/th><th><strong>Place of birth<\/strong><\/th><\/tr><\/thead><tbody><tr><td>003<\/td><td>William<\/td><td>Jones<\/td><td>05.06.1994<\/td><td>Dallas<\/td><\/tr><tr><td>004<\/td><td>Sam<\/td><td>Turner<\/td><td>04.03.1992<\/td><td>Boston<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>These two tables can be situated on separate nodes. You have probably guessed the logic behind vertical splitting. We&#8217;re <strong>splitting the columns into two tables<\/strong>, which we&#8217;ll place on separate servers. However, because we need to associate the data from one table with the data in the other, we need to use the <strong>ID column as an identifier<\/strong>.<\/p>\n\n\n\n<p><strong>The result is:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular green-rows\"><table><thead><tr><th><strong>ID<\/strong><\/th><th><strong>First name<\/strong><\/th><th><strong>Last name<\/strong><\/th><\/tr><\/thead><tbody><tr><td>001<\/td><td>John<\/td><td>Williams<\/td><\/tr><tr><td>002<\/td><td>Jane<\/td><td>Smith<\/td><\/tr><tr><td>003<\/td><td>William<\/td><td>Jones<\/td><\/tr><tr><td>004<\/td><td>Sam<\/td><td>Turner<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>And:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table is-style-regular green-rows\"><table><thead><tr><th><strong>ID<\/strong><\/th><th><strong>Date of birth<\/strong><\/th><th><strong>Place of birth<\/strong><\/th><\/tr><\/thead><tbody><tr><td>001<\/td><td>25.09.1990<\/td><td>New York<\/td><\/tr><tr><td>002<\/td><td>30.04.1989<\/td><td>San Diego<\/td><\/tr><tr><td>003<\/td><td>05.06.1994<\/td><td>Dallas<\/td><\/tr><tr><td>004<\/td><td>04.03.1992<\/td><td>Boston<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong><em>The idea is that the DBMS has far fewer records to sca<\/em><\/strong>n, and it can find the needed information much quicker.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Conclusion<\/strong><\/h2>\n\n\n\n<p>The task of <strong>speeding up a database <\/strong>sounds daunting, and, as you can see from our extensive report above, the process can sometimes be pretty complicated. Far too many factors play a role in determining the performance, and fixing them all could be a challenge for a single website owner.<\/p>\n\n\n\n<p>The good news is that if you use a <strong>managed VPS service<\/strong>, you can rely on experts to consider these factors for you. The <strong>DBMS is a part of the stack<\/strong> that you get out of the box, and it&#8217;s usually configured to work with the most popular applications and perform at its best.<\/p>\n\n\n\n<p>However, this doesn&#8217;t mean you shouldn&#8217;t know how it works or what you can do to improve its speed. The in-depth report above should give you all the information you need.<\/p>\n\n\n<div class=\"shortcode-cta-new-wrap row cloudvps\">\r\n    <img decoding=\"async\" class=\"shortcode-logo\" src=\"\/blog\/images\/shortcode-bg-new-logo-cloudvps.svg\" alt=\"shortcode-logo\" title=\"shortcode-logo\"\/>\r\n    <div class=\"shortcode-cta-img col-10 col-sm-7 col-md-5\">\r\n        <img decoding=\"async\" class=\"shortcode-bg\" src=\"\/blog\/images\/shortcode-bg-new-cloudvps.webp\" alt=\"shortcode-bg\" title=\"shortcode-bg\"\/>\r\n    <\/div>\r\n    <div class=\"shortcode-cta-content col-12 col-md-7\">\r\n        <div class=\"content\">\r\n            <div class=\"heading\">Supercharge Your Business with an All-inclusive Fully Managed Cloud<\/div>\r\n            <div class=\"list-wrap\">\r\n                <div class=\"list\">\r\n                    <div><b>Free, Effortless & No-Downtime Migration<\/b><\/div>\r\n                    <div><b>Anytime Unconditional Money-back Guarantee<\/b><\/div>\r\n                    <div><b>Full Scalability & 24\/7 Expert Cloud Support<\/b><\/div>\r\n                <\/div>\r\n            <\/div>\r\n            <div class=\"btn-wrap row\">\r\n                <div class=\"col-auto\">\r\n                    <a class=\"button green\" href=\"https:\/\/www.scalahosting.com\/managed-cloud-hosting.html\">Start Now<\/a>\r\n                <\/div>\r\n                <div class=\"col-auto\">\r\n                    <button class=\"button brown_outer\" title=\"Contact Sales\" onclick=\"chatChangeVisibility(this, 'maximize', {}, 5);\">Chat with our Experts<\/button>\r\n                <\/div>\r\n            <\/div>\r\n        <\/div>\r\n    <\/div>\r\n<\/div>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>FAQ<\/strong><\/h2>\n\n\n\n<p><strong>Q:<\/strong> <strong>How do I check my VPS&#8217;s performance?<\/strong><\/p>\n\n\n\n<p><strong>A:<\/strong> If your server&#8217;s performance is not up to scratch, you will likely notice it while browsing your pages. That said, it&#8217;s best to rely on one of the<strong> many online page speed test tools<\/strong>. Use them regularly, and you can <strong>set a benchmark for how quick your website should be<\/strong>. When it deviates from that benchmark, you&#8217;ll be able to spot the difference before it becomes noticeable to the user.<\/p>\n\n\n\n<p><strong>Q:<\/strong> <strong>How do you optimize your database performance?<\/strong><\/p>\n\n\n\n<p><strong>A:<\/strong> Learning more about how your database works will help you understand what needs to be done to speed it up. It will help you learn<strong> how to implement techniques like indexing and caching <\/strong>and show you how they <strong>help boost performance<\/strong>.<\/p>\n\n\n\n<p><strong>Q:<\/strong> <strong>What is the goal of optimizing database performance?<\/strong><\/p>\n\n\n\n<p><strong>A:<\/strong> A lot of what you see on your screen when browsing a typical website is stored in the database. The quicker this data is delivered, the quicker your page will be. And the<strong> quicker the page, the better the user experience<\/strong> \u2013 something crucial for every online project.<\/p>\n\n\n\n<script type=\"application\/ld+json\">\n    {\n      \"@context\": \"https:\/\/schema.org\",\n      \"@type\": \"FAQPage\",\n      \"mainEntity\": [{\n        \"@type\": \"Question\",\n        \"name\": \"How do I check my VPS's performance?\",\n        \"acceptedAnswer\": {\n          \"@type\": \"Answer\",\n          \"text\": \"If your server's performance is not up to scratch, you will likely notice it while browsing your pages. That said, it's best to rely on one of the many online page speed test tools. Use them regularly, and you can set a benchmark for how quick your website should be. When it deviates from that benchmark, you'll be able to spot the difference before it becomes noticeable to the user.\"\n        }\n      }, {\n        \"@type\": \"Question\",\n        \"name\": \"How do you optimize your database performance?\",\n        \"acceptedAnswer\": {\n          \"@type\": \"Answer\",\n          \"text\": \"Learning more about how your database works will help you understand what needs to be done to speed it up. It will help you learn how to implement techniques like indexing and caching and show you how they help boost performance.\"\n        }\n      },{\n        \"@type\": \"Question\",\n        \"name\": \"What is the goal of optimizing database performance?\",\n        \"acceptedAnswer\": {\n          \"@type\": \"Answer\",\n          \"text\": \"A lot of what you see on your screen when browsing a typical website is stored in the database. The quicker this data is delivered, the quicker your page will be. And the quicker the page, the better the user experience \u2013 something crucial for every online project.\"\n        }\n      }]\n    }\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>How do you improve your website performance? One obvious solution would be to upgrade your hosting account. Your site will &#8230;<\/p>\n","protected":false},"author":113,"featured_media":69656,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_seopress_robots_primary_cat":"none","_seopress_titles_title":"","_seopress_titles_desc":"","_seopress_robots_index":"","footnotes":""},"categories":[61],"tags":[],"class_list":["post-69640","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cloud-hosting"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/posts\/69640","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/users\/113"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/comments?post=69640"}],"version-history":[{"count":3,"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/posts\/69640\/revisions"}],"predecessor-version":[{"id":71123,"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/posts\/69640\/revisions\/71123"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/media\/69656"}],"wp:attachment":[{"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/media?parent=69640"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/categories?post=69640"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scalahosting.com\/blog\/wp-json\/wp\/v2\/tags?post=69640"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}