-
There are a lot of things I love about Prometheus; its data model is fantastic for monitoring applications and PromQL language is often more expressive than SQL for data retrieval needs you have in the observability space. One thing, though, I hate about Prometheus with a deep passion is the behavior of its rate() and similar functions, deeply rooted in the Prometheus computational model, which I was told by the development team is not likely to change.
So What’s the Problem, and Why is it Such a Big Deal?
First – the problem. rate() functions give you the rate of change of the time series for the Interval supplied, so rate(mysql_global_status_questions[10s]) will basically give us the average number of MySQL questions over the last 10seconds. Everything is great so far.
But what if the resolution of this time series is lower than 10 seconds, for example, if we take mysql_global_status_questions measurement only every minute? In this case, rate() function will return nothing and data will disappear from the graph.
What would I like to see instead? Give the common sense answer! If I tell you MySQL Question was 1M at 0:00 and 2M at 10:00, and ask you what the average number of queries per second was from 4:00 to 5:00, you will just use the best estimate you have available and give the average based on the data available.
Of course, such an approach is not without its problems, for example, it is possible MySQL actually went to 10M queries at 5:00 and when was restarted it went to 2M, and then the data will be wrong; yet I believe for most cases having such data is more preferred to having no data available.
Existing “Solutions”
One “solution” Prometheus provides to this problem is irate() function which gives you the “instant rate” based on the last two data points in time series. If you use irate() with a large enough interval, you can avoid getting “no data” but you get into another problem: you’ll be getting very volatile data based on two measurements, which, while of less volatile value, are smoothed over a longer period of time and might be desired.
Another problem with irate() is that only rate() function has such a corresponding function, while other functions such as avg_over_time() or max_over_time() do not have any great options.
One solution, which is often recommended, is to just build your dashboards to match the data capture resolution so you can’t get into such situations.
This is a non-starter for our use case at Percona.
We use Prometheus as a key component in Percona Monitoring and Management (PMM) and the data capture resolution is highly configurable, and so can be different in the different periods of time and different time series in the system. Additionally, most of the dashboards we provide are dynamic, using a lower averaging period as you “zoom in” to the data.
VictoriaMetrics to the Rescue
VictoriaMetrics is a Time Series Database which can be connected to Prometheus using the RemoteWrite backend. It implements Read API, which is mostly compatible with Prometheus as well as MetricsQL, which is mostly compatible with PromQL and offers some additional language features.
VictoriaMetrics has other advantages compared to Prometheus, ranging from massively parallel operation for scalability, better performance, and better data compression, though what we focus on for this blog post is a rate() function handling.
VictoriaMetrics handles rate() function in the common sense way I described earlier!
Let’s take a look at the difference in practice. Here I am using a prototype build of Percona Monitoring and Management with VictoriaMetrics. In the “Questions” panel we use the needlessly complicated formula:
rate(mysql_global_status_questions{service_name=~"$service_name"}[$interval]) or
irate(mysql_global_status_questions{service_name=~"$service_name"}[5m])
Which we can simplify to the “common sense” formula we’d like to use, without workarounds required:
rate(mysql_global_status_questions{service_name=~"$service_name"}[$interval])
Let’s now compare the graphs between Prometheus (Left) and VictoriaMetrics(Right)
1h Range
For 1 hour range, we get high enough resolution for both Prometheus and VictoriaMetrics display data. The differences in the graphs come from the fact it is two separate instances running similar workloads rather than the same data in both data stores.
5min Range
In this case, as you can see, Prometheus shows no data while VictoriaMetrics provides data even if the attempted resolution is 1sec and data is available with only 5 seconds resolution.
Summary
We’re very early in the process evaluating VictoriaMetrics but I’m super thrilled it solves this very annoying problem we have with Prometheus query handling. I wonder if this is a problem for you as well, and if you too find VictoriaMetrics behavior more user-friendly or if Prometheus’ behavior is preferred in your environment.
-
I will try to make it short and clear: if you are writing SQL queries with “NOT IN” like
SELECT … WHERE x NOT IN (SELECT y FROM …)
you have to be sure to first understand what happens when “x” or “y” are NULL: it might not be what you want!…
Facebook
Twitter
LinkedIn
-
This blog is about one of the issues encountered by our Remote DBA Team in one of the production servers. We have a setup of MySQL 5.7 Single Primary (Writer) GR with cluster size of 3 . Due to OOM, the MySQL process in the primary node got killed, this repeated over the course of the time.
We all know about the OOM (out of memory), theoretically, it is a process that the Linux kernel employs when the system is critically low on memory.
In a dedicated DB server, when the OOM triggers the direct impact will be on mysqld process since it will be the most memory consuming one.
Going forward will look into the detailed analysis made to tackle the issue of OOM.
DB Environment:-
Service – Group Replication Cluster
Cluster Nodes – 3
GR mode – Single Primary
MySQL Version – 5.7.24
CPU Core – 4
Memory – 11G
innodb_buffer_pool_size – 8G (72% of RAM)
Analysis:-
We had faced this issue only in the GR Primary node and not in any of the Secondary nodes (ProxySQL takes care of load balancing). The memory allocation is same across the cluster. So we had started to dig deep on memory usage in the primary node. I am splitting this memory issue analysis into 3 levels.
MySQL Engine(Innodb) Memory Utilisation.
MySQL Global Memory Utilisation.
MySQL Group Replication Memory Utilisation.
1).MySQL Engine(Innodb) Memory Utilisation:-
Group Replication supports only the InnoDB engine. So in the case of InnoDB transaction-based engine, The major buffer is innodb_buffer_pool_size. As a rule of thumb, we can set it to 70-80% of the system memory. Depending on the server configuration, So, here we have set it around 70% of RAM to the InnoDB Buffer Pool. ( every rule has exceptions ).
Below I have shared the memory usage by InnoDB at Linux Kernel.
$ sudo pmap -x $(pidof mysqld) | grep -i anon
.
.
.
00007f9d7dd3a000 8543680 6168996 6129452 rw--- [ anon ]
.
.
mydbops@localhost:performance_schema> select (8543680/1024/1024) as 'innodb memory utilisation in GB';
+---------------------------------+
| innodb memory utilization in GB |
+---------------------------------+
| 8.14788818 |
+---------------------------------+
2). Global Memory Utilisation:-
The memory utilization by all the global events in MySQL can be checked using the performance_schema. This includes the memory usage by the below MySQL events.
Events for engine based memory usage ( InnoDB, MyISAM, memory, blackhole, temptable and others. )
Events for the SQL Layer (memory/SQL)
Events for the client-server communication memory usage (memory/client)
To get these events output, the respective memory instrumentation in performance_schema. setup_instruments table were enabled.
update performance_schema.setup_instruments set ENABLED = 'YES' where NAME like 'memory/%';
After enabling the instruments, we can fetch the memory usage at all the global events. Observing the stats it is found that they events took only 172MB on an average.
mydbops@localhost:performance_schema> select (sum(current_number_of_bytes_used))/1024/1024 'Global memory usage in MB' from memory_summary_global_by_event_name;
+---------------------------+
| Global memory usage in MB |
+---------------------------+
| 172.50711823 |
+---------------------------+
In combination, MySQL engine (Innodb) [ 8.14GB] and global MySQL events memory usage[172MB] total usage is clocked around 8.3GB at MySQL level.
But when we observed the Linux Kernel the total memory usage of MySQL process is around 10GB.
$ sudo pmap -x $(pidof mysqld) | tail -n 1
total kB 10395760
So, now the question is,
Where the remaining 1.7GB is utilized ?
Which process inside MySQL is consuming this memory?
Do we have events in Performance Schema to measure this memory leak?
Here is the answer, The remaining 1.7GB is used by the Group Replication Cache. Let’s see how.
3). Group Replication Memory Utilisation:-
Commonly in Group Replication, the primary node will capture the recent events in the memory cache. Using this cache other nodes will get the state transfer. In MySQL version 5.7, there is no variable to control this memory cache utilization, it grows exponentially and also it doesn’t have any events to calculate the memory utilization,
Because of this limitation, we have faced the OOM in MySQL 5.7 GR.
To overcome this issue, the XCOM Cache Management has been introduced from MySQL 8.0.16. From this version, where we can control the cache used by Group Replication using the variable group_replication_message_cache_size. And also MySQL has introduced one more event with the name of ‘GCS_XCom::xcom_cache‘. By fetching the details from this event in the performance_schema.memory_summary_global_by_event_name table, we can get the memory consumption by the Group Replication Cache.
Query:-
mysql>SELECT EVENT_NAME,CURRENT_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/group_rpl/GCS_XCom::xcom_cache';
+---------------------------------------+------------------------------+
| EVENT_NAME | CURRENT_NUMBER_OF_BYTES_USED |
+---------------------------------------+------------------------------+
| memory/group_rpl/GCS_XCom::xcom_cache | 1071827570 |
+---------------------------------------+------------------------------+
Conclusion:-
Memory Leak in GR 5.7 was due to the Group replication message cache.
To avoid this issue,
Permanent Fix – Upgrade to MySQL 8.0.16 and get the benefits from the XCOM Cache Management.
Temporary Fix – Reduce the Global Buffer Innodb_buffer_pool_size.
-
The blog title seems something crazy ?
Yes, by default your InnoDB transactions don’t have the Atomicity . But, you can control the atomicity of your InnoDB transaction with the variable innodb_rollback_on_timeout. We had a weird data inconsistency issue within the transaction . In this blog , I am going to explain “How it will happen?” and “How it can be fixed ?” .
Whats is Atomicity ?
From Wiki ,
Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged.
What is Innodb_rollback_on_timeout ?
From MySQL documentation,
InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb-rollback-on-timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction.
Here the transaction timeout will be happen based on the “innodb_lock_wait_timeout” setting .
All good, now we can experiment how it is working practically .
Experiment 1 ( Innodb_rollback_on_timeout = OFF )
I have created two MySQL client sessions ,
session 1 :
innodb_rollback_on_timeout=OFF
innodb_wait_timeout=50
Started a transaction
Ran the UPDATE to set name=’christ’ where id=1 ( not COMMIT )
now I am going to lock the second row ( id=2 ) at different session ( session 2 )
session 2 :
Started the transaction at session 2
Ran the UPDATE to set name=’sri’ where id=2 ( not COMMIT )
Now, I am going to update the same id = 2 at session 1 , so that it will exceed the innodb_lock_wait_timeout and the statement will fail .
session 1 :
Trying to access the row ( id=2 ) through the UPDATE
The UPDATE was failed with lock wait timeout because the session 2 is already hold that row .
Triggered the COMMIT
But after commit the transaction,
My first UPDATE was committed even the second statement was failed . You can see the output from the screenshot .
Sounds like ATOMICITY is missing ?
Lets do the second experiment …
Experiment 2 ( Innodb_rollback_on_timeout = ON ) :
I am going to repeat the same procedure with innodb_rollback_on_timeout=ON,
session 1 :
started the transaction and updated the row id=1
session 2 :
locking the row id=2
session 1 :
Creating the lock wait timeout inside the transaction and committing the transaction .
No changes happened . Make sense ?
With innodb_rollback_on_timeout = ON , Both the statements were not committed and the complete transaction has rolled back . THE ENTIRE TRANSACTION HAS ROLLBACK . So, this is the exact Atomicity right ?
Note : Changing the innodb_rollback_on_timeout requires the MySQL restart .
Hope this blog helps to understand the variable innodb_rollback_on_timeout . The blog proves, Having the innodb_rollback_on_timeout = ON provides the perfect/complete atomicity on your InnoDB system .
Thanks !!!
-
MySQL Connector in 8.0.19 includes the support of DNS SRV.The following URL can be a good starting point to understand what this feature is about.https://www.slideshare.net/Grypyrg/mysql-connectors-8019-dns-srvhttps://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-dns-srv.htmlhttps://learn.hashicorp.com/consul/security-networking/forwarding MySQL Router in MySQL InnoDB Cluster setup offers the transparent access to the backend database servers. However, the application has to know the IP Address of the Router in order to get access to the InnoDB Cluster.In general, MySQL Router can be installed together with the application. By doing this, the application can access to 'localhost' / 127.0.0.1 as IP address to the MySQL Router.If the MySQL Router is installed on separated machine or VM, there is external service to discover the ROUTER IP address and also about the health check with the Router(s).To achieve external access to Router instead of 'localhost' access within the same box deployment with the application, there are couple of options1. Load Balancer 2. Virtual IP failover3. Hard coded in application about the IPs of MySQL Routers4. New!! - Using DNS SRV support from MySQL Connector(other options such as VM respawn, etc...)This article is written to share a DEMO setup of DNS SRV support in MySQL Connector/J 8.0.19 with Consul.The DEMO setup with Consul is not the scope of this article. In general, it provides a simple testing idea with the DNS SRV support with Connector/J in 8.0.19.Environment1. MySQL InnoDB Cluster A running 3 node InnoDB Cluster.For example : MySQL [primary ssl] JS> dba.getCluster().status(){ "clusterName": "mycluster", "defaultReplicaSet": { "name": "default", "primary": "primary:3310", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "primary:3310": { "address": "primary:3310", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.19" }, "primary:3320": { "address": "primary:3320", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.19" }, "primary:3330": { "address": "primary:3330", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.19" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "primary:3310"}2. MySQL Router running on 2 VMs - port = 6446 as RW routing a. IP 192.168.56.41:6446 b. IP 192.168.56.42:6446[mysql@virtual-42 lab]$ mysql -udemo -pdemo -h192.168.56.41 -P6446 -e "select @@hostname, @@port;"mysql: [Warning] Using a password on the command line interface can be insecure.+----------------------+--------+| @@hostname | @@port |+----------------------+--------+| virtual-41.localhost | 3310 |+----------------------+--------+[mysql@virtual-42 lab]$ mysql -udemo -pdemo -h192.168.56.42 -P6446 -e "select @@hostname, @@port;"mysql: [Warning] Using a password on the command line interface can be insecure.+----------------------+--------+| @@hostname | @@port |+----------------------+--------+| virtual-41.localhost | 3310 |+----------------------+--------+ 3. Consul and Register service router.service.consul to point to the 2 VM Router Services.4. Java Application connecting to Router Service1. Consul Download and SetupRefer to https://www.consul.io/docs/install/index.html a. Download the binary for the platform b. Assuming this is Linux box environment, as demo : copy the binary "consul" to /sbin (as root)2. Create the service json /etc/consul.d/router.json# cat /etc/consul.d/router.json{ "services" : [ { "id": "router1", "name": "router", "tags": [ "rw" ], "address": "192.168.56.41", "port": 6446, "checks": [ { "args": ["/demo/consul/check1-6446.sh"], "interval": "10s" } ] }, { "id": "router2", "name": "router", "tags": [ "rw" ], "address": "192.168.56.42", "port": 6446, "checks": [ { "args": ["/demo/consul/check2-6446.sh"], "interval": "10s" } ] } ]}2. Health Check Script for Services (/demo/consul/check1-6446.sh and /demo/consul/check2-6446.sh)Creating the health check script as (Assuming mysql program is in the path)<check1-6446.sh>#! /bin/bashmysql -ugradmin -pgrpass -hprimary -P6446 -e "select 1;" > /dev/null 2>&1if [ $? != 0 ]then exit 2fi<check2-6446.sh>#! /bin/bashmysql -ugradmin -pgrpass -hsecondary -P6446 -e "select 1;" > /dev/null 2>&1if [ $? != 0 ]then exit 2fi3. Startup consul agent with root (so that it listens to port=53 instead of 8600)The default DNS PORT is 8600. However DNS service is to be run on port 53. Connector search for DNS Service which has to be running on port 53.Options a. consul running on port 53b. forwarding consul service to DNS services (Please check on other resources : e.g. https://learn.hashicorp.com/consul/security-networking/forwarding )Note : This article is demo only. consul is used but there are other choices.# consul agent -dev -enable-script-checks -dns-port=53 -config-dir=/etc/consul.d &Once the agent is started, you can find the services running as# curl http://127.0.0.1:8500/v1/health/checks/router[ { "Node": "virtual-42.localhost", "CheckID": "service:router1", "Name": "Service 'router' check", "Status": "passing", "Notes": "", "Output": "", "ServiceID": "router1", "ServiceName": "router", "ServiceTags": [ "rw" ], "Type": "script", "Definition": {}, "CreateIndex": 12, "ModifyIndex": 14 }, { "Node": "virtual-42.localhost", "CheckID": "service:router2", "Name": "Service 'router' check", "Status": "passing", "Notes": "", "Output": "", "ServiceID": "router2", "ServiceName": "router", "ServiceTags": [ "rw" ], "Type": "script", "Definition": {}, "CreateIndex": 13, "ModifyIndex": 15 }]4. Change the /etc/resolv.conf search localhostnameserver 127.0.0.1domain consul5. Test the Java Application a. Make sure the Connector/J 8.0.19 driver is downloaded. b. Sample Source Attached - c. To run :java -cp ./mysql-connector-java-8.0.19.jar:. LBDemo01The output is simply like this java -cp ./mysql-connector-java-8.0.1jar:. LBDemo01Spawned threads : 3By killing/stopping one of the ROUTER, the application can continue to run.Sample Java Application - LBDemo01.java
cat LBDemo01.java// package mysql.demo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import com.mysql.cj.jdbc.Driver;public class LBDemo01 { private static long ttltime=0; private static String driver = "com.mysql.cj.jdbc.Driver"; private static String database = "test"; private static String baseUrl = "jdbc:mysql+srv://demo:demo@router.service.consul"; private static String user = "demo"; private static String password = "demo"; public static void main(String[] args) throws Exception { createTable(); ArrayList<Thread> threads = new ArrayList<Thread>(10);; for (int i=0;i<3;i++) { Thread t = new Thread(new Repeater()); t.start(); threads.add(t); } System.out.println("Spawned threads : " + threads.size()); for(int i=0;i<threads.size();i++) { ((Thread) threads.get(i)).join(); } System.out.println("Finished - " + ttltime); } private static void createTable() throws ClassNotFoundException, SQLException { Connection c = getNewConnection(); try { c.setAutoCommit(false); Statement s = c.createStatement(); s.executeUpdate("create table if not exists test.mytable (f1 int auto_increment not null primary key, f2 varchar(200)) engine=innodb;"); c.commit(); } catch (SQLException e) { e.printStackTrace(); } c.close(); } static Connection getNewConnection( ) throws SQLException, ClassNotFoundException { java.util.Properties pp = new java.util.Properties(); pp.setProperty("user", user); pp.setProperty("password", password); // black list for 60seconds pp.setProperty("loadBalanceBlacklistTimeout", "60000"); pp.setProperty("autoReconnect", "false"); Class.forName(driver); return DriverManager.getConnection(baseUrl, pp); } static void executeSimpleTransaction(Connection c, int conn, int trans){ try { c.setReadOnly(false); c.setAutoCommit(false); Statement s = c.createStatement(); s.executeUpdate("insert into test.mytable (f2) values ('Connection: " + conn + ", transaction: " + trans +"');" ); c.commit(); } catch (SQLException e) { e.printStackTrace(); } } public static class Repeater implements Runnable { public void run() { for(int i=0; i < 1000; i++){ try { Connection c = getNewConnection(); long mystart, myend, myttl=0; for(int j=0; j < 10; j++){ // To register the start time mystart = System.currentTimeMillis(); executeSimpleTransaction(c, i, j); // To time the execution time and save it onto the totaltime myend = System.currentTimeMillis(); myttl += (myend - mystart); incTTL(myttl); Thread.sleep(Math.round(100 * Math.random())); } c.close(); Thread.sleep(200); } catch (Exception e) { e.printStackTrace(); } } } } public synchronized static void incTTL(long m) { ttltime += m; }}
Greenways residents are enjoying a regular pancake morning in the Community Centre. A gold coin donation was collected to assist the children’s ward at the Frankston Hospital. Read More…
One of the great advantages of living at Greenways Village is the ability to just lock the door and travel. The village staff take care of the day to day, looking after our home while we are away. Testimonials
