MySQL

This post is the first post of a series of articles on extending MySQL with the Component Infrastructure, the list above will be updated as new articles are published: Extending MySQL using the Component Infrastructure – part 1Extending MySQL using ... [More] the Component Infrastructure – part 2: building the serverBefore MySQL 8.0, to add a feature to MySQL, writing a plugin was the only way. Now it’s possible to quickly extend MySQL Server by writing a component. The MySQL Component Infrastructure is designed to overcome some of the architectural issues of the plugin subsystem, namely: plugins can only “talk” to the server and not with other pluginsplugins have access to the server symbols and can call them directly (no encapsulation)there’s no explicit set of dependencies of a plugin, thus it’s hard to initialize them properlyplugins require a running server to operateThis article is the first of a series about writing our first usable component. Our Component To show how to create a component, I didn’t want to create the usual “hello world” example. Recently, I got a question about how is it possible to deal with Viruses in MySQL ? Usually, the data should be scanned before being inserted on the database of course. But, yes, it could be a good idea to also have the possibility to scan data stored in MySQL. Generally, a anti-virus is scanning files on the filesystem. But that doesn’t work when compression or encryption is used. This is the same table with the EIRCAR TEST virus stored in one column: Standard InnoDB Table: [root@imac virus]# clamscan t1.ibd /var/lib/mysql/virus/t1.ibd: {HEX}EICAR.TEST.3.UNOFFICIAL FOUND ----------- SCAN SUMMARY ----------- Known viruses: 8763291 Engine version: 0.103.4 Scanned directories: 0 Scanned files: 1 Infected files: 1 Data scanned: 0.11 MB Data read: 0.11 MB (ratio 1.00:1) Time: 15.812 sec (0 m 15 s) Start Date: 2021:12:21 10:44:28 End Date: 2021:12:21 10:44:44 Compressed InnoDB Table: mysql> alter table t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; [root@imac virus]# clamscan t1.ibd /var/lib/mysql/virus/t1.ibd: OK ----------- SCAN SUMMARY ----------- Known viruses: 8763291 Engine version: 0.103.4 Scanned directories: 0 Scanned files: 1 Infected files: 0 Data scanned: 0.11 MB Data read: 0.05 MB (ratio 2.00:1) Time: 16.338 sec (0 m 16 s) Start Date: 2021:12:21 11:00:58 End Date: 2021:12:21 11:01:15 Encrypted InnoDB Table: mysql> alter table t1 encryption='y'; [root@imac virus]# clamscan t1.ibd /var/lib/mysql/virus/t1.ibd: OK ----------- SCAN SUMMARY ----------- Known viruses: 8763291 Engine version: 0.103.4 Scanned directories: 0 Scanned files: 1 Infected files: 0 Data scanned: 0.22 MB Data read: 0.11 MB (ratio 2.00:1) Time: 15.990 sec (0 m 15 s) Start Date: 2021:12:21 10:53:58 End Date: 2021:12:21 10:54:14 As you can see, the same virus stored in the table is not detected while scanning the .idb file on the filesystem when the tablespace is compressed and/or encrypted. Therefor, the component we will create through this series will scan data for known viruses using ClamAV library installed on the same server as MySQL. ClamAV® is an open-source (GPL) anti-virus engine used in a variety of situations, including email and web scanning, and endpoint security. It provides many utilities for users, including a flexible and scalable multi-threaded daemon, a command-line scanner and an advanced tool for automatic database updates. Component Functionalities Our component will create a function (similar to previous User Defined Function) to scan the data sent as parameter: virus_scan(). Our component will also create a new privilege required to use the new function: VIRUS_SCAN. Our component will also write messages to the MySQL Server’s error log. We will also add a second function to reload the ClamAV engine in case of a virus database update: virus_reload_engine(). And finally, our component will create two status variables to store the clamav total signatures loaded and the amount of eventual viruses found: viruscan.clamav_signature & viruscan_virus_found. Development Requirements To be able to create our component, we need to have the MySQL Server source (from GitHub) and clamav-devel and clamav-lib. clamav-lib will also be required on the MySQL Server where the component will be installed. As teaser this is an overview of our final component: Conclusion To prepare the next article, you can already start to download the source of MySQL Server. In our next article we will setup your development environment and build MySQL from source. Enjoy MySQL and get ready to extend it ! [Less]

Recently we upgrade our database cluster to version 8.x . Read this PDF to get some  experience    MySQL_8.x_upgrade

