#ClusterControl

[ayeshavohra-eprnews-posts]

Severalnines' Multi-Cloud, Full-Ops Database Management Provides Extra Protection for Disaster Recovery #BusinessSecurity #BusinessTechnology #CloudComputing #DatabaseManagementSystems #GeneralInterest #OpenSource #ITSecurity #ClusterControl #DatabaseDisaster #DisasterRecovery #MultiCloudDeployments #ePRNews @Severalnines

https://eprnews.com/severalnines-multi-cloud-full-ops-database-management-provides-extra-protection-for-disaster-recovery-467843/

[anotherlauncher]

Performance Scalability Post:

Symptom:

Slowness observed. "node select queries are taking up majority of time resulting in slowness of the application"

in PDOStatement::execute called at /core/lib/Drupal/Core/Database/StatementWrapper.php (145)...Core\Database\StatementWrapper::execute called at /core/lib/Drupal/Core/Database/Connection.php (937)...Database\Connection::query called at /core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php (183)...

These queries are taking longer than 5 seconds.

SELECT "base_table"."vid" AS "vid", "base_table"."nid" AS "nid" + FROM + "node" "base_table" + INNER JOIN "node" "node" ON "node"."nid" = "base_table"."nid" + INNER JOIN "node_field_data" "node_field_data" ON "node_field_data"."nid" = "base_table"."nid" + WHERE ((LOWER("node"."uuid") IN (LOWER('$1')))) AND ("node_field_data"."default_langcode" IN ('$2'))

Analysis:

Repeat the issue locally:

Edit postgresql.conf

log_statement = 'none' log_min_duration_statement = 5000

I also set `log_statement = 'all'` initally but that creates a huge file which you should be careful of not to overload your system. Though it can be helpful to see everything.

Otherwise setting `log_min_duration_statement = 5000` means only log queries that take over 5 seconds.

Then there was some time figuring out how to replicate the issue. But eventually I found it was related to saving an article based on some custom event we have.

The above codeblock points to the execute() function in Driver/pgsql

the execute() function uses the compile() function which uses translateCondition()

in translateCondition() we are seeing the `['where'] = 'LOWER('` . section which we can see in the slow logs. We saw this same function with similar behavior existing in Drupal 8. But for some reason it was not a performance issue then. Now with Drupal 9 the performance impact is now more visible.

