Quest_in_OracleDBA

Query to list all the user and their privileges in mariadb/mysql2022-05-02T11:27:00.005+05:30
  
Query to list all the user and their privileges in mariadb/mysql
      SELECT
    CONCAT("`",gcl.Db,"`") AS 'Database(s) Affected',
    CONCAT("`",gcl.Table_name,"`") AS 'Table(s) Affected',
    gcl.User AS 'User-Account(s) Affected',
    IF(gcl.Host='%','ALL',gcl.Host) AS 'Remote-IP(s) Affected',
    CONCAT("GRANT ",UPPER(gcl.Column_priv)," (",GROUP_CONCAT(gcl.Column_name),") ",
                 "ON `",gcl.Db,"`.`",gcl.Table_name,"` ",
                 "TO '",gcl.User,"'@'",gcl.Host,"';") AS 
'GRANT Statement (Reconstructed)'
FROM mysql.columns_priv gcl
GROUP BY CONCAT(gcl.Db,gcl.Table_name,gcl.User,gcl.Host)
/* SELECT * FROM mysql.columns_priv */
 
UNION
 
/* [Database.Table]-Specific Grants */
SELECT
    CONCAT("`",gtb.Db,"`") AS 'Database(s) Affected',
    CONCAT("`",gtb.Table_name,"`") AS 'Table(s) Affected',
    gtb.User AS 'User-Account(s) Affected',
    IF(gtb.Host='%','ALL',gtb.Host) AS 'Remote-IP(s) Affected',
    CONCAT(
        "GRANT ",UPPER(gtb.Table_priv)," ",
        "ON `",gtb.Db,"`.`",gtb.Table_name,"` ",
        "TO '",gtb.User,"'@'",gtb.Host,"';"
    ) AS 'GRANT Statement (Reconstructed)'
FROM mysql.tables_priv gtb
WHERE gtb.Table_priv!=''
/* SELECT * FROM mysql.tables_priv */
 
UNION
 
/* Database-Specific Grants */
SELECT
    CONCAT("`",gdb.Db,"`") AS 'Database(s) Affected',
    "ALL" AS 'Table(s) Affected',
    gdb.User AS 'User-Account(s) Affected',
    IF(gdb.Host='%','ALL',gdb.Host) AS 'Remote-IP(s) Affected',
    CONCAT(
        'GRANT ',
        CONCAT_WS(',',
            IF(gdb.Select_priv='Y','SELECT',NULL),
            IF(gdb.Insert_priv='Y','INSERT',NULL),
            IF(gdb.Update_priv='Y','UPDATE',NULL),
            IF(gdb.Delete_priv='Y','DELETE',NULL),
            IF(gdb.Create_priv='Y','CREATE',NULL),
            IF(gdb.Drop_priv='Y','DROP',NULL),
            IF(gdb.Grant_priv='Y','GRANT',NULL),
            IF(gdb.References_priv='Y','REFERENCES',NULL),
            IF(gdb.Index_priv='Y','INDEX',NULL),
            IF(gdb.Alter_priv='Y','ALTER',NULL),
            IF(gdb.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL),
            IF(gdb.Lock_tables_priv='Y','LOCK TABLES',NULL),
            IF(gdb.Create_view_priv='Y','CREATE VIEW',NULL),
            IF(gdb.Show_view_priv='Y','SHOW VIEW',NULL),
            IF(gdb.Create_routine_priv='Y','CREATE ROUTINE',NULL),
            IF(gdb.Alter_routine_priv='Y','ALTER ROUTINE',NULL),
            IF(gdb.Execute_priv='Y','EXECUTE',NULL),
            IF(gdb.Event_priv='Y','EVENT',NULL),
            IF(gdb.Trigger_priv='Y','TRIGGER',NULL)
        ),
        " ON `",gdb.Db,"`.* TO '",gdb.User,"'@'",gdb.Host,"';"
    ) AS 'GRANT Statement (Reconstructed)'
FROM mysql.db gdb
WHERE gdb.Db != ''
/* SELECT * FROM mysql.db */
 
UNION
 