With modern application design, systems are becoming more diverse, varied and have more components than ever before. Developers are often forced to become master chefs adding the ingredients from dozens of different technologies and blending them ... [More] together to create something tasty and amazing. But with so many different ingredients, it is often difficult to understand how the individual ingredients interact with each other. The more diverse the application, the more likely it is that some seemingly insignificant combination of technology may cause cascading effects. Many people I talk to have hundreds if not thousands of different libraries, APIs, components, and services making up the systems they support. In this type of environment, it is very difficult to know what small thing could add up to something much bigger. Look at some of the more recent big cloud or application outages, they often have their root cause in something rather small. Street Fight: Python 3.10 -vs- 3.9.7 Let me give you an example of something I ran across recently. I noticed that performance on two different nodes running the same hardware/application code was performing drastically different than one another. The app server was running close to 100% CPU on one server while the other was around 40%. Each had the same workload and the same database, etc. It turned out that one server was using Python 3.10.0 and the other was running 3.9.7. This combined with the MySQL Connector/Python lead to almost a 50% reduction in database throughput (the 3.10.0 release saw a regression). However, this performance change was not seen either in my PostgreSQL testing or in testing the mysqlclient connector. It happened only when running the pure python version of the MySQL connector. See the results below: Note: This workload was using a warmed BP, with workload running for over 24 hours before the test. I cycled the application server making the change to either the version of python or the MySQL library. These tests are repeatable regardless of the length of the run. All data fits into memory here. I am not trying to make an authoritative statement on a specific technology, merely pointing out the complexity of layers of technology. Looking at this purely from the user perspective, this particular benchmark simulates certain types of users.  Let’s look at the number of users who could complete their actions per second.  I will also add another pure python MySQL driver to the mix. Note: There appears to be a significant regression in 3.10. The application server was significantly busier when using Python 3.10 and one of the pure python drivers than when running the same test in 3.9 or earlier. The main difference between the MySQL Connector and mysqlclient is the mysqlclient is using the C libmysqlclient. Oddly the official MySQL Connector says it should switch between the pure python and C version if available, but I was not seeing that behavior ( so I have to look into it ). This resulted in the page load time for the app in Python 3.10.0 taking 0.05 seconds up from 0.03 seconds in Python 3.9.7. However, the key thing I wanted to highlight is that sometimes seemingly small or insignificant changes can lead to a drastic difference in performance and stability. You could be running along fine for months or even years before something upgrades to something that you would not think would drastically impact performance. Can This Be Fixed With Better Testing? You may think this is a poster child for testing before upgrading components, and while that is a requirement, it won’t necessarily prevent this type of issue. While technology combinations, upgrades, and releases can often have some odd side effects, oftentimes issues they introduce remain hidden and don’t manifest until some outside influence pops up. Note: These generally happen at the worst time possible, like during a significant marketing campaign, event, etc. The most common way I see nasty bugs get exposed is not through a release or in testing but often with a change in workload. Workload changes can hide or raise bottlenecks at the worst times. Let’s take a look at the above combination of Python version and different connectors with a different workload: Here the combination of reporting and read/write workload push all the app nodes and database nodes to the redline. These look fairly similar in terms of performance, but the workload is hiding the above issues I mentioned. A system pushed to the red will behave differently than it will in the real world. If you ended up testing upgrading python on your app servers to 3.10.0 by pushing your systems to the max, you may see the above small regression as within acceptable limits. In reality, however, the upgrade could net you seeing a 50% decrease in throughput when moved to production. Do We Care? Depending on how your application is built many people won’t notice the above-mentioned performance regression after the upgrade happens. First, most people do not run their servers even close to 100% load, adding more load on the boxes may not immediately impact their user’s performance. Adding .02 seconds of load time to a user may be imperceptible unless under heavy load ( which would increase that load time). The practical impact is to speed up the point at which you need to either add more nodes or upgrade their instances sooner. Second, scaling application nodes automatically is almost a requirement in most modern cloud-native environments. Reaching a point where you need to add more nodes and more processing power will come with increases in users on your application, so it is easily explained away. Suppose users won’t immediately notice and the system will automatically expand as needed ( preventing you from knowing or getting involved ). In that case, do we, or should we care about adding more nodes or servers? Adding nodes is cheap; it is not free. First, there is a direct cost to you. Take your hosting costs for your application servers and double them in the case above. What is that? $10K, $100K, $1M a year? That is money that is wasted. Look no further than the recent news lamenting the ever-increasing costs of the cloud i.e.: https://siliconangle.com/2021/11/28/cloud-computing-costs-high-can/ https://www.datanami.com/2021/11/17/cloud-getting-expensive-thats-by-design-but-dont-blame-the-clouds/ ,https://www.zdnet.com/article/how-to-get-cloud-storage-costs-under-control/ ) Second, there is a bigger cost that comes with complexity. Observability is such a huge topic because everything we end up doing in modern environments is done in mass. The more nodes and servers you have the more potential exists for issues or one node behaving badly. While our goal is to create a system where everything is replaceable and can be torn down and rebuilt to overcome problems, this is often not the reality. Instead, we end up replicating bad code, underlying bottlenecks, and making a single problem a problem at 100x the scale. We need to care. Application workload is a living entity that grows, shrinks, and expands with users. While modern systems need to scale quickly up and down to meet the demand, that should not preclude us from having to look out for hidden issues and bottlenecks. It is vitally important that we understand our applications workloads and look for deviations in the normal patterns.  We need to ask why something changed and dig in to find the answer.  Just because we can build automation to mitigate the problem does not mean we should get complacent and lazy about fixing and optimizing our systems. [Less]