public static function translateCondition(&$condition, SelectInterface  $sql_query, $case_sensitive) { if (is_array($condition['value']) && $case_sensitive === FALSE) { $condition['where'] = 'LOWER(' . $sql_query->escapeField($condition['real_field']) . ') ' . $condition['operator'] . ' ('; $condition['where_args'] = []; // Only use the array values in case an associative array is passed as an // argument following similar pattern in // \Drupal\Core\Database\Connection::expandArguments(). $where_prefix = str_replace('.', '_', $condition['real_field']); foreach ($condition['value'] as $key => $value) { $where_id = $where_prefix . $key; $condition['where'] .= 'LOWER(:' . $where_id . '),'; $condition['where_args'][':' . $where_id] = $value; } $condition['where'] = trim($condition['where'], ','); $condition['where'] .= ')'; } parent::translateCondition($condition, $sql_query, $case_sensitive); } }

In general we likely would not want to change the Core execute() function or the translateCondition() function since this is good behavior most of the time.

Though it is odd that "PostgreSQL is a case-sensitive database by default" and yet the default ends up fitting into the condition `$case_sensitive === FALSE`. Potentially core should be updated. But this should likely be a discussion with the maintainers.

['where'] = 'LOWER(' . is part of:

translateCondition() function which is called in core/lib/Drupal/Core/Entity/Query/Sql/pgsql/Condition.php

New Relic:

In New Relic we can see `Postgres node select` was consuming 84% of the db operations.

Cluster Control:

In ClusterControl we can see the top slowest Query was the `SELECT "base_table"."vid" AS` as seen above and it had a near 3 million count in just a few days.

Fix:

Alter the query that uses LOWER for the condition that matches 'LOWER("node"."uuid")

/** * Implements hook_query_TAG_alter(). * * @param \Drupal\Core\Database\Query\AlterableInterface $query */ function core_query_entity_query_node_alter(Drupal\Core\Database\Query\AlterableInterface $query) { $conditions = &$query->conditions(); if (isset($conditions[0]['field']) && $conditions[0]['field'] == 'LOWER("node"."uuid") IN (LOWER(:node_uuid0))') { $conditions[0]['field'] = '"node"."uuid" = :node_uuid0'; } }

Now after the fix, the LOWER still hits all other queries it normally would hit, except for the condition of node uuid

We now see it as :

SELECT "base_table"."vid" AS "vid", "base_table"."nid" AS "nid" + FROM + "node" "base_table" + INNER JOIN "node" "node" ON "node"."nid" = "base_table"."nid" + INNER JOIN "node_field_data" "node_field_data" ON "node_field_data"."nid" = "base_table"."nid" + WHERE ((("node"."uuid" = '$1'))) AND ("node_field_data"."default_langcode" IN ('$2'))

[globalmediacampaign]

Understanding Indexes in MySQL: Part Three

This blog post should act as the third part in the series of blogs about indexes in MySQL. In the second part in the blog post series about MySQL indexes we have covered indexes and storage engines, we have touched upon some PRIMARY KEY considerations, how to match a column prefix, we have covered some FULLTEXT index considerations, we have told you how should you go about using B-Tree indexes with wildcards and how to use ClusterControl to monitor the performance of your queries and, subsequently, indexes. In this blog post we will go into some more details about indexes in MySQL: we will cover hash indexes, index cardinality, index selectivity, we will tell you interesting details about covering indexes and we will also go through some indexing strategies. And, of course, we will touch upon ClusterControl. Let’s begin, shall we? Hash Indexes in MySQL MySQL DBAs and developers dealing with MySQL also have another trick up their sleeve as far as MySQL is concerned - hash indexes are also an option. Hash indexes are frequently used in the MEMORY engine of MySQL - as with pretty much everything in MySQL, those kinds of indexes have their own upsides and downsides. The main downside of these kinds of indexes is that they are used only for equality comparisons that use the = or <=> operators meaning that they’re not really useful if you want to search for a range of values, but the main upside is that lookups are very fast. A couple more downsides include the fact that developers cannot use any leftmost prefix of the key to find rows (if you want to do that, make use of B-Tree indexes instead), the fact that MySQL cannot approximately determine how many rows there are between two values - if hash indexes are in use, the optimizer cannot use a hash index to speed up ORDER BY operations either. Bear in mind that hash indexes are not the only thing the MEMORY engine supports - MEMORY engines can have B-Tree indexes too. Index Cardinality in MySQL As far as MySQL indexes are concerned, you might also heard another term going around - this term is called index cardinality. In very simple terms, index cardinality refers to the uniqueness of values stored in a column that uses an index. To view the index cardinality of a specific index, you can simply go to the Structure tab of phpMyAdmin and observe the information there or you can also execute a SHOW INDEXES query: mysql> SHOW INDEXES FROM demo_table; +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | demo_table | 1 | demo | 1 | demo | A | 494573 | NULL | NULL | | BTREE | | | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) The SHOW INDEXES query output that can be seen above as you can see has a lot of fields, one of which depicts the index cardinality: this field returns an estimated number of unique values in the index - the higher the cardinality, the greater the chance that the query optimizer uses the index for lookups. With that being said, index cardinality also has a brother - his name is index selectivity. Index Selectivity in MySQL An index selectivity is the number of distinct values in relation to the number of records in the table. In simple terms, index selectivity defines how tightly a database index helps MySQL narrow the search for values. An ideal index selectivity is the value of 1. An index selectivity is calculated by dividing the distinct values in a table by the total number of records, for example, if you have 1,000,000 records in your table, but only 100,000 of them are distinct values, your index selectivity would be 0.1. If you have 10,000 records in your table and 8,500 of them are distinct values, your index selectivity would be 0.85. That’s much better. You get the point. The higher your index selectivity is, the better. Covering Indexes in MySQL A covering index is a special kind of index in InnoDB. When a covering index is in use, all the required fields for a query are included, or “covered”, by the index meaning that you can also reap the benefits of reading only the index instead of the data. If nothing else helps, a covering index could be your ticket to improved performance. Some of the benefits of using covering indexes include: One of the main scenarios where a covering index might be of use include serving queries without additional I/O reads on big tables. MySQL can also access less data due to the fact that index entries are smaller than the size of rows. Most storage engines cache indexes better than data. Creating covering indexes on a table is pretty simple - simply cover the fields accessed by SELECT, WHERE and GROUP BY clauses: ALTER TABLE demo_table ADD INDEX index_name(column_1, column_2, column_3); Keep in mind that when dealing with covering indexes, it is very important to choose the correct order of columns in the index. For your covering indexes to be effective, put the columns that you use with WHERE clauses first, ORDER BY and GROUP BY next and the columns used with the SELECT clause last. Indexing Strategies in MySQL Following the advice covered in these three parts of blog posts about indexes in MySQL can provide you with a really good foundation, but there are also a couple of indexing strategies you might want to use if you want to really tap into the power of indexes in your MySQL architecture. For your indexes to adhere to MySQL best practices, consider: Isolating the column that you use the index on - in general, MySQL does not use indexes if the columns they are used on are not isolated. For example, such a query would not use an index because it’s not isolated: SELECT demo_column FROM demo_table WHERE demo_id + 1 = 10; Such a query however, would:   SELECT demo_column FROM demo_table WHERE demo_id = 10;   Do not use indexes on the columns that you index. For example, using a query like so would not do much good so it’s better to avoid such queries if you can:   SELECT demo_column FROM demo_table WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(column_date) <= 10;   If you use LIKE queries together with indexed columns, avoid putting the wildcard at the beginning of the search query because that way MySQL will not use an index either. That is instead of writing queries like this: SELECT * FROM demo_table WHERE demo_column LIKE ‘%search query%’; Consider writing them like this: SELECT * FROM demo_table WHERE demo_column LIKE ‘search_query%’; The second query is better because MySQL knows what the column begins with and can use indexes more effectively. As with everything though, the EXPLAIN statement can be of great help if you want to make sure your indexes are actually used by MySQL. Using ClusterControl to Keep Your Queries Performant If you want to improve your MySQL performance, the advice above should set you on the right path. If you feel that you need something more though, consider ClusterControl for MySQL. One of the things that ClusterControl can help you with include performance management - as already noted in previous blog posts, ClusterControl can also help you with keeping your queries performing at the very best of their ability all the time - that’s because ClusterControl also includes a query monitor that lets you monitor the performance of your queries, see slow, long-running queries and also query outliers alerting you of the possible bottlenecks in your database performance before you might be able to notice them yourself: You can even filter your queries allowing you to make an assumption if an index was used by an individual query or not: ClusterControl can be a great tool to improve your database performance while taking the maintenance hassle off your hands. To learn more about what ClusterControl can do to improve the performance of your MySQL instances, consider having a look at the ClusterControl for MySQL page. Summary As you can probably tell by now, indexes in MySQL are a very complex beast. To choose the best index for your MySQL instance, know what indexes are and what they do, know the types of MySQL indexes, know their benefits and drawbacks, educate yourself on how MySQL indexes interact with storage engines, also take a look at ClusterControl for MySQL if you feel that automating certain tasks related to indexes in MySQL might make your day easier. Tags: MySQLIndexesclustercontrolperformance https://severalnines.com/database-blog/understanding-indexes-mysql-part-three

[amuawia]

Post has been published on http://muawia.com/?post_type=wprss_feed_item&p=274074

Deploy a Hybrid Cloud MySQL Database using ClusterControl

[dreamassignmenthelp]

Get Best Computer Science Coursework Help from Experts

The computer programming assignment help experts can solve quiz on e-business and management information system. They can solve difficult problem on data mining and data management. They can write non-plagiarized dissertation on visual foundation studio and web studio.

The computer science coursework help is the most valuable service in the academic world. There are many students facing problem in computational function and computer design. They can solve quizzes and score good grade in conversion and data transmission. Our experts have theoretical knowledge of algorithms. The assignment help experts can write research paper on software and hardware.

Are you Looking for Computer Science Tutor Online?

The computer science tutor online have sound knowledge of human-computer interaction. They are expert in web design and can write good quality essays. The writers have the capacity of simulating and modeling. They know how to solve algorithm problem. The homework help experts have written dissertation on cryptography and security. They can write good quality essay papers on regulating graphics. Computing involves social and ethical issues. We know the application of programming in game design.

Do you need help on Computer Science Assignment Topics?

The experts can write on different computer science assignment topics. They are UML, information systems, UML activity diagram, DBMS, advanced DBMS, distributed database, operating system, data structure, computer network, database design and MySQL database. We can provide non-plagiarized assignment writing.   The assignment help experts have sound knowledge of deep learning, algorithm, computing fundamentals, graphical models, etc. They can write original essays on software development and CISCO networking basics.

Improve your grade with Computer Science Coursework Help

Our writers often get request from the students of USA ‘do my computer science assignment’. They have solved many difficult problems on the following:

Cryptography

Our experts can improve information security using cryptography. They have sound knowledge of authentication, data integrity and data confidentiality. The assignment help experts have applied cryptography in military communications, computer password and digital currencies.

      2. DBMS

The assignment help experts have sound knowledge of management and creation of database. The data is managed and updated using DBMS. The programmers can retrieve data as they have sound knowledge of database management system.

     3. Computer Performance Analysis

Our writers can write high quality research papers on computer performance analysis. They have good idea of response time, throughput and computing resource. The experts have knowledge about the time of data transmission and bandwidth. They can write dissertation on performance equation.

    4. Software Development

The computer science homework help experts have solved critical problems on maintenance, deployment, debugging, testing, construction, engineering, design and requirements. They are quite aware of the software frameworks e.g., XP, UP, TSP, SEMAT, Scrum, PSP, MSF, DAD. DevOps, etc. The writers can solve quizzes on user experience, project management, software quality assurance, documentation and configuration management.

    5.Computer design

The computer programming assignment help can deal with computer design issues. They have good idea of the methodologies like agile development, waterfall development, spiral development, and others. The experts can work on projects of process meta-models, which include method engineering, soft systems methodology, ISO/IEC 12207 and others. We have dealt with dissertation on different aspects of computer design and helped the student score the best grade.

Do you need guidance on MySQL Database?

The expert on MySQL database can solve deal with projects on query caching, support of SSL, cross-platform assistance, information schema, embedded database library, MySQL cluster, data definition language, etc. If you face any problem regarding graphical user interface, MySQL workbench, ClusterControl, Adminer, DBeaver, Database Workbench, etc. We have sound knowledge of phpMyAdmin, webmin and command line interface.

[reportwire]

Improving the Performance and Scalability of eLearning Platform Databases with ClusterControl

Improving the Performance and Scalability of eLearning Platform Databases with ClusterControl

Institutions around the world are investing in their eLearning platforms to meet global demand. ClusterControl helps organizations with easy deployment and management of highly available database clusters. Press Release – updated: Jan 18, 2021 STOCKHOLM, January 18, 2021 (Newswire.com) – Severalnines is a pioneer in providing automation and management software for database clusters. Its…

[globalmediacampaign]

Understanding Indexes in MySQL: Part One

Indexes in MySQL are a very complex beast. We have covered MySQL indexes in the past, but we have never taken a deeper dive into them - we will do that in these series of blog posts. This blog post should act as a very general guide to indexes while the other parts of these series will dive a little bit deeper into these subjects.  What are Indexes? In general, as already noted in a previous blog post about indexes, an index is an alphabetical list of records with references to the pages on which they are mentioned. In MySQL, an index is a data structure that is most commonly used to quickly find rows. You might also hear the term “keys” - it refers to indexes too. What do Indexes Do? In MySQL indexes are used to quickly find rows with specific column values and to prevent reading through the entire table to find any rows relevant to the query. Indexes are mostly used when the data stored in a database system (for example, MySQL) gets bigger because the larger the table, the bigger the probability that you might benefit from indexes. MySQL Index Types As far as MySQL is concerned, you might have heard about it having multiple types of indexes: A B-Tree INDEX - such an index is frequently used to speed up SELECT queries matching a WHERE clause. Such an index can be used on fields where values do not need to be unique, it also accepts NULL values. A FULLTEXT INDEX - such an index is used to use full text search capabilities. This type of index finds keywords in the text instead of directly comparing values to the values in the index. A UNIQUE INDEX is frequently used to remove duplicate values from a table. Enforces the uniqueness of row values. A PRIMARY KEY is also an index - it’s frequently used together with fields having an AUTO_INCREMENT attribute. This type of index does not accept NULL values and once set, the values in the column which has a PRIMARY KEY cannot be changed. A DESCENDING INDEX is an index that stores rows in a descending order. This type of index was introduced in MySQL 8.0 - MySQL will use this type of an index when a descending order is requested by the query. Choosing Optimal Data Types for Indexes in MySQL As far as indexes are concerned, there’s also the need to keep in mind that MySQL supports a wide variety of data types and some data types cannot be used together with certain kinds of indexes (for example, FULLTEXT indexes can only be used on text-based (CHAR, VARCHAR or TEXT) columns - they cannot be used on any other data types) so before actually choosing the indexes for your database design, decide on the data type you are going to use on the column in question (decide what kind of data class you are going to store: are you going to store numbers? String values? Both numbers and string values? etc.), then decide on the range of the values you are going to store (choose the one that you don’t think you will exceed because increasing the data type range can be a time-consuming task later on - we recommend you opt to use a simple data type), and if you do not intend to use NULL values in your columns, specify your fields as NOT NULL whenever you can - when a nullable column is indexed, it requires an extra byte per entry. Choosing Optimal Character Sets and Collations for Indexes in MySQL Aside from data types, also keep in mind that each character in MySQL takes up space. For example, UTF-8 characters may take anywhere between 1 and 4 bytes each, so you might want to avoid indexing, for example, 255 characters and only use, say, 50 or 100 characters for a certain column. The Benefits and Drawbacks of Using Indexes in MySQL The main benefit of using indexes in MySQL is the increased performance of search queries matching a WHERE clause - indexes speed up SELECT queries matching a WHERE clause because MySQL doesn’t read through the entire table to find rows relevant to the query. However, bear in mind that indexes have their own drawbacks. The main ones are as follows: Indexes consume disk space. Indexes degrade the performance of INSERT, UPDATE and DELETE queries - when data is updated, the index needs to be updated together with it. MySQL does not protect you from using multiple types of indexes at the same time. In other words, you can use a PRIMARY KEY, an INDEX and a UNIQUE INDEX on the same column - MySQL does not protect you from doing such a mistake. If you suspect that some of your queries are becoming slower, consider taking a look into the Query Monitor tab of ClusterControl - by enabling the query monitor you can see when a certain query was last seen and its maximum and average execution time which can help you to choose the best indexes for your table. How to Choose the Best Index to Use? To choose the best index to use, you can use MySQL’s built-in mechanisms. For example, you can use the query explainer - the EXPLAIN query. It will explain what table is used, if it has partitions or not, what indexes are possible to use and what key (index) is used. It will also return the index length and the amount of rows your query returns: mysql> EXPLAIN SELECT * FROM demo_table WHERE demo_field = ‘demo’G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: demo_table partitions: NULL type: ref possible_keys: demo_field key: demo_field key_len: 1022 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) In this case, keep in mind that indexes are frequently used to help MySQL efficiently retrieve data when data sets are larger than usual. If your table is small, you might not need to use indexes, but if you see that your tables are getting bigger and bigger, chances are you might benefit from an index. In order to choose the best index to use for your specific scenario though, bear in mind that indexes can be a leading cause of performance problems too. Keep in mind that whether MySQL will effectively use the indexes or not depends on a couple of factors including the design of your queries, the indexes in use, the types of indexes in use, also your database load at the time the query is executed and other things. Here’s a couple of things to consider when using indexes in MySQL: How much data do you have? Perhaps some of it is redundant? What queries do you use? Would your queries use LIKE clauses? What about ordering? What kind of an index would you need to use to improve the performance of your queries? Would your indexes be large or small? Would you need to use an index on a prefix of the column to make its size smaller? It is worth noting that you should probably avoid using multiple types of indexes (e.g a B-Tree index, a UNIQUE INDEX and a PRIMARY KEY) on the same column too. Improving Query Performance with Indexes To improve query performance with indexes, you need to take a look at your queries - the EXPLAIN statement can help with that. In general, here’s a couple of things you should consider if you want your indexes to improve the performance of your queries: Only ask the database for what you need. In most cases, using SELECT column will be faster than using SELECT * (that is the case without using indexes too) A B-tree index might be a fit if you search for exact values (e.g SELECT * FROM demo_table WHERE some_field = ‘x’) or if you want to search for values using wildcards (e.g SELECT * FROM demo_table WHERE some_field LIKE ‘demo%’ - in this case, bear in mind that using LIKE queries with anything in the beginning of it might do more harm than good - avoid using LIKE queries with a percentage sign in front of the text you’re searching - that way MySQL might not use an index because it doesn’t know what does the row value begin with) - though keep in mind that a B-tree index can also be used for column comparisons in expressions that use the equal (=), more than (>), more than or equal to (>=), less than (<), less than or equal to (<=) or BETWEEN operators. A FULLTEXT index might be a fit if you find yourself using full-text (MATCH ... AGAINST()) search queries or if your database is designed in such a way that only uses text-based columns - FULLTEXT indexes can use TEXT, CHAR or VARCHAR columns, they cannot be used on any other types of columns. A covering index might be of use if you want to run queries without additional I/O reads on big tables. To create a covering index, cover the WHERE, GROUP BY and SELECT clauses used by the query. We will further look into the types of indexes in the upcoming parts of this blog series, but in general, if you use queries like SELECT * FROM demo_table WHERE some_field = ‘x’ a B-tree INDEX might be a fit, if you use MATCH() AGAINST() queries you should probably look into a FULLTEXT index, if your table has very long row values, you should probably look into indexing a part of the column. How Many Indexes Should You Have? If you ever used indexes to improve the performance of your SELECT queries, you have probably asked yourself a question: how many indexes should you actually have? In order to understand this, you need to keep the following things in mind: Indexes are usually the most effective with big amounts of data. MySQL uses only one index per each SELECT statement in a query (subqueries are seen as separate statements) - use the EXPLAIN query to find out which indexes are the most effective for the queries you use. Indexes should make all of your SELECT statements fast enough without compromising too much on disk space - “fast enough”, however, is relative so you would need to experiment. Indexes and Storage Engines When dealing with indexes in MySQL, also keep in mind that there might be some kinds of limitations if you use various engines (for example if you use MyISAM as opposed to InnoDB). We will go into more detail in a separate blog, but here are some ideas: The maximum number of indexes per MyISAM and InnoDB tables are 64, the maximum number of columns per index in both storage engines is 16. The maximum key length for InnoDB is 3500 bytes - the maximum key length for MyISAM is 1000 bytes. The fulltext indexes have limitations in certain storage engines - for example, the InnoDB fulltext indexes have 36 stopwords, MyISAM stopword list is a little bit bigger with 143 stopwords. InnoDB derives these stopwords from the innodb_ft_server_stopword_table variable while MyISAM derives these stopwords from the storage/myisam/ft_static.c file - all words that are found in the file will be treated as stopwords. MyISAM was the only storage engine with the support for full-text search options until MySQL 5.6 (MySQL 5.6.4 to be exact) came around meaning that InnoDB supports full-text indexes since MySQL 5.6.4. When a FULLTEXT index is in use, it finds keywords in the text instead of comparing values directly to the values in the index. Indexes play a very important role for InnoDB - InnoDB locks rows when it accesses them, so a reduced number of rows InnoDB accesses can reduce locks. MySQL allows you to use duplicate indexes on the same column. Certain storage engines have certain default types of indexes (e.g for the MEMORY storage engine the default index type is hash) Summary In this part about indexes in MySQL, we have gone through some general things related to indexes in this relational database management system. In the upcoming blog posts we will go through some more in-depth scenarios of using indexes in MySQL including the usage of indexes in certain storage engines etc. - we will also explain how ClusterControl can be used to achieve your performance goals in MySQL. Tags: MySQLIndexesclustercontrolperformance https://severalnines.com/database-blog/understanding-indexes-mysql-part-one

