Sidebar

Main Menu Mobile

  • Home
  • Blog(s)
    • Marco's Blog
  • Technical Tips
    • MySQL
      • Store Procedure
      • Performance and tuning
      • Architecture and design
      • NDB Cluster
      • NDB Connectors
      • Perl Scripts
      • MySQL not on feed
    • Applications ...
    • Windows System
    • DRBD
    • How To ...
  • Never Forget
    • Environment
TusaCentral
  • Home
  • Blog(s)
    • Marco's Blog
  • Technical Tips
    • MySQL
      • Store Procedure
      • Performance and tuning
      • Architecture and design
      • NDB Cluster
      • NDB Connectors
      • Perl Scripts
      • MySQL not on feed
    • Applications ...
    • Windows System
    • DRBD
    • How To ...
  • Never Forget
    • Environment

Another time, another place… about next conferences

Details
Marco Tusa
MySQL
17 September 2019

Missed OpportunityIs “that” time of the year … when autumn is just around the corner and temperature start to drop.
But is also the time for many exciting conferences in the MySQL world.
We have the Oracle Open world this week, and many interesting talks around MySQL 8.
Then in 2 weeks we will be at the Percona live Europe in Amsterdam, which is not a MySQL (only) conference anymore (https://www.cvent.com/events/percona-live-open-source-database-conference-europe-2019/agenda-6321c2468b1b43328f97212f3e53f4de.aspx).
Percona had move to a more “polyglot” approach not only in its services but also during the events.
This is obviously an interesting experiment, that allow people from different technologies to meet and discuss. At the end of the day it is a quite unique situation and opportunity, the only negative effect is that it takes space from the MySQL community, who is suffering a bit in terms of space, attendee and brainstorming focus on MySQL deep dive.
Said that there are few interesting talks I am looking to attend:
• Security and GDPR, many sessions
• MySQL 8.0 Performance: Scalability & Benchmarks
• Percona will also present the Percona cluster version 8, which is a must attend session
Plus the other technologies which I am only marginally interested to.

After Percona live in Amsterdam there will be a ProxySQL technology day in Ghent (https://proxysql.com/blog/proxysql-technology-day-ghent-oct3rd2019). Ghent is a very nice city and worth a visit, to reach it from Amsterdam is only 2hrs train. Given this event is the 3td of October I will just move there immediately after PLEU.
The ProxySQL event is a mid-day event starting at 5PM, with 30 minutes sessions focus on best practices on how to integrate the community award winning solution “ProxySQL” with the most common scenario and solutions.
I like that because I am expecting to see and discuss real cases and hands on issues with the participants.

So, a lot of things, right?
But once more, I want to raise the red flag about the lack of a MySQL community event.
We do have many events, most of them are following companies focus, and they are sparse and not well synchronized. Given that more than anything else, we miss A MySQL event. A place where we can group around and not only attract DBAs from companies who use it and sometime abuse it, but also a place for all of us to discuss and coordinate the efforts.

In the meantime, see you in Amsterdam, then Ghent, then Fosdem then …
Good MySQL to all

A small thing that can save a lot of mess, meet: SET PERSIST

Details
Marco Tusa
MySQL
13 August 2019

To set correct system variable values is the essential step to get the correct server behavior against the workload.  

In MySQL we have many System variables that can be changed at runtime, most of them can be set at session or at global scope. small things

To change the value of a system variable at global level in the past user need to have SUPER privileges. Once the system variable value is modified as global, the server will change his behavior for the session, and obviously as global scope. For instance, one of the most commonly adjusted variables is probably max_connections. If you have max_connection=100 in your my.cnf or as default value, and during the day as DBA you notice that the number of them is not enough, it is easy just to add new connections on the fly, the command:

SET GLOBAL MAX_CONNECTIONS=500;

Will do the work. But here is the issue. We had changed a GLOBAL value, that apply to the whole server, but this change is ephemeral and if the server restarts the setting is lost. In the past I have seen many times servers with different configurations between my.cnf and current Server settings. To prevent this or at least keep it under control good DBAs had develop scripts to checks if and where the differences exists and fix it. The main issue is that very often, we forget to update the configuration file while doing the changes, or we do on purpose to do "Fine tuning first” and forgot after.

What's new in MySQL8 about that? Well we have a couple of small changes. First of all, the privileges, as for MySQL8 user can have SYSTEM_VARIABLES_ADMIN or SUPER to modify the GLOBAL system variables. The other news is related to the ability to have GLOBAL changes to variable to PERSIST on disk and finally to know who did it and when. The new option for SET command is PERSIST. So, if I have:

(root@localhost) [(none)]>show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1500  |
+-----------------+-------+

and I want to change the value of max_connection and be sure this value is reloaded at restart, I will do:

(root@localhost) [(none)]>set PERSIST max_connections=150;

(root@localhost) [(none)]>show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 150   |
+-----------------+-------+

With the usage of PERSIST, MySQL will write the information related to: - key (variable name) - value - timestamp (including microseconds) - user - host A new file in the data directory: mysqld-auto.cnf contains the information The file is in Json format and will have the following:

{ "Version" : 1 , "mysql_server" : {
 "max_connections" : { 
"Value" : "150" , "Metadata" : {
 "Timestamp" : 1565363808318848 , "User" : "root" , "Host" : "localhost" 
} } } }

The information is also in Performance Schema:

select a.VARIABLE_NAME,b.VARIABLE_value ,SET_TIME,SET_USER,SET_HOST  
    from performance_schema.variables_info a 
        join performance_schema.global_variables b 
        on a.VARIABLE_NAME=b.VARIABLE_NAME  
    where b.VARIABLE_NAME like 'max_connections'\G

*************************** 1. row ***************************
 VARIABLE_NAME: max_connections
VARIABLE_value: 150
      SET_TIME: 2019-08-09 11:16:48.318989
      SET_USER: root
      SET_HOST: localhost

As you see the information present, report who did the change, from where, when, and the value. Unfortunately, there is no history here, but this can be easily implemented. To clear the PERSIST settings, run RESET PERSIST and all the Persistent setting will be removed. To be clear if you have:

{ "Version" : 1 , "mysql_server" : {
  "max_connections" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565367524946371 , "User" : "root" , "Host" : "localhost" } } , 
  "wait_timeout" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565368154036275 , "User" : "root" , "Host" : "localhost" } } 
} }