Recently we were asked to check the performance of the new family of AMD EPYC processors when using MySQL in Google Cloud Virtual Machines. This was motivated by a user running MySQL in the N1 machines family and willing to upgrade to N2D generation ... [More] considering the potential cost savings using the new AMD family.  The idea behind the analysis is to do a side-by-side comparison of performance considering some factors:  EPYC processors have demonstrated better performance in purely CPU-based operations according to published benchmarks.  EPYC platform has lower costs compared to the Intel Xeon platform.  The goal of this analysis is to check if cost reductions by upgrading from N1 to N2D are worth the change to avoid suffering from performance problems and eventually reduce the machine size from the current 64 cores based (N1 n1-highmem-64 – Intel Haswell) to either N2D 64 cores (n2d-highmem-64 – AMD Rome) or even to 48 cores (n2d-highmem-48 – AMD Rome), to provide some extra context we included N2 (the new generation of Intel machines) into the analysis.  In order to do a purely CPU performance comparison we created 4 different VMs: NAME: n1-64 MACHINE_TYPE: n1-highmem-64Intel Haswell – Xeon 2.30GHz *This VM corresponds to the same type as the type we use in Production. NAME: n2-64 MACHINE_TYPE: n2-highmem-64Intel Cascade Lake – Xeon 2.80GHz NAME: n2d-48 MACHINE_TYPE: n2d-highmem-48AMD Epyc Rome – 2.25Ghz NAME: n2d-64 MACHINE_TYPE: n2d-highmem-64AMD Epyc Rome – 2.25Ghz For the analysis, we used MySQL Community Server 5.7.35-log and this is the basic configuration:[mysqld] datadir   = /var/lib/mysql socket = /var/lib/mysql/mysql.sock log-error   = /var/lib/mysql/mysqld.err pid-file = /var/run/mysqld/mysqld.pid server_id                       = 100 log_bin binlog_format                   = ROW sync_binlog   = 1000 expire_logs_days                = 2 skip_name_resolve innodb_buffer_pool_size         = 350G innodb_buffer_pool_instances    = 32 innodb_concurrency_tickets   = 5000 innodb_thread_concurrency   = 128 innodb_write_io_threads   = 16 innodb_read_io_threads   = 16 innodb_flush_log_at_trx_commit  = 1 innodb_flush_method             = O_DIRECT innodb_log_file_size            = 8G innodb_file_per_table           = 1 innodb_autoinc_lock_mode        = 2 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup  = 1 table_open_cache                = 5000 thread_cache_size               = 2000 query_cache_size                = 0 query_cache_type                = 0In all cases, we placed a 1TB balanced persistent drive so we get enough IO performance for the tests. We wanted to normalize all the specs so we can focus on the CPU performance, so don’t pay too much attention to the chances for improving performance for IO operations and so.  The analysis is based on sysbench oltp read-only workload with an in-memory dataset, the reason for this is that we want to generate traffic that can saturate CPU while not being affected by IO or Memory.  The approach for the benchmark was also simple, we executed RO OLTP work for 16, 32, 64, 128, and 256 threads with a one-minute wait between runs. Scripts and results from tests can be found here.  Let’s jump into the analysis, these are the number of Queries that instances are capable to run:  The maximum amount of TPS by Instance Type by the number of threads: Threads/Instance N1-64 N2-64 N2D-48 N2D-64 16 164k 230k 144k 155k 32 265k 347k 252k 268k 64 415k 598k 345k 439k 128 398k 591k 335k 444k 256 381k 554k 328k 433k Some observations:  In all cases we reached the maximum TPS at 64 threads, this is somehow expected as we are not generating CPU context switches.  Roughly we get a maximum of 598k tps in n2-highmem-64 and 444k tps in n2d-highmem-64 instance types which are the bigger ones. While this is expected Intel-based architecture outperforms AMD by a 35%  Maximum tps seems to be reached with 64 threads, this is expected considering the number of CPU threads we can use in parallel.  While n1-highmem-64 (Intel Xeon) and n2d-highmem-48 (AMD Epyc) seems to start suffering performance issues when the amount of threads exceeds the max number of cores the bigger instances running with 64 cores are capable to sustain the throughput a bit better, these instances start to be impacted when we reach 4x the amount of CPU cores.  Let’s have a look at the CPU utilization on each node: Additional observations:  n1-highmem-64 and n2d-highmem-48 are reaching 100% utilization at 64 threads running.  With 64 threads running n2-highmem-64 reaches 100% utilization while n2d-highmem-64 is still below. Although Intel provides better throughput overall probably by having a faster CPU clock (2.8Ghz vs 2.25Ghz)  For 128 and 256 threads all CPUs show similar utilization.  For the sake of analysis this is the estimated costs of each of used machines (at the moment of writing the post):n1-highmem-64 $2,035.49/month = $0.000785297/secondn2-highmem-64 $2,549.39/month = $0.000983561/secondn2d-highmem-48 $1,698.54/month = $0.000655301/secondn2d-highmem-64 $2,231.06/month = $0.000860748/second Costs above will give us roughly at peaks of TPS:n1-highmem-64 costs are $0.0000000019/trxn2-highmem-64 costs are $0.0000000016/trxn2d-highmem-48 costs are $0.0000000019/trxn2d-highmem-64 costs are $0.0000000019/trx Conclusions While this is not a super exhaustive analysis of all implications of CPU performance for MySQL workload we get a very good understanding of cost vs performance analysis.  n1 family, currently used in production, shows very similar performance to n2d family (AMD) when running with the same amount of cores. This changes a lot when we move into the n2 family (Intel) which outperforms all other instances.  While the cut in costs for moving into n2d-highmem-48 will represent ~$4k/year the performance penalty is close to 20%. Comparing the costs per trx at peaks of loads we can see that both n2-64 and n2d-64 are pretty much the same but n2-64 will give us 35% more throughput, this is definitely something to consider if we plan to squeeze the CPU power.    If the consideration is to go with n2 generation then definitely the n2d-highmem-64 is a very good choice to balance performance and costs but n2-highmem-64 will give much better performance per dollar spent.  [Less]