/* User-Specific Grants */
SELECT
    "ALL" AS 'Database(s) Affected',
    "ALL" AS 'Table(s) Affected',
    gus.User AS 'User-Account(s) Affected',
    IF(gus.Host='%','ALL',gus.Host) AS 'Remote-IP(s) Affected',
    CONCAT(
        "GRANT ",
        IF((gus.Select_priv='N')&(gus.Insert_priv='N')&(gus.Update_priv='N')&
(gus.Delete_priv='N')&(gus.Create_priv='N')&(gus.Drop_priv='N')&(gus.Reload_priv='N')
&(gus.Shutdown_priv='N')&(gus.Process_priv='N')&(gus.File_priv='N')
&(gus.References_priv='N')&(gus.Index_priv='N')&(gus.Alter_priv='N')
&(gus.Show_db_priv='N')&(gus.Super_priv='N')&(gus.Create_tmp_table_priv='N')
&(gus.Lock_tables_priv='N')&(gus.Execute_priv='N')&(gus.Repl_slave_priv='N')
&(gus.Repl_client_priv='N')&(gus.Create_view_priv='N')&(gus.Show_view_priv='N')
&(gus.Create_routine_priv='N')&(gus.Alter_routine_priv='N')
&(gus.Create_user_priv='N')&(gus.Event_priv='N')&(gus.Trigger_priv='N')
&(gus.Create_tablespace_priv='N')&(gus.Grant_priv='N'),
            "USAGE",
            IF((gus.Select_priv='Y')&(gus.Insert_priv='Y')&(gus.Update_priv='Y')
&(gus.Delete_priv='Y')&(gus.Create_priv='Y')&(gus.Drop_priv='Y')
&(gus.Reload_priv='Y')&(gus.Shutdown_priv='Y')&(gus.Process_priv='Y')
&(gus.File_priv='Y')&(gus.References_priv='Y')&(gus.Index_priv='Y')
&(gus.Alter_priv='Y')&(gus.Show_db_priv='Y')&(gus.Super_priv='Y')
&(gus.Create_tmp_table_priv='Y')&(gus.Lock_tables_priv='Y')&(gus.Execute_priv='Y')
&(gus.Repl_slave_priv='Y')&(gus.Repl_client_priv='Y')&(gus.Create_view_priv='Y')
&(gus.Show_view_priv='Y')&(gus.Create_routine_priv='Y')&(gus.Alter_routine_priv='Y')
&(gus.Create_user_priv='Y')&(gus.Event_priv='Y')&(gus.Trigger_priv='Y')
&(gus.Create_tablespace_priv='Y')&(gus.Grant_priv='Y'),
                "ALL PRIVILEGES",
                CONCAT_WS(',',
                    IF(gus.Select_priv='Y','SELECT',NULL),
                    IF(gus.Insert_priv='Y','INSERT',NULL),
                    IF(gus.Update_priv='Y','UPDATE',NULL),
                    IF(gus.Delete_priv='Y','DELETE',NULL),
                    IF(gus.Create_priv='Y','CREATE',NULL),
                    IF(gus.Drop_priv='Y','DROP',NULL),
                    IF(gus.Reload_priv='Y','RELOAD',NULL),
                    IF(gus.Shutdown_priv='Y','SHUTDOWN',NULL),
                    IF(gus.Process_priv='Y','PROCESS',NULL),
                    IF(gus.File_priv='Y','FILE',NULL),
                    IF(gus.References_priv='Y','REFERENCES',NULL),
                    IF(gus.Index_priv='Y','INDEX',NULL),
                    IF(gus.Alter_priv='Y','ALTER',NULL),
                    IF(gus.Show_db_priv='Y','SHOW DATABASES',NULL),
                    IF(gus.Super_priv='Y','SUPER',NULL),
                    IF(gus.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL),
                    IF(gus.Lock_tables_priv='Y','LOCK TABLES',NULL),
                    IF(gus.Execute_priv='Y','EXECUTE',NULL),
                    IF(gus.Repl_slave_priv='Y','REPLICATION SLAVE',NULL),
                    IF(gus.Repl_client_priv='Y','REPLICATION CLIENT',NULL),
                    IF(gus.Create_view_priv='Y','CREATE VIEW',NULL),
                    IF(gus.Show_view_priv='Y','SHOW VIEW',NULL),
                    IF(gus.Create_routine_priv='Y','CREATE ROUTINE',NULL),
                    IF(gus.Alter_routine_priv='Y','ALTER ROUTINE',NULL),
                    IF(gus.Create_user_priv='Y','CREATE USER',NULL),
                    IF(gus.Event_priv='Y','EVENT',NULL),
                    IF(gus.Trigger_priv='Y','TRIGGER',NULL),
                    IF(gus.Create_tablespace_priv='Y','CREATE TABLESPACE',NULL)
                )
            )
        ),
        " ON *.* TO '",gus.User,"'@'",gus.Host,"' REQUIRE ",
        CASE gus.ssl_type
            WHEN 'ANY' THEN
                "SSL "
            WHEN 'X509' THEN
                "X509 "
            WHEN 'SPECIFIED' THEN
                CONCAT_WS("AND ",
                    IF((LENGTH(gus.ssl_cipher)>0),
CONCAT("CIPHER '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL),
                    IF((LENGTH(gus.x509_issuer)>0),
CONCAT("ISSUER '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL),
                    IF((LENGTH(gus.x509_subject)>0),
CONCAT("SUBJECT '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL)
                )
            ELSE "NONE "
        END,
        "WITH ",
        IF(gus.Grant_priv='Y',"GRANT OPTION ",""),
        "MAX_QUERIES_PER_HOUR ",gus.max_questions," ",
        "MAX_CONNECTIONS_PER_HOUR ",gus.max_connections," ",
        "MAX_UPDATES_PER_HOUR ",gus.max_updates," ",
        "MAX_USER_CONNECTIONS ",gus.max_user_connections,
        ";"
    ) AS 'GRANT Statement (Reconstructed)'
FROM mysql.user gus;

Find which process holds a particular connection to a MySQL database

2022-04-20T15:57:00.000+05:30

Find which process holds a particular connection to a MySQL database

Finding which process holds a particular connection to a MySQL database is often not immediately obvious or well documented.

First do this in your mysql command line client:

mysql> show full processlist;
+-----+--------+-----------------+----------+---------+------+----------+-----------------------+
| Id  | User   | Host            | db       | Command | Time | State    | Info                  |
+-----+--------+-----------------+----------+---------+------+----------+-----------------------+
|   2 | root   | localhost       | adspider | Query   |    0 | starting | show full processlist |
| 257 | fotios | localhost:35828 | adspider | Sleep   |   20 |          | NULL                  |
| 259 | fotios | localhost:35832 | adspider | Sleep   |   17 |          | NULL                  |
| 261 | fotios | localhost:35836 | adspider | Sleep   |    3 |          | NULL                  |
| 263 | fotios | localhost:35840 | adspider | Sleep   |    1 |          | NULL                  |
+-----+--------+-----------------+----------+---------+------+----------+-----------------------+
5 rows in set (0.00 sec)

Then, on your Linux command line do this to see which process holds the socket to the port of interest:

[root@li1849-192 ~]# netstat -np | grep 35828
tcp6       0      0 127.0.0.1:35828         127.0.0.1:3306          ESTABLISHED 28509/java
tcp6       0      0 127.0.0.1:3306          127.0.0.1:35828         ESTABLISHED 28432/mysqld

Top Metrics to Monitor in Your MySQL Database (Part 2)

2022-04-08T20:07:00.003+05:30

As a dba, your top priority is to keep your databases and dependent applications running smoothly at all times. to this end, your best weapon is judicious monitoring of key performance metrics. in a perfect world, you’d want to be up-to-date regarding every aspect of your database’s activity, i.e. how many events occurred, how big they were, precisely when they happened, and how long they took.

there certainly is no shortage of tools that can monitor resource consumption, provide instantaneous status snapshots, and generate wait analysis and graphs. the challenge is that some metrics can be expensive to measure, and, perhaps even more importantly, they can require a lot of work to analyze.

the purpose of part 2 of this blog series is to narrow down the field to those performance metrics that provide the most value for the effort, as well as present some tangible ways to capture and study them. it is by tracking the most useful metrics and reviewing them in the most informative way(s) that you will strike a balance between paranoid over-monitoring and firefighting unforeseen crises.

this topic is divided into two parts. in part 1 , we:

examined the benefits of performance monitoring.
outlined the main performance metric categories.
listed the monitoring tools provided by mysql, i.e.:
- server variables
- performance schema
- sys schema
learned how to monitor:
- transaction throughput
- query execution performance

this blog will focus on monitoring database connections and buffer pool metrics.

connections

connection manager threads handle client connection requests on the network interfaces that the server listens to. on all platforms, one manager thread handles tcp/ip connection requests. connection manager threads associate each client connection with a thread dedicated to it that handles authentication and request processing for that connection. manager threads create a new thread when necessary — but try to avoid doing so by consulting the thread cache first to see whether it contains a thread that can be used for the connection. when a connection ends, its thread is returned to the thread cache (if the cache isn't full). in this connection thread model, there are as many threads as there are clients currently connected.

it’s important to monitor your client connections because once the database server runs out of available connections, new client connections are refused!

the mysql connection limit defaults to 151, but it can be changed using the set statement, so it’s best to not assume anything. the connection limit is stored in the @@max_connections variable:

select @@max_connections;
+-----------------+-------+
| variable_name   | value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+ 

the connection limit can be set at any time like so:

set global max_connections = 200;

to permanently set the connection limit so that it persists once the server is restarted, add a line like this to your my.cnf configuration file:

max_connections = 200

don’t be afraid to increase the number of max_connections significantly. according to mysql docs , production servers should be able to handle connections in the high hundreds or thousands! just bear in mind that there are some caveats when the server must handle a large number of connections. for instance, thread creation and disposal become expensive when there are a lot of them. in addition, each thread requires server and kernel resources, such as stack space. therefore, in order to accommodate many simultaneous connections, the stack size per thread must be kept small. this can lead to a situation where the stack size is either too small or the server consumes large amounts of memory.

the takeaway here is that your database server should have adequate amounts of processing power and memory to accommodate a large user base.

mysql provides a few good metrics for monitoring your connections:

variable	what it represents	why you should monitor it
`threads_connected`	the total number of clients that have currently open connections to the server.	provides real-time information on how many clients are currently connected to the server. this can help in traffic analysis or in deciding the best time for a server restart.
`threads_running`	the number of threads that are not sleeping.	good for isolating which connected threads are actively processing queries at any given time, as opposed to connections that are open but are currently idle.
`connections`	the number of connection attempts (successful or not) to the mysql server.	can give you a good idea of how many people and applications are accessing the database. over time, these numbers reveal busiest times and average usage numbers.
`connection_errors_internal`	the number of connections refused due to internal server errors, i.e. failure to start a new thread or an out-of-memory condition.	although mysql exposes several metrics on connection errors, `connection_errors_internal` is probably the most useful because it is incremented only when the error comes from the server itself. internal errors can indicate an out-of-memory condition or an inability to start a new thread.

we can use the mysql show status command to show mysql variables and status information. here are a few examples:

show global status like '%threads_connected%';
+-------------------+-------+
| variable_name     | value |
+-------------------+-------+
| threads_connected | 2     |
+-------------------+-------+

show global status like '%threads_running%';
+-----------------+-------+
| variable_name   | value |
+-----------------+-------+
| threads_running | 1     |
+-----------------+-------+

show global status like 'connections';
+---------------+-------+
| variable_name | value |
+---------------+-------+
| connections   | 20    |
+---------------+-------+

aborted client and connections

every time a client is unable to connect, the server increments the aborted_connects status variable. unsuccessful connection attempts can occur for the following reasons:

a client attempts to access a database but has no privileges for it.
a client uses an incorrect password.
a connection packet does not contain the right information.
it takes more than connect_timeout seconds to obtain a connect packet.

if these kinds of things happen, it might indicate that someone is trying to break into your server! if the general query log is enabled, messages for these types of problems are logged to it.

if a client successfully connects but later disconnects improperly or is terminated, the server increments the aborted_clients status variable and logs an "aborted connection" message to the error log.

here’s how to view the number of aborted clients and connections:

mysql> show global status like 'aborted_c%';
+-----------------------------------+------------+
| variable_name                     | value      |
+-----------------------------------+------------+
| aborted_clients                   | 3          |
| aborted_connects                  | 8          |
+-----------------------------------+------------+ 

connection errors

mysql does an outstanding job of breaking down metrics on connection errors into different status variables:

show global status like 'connection_errors%';
+-----------------------------------+-------+
| variable_name                     | value |
+-----------------------------------+-------+
| connection_errors_accept          | 0     |
+-----------------------------------+-------+
| connection_errors_internal        | 0     |
+-----------------------------------+-------+
| connection_errors_max_connection  | 0     |
+-----------------------------------+-------+
| connection_errors_peer_address    | 0     |
+-----------------------------------+-------+
| connection_errors_select         | 0     |
+-----------------------------------+-------+
| connection_errors_tcpwrap    | 0     |
+-----------------------------------+-------+ 

once all available connections are in use, attempting to connect to mysql will cause it to return a "too many connections" error and increment the connection_errors_max_connections variable. your best bet in preventing this scenario is to monitor the number of open connections and make sure that it remains safely below the configured max_connections limit.

fine-grained connection metrics such as connection_errors_max_connections and connection_errors_internal can be instrumental in pinpointing the source of the problem. the following statement fetches the value of connection_errors_internal :

show global status like 'connection_errors_internal';
+----------------------------+-------+
| variable_name              | value |
+----------------------------+-------+
| connection_errors_internal | 2     |
+----------------------------+-------+ 

here are a couple of monyog screens that monitor current connections and the connection history, respectively:

monitor: current connection

monitor: connection history

buffer pool usage

mysql’s default storage engine, innodb , uses a special storage area called the buffer pool to cache data for tables and indexes. buffer pool metrics are categorized as resource metrics. as such, their main value is in the investigation, rather than the detection, of performance issues.

configuring the buffer pool

you can configure various aspects of the innodb buffer pool to improve performance.

the buffer pool defaults to a relatively small 128mb. ideally, you should increase the size of the buffer pool to as large a value as is practical, while leaving enough memory for other processes on the server to run without excessive paging. that typically amounts to about 80% of physical memory on a dedicated database server. the idea is that the larger the buffer pool, the more innodb acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads.

please note:

innodb’s memory overhead can increase the memory footprint by about 10 percent beyond the allotted buffer pool size.
once the physical memory is exhausted, your system will resort to paging and performance will suffer significantly. hence, if database performance starts to degrade while disk i/o is rising, it might be time to expand the buffer pool.

buffer-pool resizing operations are performed in chunks, and the size of the buffer pool must be set to a multiple of the chunk size times the number of instances:

innodb_buffer_pool_size = n * innodb_buffer_pool_chunk_size 
                            * innodb_buffer_pool_instances

the chunk size defaults to 128mb but is configurable as of mysql 5.7.5. the value of both parameters can be checked as follows:

show global variables like "innodb_buffer_pool_chunk_size";
show global variables like "innodb_buffer_pool_instances";

if querying innodb_buffer_pool_chunk_size returns no results, the parameter is not tunable in your version of mysql and can be assumed to be 128mb.

to set the buffer pool size and number of instances at server startup, invoke mysqld.exe with the following parameters:

$ mysqld --innodb_buffer_pool_size=8g --innodb_buffer_pool_instances=16 

as of mysql 5.7.5, you can also resize the buffer pool on-the-fly via a set command specifying the desired size in bytes. for instance, with two buffer pool instances, you could set each to 4gb by setting the total size to 8gb:

set global innodb_buffer_pool_size=8589934592;

important innodb buffer pool metrics

innodb standard monitor output, which can be accessed using show engine innodb status , provides a number of metrics pertaining to operation of the innodb buffer pool, under the buffer pool and memory section. here is some typical content:

----------------------
buffer pool and memory
----------------------
total large memory allocated 2198863872
dictionary memory allocated 776332
buffer pool size   131072
free buffers       124908
database pages     5720
old database pages 2071
modified db pages  910
pending reads 0
pending writes: lru 0, flush list 0, single page 0
pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
pages read ahead 0.00/s, evicted without access 0.00/s, random read ahead 0.00/s
lru len: 5720, unzip_lru len: 0
i/o sum[0]:cur[0], unzip sum[0]:cur[0] 

a word about the innodb buffer pool lru algorithm

in order to better understand what the above metrics mean, we should briefly review how the innodb buffer pool lru algorithm works.

innodb manages the buffer pool as a list, using a variation of the least recently used (lru) algorithm. when room is needed to add a new page to the pool, innodb evicts the least recently used page and adds the new page to the middle of the list. this “midpoint insertion strategy” treats the list as two sublists:

at the head, a sublist of “new” (or “young”) pages that were accessed recently.
at the tail, a sublist of “old” pages that were accessed less recently.

this algorithm keeps pages that are heavily used by queries in the new sublist. the old sublist contains less-used pages, which are candidates for eviction.

with that in mind, here are some of the more important fields in the innodb standard monitor output:

old database pages : the number of pages in the old sublist of the buffer pool.
pages made young, not young : the number of old pages that were moved to the head of the buffer pool (the new sublist), and the number of pages that have remained in the old sublist without being made new.
youngs/s non-youngs/s : the number of accesses to old pages that have resulted in making them young or not. this metric differs from that of the previous item in two ways. first, it relates only to old pages. second, it is based on number of accesses to pages and not the number of pages. (there can be multiple accesses to a given page, all of which are counted.)
young-making rate : hits that cause blocks to move to the head of the buffer pool.
not : hits that do not cause blocks to move to the head of the buffer pool.

the young-making rate and not rate will not normally add up to the overall buffer pool hit rate.

some mysql performance tuning tips

if you see very low youngs/s values when you do not have large scans going on, that may be an indication that you have to either reduce the delay time for a block to be moved from the old to the new sublist, or increase the percentage of the buffer pool used for the old sublist.

if you do not see a lot of non-youngs/s when you are doing large table scans (and lots of youngs/s), try tuning your delay value to be larger.

the innodb_old_blocks_time global variable specifies how long in milliseconds (ms) a page inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. if the value is 0, a page inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. if the value is greater than 0, pages remain in the old sublist until an access occurs at least that many milliseconds after the first access. for example, a value of 1000 causes pages to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.

the following statement sets the innodb_old_blocks_time to zero:

set global innodb_old_blocks_time = 0; 

the innodb_old_blocks_pct global variable specifies the approximate percentage of the buffer pool that innodb uses for the old block sublist. increasing the old sublist percentage makes it larger, so blocks in that sublist take longer to move to the tail and be evicted. this increases the likelihood that they will be accessed again and be made young. the range of values is 5 to 95. the default value is 37 (that is, 3/8 of the pool).

when scanning small tables that do fit into memory, there is less overhead for moving pages around within the buffer pool, so you can leave innodb_old_blocks_pct at its default value, or even higher, such as innodb_old_blocks_pct=50 .

there are many other global status variables you can examine in addition to innodb_old_blocks_time and innodb_old_blocks_pct :

show global status like 'innodb_buffer_pool%';
variable_namevalue
-----------------------------------------------------------
innodb_buffer_pool_dump_status, not started
innodb_buffer_pool_dump_status        not started
innodb_buffer_pool_load_status        not started
innodb_buffer_pool_pages_data460
innodb_buffer_pool_bytes_data7536640
innodb_buffer_pool_pages_dirty        0
innodb_buffer_pool_bytes_dirty        0
innodb_buffer_pool_pages_flushed1
innodb_buffer_pool_pages_free7730
innodb_buffer_pool_pages_misc2
innodb_buffer_pool_pages_total        8192
innodb_buffer_pool_read_ahead_rnd0
innodb_buffer_pool_read_ahead0
innodb_buffer_pool_read_ahead_evicted        0
innodb_buffer_pool_read_requests15397
innodb_buffer_pool_reads461
innodb_buffer_pool_wait_free0
innodb_buffer_pool_write_requests1

of these, some metrics will be more useful to you than others. standouts include:

metrics tracking the total size of the buffer pool
how much is in use
how effectively the buffer pool is serving reads

the metrics innodb_buffer_pool_read_requests and innodb_buffer_pool_reads are integral to gauging buffer pool utilization. innodb_buffer_pool_read_requests are number of request to read a row from the buffer pool and innodb_buffer_pool_reads is the number of times innodb has to perform read data from disk to fetch required data pages. reading from memory is generally much faster than reading from disk, so keep an eye out for increasing innodb_buffer_pool_reads numbers!

buffer pool efficiency can be calculated using the formula:

innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001 

here’s an example:

mysql> show global status like 'innodb_buffer_pool_rea%'; 
variable_name      value
-----------------------------------------------
innodb_buffer_pool_read_requests  | 2905072850 | 
innodb_buffer_pool_reads          | 1073291394 | 

calculating the innodb buffer pool efficiency:

(107329139 / 2905072850 * 100) = 37

in this particular case, innodb is doing more disk reads, so the innodb buffer pool is not large enough.

buffer pool utilization is another useful metric to check. the utilization metric is not available as such but can be easily calculated as follows:

(innodb_buffer_pool_pages_total - innodb_buffer_pool_pages_free)
----------------------------------------------------------------
                   innodb_buffer_pool_pages_total 

here’s an example:

show global status like 'innodb_buffer_pool_pages%';
variable_name      value
-----------------------------------------------
innodb_buffer_pool_pages_data460
innodb_buffer_pool_pages_dirty        0
innodb_buffer_pool_pages_flushed1
innodb_buffer_pool_pages_free7730
innodb_buffer_pool_pages_misc2
innodb_buffer_pool_pages_total        8192 

plugging the numbers into our formula:

(8192 – 7730) / 8192 = 0.056396484375 

we can convert that into a percentage by multiplying by 100:

0.056396484375 * 100 = 5.64%  (quite low!) 

the fact that your database is serving a large number of reads from disk while the buffer pool is near empty is not in itself cause for celebration; it may be that your cache has recently been cleared and is still in the process of refilling. however, should this condition continues for an extended amount of time, it’s very likely that there is plenty of memory to accommodate your dataset.

high buffer pool utilization is not necessarily a bad thing either, as long as old data is being aged out of the cache according to the lru policy.

only when read operations are overpowering the buffer pool, should you start to seriously think about scaling up your cache.

converting buffer pool metrics to bytes

most buffer pool metrics are reported as a count of memory pages, which is not all that useful. luckily, page counts can be converted to bytes, which makes it a lot easier to determine the actual size of your buffer pool. for instance, this simple formula gives us the total size of buffer pool in bytes:

innodb_buffer_pool_pages_total * innodb_page_size 

the innodb_page_size can be retrieved using a show variables query:

show variables like "innodb_page_size" 

the most useful buffer pool metrics at a glance:

conclusion

in this final installment of two-part blog series, we learned how to capture and study mysql metrics that provide the most value for the effort. the main points covered include:

client connection requests on the network interfaces that the server listens to are handled by the connection manager.
it’s important to monitor your client connections because, once the database server runs out of available connections, new client connections are refused!
every time a client is unable to connect, the server increments the aborted_connects status variable.
fine-grained connection metrics such as connection_errors_max_connections and connection_errors_internal can be instrumental in pinpointing the source of connection problems.
mysql’s default storage engine, innodb, uses a special storage area called the buffer pool to cache data for tables and indexes.
buffer pool metrics are categorized as resource metrics.
you can configure various aspects of the innodb buffer pool to improve performance.
innodb standard monitor output provides a number of metrics pertaining to operation of the innodb buffer pool.
the least recently used (lru) algorithm employs a “midpoint insertion strategy” that treats the pages as “old” and “new” sublists.
the lru algorithm can be tuned using the innodb_old_blocks_time and innodb_old_blocks_pct global variables.

by tracking the most useful metrics and reviewing them in the most informative way(s), we strike a balance between over-monitoring and firefighting unforeseen crises.

What is a mutex anyway?

2022-04-08T20:02:00.002+05:30

What is a mutex anyway?

While I was working on the MySQL Workbench booth at MySQL Connect this year, an attendee came up and asked what a mutex was and how important it is to them in their function as a DBA.

It’s a good question. The short answer is:

Mutexes are mutually exclusive locks and are designed to prevent concurrent access to resources that in doing so may result in unsafe conditions.

They are required as part of virtually all multi-threaded applications (not just MySQL), and are not something that you can specifically prevent from happening. This is a different concept to row-locks and table locks.

Some examples

If the InnoDB buffer pool is full, internally free space needs to be created in order to be able to load your desired page into memory. What you don’t want, is in between freeing and loading somebody else to take your desired free-slot.

When a page becomes too full, it may need to split data into other pages. It is possible that there could be a thread deadlock if two pages left and right of each-other reach this condition at once and can’t decide who should proceed first.

Should you care as a DBA?

Yes, and no.

No in the sense that you are not expected to fix these problems, they are usually very internal to the operations of the database and require a MySQL developer to fix them.

Yes in the sense that newer MySQL versions often change the way internal locking works if a mutex becomes hot for a large number of users. Hot is the terminology used to describe a mutex which has other threads blocked waiting for it for a large amount of time. This contention reduces scalability on multi-core machines because some threads sit idle, essentially waiting for the ablity to run.

Hot Mutex History

Here is an incomplete list of some of the mutexes worked on in newer versions of MySQL:

MySQL 5.7:

Index lock contention is reduced via introduction of a new SX lock mode. This improves performance of large tables.
The LOCK_thread_count mutex protected several independent internal server structures and variables, and was a bottleneck, particularly affecting server performance in the circumstance when many clients were connecting and disconnecting at once.

MySQL 5.6:

The kernel mutex is split into smaller, localized locks. This mutex previous covered many different internal tasks to InnoDB.
LOCK_open mutex is split – this mutex used to be a mutex to protect the metadata of a table within the MySQL Server.
The default configuration was changed, so that InnoDB has 8 buffer pool instances when the buffer pool is > 1GB.

MySQL 5.5:

The buffer pool mutex is split into multiple buffer pool instances. This improves scalability by hashing a page to a particular buffer pool.
The adaptive hash index can be disabled to reduce contention.
Initial work to reduces LOCK_open mutex impact is introduced via metadata locking. This was improved further in MySQL 5.6.
InnoDB was changes to allow multiple rollback segments, which increases the number of concurrent changes that can be made at once.
The log sys mutex was broken down into separate localized mutexes.
The list of dirty pages (flush list) is protected by its own mutex to prevent blocking other buffer pool operations.

How can you see if a mutex is hot?

Various metadata commands can expose mutexes that are hot. For example: SHOW ENGINE INNODB STATUS, SHOW ENGINE INNODB MUTEX and PERFORMANCE_SCHEMA. Operating specific tools can also be used, such as oprofile or Linux Perf.

Here is an example from SHOW ENGINE INNODB MUTEX (via dimitrik):

mysql> show engine innodb mutex;
+--------+----------------------------+-------------------+
| Type   | Name                       | Status            |
+--------+----------------------------+-------------------+
| InnoDB | log/log0log.c:775          | os_waits=26       |
| InnoDB | log/log0log.c:771          | os_waits=1        |
| InnoDB | buf/buf0buf.c:1208         | os_waits=3219     |
| InnoDB | buf/buf0buf.c:1208         | os_waits=6990     |
| InnoDB | buf/buf0buf.c:1208         | os_waits=4619     |
| InnoDB | buf/buf0buf.c:1208         | os_waits=5627     |
| InnoDB | buf/buf0buf.c:1208         | os_waits=7873     |
| InnoDB | buf/buf0buf.c:1208         | os_waits=4466     |
| InnoDB | buf/buf0buf.c:1208         | os_waits=16929    |
| InnoDB | buf/buf0buf.c:1208         | os_waits=19305    |
| InnoDB | buf/buf0buf.c:1208         | os_waits=16301962 |
| InnoDB | buf/buf0buf.c:1208         | os_waits=11649    |
| InnoDB | buf/buf0buf.c:1208         | os_waits=950471   |
| InnoDB | buf/buf0buf.c:1208         | os_waits=6545     |
| InnoDB | buf/buf0buf.c:1208         | os_waits=4262     |
| InnoDB | buf/buf0buf.c:1208         | os_waits=5642     |
| InnoDB | buf/buf0buf.c:1208         | os_waits=7878     |
| InnoDB | buf/buf0buf.c:1208         | os_waits=387166   |
| InnoDB | fil/fil0fil.c:1559         | os_waits=1265     |
| InnoDB | srv/srv0srv.c:987          | os_waits=460452   |
| InnoDB | combined buf/buf0buf.c:900 | os_waits=38503    |
| InnoDB | log/log0log.c:832          | os_waits=184      |
| InnoDB | combined buf/buf0buf.c:901 | os_waits=77       |
+--------+----------------------------+-------------------+
23 rows in set (0.56 sec)

Here is an example of SHOW ENGINE INNODB STATUS (via the MySQL forums):

..
---------- 
SEMAPHORES 
---------- 
OS WAIT ARRAY INFO: reservation count 334026866, signal count 322155474 
--Thread 139787989395216 has waited at ../../../storage/innobase/trx/trx0undo.c line 1684 for 165.00 seconds the semaphore: 
Mutex at 0x7f23518408c0 created file ../../../storage/innobase/trx/trx0rseg.c line 147, lock var 1 
waiters flag 1 
--Thread 139788009264912 has waited at ../../../storage/innobase/btr/btr0cur.c line 463 for 165.00 seconds the semaphore: 
S-lock on RW-latch at 0x7f230fbd2b48 created in file ../../../storage/innobase/buf/buf0buf.c line 550 
a writer (thread id 139787989395216) has reserved it in mode exclusive 
number of readers 0, waiters flag 1 
Last time read locked in file ../../../storage/innobase/btr/btr0cur.c line 463 
Last time write locked in file ../../../storage/innobase/btr/btr0cur.c line 463 
--Thread 139787997423376 has waited at ../../../storage/innobase/trx/trx0roll.c line 921 for 15.00 seconds the semaphore: 
Mutex at 0x7f23518408c0 created file ../../../storage/innobase/trx/trx0rseg.c line 147, lock var 1 
waiters flag 1 
--Thread 139788384470800 has waited at ../../../storage/innobase/btr/btr0cur.c line 463 for 15.00 seconds the semaphore: 
X-lock on RW-latch at 0x7f230f558038 created in file ../../../storage/innobase/buf/buf0buf.c line 550 
a writer (thread id 139788006655760) has reserved it in mode exclusive 
number of readers 0, waiters flag 1 
Last time read locked in file ../../../storage/innobase/btr/btr0cur.c line 463 
Last time write locked in file ../../../storage/innobase/btr/btr0cur.c line 463 
..

And one for PERFORMANCE_SCHEMA (via Mark Leith):

SELECT EVENT_NAME,
 SUM_TIMER_WAIT/1000000000 WAIT_MS,
 COUNT_STAR
FROM performance_schema.EVENTS_WAITS_SUMMARY_GLOBAL_BY_EVENT_NAME 
ORDER BY SUM_TIMER_WAIT DESC, COUNT_STAR DESC LIMIT 30;
+---------------------------------------------------------+-------------+------------+
| EVENT_NAME                                              | WAIT_MS     | COUNT_STAR |
+---------------------------------------------------------+-------------+------------+
| wait/io/file/innodb/innodb_data_file                    | 150562.0345 |     182750 |
| wait/io/file/innodb/innodb_log_file                     |  77795.5425 |       8913 |
| wait/synch/rwlock/innodb/checkpoint_lock                |  11636.7894 |       1284 |
| wait/synch/rwlock/innodb/btr_search_latch               |   7429.2204 |   39677465 |
| wait/io/file/myisam/kfile                               |   7353.2737 |       2011 |
| wait/synch/mutex/innodb/kernel_mutex                    |   4115.0534 |   59198510 |
| wait/synch/mutex/innodb/fil_system_mutex                |   3473.8341 |    1263809 |
| wait/io/file/myisam/dfile                               |   2940.5701 |        641 |
| wait/synch/mutex/innodb/buf_pool_mutex                  |   2650.8666 |   33982979 |
| wait/synch/mutex/innodb/rw_lock_mutex                   |   1261.8025 |   18877546 |
| wait/io/file/sql/FRM                                    |    116.6419 |        863 |
| wait/synch/cond/sql/COND_thread_count                   |      1.4073 |          1 |
| wait/io/file/sql/pid                                    |      1.2654 |          3 |
| wait/synch/mutex/innodb/mutex_list_mutex                |      0.7675 |      16727 |
| wait/synch/mutex/innodb/rw_lock_list_mutex              |      0.4023 |       8331 |
| wait/io/file/sql/dbopt                                  |      0.2745 |         12 |
| wait/io/file/sql/casetest                               |      0.2041 |          5 |
| wait/synch/mutex/innodb/thr_local_mutex                 |      0.2009 |       2050 |
| wait/synch/mutex/mysys/THR_LOCK_open                    |      0.1993 |        989 |
| wait/synch/rwlock/innodb/trx_purge_latch                |      0.1436 |        255 |
| wait/io/file/sql/ERRMSG                                 |      0.1432 |          5 |
| wait/synch/rwlock/sql/LOCK_grant                        |      0.1375 |        188 |
| wait/synch/rwlock/sql/MDL_lock::rwlock                  |      0.1013 |        481 |
| wait/synch/mutex/sql/Query_cache::structure_guard_mutex |      0.0923 |        628 |
| wait/synch/mutex/mysys/THR_LOCK_myisam                  |      0.0781 |        283 |
| wait/synch/rwlock/sql/Query_cache_query::lock           |      0.0676 |        198 |
| wait/io/file/mysys/charset                              |      0.0561 |          3 |
| wait/synch/mutex/sql/LOCK_open                          |      0.0529 |        543 |
| wait/synch/mutex/mysys/THR_LOCK_lock                    |      0.0504 |        244 |
| wait/synch/mutex/innodb/srv_innodb_monitor_mutex        |      0.0424 |         75 |
+---------------------------------------------------------+-------------+------------+
30 rows in set (0.00 sec)

Troubleshooting MySQL Performance Issues

2022-04-08T20:01:00.002+05:30

Troubleshooting MySQL Performance Issues

Despite your very best efforts, as tables increase in size and more and more users come online, sluggish MySQL performance eventually rears its ugly head. When that happens, you may be tempted to shrug it off as part of doing business. Don’t. By understanding the mechanisms behind database slowdowns and by methodically attacking the problem, you can home in on the culprit(s) and remedy the issues in a timely manner, before your business suffers substantial losses as a result.

The purpose of this blog is to assist you in determining the cause(s) of MySQL database performance issues using a couple of built-in tools.

Why Database Performance Slows Down

It’s no secret that database performance tends to degrade over time. While it’s easy to point the finger at the number of concurrent users, table scans, and growing tables, the reality is more complex than that. The most common reason for slow database performance is based on this “equation”:

(number of users) x (size of database) x (number of tables/views) x (number of rows in each table/view) x (frequency of updates) x (processor) x (network latency)

While the above equation does not include every possible factor, it does address the most common ones.

The Importance of Following a Monitoring Plan

Although this paper is on MySQL troubleshooting, all of the preventative database best practices also apply to MySQL. Hence, the best defense remains a good offense. Regarding MySQL database management, that means regularly monitoring your database’s performance via key metrics. A sound database monitoring plan can help you stay on top of:

Performance: By tracking performance-related metrics like query completion times you could spot any underperformers and come up with possible fixes.
Growth: Observe changes in terms of users and traffic. Database usage needs can evolve very quickly and lead to gridlock.
Security: Ensure that adequate security measures are applied.

Although following the above advice won’t eliminate performance issues, it may provide a better heads-up to issues as they develop.

Some Root Causes of Performance Issues

Getting to the root of database performance issues is a lot easier when you know what to look for. Over the years, many of the most common issues that plague relational database systems have been well documented for posterity. It turns out that the most common reasons for slow database performance are related to one or more issues in four broad areas:

Hardware/OS/Network-related causes:
- Limited amount of available disk space and/or memory on the server or clients’ machines.
- Out of date/obsolete Network Interface Cards (NIC) or other server/client hardware.
- Inappropriate OS (operating system) for running server or client applications.
- All sorts of malware.
- Network issues, excessive network traffic, network bottlenecks, or database traffic.
Native and 3rd party application interference:
- Scheduled jobs – backup, maintenance, AV-scan, application and system updates.
- Other applications and (3rd party) software installed on the machine. Remember that all programs launched at startup are always running in the background.
Database Design:
- Large number of views in a database with queries using expensive subqueries and poor indexing of tables can use extensive memory and cause swapping.
- Some database properties can slow performance while others improve performance. Be careful when choosing character sets and datatypes in particular.
- Configuration of server and application framework does not allow access to all system resources or require more resources than available. Both will reduce performance.
- Make sure that there are no database admin utilities such as backup, synchronization, or compression are running during peak hours.
- Large attachments (BLOBS) in databases.
- Inefficient coding/programming of custom applications. Often it will be possible have application logic in the application or in Stored Procedures, Triggers etc. Choose the right approach for the task. Database corruption and fragmentation (could also be paired with other symptoms including incorrect results from some queries). Consider to run some check/repair/defragmentation routine periodically.

Database-related Performance Issues

It can be surprising how many database issues stem from within the database itself. From over normalization of tables to improper indexing, to poorly constructed queries, there are many flaws that are only exposed once a certain usage threshold has been reached.

Luckily, MySQL comes equipped with a couple of excellent tools to help get at the root cause of slow MySQL performance, namely the Slow Log and Performance Schema.

The MySQL Slow Query Log

The most common internal cause of database slowdowns are queries that monopolise system resources. Factors that contribute to poor query performance include inadequate indexing, fetching a data set that is very large, complex joins, and text matching.

The slow query log is meant to identify queries that would make good candidates for optimization. It consists of SQL statements that took more than a certain number of seconds to execute.

The default value of ten seconds is stored in the long_query_time system variable. You can check its value by selecting it (note the two ‘@’ characters):

mysql> select @@long_query_time

+-------------------+

| @@long_query_time |

+-------------------+

|         10.000000 |

+-------------------+

Ten seconds is probably too long as a cut-off for most production settings. To change it, use the SET statement:

mysql> SET @@long_query_time = 5

You can also log queries that require MySQL to examine more than a certain number of rows to the slow query log. That value is defined in the min_examined_row_limit system variable. It only includes the time to resolve the query and not the time to acquire locks. If you haven’t changed it, it will contain a value of zero:

mysql> select @@ min_examined_row_limit;

+--------------------------+

| @@ min_examined_row_limit |

+--------------------------+

|         0                |

+--------------------------+

To modify it, use the SET statement. It can contain any Integer between 0 and 4294967295 on 32-bit platforms and 18446744073709551615 on 64-bit platforms:

mysql> SET @@min_examined_row_limit = 500000;

It is important to note that the slow query log is disabled by default. This is because logging can place a bit of a drag on performance. Therefore, it’s best to enable the Slow Query log (preferably from MySQL configuration file) for a short period of time, e.g. 2-3 days, when your application performance is reduced for some reason and you wish to detect the slow queries.

You can check if the MySQL slow query log is enabled using the following statement:

SHOW VARIABLES Like 'slow_query_log%';

Variable_name Value
---------------------------------
slow_query_log OFF

slow_query_log_file   C:\Program Files\MySQL\MySQL Server 5.6\data\LH12CU19884-slow.log

Follow these steps to enable the Slow Query Log:

Shut down any applications that are using MySQL.
Shut down MySQL itself.
Add the following configuration options to my.ini or my.cnf (see “Appendix 1: Where is the MySQL Configuration file located?” section for more information):

# What's the threshold for a slow query to be logged?

long_query_time=0.5

 

# Where should the queries be logged to?

slow_query_log_file=/path/to/logfile

 

# Enable slow query logging - note the dashes rather than underscores:

slow-query-log=1

Save the file, and restart MySQL.
Restart your connected applications.

A professional MySQL monitoring tool should also provide access to the Slow Query Log and its parameters:

MySQL logging information in Monyog

Viewing the Slow Query Log

MySQL provides a tool called mysqldumpslow which can be used to analyze the log file.

The following syntax will show you the top 10 queries sorted by average query time (Remember to update the file names and paths to suit your environment):

mysqldumpslow -t 10 mysql-slow-query.log > mysqldumpslow.out

Here is some sample output:

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log

Count: 90  Time=56.73s (6183s) Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost

select * from t1

The important values to look at here are the Count and the Time. The Count is the number of times this query ran within your log set. The Time is an average amount of time for each of those queries runs to complete. With the number in parentheses, in this case 6183s, being the total (Count x Time) amount of time spent on running this query.

GUI tools like Monyog can greatly simplify analyzing the MySQL Slow Query Log by showing detailed reports:

Analysis of MySQL Slow Query Log in Monyog

The Performance Schema

The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. It provides details about server execution in structured way that is accessible via regular SQL. Moreover, the PERFORMANCE_SCHEMA is a storage Engine that is only used for special tables in the performance_schema database. That database contains 52 tables and no views, comprising of a mix of tables that encompass the following categories:

Configuration Tables
Object Tables
Current Tables
History Tables
Summary Tables
Other Tables

The PERFORMANCE_SCHEMA can be utilized to troubleshoot:

Server bottlenecks, caused by locks, mutexes, IO
Less-than optimal statements
Most expensive operations
Connection issues
Memory usage
Replication failures
More…

Configuration

Starting from MySQL 5.6.6 the Performance Schema is enabled by default. It contains instruments, which are the things you can measure, and consumers which are those that use the measurements. Not all instruments and consumers are enabled out of the box, so to ensure we have everything enabled, a few options have been added to the MySQL configuration file. See the MySQL docs for more information on configuring the Performance Schema.

Long-running Processes

The Performance Schema keeps track of events that take time as instruments waits.

These tables store wait events:

events_waits_current: Current wait events
events_waits_history: The most recent wait events per thread
events_waits_history_long: The most recent wait events globally (across all threads)

Once the performance schema is enabled, it will collect metrics on all the statements executed by the server. Many of those metrics are summarized in the events_statements_summary_by_digest table, available in MySQL 5.6 and later.

Metrics on query volume, latency, errors, time spent waiting for locks, index usage, and more are available for each normalized SQL statement executed. (Normalization here means stripping data values from the SQL statement and standardizing whitespace.)

This query finds the top 10 statements by longest average run time:


SELECT substr(digest_text, 1, 50) AS digest_text_start

     , count_star

     , avg_timer_wait 

  FROM performance_schema.events_statements_summary_by_digest 

 ORDER BY avg_timer_wait DESC

LIMIT 10;

digest_text_start  count_star avg_timer_wait
-------------------------------------------------------------------------------------

SHOW FULL TABLES FROM `sakila` 				1		1110825767786

SHOW GLOBAL STATUS LIKE ? 				1		1038069287388

SELECT `digest_text`, `count_star`, `avg_timer_w	1		945742257586

SHOW FIELDS FROM `sakila` . `actor` 			1		611721261340

SELECT `digest_text` , `count_star` , `avg_timer_w	2		335116484794

SHOW FIELDS FROM `sakila` . `actor_info` SELECT `a	1		221773712160

SELECT NAME , TYPE FROM `mysql` . `proc` WHERE `Db	2		148939688506

SHOW FIELDS FROM `vehicles` . `vehiclemodelyear` 	1		144172298718

SHOW SCHEMAS 						2		132611131408

SHOW FIELDS FROM `sakila` . `customer` 			1		99954017212

Event Timer Units

Performance Schema displays event timer information in picoseconds (trillionths of a second) to normalize timing data to a standard unit. In the following example, TIMER_WAIT values are divided by 1000000000000 to show data in units of seconds. Values are also truncated to 6 decimal places.

digest_text_start  count_star avg_timer_wait
----------------------------------------------------------------------------------------


SHOW FULL TABLES FROM `sakila` 				        1		1.110825

SHOW GLOBAL STATUS LIKE ? 					1		1.038069

SELECT `digest_text`, `count_star`, `avg_timer_w		1		0.945742

etc.

Unused Indexes

The table_io_waits_summary_by_index_usage table may be employed, not only to aggregate operations per index, but also to aggregate how many operations did not use indexes when accessing the tables. This may be accomplished by including the “INDEX_NAME column is NULL” criteria in the WHERE clause. Here’s a query that produces a result set of two rows where one table’s index is being used and another’s is not:

SELECT object_schema AS schema_name, 

       object_name   AS table_name, 

       index_name, 

       count_fetch

FROM performance_schema.table_io_waits_summary_by_index_usage

WHERE count_fetch > 0;

schema_name     table_name  	index_name				count_fetch
-----------------------------------------------------------------------------------
vehicles	vehiclemodelyear    U_VehicleModelYear_year_make_model	    7273

sakila	film		   (null)				   1001

Long-running Queries

Being one of the most common causes of slow database performance, long-running queries should be at the top of your list of suspects whenever slowdowns occur.

The events_statements_history_long table contains a lot of indicative fields on the subject of wait times and their corresponding statements. The following query compiles some useful calculations on wait times to provide a clear picture of which statements are taking the longest to execute:

SELECT left(digest_text, 64) AS digest_text_start

     , ROUND(SUM(timer_end-timer_start)/1000000000, 1) AS tot_exec_ms

     , ROUND(SUM(timer_end-timer_start)/1000000000/COUNT(*), 1) AS avg_exec_ms

     , ROUND(MAX(timer_end-timer_start)/1000000000, 1) AS max_exec_ms

     , ROUND(SUM(timer_wait)/1000000000, 1) AS tot_wait_ms

     , ROUND(SUM(timer_wait)/1000000000/COUNT(*), 1) AS avg_wait_ms

     , ROUND(MAX(timer_wait)/1000000000, 1) AS max_wait_ms

     , ROUND(SUM(lock_time)/1000000000, 1) AS tot_lock_ms

     , ROUND(SUM(lock_time)/1000000000/COUNT(*), 1) AS avglock_ms

     , ROUND(MAX(lock_time)/1000000000, 1) AS max_lock_ms

     , COUNT(*) as count  

 FROM events_statements_history_long

  JOIN information_schema.global_status AS isgs

 WHERE isgs.variable_name = 'UPTIME'

 GROUP BY LEFT(digest_text,64)

 ORDER BY tot_exec_ms DESC;

digest_text_start                  avg_exec_ms    tot_wait_ms     max_wait_ms    avglock_ms      count

                            tot_exec_ms     max_exec_ms     avg_wait_ms     tot_lock_ms     max_lock_ms

--------------------------------------------------------------------------------------------------------- 

SELECT * FROM `sakila` . `rent	240.3	240.3	240.3	240.3	240.3	240.3	0.0	0.0	0.0	1

SELECT * FROM `sakila` . `film	56.6	56.6	56.6	56.6	56.6	56.6	0.0	0.0	0.0	1

UPDATE `performance_schema` . 	49.8	49.8	49.8	49.8	49.8	49.8	42.0	42.0	42.0	1

SELECT `st` . * FROM `performa	12.7	2.1	11.9	12.7	2.1	11.9	13.0	2.2	12.0	6

SELECT `st` . * FROM `performa	2.6	0.4	0.5	2.6	0.4	0.5	2.0	0.3	1.0	6

SELECT `st` . * FROM `performa	0.8	0.1	0.2	0.8	0.1	0.2	0.0	0.0	0.0	6

SHOW INDEX FROM `sakila` . `fi	0.8	0.8	0.8	0.8	0.8	0.8	0.0	0.0	0.0	1

SHOW INDEX FROM `sakila` . `re	0.6	0.6	0.6	0.6	0.6	0.6	0.0	0.0	0.0	1

Here’s a way to find a long-running query using the threads table:

SELECT PROCESSLIST_ID AS id, 

        PROCESSLIST_USER AS user, 

        PROCESSLIST_HOST AS host, 

        PROCESSLIST_DB AS db, 

        PROCESSLIST_COMMAND AS command, 

        PROCESSLIST_TIME AS time, 

        PROCESSLIST_STATE AS state, 

        LEFT(PROCESSLIST_INFO, 80) AS info

 FROM performance_schema.threads

 WHERE PROCESSLIST_ID IS NOT NULL

 AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')

 ORDER BY PROCESSLIST_TIME ASC;
 
Id  user  host	     db		         command  time  state		info

------------------------------------------------------------------------------------------------------------------

2   root  localhost  sakila	Query	   0	   Creating sort index	SELECT PROCESSLIST_ID AS id,PROCESSLIST_USER…

Memory Usage

Want to quickly ascertain how much RAM does a server have? Available since version 5.7, the sys tables provide detailed information about memory usage of each server’s allocated internal memory:

SELECT * FROM sys.memory_global_total; 

total_allocated 

---------------

458.44 MiB

This final example fetches memory utilization by thread:

SELECT thread_id, 

       user, 

       current_avg_alloc, 

       current_allocated

FROM sys.memory_by_thread_by_current_bytes

WHERE thread_id IN (145, 146)\G 

********************** 1. row ********************** 

thread_id: 145 

user: sql/slave_io 

current_allocated: 1.04 GiB 

current_avg_alloc: 2.64 KiB 

********************** 2. row ********************** 

thread_id: 146 

user: sql/slave_sql 

current_allocated: 1.79 MiB 

current_avg_alloc: 72 bytes 

2 rows in set (0.11 sec)

Conclusion

Troubleshooting MySQL database issues can help in reducing slowdowns and downtime. In this aspect, Slow Query Log and Performance Schema can be a major contributor in identifying key performance issues.

In this blog, we demonstrated how slow query log is meant to identify queries that would make good candidates for optimization and how Performance Schema keeps track of long-running processes, unused indexes, memory usage, and other metrics can be helpful in troubleshooting sluggish MySQL performance.

As tables increase in size and more and more users come online, sluggish database performance is bound to happen sooner or later. By understanding the mechanisms behind MySQL slowdowns and by employing MySQL’s built-in tools, you can home in on the culprit(s) and remedy the issues in a timely manner.

Appendix 1: Where is the MySQL Configuration file located?

The location of the MySQL configuration file (either my.ini or my.cnf) depends on your OS.

MySQL will look at each location in order, and use the first file that it finds. Often, if the file does not exist, it must be created first.

Linux based MySQL systems will use configuration files in the following order of precedence

/etc/my.cnf
/etc/mysql/my.cnf
SYSCONFDIR/my.cnf
$MYSQL_HOME/my.cnf

SYSCONFDIR refers to the directory specified when MySQL was built; typically reverse to the etc directory located under the compiled-in installation directory.

MYSQL_HOME is an environment variable referring to the path where my.cnf can be found.

Windows-based MySQL systems will use the configuration files in the following order of precedence

%PROGRAMDATA%\MySQL\<MySQL Server Version>\my.ini
%PROGRAMDATA%\MySQL\<MySQL Server Version>\my.cnf
%WINDIR%\my.ini
%WINDIR%\my.cnf
C:\my.ini
C:\my.cnf
INSTALLDIR\my.ini
INSTALLDIR\my.cnf

Top Metrics to Monitor in Your MySQL Databases (Part 1)

2022-04-08T19:56:00.003+05:30

As tables increase in size and more and more users come online, it becomes increasingly necessary to fine-tune your database server from time to time. The secret to knowing what adjustments need to be made is to perform regular monitoring. Most databases offer dozens — if not hundreds — of performance metrics that you can assess.

In part one, we’ll:

Examine the benefits of performance monitoring.
Outline the main performance metric categories.
List the monitoring tools provided by MySQL:
- Server variables
- Performance schema
- Sys schema
Learn how to monitor:
- Transaction throughput
- Query execution performance

Benefits of Following a Monitoring Plan

Most applications are backed by a database, including MySQL. To keep your databases and applications running smoothly, it’s crucial to monitor them effectively. A good database monitoring plan can help you stay on top of:

Performance: The main subject of this article, monitoring your database’s performance, can help detect possible bottlenecks and other issues before catastrophe strikes. Beyond helping you avert emergencies, performance metrics can assist you in deciding whether a performance increase is necessary or feasible. For instance, by keeping a track record of query execution times, you could spot any suboptimal SQL statements and come up with possible improvements.
Growth: Observe requirements in terms of users and traffic. Database usage needs tend to evolve faster than expected.
Security: Ensure adequate security measures have been applied.

Database Metric Types

Before we go through the process of identifying metrics to follow, perhaps we should start at the beginning and ask, “what are metrics?”

Metrics capture a value pertaining to your systems at a specific point in time; for example, the number of users currently logged into the database.

Therefore, metrics are usually collected at regular intervals, such as once per second, one per minute, etc., in order to monitor a system over time.

There are two important categories of metrics: those that are most useful in identifying problems and those whose primary value is in investigating problems. This article covers which data to collect so that you can:

Recognize potential problems before they occur.
Quickly investigate and get to the bottom of performance issues.

Beyond metrics, there are other types of database monitoring that will not be addressed here. These include the monitoring of events and security.

How Often Should Monitoring Be Performed?

How often you monitor different aspects of your database depends on how mission-critical it and the application(s) that it supports are. If a failure or disruption will result in serious impact on your business operations and/or organization, or perhaps even result in catastrophe, then you need to be on top of both performance and security issues at all times. Luckily, you can reduce your need to constantly monitor your performance dashboard to a weekly inspection by setting up alerts to inform you of critical issues in real-time.

Database performance metrics should be monitored by specialized tools that provide real-time and/or periodic alerts. Real-time alerts are a must for mission-critical databases or databases with sensitive information that is susceptible to attack so that you can take care of urgent issues as soon as they occur. Real-time preventive measures can protect your database from certain types of attacks, even before you have time to respond.

The DBAs, IT operations staff, and users have shared responsibility in performance monitoring since some factors that affect database performance lie beyond the database itself. It also makes sense to include some of the app developers in the loop so that they can investigate the application side of things.

Although DBAs do not need to monitor the applications that interact with the database, it’s important that they possess a general understanding of how applications are implemented and their architecture.

Performance Metric Categories

The previous section described the two main uses of database metrics: problem identification and problem investigation. Likewise, there are two important categories of metrics that pertain to performance: work metrics and resource metrics. For each system that is part of your software infrastructure, consider which work metrics and resource metrics are applicable and available, and collect whatever you can. Even though you won’t need to monitor every metric, some may play a greater role once you’ve identified performance issues, i.e. during problem investigation.

The next two sections cover each of the two performance metric types in more detail.

Work Metrics

Work metrics gauge the top-level health of your database by measuring its useful output. Work metrics may be broken down into four subtypes:

Throughput: The amount of work the system is doing per unit of time. Throughput is usually recorded as an absolute number. Examples include the number of transactions or queries per second.
Success: Represents the percentage of work that was executed successfully, i.e. the number of successful queries.
Error: Captures the number of erroneous results, usually expressed as a rate of errors per unit of time. This yields errors per unit of work. Error metrics are often captured separately from success metrics when there are several potential sources of error, some of which are more serious or actionable than others.
Performance: Quantifies how efficiently a component is doing its work. The most common performance metric is latency, which represents the time required to complete a unit of work. Latency can be expressed as an average or as a percentile, such as “99% of requests returned within 0.1s.”

The above metrics provide high-level but telling data that can help you quickly answer the most important questions about a system’s internal health and performance. That is to say:

Is the database available and effectively doing what it was designed to do?
How fast is it producing work?
What is the quality of that work?

Resource Metrics

Resources are hardware, software, and network components that are required by the database to do its job. Some resources are low-level, such as physical components like CPU, memory, disks, and network interfaces. Higher-level resources such as the query cache and database waits can also be considered a resource and therefore monitored.

Resource metrics are useful in helping you reconstruct a detailed picture of the database’s state, making them especially valuable for investigation and diagnosis of problems. Resource metrics cover four key areas:

Utilization: The percentage of time that the database is busy, or the percentage of the database’s capacity that is in use.
Saturation: A measure of the amount of requested work that the database cannot yet service, and waits in the queue.
Errors: Represents internal errors that may or may not be observable in the database’s output.
Availability: Denotes the percentage of time that the database responded to requests.

Monitoring Performance

Both work and resource metrics include two other types of metrics:

Work metrics:
- Database/transaction/query throughput
- Query execution performance
Resource metrics:
- Connections
- Buffer pool usage

The next several sections will focus on some of the best work metrics to monitor. Resource metrics will be covered in Part 2.

Throughput

In general terms, throughput measures the speed of a database system and is typically expressed as a number of transactions per second. Having said that, consider the following differences:

“Write” transactions vs. “read” transactions
Sustained rates vs. peak rates
A 10-byte row vs. a 1000-byte row

Due to these differences, it is best to measure:

Database throughput (the database as a whole)
Transaction throughput (any operation)
Query throughput (query execution)

Throughput Metrics in MySQL

Indeed, MySQL provides throughput metrics for all of the above transaction types.

The Questions and Queries Status Variables

There are two general MySQL status variables for measuring query execution: questions and queries. Of the two, the client-centric view provided by the questions metric often makes it easier to interpret than the queries counter; the latter also counts statements executed as part of stored programs, as well as commands such as PREPARE and DEALLOCATE PREPARE that run as part of server-side prepared statements.

SHOW GLOBAL STATUS LIKE "Questions"
Variable_name  Value
--------------------
Questions      66
​
SHOW GLOBAL STATUS LIKE "Queries";
Variable_name  Value
--------------------
Queries        149

You can also monitor the breakdown of read and write commands to better understand your database’s workload and identify potential bottlenecks. Read queries are generally captured by the Com_select metric. Writes increment one of three status variables, depending on the statement type:

Writes = Com_insert + Com_update + Com_delete

SHOW GLOBAL STATUS LIKE "Com_select";
Variable_name  Value
--------------------
Com_select     49

But when are MySQL counters incremented? While the MySQL docs do list all of the various counter variables, they do not describe in detail when exactly each counter is incremented. This might seem like a trivial point, but really it is not, especially if you’re capturing metrics with high resolution in order to diagnose MySQL performance incidents.

For instance, if you would count queries when they start, a spike in the number of queries in a given second could be due to an increase in traffic. However, if you measure queries at the completion, spikes could be caused by some critical resource becoming available, which allowed many queries to complete. This often occurs with table-level locks or row-level locks on InnoDB.

MySQL increments the questions and queries counters before executing the query. As a result, you may see a very uniform rate of questions when actually a lot of queries were started but were not completing quickly due to waiting on some resource.

The Threads_running Status Variable

To check for unusual numbers of queries running concurrently and struggling to complete in time, it can be instructive to look at the Threads_running status variable.

SHOW GLOBAL STATUS LIKE "Threads_running";
Variable_name   Value
---------------------
Threads_running 29

A professional monitoring tool can present throughput metrics as a graph to make peaks and valleys more readily apparent:

Database throughputs

Transaction throughput

Measuring query execution performance is all about finding those queries that take too long to identify the required data or bring the data back. One of the best metrics to gauge query speed is latency. In terms of query execution, latency is simply the amount of time it takes a query to execute and return a result set — in other words, the time to make one round trip.

MySQL provides a few options for monitoring query latency, including built-in metrics and the performance schema. Enabled by default since MySQL 5.6.6, the tables of the performance_schema database within MySQL store low-level statistics about server events and query execution.

The Slow_queries Server Variable

It stores the number of queries that have taken more than long_query_time seconds. What’s great about this counter is that it increments regardless of whether the slow query log is enabled. That’s a good thing because the slow query log is disabled by default because logging can place a bit of a drag on performance.

SHOW GLOBAL STATUS LIKE "Slow_queries";
Variable_name  Value
--------------------
Slow_queries   99

Many key metrics are contained in the performance schema’s events_statements_summary_by_digest table, which captures information about query volume, latency, errors, time spent waiting for locks, and index usage. These metrics and more are available for each SQL statement executed. Statements are presented in normalized form, meaning that data values are removed from the SQL and whitespace is standardized.

This query finds the top 10 statements by longest average run time:

SELECT substr(digest_text, 1, 50) AS digest_text_start
     , count_star
     , avg_timer_wait 
  FROM performance_schema.events_statements_summary_by_digest 
 ORDER BY avg_timer_wait DESC
LIMIT 10;
digest_text_start    count_star        avg_timer_wait
----------------------------------------------------------------------------------------------
SHOW FULL TABLES FROM `sakila`         11110825767786
SHOW GLOBAL STATUS LIKE ? 11038069287388
SELECT `digest_text`, `count_star`, `avg_timer_w1945742257586
SHOW FIELDS FROM `sakila` . `actor` 1611721261340
SELECT `digest_text` , `count_star` , `avg_timer_w        2335116484794
SHOW FIELDS FROM `sakila` . `actor_info` SELECT `a       1221773712160
SELECT NAME , TYPE FROM `mysql` . `proc` WHERE `Db       2148939688506
SHOW FIELDS FROM `vehicles` . `vehiclemodelyear`        1144172298718
SHOW SCHEMAS   2132611131408
SHOW FIELDS FROM `sakila` . `customer`         199954017212

Performance schemas display event timer information in picoseconds (trillionths of a second) to present timing data in a standard unit. In the following example, TIMER_WAIT values are divided by 1,000,000,000,000 to convert time data into seconds. Values are also truncated to six decimal places:

SELECT substr(digest_text, 1, 50) AS digest_text_start
     , count_star
     , TRUNCATE(avg_timer_wait/1000000000000,6) 
  FROM performance_schema.events_statements_summary_by_digest 
 ORDER BY avg_timer_wait DESC
LIMIT 10;
digest_text_start     count_star   avg_timer_wait
-----------------------------------------------------------------------------------------
SHOW FULL TABLES FROM `sakila`         11.110825
SHOW GLOBAL STATUS LIKE ? 11.038069
SELECT `digest_text`, `count_star`, `avg_timer_w        10.945742
etc…

Now we can easily see that the longest query took a little over one second to run.

The sys Schema

Rather than write SQL statements against the performance schema, it is generally easier to use the sys schema. It contains easily interpretable tables for inspecting your performance data.

The sys schema comes installed with MySQL starting with version 5.7.7, but users of earlier versions can also install it. For instance, to install the sys schema on MySQL 5.6, run the following commands:

git clone https://github.com/mysql/mysql-sys.git
cd mysql-sys/
mysql -u root -p < ./sys_56.sql

The sys schema provides an organized set of metrics in a more human-readable format, making the corresponding queries much simpler. For instance, to find the slowest statements (those in the 95th percentile by runtime), run the following query:

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

Here again, a professional monitoring tool can really pay dividends by consolidating various performance metrics into one cohesive view:

Query execution performance

Conclusion

This article is the first of a two-part series on the top MySQL performance metrics. In Part 1, we learned:

In order to keep your databases and applications running smoothly, it’s crucial to monitor them effectively.
There are two important categories of metrics: those that are most useful in identifying problems and those whose primary value is in investigating problems.
How often you monitor different aspects of your database depends on how mission-critical it and the application(s) that it supports are.
There are two important categories of metrics that pertain to performance: work metrics and resource metrics.
Both work and resource metrics include two types of metrics, as follows:
- Work metrics:
  - Throughput
  - Query execution performance
- Resource metrics:
  - Connections
  - Buffer pool usage
There are two general MySQL status variables for measuring query execution: questions and queries. Of the two, the client-centric view provided by the questions metric often makes it easier to interpret than the queries counter.
MySQL provides a few options for monitoring query latency, including its built-in metrics and the performance schema. Enabled by default since MySQL 5.6.6, the tables of the performance_schema database within MySQL store low-level statistics about server events and query execution.
Many key metrics are contained in the performance schema’s events_statements_summary_by_digest table, which captures information about query volume, latency, errors, time spent waiting for locks, and index usage.
The sys schema contains easily interpretable tables for inspecting your performance data.

How To Configure a Galera Cluster with MariaDB on CentOS 7 Servers

2022-04-07T18:47:00.005+05:30

How To Configure a Galera Cluster with MariaDB on CentOS 7 Servers

Introduction

Clustering adds high availability to your database by distributing changes to different servers. In the event that one of the instances fails, others are quickly available to continue serving.

Clusters come in two general configurations, active-passive and active-active. In active-passive clusters, all writes are done on a single active server and then copied to one or more passive servers that are poised to take over only in the event of an active server failure. Some active-passive clusters also allow SELECT operations on passive nodes. In an active-active cluster, every node is read-write and a change made to one is replicated to all.

In this guide, you will configure an active-active MariaDB Galera cluster. For demonstration purposes, you will configure and test three CentOS 7 Droplets that will act as nodes in the cluster. This is the smallest configurable cluster.

Prerequisites

Step 1 — Adding the MariaDB Repositories to All Servers

In this step, you will add the relevant MariaDB package repositories to each of your three servers so that you will be able to install the right version of MariaDB used in this tutorial. Once the repositories are updated on all three servers, you will be ready to install MariaDB.

One thing to note about MariaDB is that it originated as a drop-in replacement for MySQL, so in many configuration files and startup scripts, you’ll see mysql rather than mariadb. In many cases, these are interchangeable. For consistency’s sake, we will use mariadb in this guide where either could work.

In this tutorial, you will use MariaDB version 10.4. Since this version isn’t included in the default CentOS repositories, you’ll start by adding the external CentOS repository maintained by the MariaDB project to all three of your servers.

Note: MariaDB is a well-respected provider, but not all external repositories are reliable. Be sure to install only from trusted sources.

First, you’ll add the MariaDB repository key by creating a repository file with a text editor. This tutorial will use vi:

sudo vi /etc/yum.repos.d/mariadb.repo

Next, add the following contents to the file by pressing i to enter insert mode, then adding the following:

/etc/yum.repos.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Press the esc key to return to normal mode, then type :wq to save and exit the file.

Once you have created the repository file, enable it with the following command:

sudo yum makecache --disablerepo='*' --enablerepo='mariadb'

The makecache command caches the repository metadata so that the package manager can install MariaDB, with --disablerepo and --enablerepo targeting the command to the mariadb repo file that you just created.

You will receive the following output:

OutputLoaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
mariadb                                                                                                                                                                              | 2.9 kB  00:00:00
(1/3): mariadb/primary_db                                                                                                                                                            |  43 kB  00:00:00
(2/3): mariadb/other_db                                                                                                                                                              | 8.3 kB  00:00:00
(3/3): mariadb/filelists_db                                                                                                                                                          | 238 kB  00:00:00
Metadata Cache Created

Once you have enabled the repository on your first server, repeat for your second and third servers.

Now that you have successfully added the package repository on all three of your servers, you’re ready to install MariaDB in the next section.

Step 2 — Installing MariaDB on All Servers

In this step, you will install the actual MariaDB packages on your three servers.

Beginning with version 10.1, the MariaDB Server and MariaDB Galera Server packages are combined, so installing MariaDB-server will automatically install Galera and several dependencies:

sudo yum install MariaDB-server MariaDB-client

You will be asked to confirm whether you would like to proceed with the installation. Enter yes to continue with the installation. You will then be prompted to accept the GPG key that authenticates the MariaDB package. Enter yes again.

When the installation is complete, start the mariadb service by running:

sudo systemctl start mariadb

Enable the mariadb service to be automatically started on boot by executing:

sudo systemctl enable mariadb

From MariaDB version 10.4 onwards, the root MariaDB user does not have a password by default. To set a password for the root user, start by logging into MariaDB:

sudo mysql -uroot

Once you’re inside the MariaDB shell, change the password by executing the following statement, replacing your_password with your desired password:

set password = password("your_password");

You will see the following output indicating that the password was set correctly:

OutputQuery OK, 0 rows affected (0.001 sec)

Exit the MariaDB shell by running the following command:

quit;

You now have all of the pieces necessary to begin configuring the cluster, but since you’ll be relying on rsync and policycoreutils-python in later steps to sync the servers and to control Security-Enhanced Linux (SELinux), make sure they’re installed before moving on:

sudo yum install rsync policycoreutils-python

This will confirm that the newest versions of rsync and policycoreutils-python is already available or will prompt you to upgrade or install it.

Once you have completed these steps, repeat them for your other two servers.

Now that you have installed MariaDB successfully on each of the three servers, you can proceed to the configuration step in the next section.

Step 3 — Configuring the First Node

In this step you will configure your first Galera node. Each node in the cluster needs to have a nearly identical configuration. Because of this, you will do all of the configuration on your first machine, and then copy it to the other nodes.

By default, MariaDB is configured to check the /etc/mysql/conf.d directory to get additional configuration settings from files ending in .cnf. Create a file in this directory with all of your cluster-specific directives:

sudo vi /etc/my.cnf.d/galera.cnf

Add the following configuration into the file. The configuration specifies different cluster options, details about the current server and the other servers in the cluster, and replication-related settings. Note that the IP addresses in the configuration are the private addresses of your respective servers; replace the highlighted lines with the appropriate IP addresses:

/etc/my.cnf.d/galera.cnf

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://First_Node_IP,Second_Node_IP,Third_Node_IP"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="This_Node_IP"
wsrep_node_name="This_Node_Name"

The first section modifies or re-asserts MariaDB/MySQL settings that will allow the cluster to function correctly. For example, Galera won’t work with MyISAM or similar non-transactional storage engines, and mysqld must not be bound to the IP address for localhost.
The “Galera Provider Configuration” section configures the MariaDB components that provide a WriteSet replication API. This means Galera in your case, since Galera is a wsrep (WriteSet Replication) provider. You specify the general parameters to configure the initial replication environment. This doesn’t require any customization.
The “Galera Cluster Configuration” section defines the cluster, identifying the cluster members by IP address or resolvable domain name and creating a name for the cluster to ensure that members join the correct group. You can change the wsrep_cluster_name to something more meaningful than test_cluster or leave it as-is, but you must update wsrep_cluster_address with the private IP addresses of your three servers.
The “Galera Synchronization Configuration” section defines how the cluster will communicate and synchronize data between members. This is used only for the state transfer that happens when a node comes online. For your initial setup, you are using rsync, because it’s commonly available and does what you’ll need for now.
The “Galera Node Configuration” section clarifies the IP address and the name of the current server. This is helpful when trying to diagnose problems in logs and for referencing each server in multiple ways. The wsrep_node_address must match the address of the machine you’re on, but you can choose any name you want in order to help you identify the node in log files.

When you are satisfied with your cluster configuration file, copy the contents into your clipboard and save and close the file.

Now that you have configured your first node successfully, you can move on to configuring the remaining nodes in the next section.

Step 4 — Configuring the Remaining Nodes

In this step, you will configure the remaining two nodes. On your second node, open the configuration file:

sudo vi /etc/mysql/my.cnf.d/galera.cnf

Paste in the configuration you copied from the first node, then update the Galera Node Configuration to use the IP address or resolvable domain name for the specific node you’re setting up. Finally, update its name, which you can set to whatever helps you identify the node in your log files:

/etc/mysql/my.cnf.d/galera.cnf

. . .
# Galera Node Configuration
wsrep_node_address="This_Node_IP"
wsrep_node_name="This_Node_Name"
. . .

Save and exit the file.

Once you have completed these steps, repeat them on the third node.

With Galera configured on all of your nodes, you’re almost ready to bring up the cluster. But before you do, make sure that the appropriate ports are open in your firewall and that a SELinux policy has been created for Galera.

Step 5 — Opening the Firewall on Every Server

In this step, you will configure your firewall so that the ports required for inter-node communication are open.

On every server, check the status of the firewall you set up in the Prerequisites section by running:

sudo firewall-cmd --list-all

In this case, only SSH, DHCP, HTTP, and HTTPS traffic is allowed through:

Outputpublic
  target: default
  icmp-block-inversion: no
  interfaces:
  sources:
  services: ssh dhcpv6-client http https
  ports:
  protocols:
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

If you tried to start the cluster now, it would fail because the firewall would block the connections between the nodes. To solve this problem, add rules to allow MariaDB and Galera traffic through.

Galera can make use of four ports:

3306 For MariaDB client connections and State Snapshot Transfer that use the mysqldump method.
4567 For Galera Cluster replication traffic. Multicast replication uses both UDP transport and TCP on this port.
4568 For Incremental State Transfers, or IST, the process by which a missing state is received by other nodes in the cluster.
4444 For all other State Snapshot Transfers, or SST, the mechanism by which a joiner node gets its state and data from a donor node.

In this example, you’ll open all four ports while you do your setup. Once you’ve confirmed that replication is working, you’d want to close any ports you’re not actually using and restrict traffic to just servers in the cluster.

Open the ports with the following commands:

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp
sudo firewall-cmd --permanent --zone=public --add-port=4567/tcp
sudo firewall-cmd --permanent --zone=public --add-port=4568/tcp
sudo firewall-cmd --permanent --zone=public --add-port=4444/tcp
sudo firewall-cmd --permanent --zone=public --add-port=4567/udp

Using --zone=public and --add-port= here, firewall-cmd is opening up these ports to public traffic. --permanent ensures that these rules persist.

Now, add each server to the public zone by executing the following commands, replacing the highlighted text with the respective private IP addresses of your nodes:

sudo firewall-cmd --permanent --zone=public --add-source=galera-node-1-ip/32
sudo firewall-cmd --permanent --zone=public --add-source=galera-node-2-ip/32
sudo firewall-cmd --permanent --zone=public --add-source=galera-node-3-ip/32

Reload the firewall to apply the changes:

sudo firewall-cmd --reload

After you have configured your firewall on the first node, create the same firewall settings on the second and third node.

Now that you have configured the firewalls successfully, you’re ready to create a SELinux policy in the next step.

Step 6 — Creating a SELinux Policy

In this section, you will create a SELinux policy that will allow all the nodes in the cluster to be able to communicate with each other and perform cluster operations.

SELinux is a Linux kernel module that improves the security of operating systems with its support for access control and mandatory access control policies. It is enabled by default on CentOS 7 and restricts the MariaDB daemon from performing many activities.

In order to create the policy, you will perform various activities on the cluster with the SELinux mode set to permissive for MySQL. You will then create a policy from the logged events and finally set the SELinux mode to enforcing once the policy is installed successfully.

First, allow access to the relevant ports by running the following commands on all three servers:

sudo semanage port -a -t mysqld_port_t -p tcp 4567
sudo semanage port -a -t mysqld_port_t -p udp 4567
sudo semanage port -a -t mysqld_port_t -p tcp 4568
sudo semanage port -a -t mysqld_port_t -p tcp 4444

Note: You may receive a ValueError when allowing access to some of these ports. This means that the SELinux status of that port has already been set, which in this case will not affect the process of this tutorial.

In these commands, you are using the SELinux management tool semanage with the -a flag to add specified ports and to ignore the database server.

Next, run the following command on all three servers, which sets the MySQL SELinux domain to permissive mode temporarily.

sudo semanage permissive -a mysqld_t

This command can take a minute to complete and will not display any output.

Next, stop the database server on all the nodes so that you will be able to bootstrap the database cluster with shared SELinux policies. To do this, run the following command on all three nodes:

sudo systemctl stop mariadb

Now, bootstrap the cluster to generate inter-node communication events that will be added to the SELinux policy. On the first node, bootstrap the cluster by executing:

sudo galera_new_cluster

Create a database and table for the specific purpose of logging SST events by running the following on the first node:

mysql -u root -p -e 
'CREATE DATABASE selinux;
CREATE TABLE selinux.selinux_policy (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
INSERT INTO selinux.selinux_policy VALUES ();'

Now start the server on the second node:

sudo systemctl start mariadb

Then do the same on the third node:

sudo systemctl start mariadb

You will not see any output for the previous commands. To generate IST events, execute the following on all three servers:

mysql -u root -p -e 'INSERT INTO selinux.selinux_policy VALUES ();'

Now create and enable the SELinux policy by executing the following commands on all three servers:

sudo grep mysql /var/log/audit/audit.log | sudo audit2allow -M Galera

This first command searches for generated events in the audit.log file and pipes them to a module named Galera.pp generated by the audit2allow tool. This will result in the following output:

Output******************** IMPORTANT ***********************
To make this policy package active, execute:

semodule -i Galera.pp

Next, follow the instructions in the output and use the following command to install the generated module:

sudo semodule -i Galera.pp

Now that the policy is active, disable permissive mode for the MariaDB server:

sudo semanage permissive -d mysqld_t

Now that you have successfully created a SELinux policy and enabled it, you are ready to start the cluster in the next section.

Step 7 — Starting the Cluster

In this step, you will start your MariaDB cluster. To begin, you need to stop the running MariaDB service so that you can bring your cluster online.

Stop MariaDB on All Three Servers

When stopping the MariaDB service, it is important to execute this action on your servers in a specific order. This shutdown sequence ensures that the first node will be able to safely bootstrap the cluster when it starts up.

First, run the following command on the third node:

sudo systemctl stop mariadb

Next, stop the service on the second node:

sudo systemctl stop mariadb

Finally, stop the service on the first node:

sudo systemctl stop mariadb

systemctl doesn’t display the outcome of all service management commands, so to be sure you succeeded, use the following command on each of your servers:

sudo systemctl status mariadb

The last line will look something like the following:

Output. . . 
Apr 26 03:34:23 galera-node-01 systemd[1]: Stopped MariaDB 10.4.4 database server.

Once you’ve shut down mariadb on all of the servers, you’re ready to proceed.

Bring Up the First Node

To bring up the first node, you’ll need to use a special startup script. The way you’ve configured your cluster, each node that comes online tries to connect to at least one other node specified in its galera.cnf file to get its initial state. Without using the galera_new_cluster script that allows systemd to pass the --wsrep-new-cluster parameter, a normal systemctl start mariadb would fail because there are no nodes running for the first node to connect with.

sudo galera_new_cluster

This command will not display any output on successful execution. When this script succeeds, the node is registered as part of the cluster, and you can see it with the following command:

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

You will see the following output indicating that there is one node in the cluster:

Output+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

On the remaining nodes, you can start mariadb normally. They will search for any member of the cluster list that is online, so when they find one, they will join the cluster.

Bring Up the Second Node

Now you can bring up the second node. Start mariadb:

sudo systemctl start mariadb

No output will be displayed on successful execution. You will see your cluster size increase as each node comes online:

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

You will see the following output indicating that the second node has joined the cluster and that there are two nodes in total.

Output+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

Bring Up the Third Node

It’s now time to bring up the third node. Start mariadb:

sudo systemctl start mariadb

Run the following command to find the cluster size:

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

You will see the following output, which indicates that the third node has joined the cluster and that the total number of nodes in the cluster is three.

Output+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

At this point, the entire cluster is online and communicating successfully. Next, you can ensure the working setup by testing replication in the next section.

Step 8 — Testing Replication

You’ve gone through the steps up to this point so that your cluster can perform replication from any node to any other node, known as active-active replication. Follow the following steps to test and see if the replication is working as expected.

Write to the First Node

You’ll start by making database changes on your first node. The following commands will create a database called playground and a table inside of this database called equipment.

mysql -u root -p -e 
'CREATE DATABASE playground;
CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));
INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue");'

In the previous command, the CREATE DATABASE statement creates a database named playground. The CREATE statement creates a table named equipment inside the playground database having an auto-incrementing identifier column called id and other columns. The type column, quant column, and color column are defined to store the type, quantity, and color of the equipment respectively. The INSERT statement inserts an entry of type slide, quantity 2, and color blue.

You now have one value in your table.

Read and Write on the Second Node

Next, look at the second node to verify that replication is working:

mysql -u root -p -e 'SELECT * FROM playground.equipment;'

If replication is working, the data you entered on the first node will be visible here on the second:

Output+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+

From this same node, you can write data to the cluster:

mysql -u root -p -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("swing", 10, "yellow");'

Read and Write on the Third Node

From the third node, you can read all of this data by querying the table again:

mysql -u root -p -e 'SELECT * FROM playground.equipment;'

You will see the following output showing the two rows:

Output   +----+-------+-------+--------+
   | id | type  | quant | color  |
   +----+-------+-------+--------+
   |  1 | slide |     2 | blue   |
   |  2 | swing |    10 | yellow |
   +----+-------+-------+--------+

Again, you can add another value from this node:

mysql -u root -p -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("seesaw", 3, "green");'

Read on the First Node

Back on the first node, you can verify that your data is available everywhere:

mysql -u root -p -e 'SELECT * FROM playground.equipment;'

You will see the following output, which indicates that the rows are available on the first node.

Output   +----+--------+-------+--------+
   | id | type   | quant | color  |
   +----+--------+-------+--------+
   |  1 | slide  |     2 | blue   |
   |  2 | swing  |    10 | yellow |
   |  3 | seesaw |     3 | green  |
   +----+--------+-------+--------+

You’ve verified successfully that you can write to all of the nodes and that replication is being performed properly.

Conclusion

At this point, you have a working three-node Galera test cluster configured. If you plan on using a Galera cluster in a production situation, it’s recommended that you begin with no fewer than five nodes.

Reference: https://www.digitalocean.com/community/tutorials/how-to-configure-a-galera-cluster-with-mariadb-on-centos-7-servers

High level Steps involved - Disaster Recovery for Galera Cluster

2022-04-07T18:29:00.001+05:30

Geo-Distributed MySQL Clusters for the Enterprise

Galera Cluster provides high availability and scalability for MySQL. A Galera cluster consists of 3 or more Galera instances in a local network using synchronous replication. Galera Cluster supports multi-master and now has a GUI available for cluster management. While this provides high availability in a local region or site, it does not provide any provisions for disaster recovery (DR) or any multi-site deployment in general, so let’s explore how we could extend the functionality of Galera Cluster to deploy at geo-scale.

Linking Two (2) or More Galera Clusters

To create a multi-site Galera Cluster, it’s tempting to simply add more cluster nodes in a remote region. However, due to synchronous replication and high latencies over the WAN, cluster and application performance would become unacceptable and thus this topology is not recommended. Instead, we turn to native MySQL replication, which is asynchronous and does not come with a significant performance penalty.

The steps to establish replication from Site 1 to Site 2 (DR) are:

Create a cluster in Site 1.
Create a cluster in Site 2 from Site 1. At this point, the 2 clusters are identical.
Configure necessary firewall rules between sites to allow replication from Site 1 to Site 2.
Also, configure the firewall to allow replication in the reverse direction (see below).
Add the following to a cluster node in site 1 (and site 2 for failback, see below):
1
2
3
log-bin=galera-bin
log-slave-updates
server-id=1
You can adjust values as needed, it’s just important to enable binary logging AND to log slave updates so that writes from other nodes will be reflected in the binary log of the current node
Restart your Galera cluster (Site 1) to make sure settings take effect
Now just set up regular native MySQL replication stream using Site 1 as master and Site 2 as slave
You can use a proxy for MySQL on Site 2 to route writes to all Galera cluster nodes
At this point, be sure to set up monitoring on both clusters so you are notified of any issues!

With the template above and mixing various technologies, you can achieve a DR site for your Galera Cluster. To perform a “failover” or otherwise activate the DR site, simply point your applications to the DR site. IMPORTANT: note the binary log position on Site 2 before sending any writes to it. At this point, the primary site is out of sync with the DR site. If in fact the primary site can be recovered and is viable, then simply establish native MySQL replication in the reverse direction, but start from the binary log position you noted just before the failover. If this is not available, or the primary site cannot be recovered, then it must be reprovisioned. Again, when reprovisioning, be sure to note the binary log position in the MySQL backup!

What Else to Consider with Geo-Distributed Galera Cluster?

Actually using native replication to join Galera Clusters requires us to plan a few additional items:

How do we actually failover?
1. Stop application traffic to the primary site.
2. Repoint all application traffic to Site 2.
3. Note that the above steps will take your application offline for however long it takes to perform the above operations.
How do we fail back?
1. We will have to reprovision Site 1 since the data is stale.
  1. Schedule downtime, do backup and restore, all nodes now in sync.
  2. Take backup, restore onto Site 1, then establish replication from Site 2 to Site 1.
2. Stop application traffic to Site 2.
3. Be sure replication traffic has caught up if using replication.
4. Reestablish replication from Site 1 to Site 2.
5. Repoint application traffic to Site 2.
6. Again, the above steps will incur downtime for your application.
Monitoring and Management.
1. Need to monitor:
  1. Galera Cluster in both sites.
  2. MySQL native replication.
  3. Optional: MySQL Router or other proxy, if in use
  4. Various tools required for each item above.
2. Each technology needs to be managed separately, most likely using Do It Yourself (DIY) scripts.
3. Requires development to view and manage the entire topology from a high level.

MariaDB Best practices for Optimization & Tuning

2022-03-30T15:02:00.064+05:30

MariaDB Best practices for Optimization & Tuning

InnoDB file-per-table

InnoDB Buffer Pool Size

Max Connections

Disable Swap In MySQL

Tune Your Table Cache

How to deal Query Cache

Tune/Increase Open Files Limit

InnoDB file-per-table

After setting this parameter all the tables will have their own .idb file on server

innodb_file_per_table=1

MariaDB 10.x, innodb_file_per_table=1 is a default setting. if so your new InnoDB tables and those converted from MyISAM to InnoDB will already have separate tablespaces.

if you are using MariaDB 5.5, innodb_file_per_table=0 by default.

To start using individual tablespaces, run SET GLOBAL innodb_file_per_table = 1;

To make the option permanent, add it to the cnf file.

InnoDB Buffer Pool Size

We can set the parameter to 60 percent of your memory

The default value of Buffer pool size is 8MB and you can change this value by adding the following line in your my.cnf file

innodb_buffer_pool_size = 1G

Disable Swap In MySQL

Max Connections

Tune Your Table Cache

SHOW GLOBAL STATUS LIKE 'Open%table%';

For table_open_cache, it shall be the total number of your tables

but it's best you add more depending on the type of queries you serve since temporary tables shall be cached as well.

For example, if you have 500 tables, it would be reasonable you start with 1500.

While your table_open_cache_instances, start setting it to 8.

This can improve scalability by reducing contention among sessions,

the open tables cache can be partitioned into several smaller cache instances of size table_open_cache / table_open_cache_instances.

Dealing with Query Cache

Preferred option - We think that disabling the query cache to improve the performance of MariaDB is the preferred option.

You need to make sure that query_cache_type=OFF and query_cache_size=0 so that the query cache is completely disabled.

In contrast to MySQL, MariaDB still supports query cache and doesn’t plan to withdraw support for it anytime soon.

There are those who think that using query cache gives them performance benefits,

but as this post from Percona demonstrates,

an enabled query cache increases overhead and reduces server performance.

If you want to use query cache, ensure that you monitor it by running

SHOW GLOBAL STATUS LIKE ‘Qcache%’;.

Qcache_inserts reports on how many queries have been added to the query cache,

Qcache_hits shows how many have made use of it, and

Qcache_lowmem_prunes contains the number of queries that have been dropped because of insufficient memory.

Over time, using query cache may cause it to become fragmented.

A high Qcache_free_blocks to Qcache_total_blocks ratio may point to increased fragmentation.

To defragment it, run FLUSH QUERY CACHE. This will defragment the query cache without dropping any queries and improve MariaDB performance.

Tune/Increase Open Files Limit

To ensure good server performance, the total number of client connections, database files, and log files must not exceed the maximum file descriptor limit on the operating system (ulimit -n). Linux systems limit the number of file descriptors that any one process may open to 1,024 per process. On active database servers (especially production ones) it can easily reach the default system limit.

To increase this, edit /etc/security/limits.conf and specify or add the following:

1 mysql soft nofile 65535

3 mysql hard nofile 65535

This requires a system restart. Afterwards, you can confirm by running the following:

1 $ ulimit -Sn

3 65535

5 $ ulimit -Hn

7 65535

Optionally, you can set this via mysqld_safe if you are starting the mysqld process thru mysqld_safe,

1 [mysqld_safe]

3 open_files_limit=4294967295

or if you are using systemd,

1 sudo tee /etc/systemd/system/mariadb.service.d/limitnofile.conf <<EOF

3 [Service]

5 LimitNOFILE=infinity

7 EOF

9 sudo systemctl daemon-reload

Increase Open Files Limit

2022-03-30T14:34:00.000+05:30

Increase Open Files Limit

To increase this, edit /etc/security/limits.conf and specify or add the following:

mysql soft nofile 65535
 
mysql hard nofile 65535

This requires a system restart. Afterwards, you can confirm by running the following:

$ ulimit -Sn
 
65535
 
$ ulimit -Hn
 
65535

Optionally, you can set this via mysqld_safe if you are starting the mysqld process thru mysqld_safe,

[mysqld_safe]
 
open_files_limit=4294967295

or if you are using systemd,

sudo tee /etc/systemd/system/mariadb.service.d/limitnofile.conf <<EOF
 
[Service]
 
LimitNOFILE=infinity
 
EOF
 
sudo systemctl daemon-reload

Max Connections - mariadb/mysql

2022-03-30T13:25:00.027+05:30

Max Connections

Max connection parameter in MySQL shows how many concurrent connections can be initiated on your MariaDB server. default is 151.

First thing to decide is what new maximum value you want to set for max_connections

Considerations to take into account when increasing the number of MySQL/MariaDB connections.

The maximum number which can be supported by the system will depend on:

1)The amount of available RAM.

2)How much RAM each connection takes (simple queries will require less RAM than more labor-intensive connections).

3)The acceptable response time.

According to the MySQL documentation, most Linux systems should be able to support 500-1000 connections without difficulty.

Systems that get too busy can return the too_many_connections error.

When the number of threads_connected exceeds the max_connections server variable, it's time to make a change.

Viewing the threads_connected status variable shows only the current number of connections,

but it's more useful to see what the value has peaked at, and this is shown by the max_used_connections status variable.

This error may be a symptom of slow queries and other bottlenecks,

but if the system is running smoothly this can be addressed by increasing the value of max_connections.

What Is The Source Of The Database Connections?

Use the ‘SHOW processlist’ SQL command to show you which threads are currently running.

It will, for instance, provide the following details:

User – The MySQL user who issued the statement.
Host – The hostname of the client issuing the statement.
Command – The SQL command that is executed.

The ‘show processlist SQL command:

SHOW processlist

How to change max_connections

The max_connections variable will need to be changed in two places:

Update the my.cnf file, so that the new value is used if the MySQL/MariaDB server is restarted.
Use the SET GLOBAL command to update the value on the running MySQL/MariaDB server.

Fortunately, by using this method, you will not need to restart MySQL/MariaDB, and therefore will not need to experience any downtime.

Show the Current max_connections Value

To see the current number of max_connections log in to the MySQL/MariaDB command line client with the command:

mysql -u root -p

Use the command:

SHOW variables;

This will output a list of all of the variables which are set for MySQL/MariaDB. Scroll up through the list to find the value for max_connections.

Update my.cnf

Open the file /etc/my.cnf for editing with the command:

sudo nano /etc/my.cnf

Directly beneath the first line:

[mysqld]

Add a line:

max_connections=[desired new maximum number]

For example, to set max_connections to 200, the first two lines of the file will read:

[mysqld] max_connections=200

Save and exit the file.

SET GLOBAL

mysql -u root -p

Set the new max_connections value with the command:

SET GLOBAL max_connections=[desired new maximum number];

For example, to set max_connections to 200, the command is:

SET GLOBAL max_connections=200;

Exit MySQL/MariaDB with the command:

quit;

Disable Swap In MySQL

2022-03-30T12:46:00.028+05:30

Disable Swap In MySQL

Swapping occurs when your systems runs out of physical memory and starts using the special disk known as swap.

When your RAM is occupied it starts swapping and your disk I/O gets utilized which slows down the server and if proper steps are not taken then system can crash.

The default is usually 60

you can check this by running:

sysctl vm.swappiness

To avoid swappiness, execute the following command on your command line.

sysctl -w vm.swappiness=0

The default swapiness value is 60 which will be changed to 0 which indicates that it is disabled now

For MariaDB databases, it is recommended to set swappiness to a value of 1.

vm.swappiness = 1

While some disable swap altogether, and you certainly want to avoid any database processes from using it

It can be prudent to leave some swap space to at least allow the kernel to fall over gracefully should a spike occur.

Having emergency swap available at least allows you some scope to kill any runaway processes.

Enable innodb_file_per_table

2022-03-30T12:33:00.003+05:30

Note: You may wish to convert MyISAM tables to InnoDB tables before you proceed.

innodb_file_per_table is by default ON Mysql 5.6.6 and onwards. There is plenty of stuff on Google about pros & cons ofinnodb_file_per_table.

This post details how to enable innodb_file_per_table on an existing database. Because innodb_file_per_table affects new tables only, created after innodb_file_per_table is enabled, we need to recreate old databases to force innodb_file_per_table on old tables and reclaim some disk space.

Backup First

Create a dir to take backups:

cd ~
mkdir backup
cd backup

Copy mysql data files (raw)

If all goes well, we will not need this.

For better results, shut down PHP and other apps/scripts which update mysql. You can keep Nginx running and server non-logged in visitors cached content.

service mysql stop && cp -ra /var/lib/mysql mysqldata && service mysql start

Take mysqldump

As soon as above line completes, take a mysqldump of all databases

mysqldump --routines --events --flush-privileges --all-databases > all-db.sql

Drop Databases

Create a sql file to drop all databases EXCEPT mysql database

mysql -e "SELECT DISTINCT CONCAT ('DROP DATABASE ',TABLE_SCHEMA,' ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> 'mysql' AND TABLE_SCHEMA <> 'information_schema';" | tail -n+2 > drop.sql

Verify if drop.sql has correct database names and then execute drop.sql queries.

mysql < drop.sql

Verify all InnoDB tables gone

SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'InnoDB';

Remove InnoDB files

Stop mysql server first

service mysql stop

Then

rm /var/lib/mysql/ibdata1 && rm /var/lib/mysql/ib_logfile0 && rm /var/lib/mysql/ib_logfile1

At this point most likely you will have only /var/lib/mysql/mysql directory only.

Enable innodb_file_per_table

Open my.cnf file

vim /etc/mysql/my.cnf

Add following lines

innodb_file_per_table = 1
innodb_file_format = barracuda

Time to import from mysqldump

Start mysql server now

service mysql start

Run mysql import

mysql < all-db.sql

Force mysql_upgrade (to generate performance_schema)

mysql_upgrade --force

Good Luck!

Exchanging Partitions and Subpartitions with Tables

2022-03-25T18:09:00.000+05:30

Exchanging Partitions and Subpartitions with Tables

It is possible to exchange a table partition or subpartition with a table using ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt, where pt is the partitioned table and p is the partition or subpartition of pt to be exchanged with unpartitioned table nt, provided that the following statements are true:

Table nt is not itself partitioned.
Table nt is not a temporary table.
The structures of tables pt and nt are otherwise identical.
Table nt contains no foreign key references, and no other table has any foreign keys that refer to nt.
There are no rows in nt that lie outside the boundaries of the partition definition for p. This condition does not apply if the WITHOUT VALIDATION option is used. The [{WITH|WITHOUT} VALIDATION] option was added in MySQL 5.7.5.
Both tables must use the same character set and collation.
For InnoDB tables, both tables must use the same row format. To determine the row format of an InnoDB table, query INFORMATION_SCHEMA.INNODB_SYS_TABLES.
Any partition-level MAX_ROWS setting for p must be the same as the table-level MAX_ROWS value set for nt. The setting for any partition-level MIN_ROWS setting for p must also be the same any table-level MIN_ROWS value set for nt.
This is true in either case whether not pt has an exlpicit table-level MAX_ROWS or MIN_ROWS option in effect.
The AVG_ROW_LENGTH cannot differ between the two tables pt and nt.
pt does not have any partitions that use the DATA DIRECTORY option. This restriction is lifted for InnoDB tables in MySQL 5.7.25 and later.
INDEX DIRECTORY cannot differ between the table and the partition to be exchanged with it.
No table or partition TABLESPACE options can be used in either of the tables.

In addition to the ALTER, INSERT, and CREATE privileges usually required for ALTER TABLE statements, you must have the DROP privilege to perform ALTER TABLE ... EXCHANGE PARTITION.

You should also be aware of the following effects of ALTER TABLE ... EXCHANGE PARTITION:

Executing ALTER TABLE ... EXCHANGE PARTITION does not invoke any triggers on either the partitioned table or the table to be exchanged.
Any AUTO_INCREMENT columns in the exchanged table are reset.
The IGNORE keyword has no effect when used with ALTER TABLE ... EXCHANGE PARTITION.

The syntax of the ALTER TABLE ... EXCHANGE PARTITION statement is shown here, where pt is the partitioned table, p is the partition or subpartition to be exchanged, and nt is the nonpartitioned table to be exchanged with p:

ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

Optionally, you can append a WITH VALIDATION or WITHOUT VALIDATION clause. When WITHOUT VALIDATION is specified, the ALTER TABLE ... EXCHANGE PARTITION operation does not perform row-by-row validation when exchanging a partition a nonpartitioned table, allowing database administrators to assume responsibility for ensuring that rows are within the boundaries of the partition definition. WITH VALIDATION is the default behavior and need not be specified explicitly. The [{WITH|WITHOUT} VALIDATION] option was added in MySQL 5.7.5.

One and only one partition or subpartition may be exchanged with one and only one nonpartitioned table in a single ALTER TABLE EXCHANGE PARTITION statement. To exchange multiple partitions or subpartitions, use multiple ALTER TABLE EXCHANGE PARTITION statements. EXCHANGE PARTITION may not be combined with other ALTER TABLE options. The partitioning and (if applicable) subpartitioning used by the partitioned table may be of any type or types supported in MySQL 5.7.

Exchanging a Partition with a Nonpartitioned Table

Suppose that a partitioned table e has been created and populated using the following SQL statements:

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO e VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

Now we create a nonpartitioned copy of e named e2. This can be done using the mysql client as shown here:

mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (1.34 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

You can see which partitions in table e contain rows by querying the INFORMATION_SCHEMA.PARTITIONS table, like this:

mysql> SELECT PARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

Note

For partitioned InnoDB tables, the row count given in the TABLE_ROWS column of the INFORMATION_SCHEMA.PARTITIONS table is only an estimated value used in SQL optimization, and is not always exact.

To exchange partition p0 in table e with table e2, you can use the ALTER TABLE statement shown here:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)

More precisely, the statement just issued causes any rows found in the partition to be swapped with those found in the table. You can observe how this has happened by querying the INFORMATION_SCHEMA.PARTITIONS table, as before. The table row that was previously found in partition p0 is no longer present:

mysql> SELECT PARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

If you query table e2, you can see that the “missing” row can now be found there:

mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

The table to be exchanged with the partition does not necessarily have to be empty. To demonstrate this, we first insert a new row into table e, making sure that this row is stored in partition p0 by choosing an id column value that is less than 50, and verifying this afterward by querying the PARTITIONS table:

mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.05 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

Now we once again exchange partition p0 with table e2 using the same ALTER TABLE statement as previously:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.28 sec)

The output of the following queries shows that the table row that was stored in partition p0 and the table row that was stored in table e2, prior to issuing the ALTER TABLE statement, have now switched places:

mysql> SELECT * FROM e;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|   16 | Frank | White |
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

Nonmatching Rows

You should keep in mind that any rows found in the nonpartitioned table prior to issuing the ALTER TABLE ... EXCHANGE PARTITION statement must meet the conditions required for them to be stored in the target partition; otherwise, the statement fails. To see how this occurs, first insert a row into e2 that is outside the boundaries of the partition definition for partition p0 of table e. For example, insert a row with an id column value that is too large; then, try to exchange the table with the partition again:

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition

Only the WITHOUT VALIDATION option would permit this operation to succeed:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)

When a partition is exchanged with a table that contains rows that do not match the partition definition, it is the responsibility of the database administrator to fix the non-matching rows, which can be performed using REPAIR TABLE or ALTER TABLE ... REPAIR PARTITION.

Exchanging Partitions Without Row-By-Row Validation

To avoid time consuming validation when exchanging a partition with a table that has many rows, it is possible to skip the row-by-row validation step by appending WITHOUT VALIDATION to the ALTER TABLE ... EXCHANGE PARTITION statement.

The following example compares the difference between execution times when exchanging a partition with a nonpartitioned table, with and without validation. The partitioned table (table e) contains two partitions of 1 million rows each. The rows in p0 of table e are removed and p0 is exchanged with a nonpartitioned table of 1 million rows. The WITH VALIDATION operation takes 0.74 seconds. By comparison, the WITHOUT VALIDATION operation takes 0.01 seconds.

# Create a partitioned table with 1 million rows in each partition

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (1000001),
        PARTITION p1 VALUES LESS THAN (2000001),
);

SELECT COUNT(*) FROM e;
| COUNT(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.27 sec)

# View the rows in each partition

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+-------------+
| PARTITION_NAME | TABLE_ROWS  |
+----------------+-------------+
| p0             |     1000000 |
| p1             |     1000000 |
+----------------+-------------+
2 rows in set (0.00 sec)

# Create a nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.24 sec)

# Create another nonpartitioned table of the same structure and populate it with 1 million rows

CREATE TABLE e3 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.25 sec)

# Drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Exchange partition p0 of table e with the table e2 'WITH VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION;
Query OK, 0 rows affected (0.74 sec)

# Confirm that the partition was exchanged with table e2

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Once again, drop the rows from p0 of table e

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

# Confirm that there are no rows in partition p0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

# Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)

# Confirm that the partition was exchanged with table e3

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |    1000000 |
| p1             |    1000000 |
+----------------+------------+
2 rows in set (0.00 sec)

If a partition is exchanged with a table that contains rows that do not match the partition definition, it is the responsibility of the database administrator to fix the non-matching rows, which can be performed using REPAIR TABLE or ALTER TABLE ... REPAIR PARTITION.

Exchanging a Subpartition with a Nonpartitioned Table

You can also exchange a subpartition of a subpartitioned table (see Section 22.2.6, “Subpartitioning”) with a nonpartitioned table using an ALTER TABLE ... EXCHANGE PARTITION statement. In the following example, we first create a table es that is partitioned by RANGE and subpartitioned by KEY, populate this table as we did table e, and then create an empty, nonpartitioned copy es2 of the table, as shown here:

mysql> CREATE TABLE es (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30)
    -> )
    ->     PARTITION BY RANGE (id)
    ->     SUBPARTITION BY KEY (lname)
    ->     SUBPARTITIONS 2 (
    ->         PARTITION p0 VALUES LESS THAN (50),
    ->         PARTITION p1 VALUES LESS THAN (100),
    ->         PARTITION p2 VALUES LESS THAN (150),
    ->         PARTITION p3 VALUES LESS THAN (MAXVALUE)
    ->     );
Query OK, 0 rows affected (2.76 sec)

mysql> INSERT INTO es VALUES
    ->     (1669, "Jim", "Smith"),
    ->     (337, "Mary", "Jones"),
    ->     (16, "Frank", "White"),
    ->     (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)

mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

Although we did not explicitly name any of the subpartitions when creating table es, we can obtain generated names for these by including the SUBPARTITION_NAME of the PARTITIONS table from INFORMATION_SCHEMA when selecting from that table, as shown here:

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          3 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

The following ALTER TABLE statement exchanges subpartition p3sp0 table es with the nonpartitioned table es2:

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)

You can verify that the rows were exchanged by issuing the following queries:

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          0 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM es2;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)

If a table is subpartitioned, you can exchange only a subpartition of the table—not an entire partition—with an unpartitioned table, as shown here:

mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

The comparison of table structures used by MySQL is very strict. The number, order, names, and types of columns and indexes of the partitioned table and the nonpartitioned table must match exactly. In addition, both tables must use the same storage engine:


mysql> CREATE TABLE es3 LIKE e;
Query OK, 0 rows affected (1.31 sec)

mysql> ALTER TABLE es3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE es3\G
*************************** 1. row ***************************
       Table: es3
Create Table: CREATE TABLE `es3` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE es3 ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

Steps to configure Galera cluster in MariaDB

2022-03-25T10:20:00.002+05:30

Clustering adds high availability to your database by distributing changes to different servers. In the event that one of the instances fails, others are quickly available to continue serving.

Clusters come in two general configurations, active-passive and active-active. In active-passive clusters, all writes are done on a single active server and then copied to one or more passive servers that are poised to take over only in the event of an active server failure. Some active-passive clusters also allow SELECT operations on passive nodes. In an active-active cluster, every node is read-write and a change made to one is replicated to all.

We will configure an active-active MariaDB Galera cluster.

What we need?

3x Ubuntu 16.04 Servers in the same network, so that all nodes can access each other using private IPs

Let’s Start

We will start step by step to setup the cluster.

Step 01: Adding MariaDB Repository to the servers and Install it

MariaDB is not available by default from apt-get . So we need to add manually MariaDB repository. Although, you can also install from source.

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.1/ubuntu xenial main'sudo apt-get update# Install MariaDB
sudo apt-get install mariadb-server

Althogh, rsync comes with MariaDB server installations, but make sure it is installed by:

sudo apt-get install rsync

Step 02: Configuring nodes

Each node should have exact configuration to be in cluster. So we will make changes in first node and copy it to rest 2 nodes.

By default, MariaDB is configured to check the /etc/mysql/conf.d directory to get additional configuration settings for from ending in .cnf. We will write our configurations in s/etc/mysql/conf.d/galera_cluster.cnf.

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0 # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so  # Galera Cluster Configuration 
wsrep_cluster_name="<name-of-cluster>" wsrep_cluster_address="gcomm://<first-pvt-ip>,<second-pvt-ip>,<third-pvt-ip>"  # Galera Synchronization Configuration 
wsrep_sst_method=rsync  # Galera Node Configuration 
wsrep_node_address="<this-node-ip>" 
wsrep_node_name="<this-node-name>"

Change the variables in <> according to needs and use private IPs in the configuration.

Step 03: Starting cluster

To bring up the first node, we’ll need to use a galera commands. Without using the galera_new_cluster script that allows systemd to pass the the --wsrep-new-cluster parameter, a normal systemctl start mysql would fail because there are no nodes running for the first node to connect with.

sudo galera_new_cluster

Now for rest of the two nodes, start them by:

sudo systemctl start mysql

Hurray! Your MariaDB Cluster is setup completed successfully. You can test the cluster by this:

$ mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+$

Now you can read/write in any nodes and it will be replicated to other nodes.

ORA-15040: diskgroup is incomplete

2016-03-03T17:08:00.003+05:30

ORA-15040: diskgroup is incomplete

I received this error while I was trying to bring up cluster on the second in my 2-node cluster.

When I tried to start cluster, ora.asm resource was found in intermediate state.

[root@host02 ~]# crsctl start cluster
CRS-5702: Resource 'ora.evmd' is already running on 'host02'
CRS-2800: Cannot start resource 'ora.asm' as it is already in the INTERMEDIATE state on server 'host02'
CRS-4000: Command Start failed, or completed with errors.

– Since asm was not up, OCR could not be read and hence cluster could not come up

[root@host02 ~]# crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  INTERMEDIATE host02                   OCR not started

– The alert log showed that DATA diskgroup could not be mounted.

2013-09-04 13:55:38.896
[/u01/app/11.2.0/grid/bin/oraagent.bin(28674)]CRS-5019:All OCR locations are on ASM disk groups [DATA], and none of these disk groups are mounted. Details are at “(:CLSN00100:)” in “/u01/app/11.2.0/grid/log/host02/agent/ohasd/oraagent_grid/oraagent_grid.log”.

– I tried to mount DATA diskgroup using SQL but I got ORA-15040 which indicated that disk “0” could not be read

SQL> alter diskgroup data mount;
alter diskgroup data mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "0" is missing from group number "1"

– When I listed ASM disks on second node, ASMDISK01 was not listed

[root@host02 bin]# oracleasm listdisks
ASMDISK010
ASMDISK011
ASMDISK012
ASMDISK013
ASMDISK014
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
ASMDISK07
ASMDISK08
ASMDISK09

– I issued oracleasm scandisks which led to discovery of ASMDISK01

[root@host02 bin]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "ASMDISK01"

– Now when I issued oracleasm listdisks, ASMDISK01 was also listed.

[root@host02 bin]# oracleasm listdisks
ASMDISK01
ASMDISK010
ASMDISK011
ASMDISK012
ASMDISK013
ASMDISK014
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
ASMDISK07
ASMDISK08
ASMDISK09

— Now I could mount DATA diskgroup successfully

SQL> alter diskgroup data mount;

Diskgroup altered.

Reference:http://oracleinaction.com/ora-15040/

Gather Optimizer statistics, 11g

2016-02-17T16:38:00.002+05:30

Gather Optimizer statistics, 11g

In Oracle 11g: The automatic statistics gathering job for Oracle 11g is an internal procedure named GATHER_DATABASE_STATS_JOB_PROC.

Below are the steps in case if we want to delete & gather schema stats on adhoc basis.

Gathering statistics for all objects in a schema

Ensure that the automatic statistics gathering job is scheduled, using the following SQL.

SELECT ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

Output should be 'TRUE'.

Note: GATHER_STATS_PROG is the program that runs the internal procedure GATHER_DATABASE_STATS_JOB_PROC.

Delete any existing statistics, using the following command from SQLPLUS.

EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

Change the default "ESTIMATE_PERCENT" to 100% for DBMS_STATS. Use the following procedure from SQLPLUS to change the default. Run this using the Oracle SYSDBA user ID:

EXEC DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','100');

d. Run GATHER_DATABASE_STATS using the following command from SQLPLUS. Run this command using the Oracle SYSDBA user ID.

EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', DEGREE => 2, CASCADE => TRUE);

Gathering statistics an individual table

exec dbms_stats.gather_table_stats(  -
       ownname => '  Schema_name ', -
       tabname => '  Table_name  ', -
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  -
       cascade => TRUE,  -
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

NOTE: For a more cautious approach as outlined in the text above and where column statistics are known not to be beneficial, Replace:

method_opt => 'FOR ALL COLUMNS SIZE AUTO'

with

method_opt => 'FOR ALL COLUMNS SIZE 1'

Note that the defaults for statistics gathering on different versions of Oracle are not necessarily the same, for example:

ESTIMATE_PERCENT: defaults:

9i : 100%
10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
11g: DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)

METHOD_OPT: defaults:

9i: "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
10g and 11g: "FOR ALL COLUMNS SIZE AUTO" - This setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan.

Automated Maintenance Tasks in Oracle 11g

2016-02-17T16:30:00.004+05:30

Automated Maintenance Tasks in Oracle 11g

Oracle Database 11g comes with three predefined automatic maintenance tasks:

The Automatic Optimizer Statistics Collection task collects statistics for all objects with no or stale statistics.
The Automatic Segment Advisor task provides advice on which database segments have free space that can be reclaimed.
Automatic SQL Tuning Advisor task, which examines SQL statement performance and makes SQL profile recommendations to improve the statements.

You can query the DBA_AUTOTASK_OPERATION to find out the names and status of the automatic tasks in your database. as shown in the following example:

Oracle PL/SQL

1

2

3

4

5

6

7

SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS   

---------------------------------------------------------------- -------- 

auto optimizer stats collection                                  ENABLED  

auto space advisor                                               ENABLED  

sql tuning advisor                                               ENABLED

These tasks are enabled by default.

All automated maintenance tasks are scheduled to run during the Oracle Scheduler maintenance window. The Oracle Scheduler creates a job when the window (period) starts. After the automated task job completed, the Oracle Scheduler automatically drops that job.

You can view all the predefined maintenance windows by querying the DBA_AUTOTASK_SCHEDULE view:

Oracle PL/SQL

1

2

3

4

5

6

7

8

9

10

SELECT * FROM dba_autotask_schedule;

WINDOW_NAME          START_TIME    DURATION    

-------------------- ------------- ----------- 

MONDAY_WINDOW        08/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   

MONDAY_WINDOW        15/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   

MONDAY_WINDOW        22/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   

...  

SUNDAY_WINDOW        21/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0  

SUNDAY_WINDOW        28/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0

You can disable any of the tasks by using the DBMS_AUTO_TASK_ADMIN package. If you want to disable all the automated tasks, issue the following command::

Oracle PL/SQL

1

EXEC dbms_auto_task_admin.disable;

If you want to enable all the automated tasks, issue the following command:

Oracle PL/SQL

1

EXEC dbms_auto_task_admin.enable;

You can also disable (or enable) a specific task. For example, you can issue the following command to disable “gather_stats_prog” task:

Oracle PL/SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

EXEC dbms_auto_task_admin.disable( 'auto optimizer stats collection', 

NULL, NULL );

SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS   

---------------------------------------------------------------- -------- 

auto optimizer stats collection                                  DISABLED 

auto space advisor                                               ENABLED  

sql tuning advisor                                               ENABLED

EXEC dbms_auto_task_admin.enable( 'auto optimizer stats collection', 

NULL, NULL );

SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS   

---------------------------------------------------------------- -------- 

auto optimizer stats collection                                  ENABLED  

auto space advisor                                               ENABLED  

sql tuning advisor                                               ENABLED

You can also use the “Automated Maintenance Tasks” screen of Enterprise Manager to manage these automated tasks.

Quest_in_OracleDBA

Query to list all the user and their privileges in mariadb/mysql

Query to list all the user and their privileges in mariadb/mysql

Find which process holds a particular connection to a MySQL database

Find which process holds a particular connection to a MySQL database

Top Metrics to Monitor in Your MySQL Database (Part 2)

Top Metrics to Monitor in Your MySQL Database (Part 2)

By tracking the most useful metrics and reviewing them in the most informative way(s), we strike a balance between over-monitoring and firefighting unforeseen crises.

connections

aborted client and connections

connection errors

buffer pool usage

configuring the buffer pool

important innodb buffer pool metrics

a word about the innodb buffer pool lru algorithm

some mysql performance tuning tips

converting buffer pool metrics to bytes

conclusion

What is a mutex anyway?

Some examples

Should you care as a DBA?

Hot Mutex History

How can you see if a mutex is hot?

Troubleshooting MySQL Performance Issues

Why Database Performance Slows Down

The Importance of Following a Monitoring Plan

Some Root Causes of Performance Issues

Hardware/OS/Network-related causes:

Native and 3rd party application interference:

Database Design:

Database-related Performance Issues

The MySQL Slow Query Log

Viewing the Slow Query Log

The Performance Schema

Configuration

Long-running Processes

Event Timer Units

Unused Indexes

Long-running Queries

Memory Usage

Conclusion

Appendix 1: Where is the MySQL Configuration file located?

Top Metrics to Monitor in Your MySQL Databases (Part 1)

Top Metrics to Monitor in Your MySQL Databases (Part 1)

The secret to knowing what adjustments need to be made to your MySQL database is to perform regular monitoring. Learn some tricks of the trade to be as performant as possible.

Benefits of Following a Monitoring Plan

Database Metric Types

How Often Should Monitoring Be Performed?

Performance Metric Categories

Work Metrics

Resource Metrics

Monitoring Performance

Throughput

Throughput Metrics in MySQL

The Questions and Queries Status Variables

The Threads_running Status Variable

The Slow_queries Server Variable

The sys Schema

Conclusion

How To Configure a Galera Cluster with MariaDB on CentOS 7 Servers

Introduction

Prerequisites

Step 1 — Adding the MariaDB Repositories to All Servers

Step 2 — Installing MariaDB on All Servers

Step 3 — Configuring the First Node

Step 4 — Configuring the Remaining Nodes

Step 5 — Opening the Firewall on Every Server

Step 6 — Creating a SELinux Policy

Step 7 — Starting the Cluster

Stop MariaDB on All Three Servers

Bring Up the First Node

Bring Up the Second Node

Bring Up the Third Node

Step 8 — Testing Replication

Write to the First Node

Read and Write on the Second Node

Read and Write on the Third Node

Read on the First Node

Conclusion

High level Steps involved - Disaster Recovery for Galera Cluster