[globalmediacampaign]

Automate Database Schema Object Check

Monitoring your database schema changes in MySQL/MariaDB provides a huge help as it saves time analyzing your database growth, table definition changes, data size, index size, or row size. For MySQL/MariaDB, running a query referencing information_schema together with performance_schema gives you collective results for further analysis. The sys schema provides you views that serve as collective metrics that are very useful for tracking database changes or activity. If you have many database servers, it would be tedious to run a query all the time. You also have to digest that result into a more readable and easier to understand. In this blog, we'll create an automation that would be helpful as your utility tool to have for your existing database to be monitored and collect metrics regarding database changes or schema change operations.  Creating Automation for Database Schema Object Check In this exercise, we will monitor the following metrics: No primary key tables Duplicate indexes Generate a graph for total number of rows in our database schemas Generate a graph for total size of our database schemas This exercise will give you a heads up and can be modified to gather more advanced metrics from your MySQL/MariaDB database.   Using Puppet for our IaC and Automation This exercise shall use Puppet to provide automation and generate the expected results based on the metrics we want to monitor. We'll not cover the installation and setup for the Puppet, including server and client, so I expect you to know how to use Puppet. You might want to visit our old blog Automated Deployment of MySQL Galera Cluster to Amazon AWS with Puppet, which covers the setup and installation of Puppet. We'll use the latest version of Puppet in this exercise but since our code consists of basic syntax, it would run for older versions of Puppet. Preferred MySQL Database Server In this exercise, we'll use Percona Server 8.0.22-13 since I prefer Percona Server mostly for testing and some minor deployments either business or personal use. Graphing Tool  There are tons of options to use especially using the Linux environment. In this blog, I'll use the easiest that I found and an opensource tool https://quickchart.io/. Let's Play with Puppet The assumption i have made here is that you have setup master server with registered client which is ready to communicate with the master server to receive automatic deployments. Before we proceed, here's the my server information: Master server: 192.168.40.200 Client/Agent Server: 192.168.40.160 In this blog, our client/agent server is where our database server is running.In a real-world scenario, it doesn't have to be especially for monitoring. As long as it's able to communicate into the target node securely, then that is a perfect setup as well. Setup the Module and the Code Go to the master server and in the path /etc/puppetlabs/code/environments/production/module, let's create the required directories for this exercise: mkdir schema_change_mon/{files,manifests}   Create the files that we need touch schema_change_mon/files/graphing_gen.sh touch schema_change_mon/manifests/init.pp Fill-up the init.pp script with the following content: class schema_change_mon ( $db_provider = "mysql", $db_user = "root", $db_pwd = "R00tP@55", $db_schema = [] ) { $dbs = ['pauldb', 'sbtest'] service { $db_provider : ensure => running, enable => true, hasrestart => true, hasstatus => true } exec { "mysql-without-primary-key" : require => Service['mysql'], command => "/usr/bin/sudo MYSQL_PWD="${db_pwd}" /usr/bin/mysql -u${db_user} -Nse "select concat(tables.table_schema,'.',tables.table_name,', ', tables.engine) from information_schema.tables left join ( select table_schema , table_name from information_schema.statistics group by table_schema , table_name , index_name having sum( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks on tables.table_schema = puks.table_schema and tables.table_name = puks.table_name where puks.table_name is null and tables.table_type = 'BASE TABLE' and tables.table_schema not in ('performance_schema', 'information_schema', 'mysql');" >> /opt/schema_change_mon/assets/no-pk.log" } $dbs.each |String $db| { exec { "mysql-duplicate-index-$db" : require => Service['mysql'], command => "/usr/bin/sudo MYSQL_PWD="${db_pwd}" /usr/bin/mysql -u${db_user} -Nse "SELECT concat(t.table_schema,'.', t.table_name, '.', t.index_name, '(', t.idx_cols,')') FROM ( SELECT table_schema, table_name, index_name, Group_concat(column_name) idx_cols FROM ( SELECT table_schema, table_name, index_name, column_name FROM statistics WHERE table_schema='${db}' ORDER BY index_name, seq_in_index) t GROUP BY table_name, index_name) t JOIN ( SELECT table_schema, table_name, index_name, Group_concat(column_name) idx_cols FROM ( SELECT table_schema, table_name, index_name, column_name FROM statistics WHERE table_schema='pauldb' ORDER BY index_name, seq_in_index) t GROUP BY table_name, index_name) u where t.table_schema = u.table_schema AND t.table_name = u.table_name AND t.index_name<>u.index_name AND locate(t.idx_cols,u.idx_cols);" information_schema >> /opt/schema_change_mon/assets/dupe-indexes.log" } } $genscript = "/tmp/graphing_gen.sh" file { "${genscript}" : ensure => present, owner => root, group => root, mode => '0655', source => 'puppet:///modules/schema_change_mon/graphing_gen.sh' } exec { "generate-graph-total-rows" : require => [Service['mysql'],File["${genscript}"]], path => [ '/bin/', '/sbin/' , '/usr/bin/', '/usr/sbin/' ], provider => "shell", logoutput => true, command => "/tmp/graphing_gen.sh total_rows" } exec { "generate-graph-total-len" : require => [Service['mysql'],File["${genscript}"]], path => [ '/bin/', '/sbin/' , '/usr/bin/', '/usr/sbin/' ], provider => "shell", logoutput => true, command => "/tmp/graphing_gen.sh total_len" } }   Fill up the graphing_gen.sh file. This script will run on the target node and generate graphs for the total number of rows in our database and also total size of our database. For this script, let's make it simpler ,and allow only MyISAM or InnoDB type of databases. #!/bin/bash graph_ident="${1:-total_rows}" unset json myisam innodb nmyisam ninnodb; json='' myisam='' innodb='' nmyisam='' ninnodb='' url=''; json=$(MYSQL_PWD="R00tP@55" mysql -uroot -Nse "select json_object('dbschema', concat(table_schema,' - ', engine), 'total_rows', sum(table_rows), 'total_len', sum(data_length+data_length), 'fragment', sum(data_free)) from information_schema.tables where table_schema not in ('performance_schema', 'sys', 'mysql', 'information_schema') and engine in ('myisam','innodb') group by table_schema, engine;" | jq . | sed ':a;N;$!ba;s/n//g' | sed 's|}{|},{|g' | sed 's/^/[/g'| sed 's/$/]/g' | jq '.' ); innodb=""; myisam=""; for r in $(echo $json | jq 'keys | .[]'); do if [[ $(echo $json| jq .[$r].'dbschema') == *"MyISAM"* ]]; then nmyisam=$(echo $nmyisam || echo '')$(echo $json| jq .[$r]."${graph_ident}")','; myisam=$(echo $myisam || echo '')$(echo $json| jq .[$r].'dbschema')','; else ninnodb=$(echo $ninnodb || echo '')$(echo $json| jq .[$r]."${graph_ident}")','; innodb=$(echo $innodb || echo '')$(echo $json| jq .[$r].'dbschema')','; fi; done; myisam=$(echo $myisam|sed 's/,$//g'); nmyisam=$(echo $nmyisam|sed 's/,$//g'); innodb=$(echo $innodb|sed 's/,$//g');ninnodb=$(echo $ninnodb|sed 's/,$//g'); echo $myisam "|" $nmyisam; echo $innodb "|" $ninnodb; url=$(echo "{type:'bar',data:{labels:['MyISAM','InnoDB'],datasets:[{label:[$myisam],data:[$nmyisam]},{label:[$innodb],data:[$ninnodb]}]},options:{title:{display:true,text:'Database Schema Total Rows Graph',fontSize:20,}}}"); curl -L -o /vagrant/schema_change_mon/assets/db-${graph_ident}.png -g https://quickchart.io/chart?c=$(python -c "import urllib,os,sys; print urllib.quote(os.environ['url'])") Lastly, go to the module path directory or /etc/puppetlabs/code/environments/production in my setup. Let's create the file  manifests/schema_change_mon.pp. touch manifests/schema_change_mon.pp Then fill the file manifests/schema_change_mon.pp with the following contents, node 'pupnode16.puppet.local' { # Applies only to mentioned node. If nothing mentioned, applies to all. class { 'schema_change_mon': } }   If you're done, you should have the following tree structure just like mine, root@pupmas:/etc/puppetlabs/code/environments/production/modules# tree schema_change_mon schema_change_mon ├── files │ └── graphing_gen.sh └── manifests └── init.pp What does our module do? Our module which is called schema_change_mon does collect the following, exec { "mysql-without-primary-key" : ... Which executes a mysql command and runs a query to retrieve tables without primary keys. Then, $dbs.each |String $db| { exec { "mysql-duplicate-index-$db" : which does collect duplicate indexes that exist in the database tables. Next, the lines generate graphs based on the metrics collected. These are the following lines, exec { "generate-graph-total-rows" : ... exec { "generate-graph-total-len" : … Once the query successfully runs, it generates the graph, which depends on the API provided by https://quickchart.io/. Here are the following results of the graph: Whereas the file logs simply contain strings with its table names, index names. See the result below, root@pupnode16:~# tail -n+1 /opt/schema_change_mon/assets/*.log ==> /opt/schema_change_mon/assets/dupe-indexes.log <== pauldb.c.my_index(n,i) pauldb.c.my_index2(n,i) pauldb.d.a_b(a,b) pauldb.d.a_b2(a,b) pauldb.d.a_b3(a) pauldb.d.a_b3(a) pauldb.t3.b(b) pauldb.c.my_index(n,i) pauldb.c.my_index2(n,i) pauldb.d.a_b(a,b) pauldb.d.a_b2(a,b) pauldb.d.a_b3(a) pauldb.d.a_b3(a) pauldb.t3.b(b) ==> /opt/schema_change_mon/assets/no-pk.log <== pauldb.b, MyISAM pauldb.c, InnoDB pauldb.t2, InnoDB pauldb.d, InnoDB pauldb.b, MyISAM pauldb.c, InnoDB pauldb.t2, InnoDB pauldb.d, InnoDB Why Not Use ClusterControl? As our exercise showcases the automation and getting the database schema statistics such as changes or operations, ClusterControl provides this as well. There are other features as well aside from this and you don't need to reinvent the wheel. ClusterControl can provide the transaction logs such as deadlocks as shown above, or long running queries as shown below:   ClusterControl also shows the DB growth as shown below,   ClusterControl also gives additional information such as number of rows, disk size, index size, and total size.   The schema analyzer under Performance tab -> Schema Analyzer is very helpful. It provides tables without primary keys, MyISAM tables, and duplicate indexes, It also provides alarms in case there are detected duplicate indexes or tables without primary keys such as below,   You can check out more information about ClusterControl and its other features on our Product's page. Conclusion Providing automation for monitoring your database changes or any schema statistics such as writes, duplicate indexes, operation updates such as DDL changes, and many database activities is very beneficial to the DBAs. It helps to quickly identify the weak links and problematic queries that would give you an overview of a possible cause of bad queries that would lock up your database or stale your database. Tags: database automationmonitoringschema checkPuppet https://severalnines.com/database-blog/automate-database-schema-object-check

[globalmediacampaign]

MySQL Database Deployment Automation

Automation is in vogue these days. Technology evolves quickly, and more people are contributing to existing available third party automation software platforms. As the season of fast-paced deployments, automation is now necessary and considered as de facto; you cannot get rid of automating things deemed necessary. These necessities are important to get things done quickly and eliminate redundant tasks. Once this is present, organisations and companies can focus on the logical business things that make it more interesting for growth. This blog covers a short overview tools and consideration points for database automation followed by a a topic on how to use ClusterControl for database automation.  Technologies for Database Deployment Automation Sophisticated tools for Infrastructure as Code (IaC) such as Puppet, Chef, Ansible, SaltStack, and Terraform are usually the mainstream technologies to choose from. These tools help DBAs with tasks that are easily replicated, like deploying a MySQL. Automating your MySQL deployment saves you a great deal of time, especially if you have extensive platforms to support and follow the software engineering cycles steps to deploy for your QA, staging, or development environments. Using automation makes these things quicker and easier to manage when it's needed. Choosing Your Automation Software In this blog, let's try to use Ansible. As stated earlier, using third-party software makes it easier and faster to provide automation especially for deploying your MySQL cluster. Now we'll use existing modules particularly Ansible roles that are available. As stated in our previous blog, we can use Ansible Galaxy (repository for ansible collections and roles).  Before that, you need to declare the host that shall be affected by the deployment. For example, in this blog, I have the following: $ cat /etc/hosts 192.168.30.40 debnode4 192.168.30.50 debnode5   Then in my /etc/ansible/hosts file, root@debnode3:~# tail -n2 /etc/ansible/hosts debnode4 debnode5   Then, create the file main.yml just like below, root@debnode3:~/deploy-mysql# cat main.yml --- # tasks file for deploy-mysql - hosts: all become: yes vars_files: - vars/main.yml roles: - role: geerlingguy.mysql   Since I am running using Ubuntu 16.04 for this simple MySQL deployment using Ansible for my automation, I end up having this, root@debnode3:~/deploy-mysql# cat vars/main.yml --- # vars file for deploy-mysql mysql_root_password: "R00tP@55" mysql_python_package_debian: python-mysqldb Now, testing the MySQL deployment shows, root@debnode3:~/deploy-mysql# ansible all -a "mysql -Nse 'select concat("hello world at ", @@hostname,"!");' " 2>/dev/null debnode4 | CHANGED | rc=0 >> hello world at debnode4! debnode5 | CHANGED | rc=0 >> hello world at debnode5! Automate, then formulate As we have showcased above, it's better to automate and take advantage of existing ones. In this blog, I chose Ansible because it's easier to take advantage of existing modules such as roles in this example and just a few steps to deploy a MySQL server.  Of course, that's not how simple it is. There are existing roles, such as this one, that supports replication and more variables to tune. Whatever the existing modules can do, take advantage of it rather than reinventing your own. You can just use the existing ones, then modify them according to your needs. ClusterControl for Automation ClusterControl automates the deployment of MySQL proficiently. Instead of creating your own, ClusterControl is freely downloadable, and you can use it to deploy your MySQL how many times you want. You can use the software to set up a writer-reader replication or a writer-writer replication setup. Check out the screenshot below:   You just have to specify the source host/IP address of your desired MySQL servers. ClusterControl deployment automation does a pre-checks to determine if the server is ready to manage the job to be deployed. This involves accessibility, storage availability, and hardware capacity. The checks are aligned to the variables to be tuned up once it's ready to run the job. Check out the screenshot below: Based on the job activity log, you'll see that it adjusts the variables or parameters in the Linux systems we support. If you also look at the example screenshot we have below, it checks the memory and disk availability and prepares for the MySQL deployment. Thus, it installs the required packages, not only for MySQL to run normally but also for ClusterControl to monitor and register the server and its services that we collect for monitoring. ClusterControl Developer Tools ClusterControl has its feature where you can create your own scripts specifically for advisors. How important is this for your MySQL database deployment automation is that, once the database is registered and deployed, you can further enhance or create your own Advisors. For example, checkout our previous blog titled Using ClusterControl Advisor to Create Checks for SELinux and Meltdown/Spectre: Part One. After the deployment, you can write your first Advisors then check regularly for exploits. Not only that, but you can also check custom parameters that are unique to your needs. ClusterControl s9s CLI tools ClusterControl has CLI tools which we call as s9s CLI tools which can be found here at our github repository. The s9s CLI tools enable you to deploy MySQL with just one command. For example, $ s9s cluster --create --cluster-type=mysqlreplication --nodes="192.168.1.117?master;192.168.1.113?slave;192.168.1.115?slave;192.168.1.116?master;192.168.1.118?slave;192.168.1.119?slave;" --vendor=oracle --db-admin="root" --db-admin-passwd="root123" --cluster-name=ft_replication_23986 --provider-version=5.7 --log The command above allows you to deploy a MySQL Replication cluster provided the following IP addresses with its arguments, whether it’s a master or a slave. This tool provides more powerful and efficient features aside from just automatic deployment of your favourite database that we support, such as MySQL. Check out our documentation for ClusterControl to learn more about the s9s CLI tools Tags: database automationmysql deploymentdeployment tools https://severalnines.com/database-blog/mysql-database-deployment-automation

[globalmediacampaign]

MySQL 5.6 EOL Announcement

MySQL 5.6 has reached its EOL in February 2021, and we recommend that you start to plan migrating to MySQL 8.0. The latest version of MySQL is supported until April 2026 and keeps your database features up to date with continuously receiving updates and fixes, especially the security patches. The next release of ClusterControl, will no longer provide a deployment option for MySQL5.6. However, we will continue to support and provide support for MySQL5.6 users on bugs and fixes related to ClusterControl for a stipulated time.  Here are some related MySQL 8.0 blogs: Monitoring & Ops Management of MySQL 8.0 with ClusterControl What to Monitor in MySQL 8.0 What’s New With MySQL Replication in MySQL 8.0 MySQL in 2018: What’s in 8.0 and Other Observations Tips for Upgrading to from MySQL 5.7 to MySQL 8 Moving from MySQL 5.7 to MySQL 8.0 - What You Should Know With the EOL of MySQL5.6, there will be no more updates on bug fixes or security patches. Please refer to MySQL for requirements and technical compatibility before decommissioning your application from MySQL5.6.  Stay tuned with us for more updates on our latest release. Tags: MySQLclustercontrolclustercontrol https://severalnines.com/database-blog/mysql-56-eol-announcement

[globalmediacampaign]

Deploy a Hybrid Cloud MySQL Database using ClusterControl

A hybrid cloud database is a mix of active hosts residing on both on-premises and cloud infrastructures, working as a single operational system. It helps implement a high availability environment, and since both database and applications need to be portable in order to work on on-prem and cloud, it also reduces dependency on a particular cloud vendor (cloud vendor lock-in). Deploying this kind of environment is not that straightforward though. In this blog post, we are going to look into using ClusterControl to Deploy a Hybrid Cloud MySQL Database, between on-premises and cloud infrastructure running on AWS. This setup allows us to bring the database closer to the clients and applications residing in a cloud environment, and we will be keeping a replica of the database on the on-premises for disaster recovery and live backup purposes. Architecture  Our deployment consists of four nodes - One node for ClusterControl and one node for master server other two nodes are in slave servers, where 2 of the database nodes are located in the AWS data center as the primary datacenter (as well as ClusterControl), while the third node is located in the on-premises datacenter. The following diagram illustrates our final architecture. Preparing Your Cloud Environment  First, you need to create your environment with your main Cloud Provider. In this case, we will use AWS with three EC2 instances.   Then, go to the on-premises environment and create one virtual machine or host that will be the standby node. ClusterControl - 52.14.222.75 Master - 3.17.190.53 Slave 1 - 3.14.146.131 Slave 2 - 192.168.33.15 We will assume you have a ClusterControl installation running and have already created Cloud Provider accounts. Note that we are using cloud instances with public IPs for the purpose of the blog. For a production-grade setup, you have to secure your environment, for instance cloud VMs should be within a VPC with just private IP addresses. You would have a secure connection between on-prem and the VPC.  Deploying MySQL using ClusterControl  Go to your ClusterControl GUI, and select the option “Deploy”. If you already have a MySQL instance running, then you need to select the “Import Existing Server/Database” instead.     Here we are going to do a new deployment so we are selecting the “Deploy” option.When selecting MySQL, you must specify User, Key or Password, and port to connect by SSH to your MySQL nodes.      For more details about the SSH connectivity please check the ClusterControl user requirements.   After setting up the SSH access, you must define the database user and version. Datadir is optional. You can also specify which repository to use. Here we are choosing MySQL 8.0 version. Make sure you get a green tick icon indicating ClusterControl is able to connect to the database nodes to perform the deployment. Click on the "Deploy" button to start the deployment and you can monitor the deployment progress under Activity -> Jobs -> Create Cluster. You can monitor the creation status in the ClusterControl activity monitor.     Once the task is finished, you can see your new MySQL setup in the main ClusterControl screen. And check your final topology in the Topology View Section. Conclusion  ClusterControl features will allow you to quickly set up replication in a Hybrid Cloud environment, between different Cloud Providers, or even between a cloud provider and an On-prem environment. About the communication between the cloud providers, or between private and public cloud, for security reasons, you must restrict the traffic only from known sources, to reduce the risk of unauthorized access to your network. Tags: clustercontrolHybrid CloudMySQLdeploymentDatabase https://severalnines.com/database-blog/deploy-hybrid-cloud-mysql-database-using-clustercontrol

[globalmediacampaign]

Hybrid Cloud Replication for MySQL for High Availability

Hybrid environments, where a part of the database infrastructure is located on-prem and some of it is located in a public cloud are not uncommon. There may be different reasons to use such setup - scalability, flexibility, high availability, disaster recovery. How to implement this setup in a proper way? This might be challenging as you have to consider several pieces of a puzzle that have to fit together. This blog is intended to give you some insights into how such a setup may look like. Connectivity We are not going into details here because there are many ways of setting up connectivity between your on-prem setup and the public cloud. It will depend on the infrastructure you have in place, the public cloud you want to use and many other factors. Range of options may start with BGP-enabled routers, through hardware VPN, software VPN ending up on SSH tunnels as a way to temporarily connect your network to the instances in a public cloud. What is important, whatever you are going to do, the final outcome should be full and transparent connectivity from your on-premises network to the instances located in the public cloud. High Availability Considerations MySQL replication is a great way to build highly available systems but it comes with significant limitations. The main thing to consider is the writer - you can have only one place to send your writes to - the master. No matter how you want to design the whole environment, you have to carefully consider the placement of the master. Most likely you want it to be a part of the environment, which contains the application hosts. Let’s consider the following setup: We have an on-prem setup with three MySQL nodes and two additional slaves located in the public cloud, acting as a disaster recovery means for the company, it is quite clear that the writeable node should be collocated with the application hosts in the private part of the cloud. We want to keep the latency as low as possible for the connections that matter the most. This kind of design focuses on the availability of the databases - if the nodes located on prem won’t be available, application hosts may be able to connect to the remote part of the setup - database nodes located in the public cloud. Ideally, you would use some sort of a proxy for this - ProxySQL is one of the solutions that can track the topology and reconfigure as needed based on the existing replication chain. If you do want to consider more of an active-active setup where you have application nodes across both private and public, you have to make some compromises as the writes will have to be transferred over the WAN, from the public to private cloud (or vice versa, if your main location where you operate in the public cloud). Again, ProxySQL is the proxy of choice. What is great, ProxySQL can be configured as a ProxySQL Cluster, ensuring that the configuration changes introduced in the one node will be replicated across remaining ProxySQL nodes. Failure Handling Let’s consider a couple of failure scenarios. Before anything, we have to keep in mind that MySQL asynchronous replication is not cluster-aware therefore the network split is something that has to be handled manually - it will be up to the user to make the decision and pull the switch to promote one of the slaves in the environment that is available. It is also up to the user to ensure that the environment, which has lost the network connectivity will behave as it should and it will not continue to operate. If the private part of the cloud will become unavailable, as we mentioned earlier, manual action will be required to promote one of the slaves to become a new master. Then all remaining web application servers located in the public cloud, using local ProxySQL, will have their traffic redirected to the new master and all remaining slaves. On the other hand, given that we lost three out of five MySQL nodes, we want to scale out the public cloud setup - ClusterControl can help you in efficiently adding additional nodes to your cluster. Another scenario might be that the writer has crashed while the connectivity between our on-prem setup and the public cloud works just fine. In such a scenario we want to promote one of the slaves to become a new master. Depending on the requirements, we may also want the new master to be promoted between nodes in a given part of the environment. ClusterControl has the ability to whitelist or blacklist the nodes for the failover, ensuring that you have full control over the failover process and that you can pick which nodes should be considered as the candidates for a new master and in which order. We hope this blog gave you some idea about how the hybrid cloud setup for MySQL replication works and how it can protect you in case of the database or network failures.   Tags: Hybrid CloudDatabaseMySQLhigh availability https://severalnines.com/database-blog/hybrid-cloud-replication-mysql-high-availability

[globalmediacampaign]

How to Setup Automatic Failover for the Moodle MySQL Database

In a previous blog, we had discussed how to migrate a standalone Moodle setup to scalable setup based on a clustered database.  The next step you will need to think about is the failover mechanism - what do you do if and when your database service goes down.  A failed database server is not unusual if you have MySQL Replication as your backend Moodle database, and if it happens, you will need to find a way to recover your topology by for instance promoting a standby server to become a new primary server. Having automatic failover for your Moodle MySQL database helps  application uptime. We will explain how failover mechanisms work, and how to build automatic failover into your setup. High Availability Architecture for MySQL Database High availability architecture can be achieved by clustering your MySQL database in a couple of different ways. You can use MySQL Replication, set up multiple replicas that closely follow your primary database. On top of that, you can put a database load balancer to split the read/write traffic, and distribute the traffic across read-write and read-only nodes. Database high availability architecture using MySQL Replication can be described as below : It consists of one primary database, two database replicas, and database load balancers (in this blog, we use ProxySQL as database load balancers), and keepalived as a service to monitor the ProxySQL processes. We use Virtual IP Address as a single connection from the application. The traffic will be distributed to the active load balancer based on the role flag in keepalived.  ProxySQL is able to analyze the traffic and understand whether a request is a read or a write. It will then forward the request to the appropriate host(s).  Failover on MySQL Replication MySQL Replication uses binary logging to replicate data from the primary to the replicas. The replicas connect to the primary node, and every change  is replicated and written to the replica nodes’ relay logs through IO_THREAD. After the changes are stored in the relay log, the SQL_THREAD process will proceed with applying data into the replica database. The default setting for parameter read_only in a replica is ON. It is used to protect the replica itself from any direct write, so the changes will always come from the primary database. This is important as we do not want the replica to diverge from the primary server. Failover scenario in MySQL Replication happens when the primary is not reachable. There can be many reasons for this; e.g., server crashes or network issues. You need to promote one of the replicas to primary, disable the read-only parameter on the promoted replica so it can be writable. You also need to change the other replica to connect to the new primary. In GTID mode, you do not need to note the binary log name and position from where to resume replication. However, in traditional binlog based replication, you definitely need to know the last binary log name and position from which to carry on. Failover in binlog based replication is quite a complex process, but even failover in GTID based replication is not trivial either as you need to look out for things like errant transactions. Detecting a failure is one thing, and then reacting to the failure within a short delay is probably not possible without automation.  How ClusterControl Enables Automatic Failover  ClusterControl has the ability to perform automatic failover for your Moodle MySQL database. There is an Automatic Recovery for Cluster and Node feature which will trigger the failover process when the database primary crashes.  We will simulate how Automatic Failover happens in ClusterControl. We will make the primary database crash, and just see on the ClusterControl dashboard. Below is the current Topology of the cluster : The database primary is using IP Address 10.10.10.11 and the replicas are : 10.10.10.12 and 10.10.10.13. When the crash happens on the primary, ClusterControl triggers an alert and a failover starts as shown in the below picture: One of the replicas will be promoted to primary, resulting in the Topology as in the  below picture: The IP address 10.10.10.12 is now serving the write traffic as primary, and also we are left with only one replica which has IP address 10.10.10.13. On the ProxySQL side, the proxy will detect the new primary automatically. Hostgroup (HG10) still serve the write traffic which has member 10.10.10.12 as shown below: Hostgroup (HG20) still can serve read traffic, but as you can see the node 10.10.10.11 is offline because of the crash : Once the primary failed server comes back online, it will not be automatically re-introduced in the database topology. This is to avoid losing troubleshooting information, as re-introducing the node as a replica might require overwriting some logs or other information. But it is possible to configure auto-rejoin of the failed node.  Tags:  MySQL moodle lms automatic failover https://severalnines.com/database-blog/how-setup-automatic-failover-moodle-mysql-database

[globalmediacampaign]

How to Backup Your Moodle MySQL Database

Data is an essential part of running a Moodle environment. Should the IT operations team lose data accidentally, then it could lead to a huge loss. So, to protect valuable information, sysadmins need to back up their Moodle database. Backups are one of the most important things to take care of while managing a database. In this blog we are going to look into how to backup your Moodle database using ClusterControl. ClusterControl provides you with centralized backup management for the Moodle databases, and it provides you with hot backups of large datasets, point in time recovery, in-transit data encryption, data integrity via automatic restore verification, cloud backups (AWS, Google and Azure) for Disaster Recovery, retention policies to ensure compliance, and automated alerts and reporting. Building a Database Backup Strategy  ClusterControl will provide two options, Instant Backups  Scheduled Backups Instant Backups  With ClusterControl, taking an instant backup is pretty straightforward. Go to Backup -> Create Backup and you will be presented with the following dialog, By default it will take a mysqldump, the options are very straightforward you can choose based on your need. Here we are going to use Xtrabackup, which is a physical backup method that is probably your preferred option if you have a large database. Partial backup is available if you would only like to backup specific database schemas or tables. In the next step, you can compress and encrypt your backup, and specify the retention period. Here, you also have the “Verify Backup” feature. Once the required options are enabled, click the create backup button. You can monitor the progress of a backup by looking at Activity -> Jobs. ClusterControl lists a report of all backups that have been taken, successful as well as failed ones. Schedule Backup  Scheduling a backup in advance is what you should do by default, it offers easy accessibility to other features like encryption, automatic test/verification of backup data, and cloud archiving. Go straight to Backup -> Create Backup -> Schedule Backup and you will be presented with the following dialog, There are a couple of settings to decide on. You can store a backup on the controller or locally, on the database node where the backup is taken. You need to decide on the location in which the backup should be stored, and which databases you’d like to backup; all data sets or separate schemas? See the image below: The Advanced setting would take advantage of a cron-like configuration for more granularity.  Timezone  When scheduling backup with ClusterControl, all time must be scheduled in the UTC timezone of the ClusterControl server. The reason is to avoid the confusion of backup execution time. When working with a cluster, the database servers could be spread in different time zones and different geographical areas. Using one reference timezone to manage them all will ensure the backups are always executed at the correct time. You can see the below image, how you could flag the options accordingly and there are tooltip icons which provide more information of the options you would like to leverage for your backup policy. For this schedule, there was one more feature called Failover Host. If we enable this option,  in case of failure of the backup host, ClusterControl will pick another available node and trigger the backup on that node. Once the schedule is created, you can edit the configuration backups, enable/disable the backup or delete the schedule under "Scheduled Backups" tab: Backup Management with ClusterControl cli  The s9s is a command line tool to interact with ClusterControl. This is especially useful for command line aficionados.  ​[root@centos11 vagrant]# s9s backup --list --cluster-id=37 --long --human-readable ID PI CID V I STATE OWNER HOSTNAME CREATED SIZE TITLE 5 - 37 - F COMPLETED XXXX 192.168.33.15 2020-12-31 01:50:22 4.2G Untitled Backup Record 6 5 37 - I COMPLETED XXXX 192.168.33.15 2020-12-31 02:12:30 92K Untitled Backup Record 8 - 37 - F COMPLETED XXXX 192.168.33.15 2021-01-11 00:07:19 1.7G Untitled Backup Record Total 3 Summary The database is a critical component of a Moodle platform, and a backup is one of the most important things to take care of while managing the database. It is important to get it right, as in case of major disaster, it is important to be able to recover the data. This can be conveniently achieved by using ClusterControl. Tags:  MySQL moodle backups backup management lms https://severalnines.com/database-blog/how-backup-your-moodle-mysql-database

[globalmediacampaign]

Tips for Monitoring MySQL for Moodle

Database monitoring is a critical part of any application’s maintenance. Finding database issues on time can help the application remain healthy and accessible. Without monitoring in place, database outages can be prolonged, resulting in increased downtime for users. In this blog we are going to explain what you need to monitor in a Moodle Database, and how you can do it using ClusterControl. ClusterControl Monitoring  Using ClusterControl you can monitor the databases efficiently, it can do so by deploying agents on the database hosts or in an agentless fashion, by simply connecting to the databases via SSH . Data is presented in a GUI, in the form of a number of dashboards and charts.  How Does ClusterControl Monitoring Work ? ClusterControl automatically collects database metrics as well as underlying server performance metrics. Since a high availability database stack would most probably consist of a load balancer (for Galera Cluster, one would have either HAProxy, ProxySQL or MaxScale), it is also included in the instances being monitored. It alerts on performance or availability problems for both database and server components. It also generates comprehensive reports to show database utilization and capacity issues. Real Time Monitoring  ClusterControl allows you to monitor your database servers in real-time. It has a predefined set of dashboards for you, to analyze some of the most common metrics mentioned above and even more.  It also allows you to customize the graphs and you can enable agent-based monitoring to generate more detailed dashboards for both system level and DB level.  Query Monitoring  Also, you have the query monitor and the performance sections, where you can find all your database information. With these features, you can understand database activity. Slow queries and things like deadlocks can be devastating to the stability of a database, and can affect all users of the application. So it is important to keep track of them. Moodle itself has its own schema, but at the time of writing, there’s over 1700 third party Moodle plugins that come with their own database tables. So how do you keep track of how the queries in these plugins are performing, and identify poorly written queries that can drag down the performance of the entire Moodle database. The query outliers show queries that take more time to execute than normally, longer being defined as a latency deviation of 2 sigmas + average_query_time. This can be an interesting feature as it would help identify queries that suddenly start to perform poorly, which might be because of a change in configuration, an upgrade, or for instance adding a new plugin. DB Growth  The ClusterControl frequently collects db, table, index stats. We can easily find the largest tables in your db. This can help understand when you might need to add more storage to the database, or for instance, if you need to do some cleanup on some tables. Alerting You can create alerts, which inform you of events in your cluster, or integrate it with different services such as PagerDuty, VictorOps or Slack. Summary  In this blog, we mentioned some important metrics to monitor in your Moodle Database. Monitoring your moodle database is necessary but also a time-consuming task if you don’t have any tools to help with this. We hope those couple of tips help to monitor your Moodle database backend much easier and to a greater extent. Tags:  MySQL moodle lms database monitoring https://severalnines.com/database-blog/tips-monitoring-mysql-moodle

[globalmediacampaign]

Performance Testing Using MySQLdump and the MySQL Shell Utility

In my previous post I explained how to take a logical backup using the mysql shell utilities. In this post, we shall compare the speed of the backup and restoration process. MySQL Shell Speed Test  We are going to do a comparison of  backup and recovery speed of mysqldump and MySQL shell utility tools. Below tools are used for speed comparison: mysqldump util.dumpInstance util.loadDump Hardware Configuration Two standalone servers with identical configurations. Server 1    * IP: 192.168.33.14    * CPU: 2 Cores    * RAM: 4 GB    * DISK: 200 GB SSD Server 2    * IP: 192.168.33.15    * CPU: 2 Cores    * RAM: 4 GB    * DISK: 200 GB SSD Workload Preparation On Server 1 (192.168.33.14), We have loaded approx 10 GB data. Now, We want to restore the data from Server 1 (192.168.33.14) to Server 2 (192.168.33.15). MySQL Setup MySQL Version: 8.0.22 InnoDB Buffer Pool Size: 1 GB InnoDB Log File Size: 16 MB Binary Logging: On We loaded 50M records using sysbench. [root@centos14 sysbench]# sysbench oltp_insert.lua --table-size=5000000 --num-threads=8 --rand-type=uniform --db-driver=mysql --mysql-db=sbtest --tables=10 --mysql-user=root --mysql-password=****** prepare WARNING: --num-threads is deprecated, use --threads instead sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Initializing worker threads... ​Creating table 'sbtest3'... Creating table 'sbtest4'... Creating table 'sbtest7'... Creating table 'sbtest1'... Creating table 'sbtest2'... Creating table 'sbtest8'... Creating table 'sbtest5'... Creating table 'sbtest6'... Inserting 5000000 records into 'sbtest1' Inserting 5000000 records into 'sbtest3' Inserting 5000000 records into 'sbtest7 . . . Creating a secondary index on 'sbtest9'... Creating a secondary index on 'sbtest10'... Test Case One In this case we are going to take a logical backup using mysqldump command. Example  [root@centos14 vagrant]# time /usr/bin/mysqldump --defaults-file=/etc/my.cnf --flush-privileges --hex-blob --opt --master-data=2 --single-transaction --triggers --routines --events --set-gtid-purged=OFF --all-databases |gzip -6 -c > /home/vagrant/test/mysqldump_schemaanddata.sql.gz start_time = 2020-11-09 17:40:02 end_time   = 2020-11-09 37:19:08 It took nearly 20 minutes 19 seconds to take a dump of all databases with a total size of around 10GB. Test Case Two Now let's try with MySQL shell utility. We are going to use dumpInstance to take a full backup. Example  MySQL localhost:33060+ ssl JS > util.dumpInstance("/home/vagrant/production_backup", {threads: 2, ocimds: true,compatibility: ["strip_restricted_grants"]}) Acquiring global read lock Global read lock acquired All transactions have been started Locking instance for backup Global read lock has been released Checking for compatibility with MySQL Database Service 8.0.22 NOTE: Progress information uses estimated values and may not be accurate. Data dump for table `sbtest`.`sbtest1` will be written to 38 files Data dump for table `sbtest`.`sbtest10` will be written to 38 files Data dump for table `sbtest`.`sbtest3` will be written to 38 files Data dump for table `sbtest`.`sbtest2` will be written to 38 files Data dump for table `sbtest`.`sbtest4` will be written to 38 files Data dump for table `sbtest`.`sbtest5` will be written to 38 files Data dump for table `sbtest`.`sbtest6` will be written to 38 files Data dump for table `sbtest`.`sbtest7` will be written to 38 files Data dump for table `sbtest`.`sbtest8` will be written to 38 files Data dump for table `sbtest`.`sbtest9` will be written to 38 files 2 thds dumping - 36% (17.74M rows / ~48.14M rows), 570.93K rows/s, 111.78 MB/s uncompressed, 50.32 MB/s compressed 1 thds dumping - 100% (50.00M rows / ~48.14M rows), 587.61K rows/s, 115.04 MB/s uncompressed, 51.79 MB/s compressed Duration: 00:01:27s Schemas dumped: 3 Tables dumped: 10 Uncompressed data size: 9.78 GB Compressed data size: 4.41 GB Compression ratio: 2.2 Rows written: 50000000 Bytes written: 4.41 GB Average uncompressed throughput: 111.86 MB/s Average compressed throughput: 50.44 MB/s It took a total of 1 minute 27 seconds to take a dump of the entire database (same data as used for mysqldump) and also it shows its progress which will be really helpful to know how much of the backup has completed. It gives the time it took to perform the backup. The parallelism depends on the number of cores in the server. Roughly increasing the value won’t be helpful in my case. (My machine has 2 cores). Restoration Speed Test  In the restoration part, we are going to restore the mysqldump backup on another standalone server. The backup file was already moved to the destination server using rsync. Test Case 1  Example  [root@centos15 vagrant]#time gunzip < /mnt/mysqldump_schemaanddata.sql.gz | mysql -u root -p It took around 16 minutes 26 seconds to restore the 10GB of data. Test Case 2  In this case we are using mysql shell utility to load the backup file on another standalone host. We already moved the backup file to the destination server. Let’s start the restoration process. Example  ​ MySQL localhost:33060+ ssl JS > util.loadDump("/home/vagrant/production_backup", {progressFile :"/home/vagrant/production_backup/log.json",threads :2}) Opening dump... Target is MySQL 8.0.22. Dump was produced from MySQL 8.0.22 Checking for pre-existing objects... Executing common preamble SQL Executing DDL script for schema `cluster_control` Executing DDL script for schema `proxydemo` Executing DDL script for schema `sbtest` . . . 2 thds loading 1% (150.66 MB / 9.78 GB), 6.74 MB/s, 4 / 10 tables done 2 thds loading / 100% (9.79 GB / 9.79 GB), 1.29 MB/s, 10 / 10 tables done [Worker001] sbtest@sbtest8@@37.tsv.zst: Records: 131614 Deleted: 0 Skipped: 0 Warnings: 0 [Worker002] sbtest@sbtest10@@37.tsv.zst: Records: 131614 Deleted: 0 Skipped: 0 Warnings: 0 Executing common postamble SQL 380 chunks (50.00M rows, 9.79 GB) for 10 tables in 2 schemas were loaded in 40 min 6 sec (avg throughput 4.06 MB/s) It took around 40 minutes 6 seconds to restore the 10GB of data. Now let's try to disable the redo log and start the data importing using mysql shell utility. ​mysql> alter instance disable innodb redo_log; Query OK, 0 rows affected (0.00 sec) MySQL localhost:33060+ ssl JS >util.loadDump("/home/vagrant/production_backup", {progressFile :"/home/vagrant/production_backup/log.json",threads :2}) Opening dump... Target is MySQL 8.0.22. Dump was produced from MySQL 8.0.22 Checking for pre-existing objects... Executing common preamble SQL . . . 380 chunks (50.00M rows, 9.79 GB) for 10 tables in 3 schemas were loaded in 19 min 56 sec (avg throughput 8.19 MB/s) 0 warnings were reported during the load. After disabling the redo log, the average throughput was increased up to 2x. Note: Do not disable redo logging on a production system. It allows shutdown and restart of the server while redo logging is disabled, but an unexpected server stoppage while redo logging is disabled can cause data loss and instance corruption. Physical Backups  As you may have noticed, the logical backup methods, even if multithreaded, are quite time consuming even for a small data set that we tested them against. This is one of the reasons why ClusterControl provides physical backup method that’s based on the copying of the files - in such case we are not limited by the SQL layer that processes logical backup but rather by hardware - how fast the disk can read the files and how fast the network can transfer data between the database node and backup server. ClusterControl comes with different ways to implement physical backups, which method is available will depend on the cluster type and sometimes even the vendor. Let’s take a look at the Xtrabackup executed by ClusterControl that will create a full backup of the data on our test environment. We are going to create an ad-hoc backup this time but ClusterControl lets you create a full backup schedule as well. Here we pick the backup method (xtrabackup) as well as the host we are going to take the backup from. We can also store it locally on the node or it can be streamed to a ClusterControl instance. Additionally, you can upload the backup to the cloud (AWS, Google Cloud and Azure are supported). The backup took around 10 mins to complete. Here the logs from cmon_backup.metadata file. ​[root@centos14 BACKUP-9]# cat cmon_backup.metadata { "class_name": "CmonBackupRecord", "backup_host": "192.168.33.14", "backup_tool_version": "2.4.21", "compressed": true, "created": "2020-11-17T23:37:15.000Z", "created_by": "", "db_vendor": "oracle", "description": "", "encrypted": false, "encryption_md5": "", "finished": "2020-11-17T23:47:47.681Z" } Now let's try the same to restore using ClusterControl. ClusterControl > Backup > Restore Backup  Here we pick the restore backup option, it will support time and log based recovery too. Here we choose the backup file source path and then destination server. You also have to make sure this host can be reached from ClusterControl node using SSH. We don't want ClusterControl to set up software, so we disabled that option. After restoration it will keep the server running. It took around 4 minutes 18 seconds to restore the 10GB of data. Xtrabackup does not lock your database during the backup process. For large databases (100+ GB), it provides much better restoration time as compared to mysqldump/shell utility. Also lusterControl supports partial backup and restoration as one of my colleagues explained in his blog: Partial backup and restore. Conclusion Each method has its own pros and cons. As we have seen, there is not one method that works best for everything that you need to do. We need to choose our tool based on our production environment and target time for recovery. Tags:  MySQL database performance backup management restore https://severalnines.com/database-blog/performance-testing-using-mysqldump-and-mysql-shell-utility