Hey everyone, I’m creating a dedicated page of ‘Friend links‘ for all of my Medium first articles and blogs. Continue reading to learn more and how to gain access to them… Image by David Schwarzenberg from Pixabay Medium’s Friend Link If ... [More] you’re a paying Medium member, not only can you read unlimited blog posts by any author on the platform, but you can also publish your blog posts and articles behind the paywall and earn some money from them. Every article you put behind the paywall also has a ‘Friend Link‘ that you can share as you like. Anyone with your ‘Friend Link‘, can read the article for free without being a paying member. You’re also able to read the article even after having used your monthly allotment of free articles (which is 3 at the time of writing). The articles ‘Friend Links‘ I’m sharing are those that I published first on Medium and only then cross-posted here on my blog or other platforms. So these ‘Friend Links‘ aren’t available anywhere else. Get access to all the ‘Friend Links‘ by subscribing to OpenLamp.tech, the newsletter for PHP/MySQL developers. Visit the section below to sign up. By signing up, access to these ‘Friend Links‘ will be sent to you. This list of ‘Friend Links‘ will continue to grow as I publish more posts on Medium so you will always have access to my Medium paywalled content – for free via the ‘Friend Link‘. The Newsletter for PHP and MySQL Developers Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The post My Friendly Medium List appeared first on Digital Owl's Prose. [Less]

Hey everyone, I’m creating a dedicated page of ‘Friend links‘ for all of my Medium first articles and blogs. Continue reading to learn more and how to gain access to them… Image by David Schwarzenberg from Pixabay Medium’s Friend Link If ... [More] you’re a paying Medium member, not only can you read unlimited blog posts by any author on the platform, but you can also publish your blog posts and articles behind the paywall and earn some money from them. Every article you put behind the paywall also has a ‘Friend Link‘ that you can share as you like. Anyone with your ‘Friend Link‘, can read the article for free without being a paying member. You’re also able to read the article even after having used your monthly allotment of free articles (which is 3 at the time of writing). The articles ‘Friend Links‘ I’m sharing are those that I published first on Medium and only then cross-posted here on my blog or other platforms. So these ‘Friend Links‘ aren’t available anywhere else. Get access to all the ‘Friend Links‘ by subscribing to OpenLamp.tech, the newsletter for PHP/MySQL developers. Visit the section below to sign up. By signing up, access to these ‘Friend Links‘ will be sent to you. This list of ‘Friend Links‘ will continue to grow as I publish more posts on Medium so you will always have access to my Medium paywalled content – for free via the ‘Friend Link‘. The Newsletter for PHP and MySQL Developers Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The post My Friendly Medium List appeared first on Digital Owl's Prose. [Less]