RESET PERSISTE will do:

{ "Version" : 1 , "mysql_server" : {  }

Which is removing ALL THE SETTINGS not just one.

Anyhow why is this a good thing to have?

First because we have no excuse now, when we change a variable, we have all the tools needed to make sure we will have it up at startup if this is the intention of the change.

Second is good because storing the information in a file, and not only showing it from PS, allow us to include such information in any automation tool we have.
This in the case we decide to take action or just to keep track of it, like comparison with my.cnf and fixing the discrepancies automatically also at service down or when cloning.


On this let me say that WHILE you can change the value in the file mysqld-auto.cnf, and have the server at restart use that value as the valid one.
This is not recommended, instead please fix the my.cnf and remove the information related to PERSIST.
To touch that file is also dangerous because if you do stupid things like removing a double quote or in any way affecting the Json format, the server will not start, but there will be NO error in the log.

{ "Version" : 1 , "mysql_server" : { "wait_timeout": { "Value : "150" , "Metadata" : { "Timestamp" : 1565455891278414, "User" : "root" , "Host" : "localhost" } } } }
                                                           ^^^ missing double quote 
tusa@tusa-dev:/opt/mysql_templates/mysql-8.0.17futex$ ps aux|grep 8113
tusa      8119  0.0  0.0  14224   896 pts/1    S+   12:54   0:00 grep --color=auto 8113
[1]+  Exit 1                  bin/mysqld --defaults-file=./my.cnf

I have opened a bug for this (https://bugs.mysql.com/bug.php?id=96501).

A short deep dive in the code (you can jump it if you don't care)

The new feature is handled in the files <source>/sql/persisted_variable.(h/cc) The new structure dealing with the PERSIST actions is:

struct st_persist_var {
  std::string key;
  std::string value;
  ulonglong timestamp;
  std::string user;
  std::string host;
  bool is_null;
  st_persist_var();
  st_persist_var(THD *thd);
  st_persist_var(const std::string key, const std::string value,
                 const ulonglong timestamp, const std::string user,
                 const std::string host, const bool is_null);
};

And the main steps are in the constructors st_persist_var. About that, should be noted that when creating the timestamp the code is generating a value that is NOT fully compatible with the MySQL functions FROM_UNIXTIME. The code assigning the timestamp value pass/assign also the microseconds passing them from the timeval (tv) structure:

st_persist_var::st_persist_var(THD *thd) {
  timeval tv = thd->query_start_timeval_trunc(DATETIME_MAX_DECIMALS);
  timestamp = tv.tv_sec * 1000000ULL + tv.tv_usec;
  user = thd->security_context()->user().str;
  host = thd->security_context()->host().str;
  is_null = false;
}

Where:

    tv.tv_sec = 1565267482
    tv.tc_usec = 692276

this will generate: timestamp = 1565267482692276 this TIMESTAMP is not valid in MySQL and cannot be read from the time functions, while the segment related to tv.tv_sec = 1565267482 works perfectly.

(root@localhost) [(none)]>select FROM_UNIXTIME(1565267482);
+---------------------------+
| FROM_UNIXTIME(1565267482) |
+---------------------------+
| 2019-08-08 08:31:22       |
+---------------------------+

(root@localhost) [(none)]>select FROM_UNIXTIME(1565267482692276);
+---------------------------------+
| FROM_UNIXTIME(1565267482692276) |
+---------------------------------+
| NULL                            |
+---------------------------------+

this because the timestamp with microsecond is formatted differently in MySQL : PERSIST_code = 1565267482692276 MySQL = 1565267482.692276 If I run: select FROM_UNIXTIME(1565267482.692276); I get the right result:

+----------------------------------+
| FROM_UNIXTIME(1565267482.692276) |
+----------------------------------+
| 2019-08-08 08:31:22.692276       |
+----------------------------------+

of course, I can use the trick:

select FROM_UNIXTIME(1565267482692276/1000000);
+-----------------------------------------+
| FROM_UNIXTIME(1565267482692276/1000000) |
+-----------------------------------------+
| 2019-08-08 08:31:22.6922                |
+-----------------------------------------+

Well that's all for the behind the scene info, keep in mind if you want to deal with the value coming from the Json file.

Conclusion

Sometimes the small things can be better than the HUGE shining things. I saw many times DBAs in trouble because they do not have this simple feature in MySQL, and many MySQL failing to start, or behave not as expected. Given that, I welcome PERSIST and I am sure that the people who manage thousands of servers, have different workloads and automation in place, will see this as a good thing as well.

References:

https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestamp
https://lefred.be/content/where-does-my-mysql-configuration-variable-value-come-from/
https://lefred.be/content/what-configuration-settings-did-i-change-on-my-mysql-server/

 

mysql Configuration MySQL8 variables

My first impression on Mariadb 10.4.x with Galera4

Details
Marco Tusa
MySQL
27 July 2019

MariaDB 10.4 has being declared GA, and few presentations on Galera4 in the last conferences were hold.

So, I thought, it is time to give it a try and see what is going on.

It is not a secret that I consider the Codership guys my heroes, and that I have push for Galera as solid and very flexible HA solution, for many years.

Given that my first comment is that it was a shame to have Galera4 available only in MariaDB, I would have preferred to test the MySQL vanilla version for many reasons, but mainly because the MySQL/Oracle is and remain the official and main line of the MySQL software, you like it or not, and as such I want to see how the Galera4 behave with that. Anyhow Codership state that the other versions will be out AFTER the summer, and I hope this will be true.

To test the new version given I do not have the vanilla MySQL, I decide to use the other distribution coming from Percona. At the end the test where done comparing MariaDB 10.4.x with PXC 5.7.x. In short Galera4 Vs Galera3.

I setup on the same machines the two different software, and I configure as close as possible. Said that I did 2 main set of tests: Data ingest and OLTP, both running for 90 minutes, not pushing like hell, but gently simulate some traffic. Configuration files can be found here.

Galera4 stream replication was disable, following the Codership instruction (wsrep_trx_fragment_size=0).

Test1 Ingest

For the ingest test I had use my stresstool application (here) with only 10 threads and 50 batch inserts each thread, the schema definition is in the windmills.json file.

As always, an image says more than many words:

ingest execution by thread

In general terms, PXC was able to execute same load in less than MariaDB.

ingest events thread

And PXC was able to deal with a higher number of events per thread as well.

The average galera latency was around 9ms in the writer and around 5ms for the receivers in PXC. With same load, same machines, same configuration:

Screen Shot 2019 07 24 at 21658 PM

The latency in MariaDB was significantly higher around 19ms for the writer, and between 9 and 5 ms for the receivers.

Screen Shot 2019 07 24 at 13350 PM

In short overall PXC 5.7 with galera3 was performing better than MariaDB 10.4 with galera4.

The amount of data on transmitted and received on PXC was higher (good) than Mariadb:

PXC:

Screen Shot 2019 07 24 at 22127 PM

MariaDB:

Screen Shot 2019 07 24 at 14453 PM

OLTP

For oltp test I have sysbenc with oltp r/w tests, 180 threads (90 from two different application nodes), 200K rows for table, 40 tables and 90 minutes run.

Let see what happened:

oltp evens write

PXC was performing better than MariaDB, executing more writes/s and and more events_thread/sec.

Checking the latency, we can see:

oltp latency

Also in this case PXC was having less average latency than MariaDB in the execution.

What about galera?

For PXC/Galera3, the average galera latency was around 3.5ms in the writer and less in the receivers:

Screen Shot 2019 07 26 at 124919 PM

In this case the latency in Galera4 was same or less of the one in Galera3:

Screen Shot 2019 07 26 at 51341 PM

Also analyzing the MAX latency:

Galera3

Screen Shot 2019 07 26 at 124936 PM

Galera4

Screen Shot 2019 07 26 at 51354 PM

We can see that Galera4 was dealing with it much better than the version3.

 

I have done many other checks and it seems to me that in the OLTP, but I do not exclude this is valid for ingest as well, Galera4 is in some way penalize by the MariaDB platform.

I am just at the start of my investigation and I may be wrong, but I cannot confirm or deny until Codership will release the code for MySQL.

Conclusions

Galera4 seems to come with some very good new feature, please review Seppo presentation, and one thing I noticed it comes with optimized node communication, reducing the latency fluctuation.

Just this for me is great, plus we will have stream replication that can be very useful but I have not tested it yet.

Nevertheless, I would not move to it just yet. I would wait to have the other MySQL distribution supported, do some tests, and see where the performance problem is.

Because at the moment also with not heavy load, current version of PXC 5.7/Galera3 runs better than MariaDB/Galera4, so why I should migrate to a platform that locks me in like MariaDB, and do not give me benefit (yet)? Also considering that once Galera4 will be available for the standard MySQL versions, we can have all the good coming from Galera4, without being lock in by MariaDB.

A small note about MariaDB, while I was playing with it, I noticed that by default MariaDB comes with the plugin level BETA, which means potentially run in production code that is still in beta stage, no comment!surprise emo

References

https://github.com/Tusamarco/blogs/tree/master/Galera4

https://galeracluster.com/

Seppo presentation https://www.slideshare.net/SakariKeskitalo/galera-cluster-4-presentation-in-percona-live-austin-2019

https://mariadb.org/mariadb-10-4-4-now-available/

mysql community Mariadb oracle Percona galera codership performance

More Articles ...

  1. Reasoning around the recent conferences in 2019
  2. ProxySQL Native Support for Percona XtraDB Cluster (PXC)
  3. How Not to do MySQL High Availability: Geographic Node Distribution with Galera-Based Replication Misuse
  4. MySQL High Availability On-Premises: A Geographically Distributed Scenario
  5. MySQL 8: Load Fine Tuning With Resource Groups
  6. PXC loves firewalls (and System Admins loves iptables)
  7. No orange pants this year
  8. Leveraging ProxySQL with AWS Aurora to Improve Performance
  9. How to Implement ProxySQL with AWS Aurora
  10. ProxySQL server version impersonation
  11. ProxySQL Firewalling
  12. ProxySQL PXC Single Writer Mode and auto failover, re-bootstrap
  13. How ProxySQL deal with schema (and schemaname)
  14. How ProxySQL deal with schema (and schemaname) Long story
  15. Sweet and sour can become bitter
  16. Group-Replication, sweet & sour
  17. ProxySQL and Mirroring what about it?
  18. InnoDB Page Merging and Page Splitting
  19. Setup ProxySQL as High Available (and not a SPOF)
  20. ProxySQL – Percona Cluster (Galera) integration
  21. ProxySQL and MHA integration
Page 15 of 22
  • Start
  • Prev
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • Next
  • End

Related Articles

  • The Jerry Maguire effect combines with John Lennon “Imagine”…
  • The horizon line
  • La storia dei figli del mare
  • A dream on MySQL parallel replication
  • Binary log and Transaction cache in MySQL 5.1 & 5.5
  • How to recover for deleted binlogs
  • How to Reset root password in MySQL
  • How and why tmp_table_size and max_heap_table_size are bounded.
  • How to insert information on Access denied on the MySQL error log
  • How to set up the MySQL Replication

Latest conferences

We have 2842 guests and no members online

login

Remember Me
  • Forgot your username?
  • Forgot your password?
Bootstrap is a front-end framework of Twitter, Inc. Code licensed under MIT License. Font Awesome font licensed under SIL OFL 1.1.