Hey everyone, I’m creating a dedicated page of ‘Friend links‘ for all of my Medium first articles and blogs. Continue reading to learn more and how to gain access to them… Image by David Schwarzenberg from Pixabay Medium’s Friend Link If ... [More] you’re a paying Medium member, not only can you read unlimited blog posts by any author on the platform, but you can also publish your blog posts and articles behind the paywall and earn some money from them. Every article you put behind the paywall also has a ‘Friend Link‘ that you can share as you like. Anyone with your ‘Friend Link‘, can read the article for free without being a paying member. You’re also able to read the article even after having used your monthly allotment of free articles (which is 3 at the time of writing). The articles ‘Friend Links‘ I’m sharing are those that I published first on Medium and only then cross-posted here on my blog or other platforms. So these ‘Friend Links‘ aren’t available anywhere else. Get access to all the ‘Friend Links‘ by subscribing to OpenLamp.tech, the newsletter for PHP/MySQL developers. Visit the section below to sign up. By signing up, access to these ‘Friend Links‘ will be sent to you. This list of ‘Friend Links‘ will continue to grow as I publish more posts on Medium so you will always have access to my Medium paywalled content – for free via the ‘Friend Link‘. The Newsletter for PHP and MySQL Developers Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The post My Friendly Medium List appeared first on Digital Owl's Prose. [Less]

 SCaLE 19X – the 19th annual Southern California Linux Expo – will take place  March 3-6, 2022 in Pasadena, CA. SCaLE is the largest community-run open-source and free software conference in North America. It is held annually in the greater Los ... [More] Angeles area.  A year might have been skipped due to Covid-19 but it looks like we will be back in person this year.  The MySQL Track features nine talks on a variety of subjects from some of the best  known MySQL experts.  Dave Stokes - MySQL New FeaturesMySQL now features quarterly releases that come with new features. So if you do not know about hash joins, replica set, dual password, or other new features then you need to be in this presentation.Peter Zaitsev - State of MySQL Security in 2021In this talk we will look into the state of MySQL Security in 2021.  We will cover the new security focused features available in MySQL (and MySQL Compatible variants) as well as share best practices to consider for secure MySQL deployment.Peter Zaitsev - 18 Things To Do When You Have a MySQL BottleneckThe presentation will be focused on dealing with an unexpected high traffic event as it is happening.Pep Pla - Forward to the Past: Point in time recovery for MySQLDisasters happen. Somebody runs a process that renders the data in your database unusable, inconsistent, or just deleted. Wouldn't it be great to be able to recover your database to the state just before the disaster happened?Pep Pla -- I need some (MySQL) backup!"If you have a hammer, all you see is nails." also known as the Law of the Instrument describes the cognitive bias that involves over-reliance on a familiar tool. To protect MySQL data, a DBA must have a toolbox full of the proper utensils. To know only one approach is not enough.Mattias Crauwels - MySQL backup best-practices in 2022Backups are important for your business! I will give you some best practices on how to do it properly!Michael Marx - The Great Migration: Galera Cluster to InnoDB ClusterInnoDB cluster has several advantages when  you need highly available data and in this presentation you will see how to convert from Galera for better performance.Michael Marx -  MySQL enhanced with HeatwaveHeatWave is a massively parallel, high performance, in-memory query accelerator for Oracle MySQL Database Service that accelerates MySQL performance by orders of magnitude for analytics and mixed workloads.Dave Stokes - MySQL IndexesMySQL indexes can greatly speed up your queries, or doom you database to performance night mares.  Learn about the various types of indexes, how test them, and how to use them efficiently. Hopefully I will see you at these talks in March or at the MySQL Booth in the expo hall!All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him [Less]

  Aurora cluster promises a high availability solution and seamless failover procedure. However, how much is actually the downtime when a failover happens? And how proxySQL can help in minimizing the downtime ? A little sneak peek on the results ... [More] ProxySQL achieves up to 25x less downtime and the impressive up to ~9800x less errors during unplanned failovers. How proxySQL achieves this:  Less downtime “Queueing” feature when an instance in a hostgroup becomes unavailable. So what is ProxySQL? ProxySQL is a middle layer between the database and the application. ProxySQL protects databases from high traffic spikes, prevents databases from having high number of connections due to the multiplexing feature and minimizes the impact during planned/unexpected failovers or crashes of DBs.  This blog will continue with measuring the impact of an unexpected failover using proxySQL v2.3.2 in comparison to using the cluster’s writer endpoint, which is considered to be the middle layer between the application and the databases. In our setup we use a aurora mysql cluster 2.10.0 of db.t3.small class, having 1 writer instance and 2 reader instances. ProxySQL is installed on an EC2 instance on the same region as the cluster and we will use both sysbench and simple multithreaded bash script using the native mysql client. In this way we will verify the actual downtime in both use cases. ProxySQL setup ProxySQL natively supports Aurora as described in the following link. (https://proxysql.com/documentation/aws-aurora-configuration/) ProxySQL configuration file mysql_variables= { threads=2 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:6033" default_schema="information_schema" stacksize=1048576 server_version="5.5.30" connect_timeout_server=3000 monitor_username="monitor" monitor_password="monitor" monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 } Proxysql Aurora tables setup Step 1. Create proxysql’s monitor user in Aurora cluster  create user if not exists monitor identified with mysql_native_password by 'xxx'; grant replication client on *.* to monitor; Step 2. Configure aurora mysql instances on proxySQL INSERT INTO mysql_servers (hostgroup_id,hostname) VALUES (1,'test-proxysql-0.asdfasf.eu-west-1.rds.amazonaws.com'); INSERT INTO mysql_servers (hostgroup_id,hostname) VALUES (1,'test-proxysql-1.asdfasf.eu-west-1.rds.amazonaws.com'); INSERT INTO mysql_servers (hostgroup_id,hostname) VALUES (1,'test-proxysql-2.asdfasf.eu-west-1.rds.amazonaws.com'); Step 3. Configure writer and reader hostgroups. INSERT INTO mysql_aws_aurora_hostgroups(writer_hostgroup, reader_hostgroup, domain_name,writer_is_also_reader) VALUES (0,1, '.asdfasf.eu-west-1.rds.amazonaws.com',1); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; Step 4. Configure user INSERT INTO mysql_users (username,password,active,default_hostgroup, use_ssl) values ('sbtest','sbtestsbtest',1,0, 0); LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;   Test and Results For simplicity reasons we only directed traffic to the writer instance.  In both test cases(using proxysql and using cluster endpoint)  we used the same sysbench test and the same mysql client bash script. As below: sysbench --report-interval=1 --mysql-host=proxysql01 --mysql-port=6033 --mysql-user=sbtest --mysql-password=sbtestsbtest --mysql-db=sbtest --table_size=20000 --tables=4 --time=120 --threads=4 --skip_trx=off --mysql-ignore-errors=2013,2003,1290,1213 --db-ps-mode=disable --reconnect=1000 oltp_read_write sysbench --report-interval=1 --mysql-host=test-proxysql-cluster.cluster-asdfasf.eu-west-1.rds.amazonaws.com --mysql-port=3306 --mysql-user=sbtest --mysql-password=sbtestsbtest --mysql-db=sbtest --table_size=20000 --tables=4 --time=120 --threads=4 --skip_trx=off --mysql-ignore-errors=2013,2003,1290,1213 --db-ps-mode=disable --reconnect=1000 oltp_read_write run   Concerning the sysbench test it is worth mentioning that since sysbench is using persistent connection, we had to modify the mysql driver (https://github.com/akopytov/sysbench/blob/master/src/drivers/mysql/drv_mysql.c) in order to close connections and reconnect when specific errors are reported (ER_OPTION_PREVENTS_STATEMENT 1290, CR_CONN_HOST_ERROR 2003). If we didn’t modify it, the actual results would be like the graphs below. As you can see after the failover triggered, when using the cluster’s endpoint there is a total unavailability reporting –> ERROR 1290 The MySQL server is running with the –read-only option so it cannot execute this statement. This means that using the cluster’s endpoint, although the actual writer had already changed, sysbench still tried to send traffic to the former writer. However, as you can see proxySQL recognises almost immediately who is the new writer and redirects the traffic with minimal downtime.  Image 1 Image 2 So, why is this important to mention? Because if your application is using persistent connection after an unexpected failover using Aurora’s endpoint, you should restart the application in order to get a new connection to the newly promoted writer.  So, we tested 2 different failover scenarios. During the test either the sysbench or bash script was running.  In the first scenario we just trigger a failover by simply executing the following command aws rds failover-db-cluster --db-cluster-identifier test-proxysql-cluster.  In the second scenario we deleted the writer as follows: WRITER=$( aws rds describe-db-clusters --db-cluster-identifier test-proxysql-cluster| jq -r '.DBClusters[0].DBClusterMembers[] | select(.IsClusterWriter==true) | .DBInstanceIdentifier' | head -n1  ) && \ aws rds delete-db-instance --db-instance-identifier $WRITER We ran multiple tests and after each and every test we collected data like the TPS, the errors and how much was the total downtime. Before starting we need to clarify the following:  There are 2 different failovers executed. One using proxySQL and one using the cluster endpoint. However the time that takes a failover to be triggered using the aws rds failover-db-cluster –db-cluster-identifier test-proxysql-cluster differs. So in the graphs you will sometimes see the traffic to be dropped earlier either in proxySQL or in aurora endpoint test.  You will notice that in some of the tests when we use cluster’s endpoint there are 2 downtime periods, although we triggered the failover once. This is due to the nature of how the DNS refresh is working on Aurora cluster’s endpoints. So there are actually 2 downtime periods during the same failover.   1st Scenario  1st Test Sysbench Image 3 Image 4 Stats using cluster endpoint:  TOTAL ERRORS: 11464 ERROR 1290: 11460 ERROR 2013: 4 Downtime: ~ 32 seconds  Stats using proxysql: TOTAL ERRORS: 3 ERROR 1290: 0 ERROR 2013: 3 Downtime: ~ 2 seconds Image 5 Image 6   2nd Test Sysbench Image 7 Image 8 Stats using cluster endpoint:  TOTAL ERRORS: 1938 ERROR 1290: 1934 ERROR 2013: 3 Downtime: ~ 10 seconds  Stats using proxysql: TOTAL ERRORS: 1 ERROR 1290: 0 ERROR 2013: 1 Downtime: ~ 2 seconds  Image 9 Image 10   3rd Test Sysbench Image 11 Image 12 Stats using cluster endpoint:  TOTAL ERRORS: 6619 ERROR 1290: 6614 ERROR 2013: 5 Downtime: ~ 12 seconds  Stats using proxysql: TOTAL ERRORS: 105 ERROR 1290: 104 ERROR 2013: 1 Downtime: ~ 3 seconds Image 13 Image 14   4th Test Sysbench Image 15 Image 16 Stats using cluster endpoint:  TOTAL ERRORS: 9826 ERROR 1290: 9822 ERROR 2013: 4 Downtime: ~ 25 seconds  Stats using proxysql: TOTAL ERRORS: 1 ERROR 1290: 0 ERROR 2013: 1 Downtime: ~ 1 seconds Image 17 Image 18 The results are impressive and revealed that using proxySQL failover completed in most cases in less than 4 seconds, and the total unavailability was about 3 seconds, while using the cluster’s writer  endpoint the downtime varied and in one of the tests took almost 32 seconds. Most of the errors reported were Ignoring error 1290 The MySQL server is running with the –read-only option so it cannot execute this statement and Can’t connect to MySQL server error 2013. Note that we performed 20 tests but for the sake of space we only present 4 of them. It is worth mentioning that the average downtime using cluster endpoint was 18,7 seconds with max downtime of 63 seconds, and the corresponding average downtime using proxySQL was 2,14 seconds with max of 5 seconds downtime. As concerns the errors the average errors using cluster endpoint was 4876,12 errors/per failover and the average errors using proxysql was 9,15(!!)  errors/per failover.   1st Test with mysql-client bash Image 19 Image 20 Stats using cluster endpoint:  TOTAL ERRORS: 1204 ERROR 1290: 432 ERROR 2013: 3 ERROR 2003: 769 Downtime: ~ 15 seconds  Stats using proxysql: TOTAL ERRORS: 5 ERROR 1290: 0 ERROR 2013: 5 ERROR 2003: 0 Downtime: ~ 2 seconds  Image 21 Image 22   2nd Test with mysql-client bash Image 22 Image 23 Stats using cluster endpoint:  TOTAL ERRORS: 1317 ERROR 1290: 208 ERROR 2013: 4 ERROR 2003: 1105 Downtime: ~ 9 seconds  Stats using proxysql: TOTAL ERRORS: 5 ERROR 1290: 0 ERROR 2013: 5 ERROR 2003: 0 Downtime: ~ 2 seconds  Image 24 Image 25   3rd Test with mysql-client bash Image 26 Image 27 Stats using cluster endpoint:  TOTAL ERRORS: 3342 ERROR 1290: 2192 ERROR 2013: 3 ERROR 2003: 1147 Downtime: ~ 24 seconds  Stats using proxysql: TOTAL ERRORS: 5 ERROR 1290: 0 ERROR 2013: 5 ERROR 2003: 0 Downtime: ~ 2 seconds Image 28 Image 29 Using the mysql-client the results are about the same. Note that the way that this bash script works is to open and close connections for each and every transaction. That’s why here we also see the error Can’t connect to server 2003. 2nd Scenario (Unexpected failover – Deleting writer) 1st Test Sysbench Image 30 Image 31 Stats using cluster endpoint:  TOTAL ERRORS: 2772 ERROR 1290: 2768 ERROR 2013: 4 Downtime: ~ 13 seconds  Stats using proxysql: TOTAL ERRORS: 4 ERROR 1290: 0 ERROR 2013: 4 Downtime: ~ 2 seconds Image 32 Image 33   2nd Test Sysbench Image 34 Image 35 Stats using cluster endpoint:  TOTAL ERRORS: 1470 ERROR 1290: 1466 ERROR 2013: 4 Downtime: ~ 10 seconds  Stats using proxysql: TOTAL ERRORS: 4 ERROR 1290: 0 ERROR 2013: 4 Downtime: ~ 4 seconds Image 36 Image 37   Conclusion  So, the question that arrives is how proxySQL achieves this? The impact minimization of a failover is achieved, because proxySQL has in it configured the instance endpoints and polling them frequently to see who is the writer and who is the reader. Thus, when something changes in the status of the instances proxySQL immediately recognizes it and takes the appropriate action.  In more detail, the instances’ endpoints are configured in the mysql_servers. The hostgroups of the writer and the readers are configured in the mysql_aws_aurora_hostgroups table. In mysql_aws_aurora_hostgroups table the column check_interval_ms defines how frequently ProxySQL’s monitor will check the status of the cluster, by default every 1 second. So, ProxySQL’s monitor connects to all the hosts in the cluster and queries the information_schema.replica_host_status every 1 second. The query is executed in one node at time in a round robin fashion. Depending on the answer proxysql decides which is the writer and which is the reader, and subsequently updates the instances’ hostgroup in mysql_servers table. To be more precise  ProxySQL determines which one is the writer/master based on SESSION_ID : if the value is MASTER_SESSION_ID, the server specified in the SERVER_ID is the master/writer. All the other servers with SESSION_ID not equal to MASTER_SESSION_ID are replicas. So, whenever a failover happens proxySQL will understand it at most within 1 second. Then proxySQL triggers a procedure in order to update the mysql_servers table with the new writer.  Whenever Aurora updates the information_schema.replica_host_status table with the new writer proxySQL within at most 1 second will update the hostgroups in the mysql_servers table based on the answer received  by the instances. In comparison, this update in the cluster’s endpoint is done by updating its DNS record, which is taking from 5x times up to 25x times to be done, based on our tests. Another great improvement that proxySQL offers are the errors. The errors a client receives can be minimized up to ~3500x. This happens due to the following two reasons.  ProxySQL minimizes downtime as we already mentioned. So less errors are received.  But the reason why it achieves this impressive number of 3500x times better is mainly because of the 2nd reason.  ProxySQL holds in a “queue” (not an actual queue) the sessions that cannot be served by a hostgroup. How much time this session can be held is determined by the mysql-connect_timeout_server_max variable. This variable configures the maximum timeout for retrying to connect from proxysql to a backend in a hostgroup. In more detail, when proxysql tries to establish a connection to a backend and the attempt fails due to timeout after mysql-connect_timeout_server milliseconds, proxysql will retry to establish a new connection (against the same backend or another backend in the same hostgroup) according to parameters mysql-connect_retries_on_failure and mysql-connect_retries_delay. ProxySQL stops retryning when the final timeout of mysql-connect_timeout_server_max milliseconds is reached, and an error is returned to the client with code 9001 with the message “Max connect timeout reached while reaching hostgroup…”. So what is next? The next thing we want to discuss in the upcoming blog is the setup of proxySQL for MySQL in Azure. Stay tuned!    The post Failover comparison in Aurora MySQL 2.10.0 using proxySQL vs Aurora’s cluster endpoint appeared first on ProxySQL. [Less]

Security will always be a main focal point of a company’s data. A common question I get from clients is, “how do I enable encryption?” Like every good consulting answer, it depends on what you are trying to encrypt. This post is a high-level summary ... [More] of the different options available for encryption in Percona Server for MySQL. Different certifications require different levels of encryption. For example, PCI requires both encryptions of data at rest and in transit. Here are the main facets of encryption for MySQL: Data at Rest Full disk encryption (at the OS level) Transparent Data Encryption – TDE Column/field-level encryption Data in Transit TLS Connections Data at Rest Data at rest is frequently the most asked about part of encryption. Data at rest encryption has multiple components, but at the core is simply ensuring that the data is encrypted at some level when stored. Here are the primary ways we can look at the encryption of data at rest. Full Disk Encryption (FDE) This is the easiest and most portable method of encrypting data at rest. When using full disk encryption, the main goal is to protect the hard drives in the event they are compromised. If a disk is removed from the server or the server is removed from a rack, the disk isn’t readable without the encryption key. This can be managed in different ways, but the infrastructure team generally handles it. Frequently, enterprises already have disk encryption as part of the infrastructure stack. This makes FDE a relatively easy option for data at rest encryption. It also has the advantage of being portable. Regardless of which database technology you use, the encryption is managed at the server level. The main disadvantage of FDE is that when the server is running, and the disk is mounted, all data is readable. It offers no protection against an attack on a running server once mounted. Transparent Data Encryption (TDE) Moving up the chain, the next option for data at rest encryption is Transparent Data Encryption (TDE). In contrast to FDE, this method encrypts the actual InnoDB data and log files. The main difference with database TDE is that the encryption is managed through the database, not at the server level. With this approach, the data and log files are encrypted on disk by the database. As data is read by MySQL/queries, the encrypted pages are read from disk and decrypted to be loaded into InnoDB’s buffer pool for execution. For this method, the encryption keys are managed either through local files or a remote KMS (such as Hashicorp Vault) with the keyring_plugin. While this approach helps prevent any OS user from simply copying data files, the decrypted data does reside in memory which could be susceptible to a clever hacker. We must rely on OS-level memory protections for further assurance. It also adds a level of complexity for key management and backups that is now shifted to the DBA team. Column Level Encryption While the prior methods of at-rest encryption can help to meet various compliance requirements, both are limited when it comes to a running system. In either case, if a running system is compromised, the data stored is fully readable. Column level encryption works to protect the data in a running system without a key. Without a key, the data in the encrypted column is unreadable. While this method protects selected data in a running system, it often requires application-level changes. Inserts are done with a specific encryption function (AES_ENCRYPT in MySQL, for example). To read the data, AES_DECRYPT with the specified key is required. The main risk with this approach is sending the plaintext values as part of the query. This can be sniffed if not using TLS or potentially leaked through log files. The better approach is to encrypt the data in the application BEFORE sending it to MySQL to ensure no plaintext is ever passed between systems. In some cases, you can use a shared key for the entire application. Other approaches would be to use an envelope method and store a unique key alongside each encrypted value (protected by a separate master key). Either way, it is important to understand one of the primary downsides to this approach – indexes and sort order can and will be impacted. For example, if you are encrypting the SSN number, you won’t be able to sort by SSN within MySQL. You would be able to look up a row using the SSN number but would need to pass the encrypted value. Data in Transit Now that we’ve discussed the different types of data-at-rest encryption, it is important to encrypt traffic to and from the database. Connecting to the server via TLS ensures that any sensitive sent to or from the server is encrypted. This can prevent data from leaking over the wire or via man-in-the-middle attacks. This is a straightforward way to secure communication, and when combined with some at-rest encryption, serves to check a few more boxes towards various compliances. Summary Overall, there are several aspects of encryption in MySQL. This makes it possible to meet many common compliance requirements for different types of regulations. Security is a critical piece of the database tier, and these discussions are needed across teams in an organization. Ensuring that security, infrastructure, and the database team are on the same page is essential, especially during the design phase. Let our Professional Services team help you implement the approach that is best suited for your requirements – we are here to help! Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use! Download Percona Distribution for MySQL Today [Less]