emGee Software Solutions Custom Database Applications

Share this

Web Technologies

Percona Live Presents: The First Ever TiDB Track

Planet MySQL - Mon, 05/06/2019 - 13:45

The PingCAP team has always been a strong supporter of Percona and the wider open source database community. As the people who work day in and day out on TiDB, an open source NewSQL database with MySQL compatibility, open source database is what gets us in the morning, and there’s no better place to share that passion than Percona Live.

At this year’s Percona Live in Austin, Texas, we are particularly excited to bring you a full track of talks and demo on the latest development in TiDB during Day 1 of the conference.

Who would benefit from the TiDB track

The TiDB track is designed to share with developers, DBAs, and practitioners in general technical know-hows, reproducible benchmarks (no benchmark-eting), and best practices on how TiDB can solve their problems. There are 7 talks total by folks from PingCAP and Intel that cover the full gamut of how you can test, migrate, and use TiDB in the cloud to solve technical problems and deliver business value. Here’s a run down of the talk topics:

Phew! That’s a lot. I hope you are excited to join us for this track. As Peter Zaitsev and Morgan Tocker (one of the TiDB track speakers) noted in a recent Percona webinar, there’s a lot TiDB can do to help scale MySQL while avoiding common manual sharding issues. This track will peel the onion to show you all the fun stuff under the hood.

Whose presentations do you look forward to?

Besides the TiDB track, there are many other presentations we are excited about. In particular, I look forward to attending Stacy Yuan and Yashada Jadhav of PayPal’s talk on MySQL Security and Standardization, and Vinicius Grippa of Percona’s presentation on enhancing MySQL Security.

See you soon in Austin!

The post Percona Live Presents: The First Ever TiDB Track appeared first on Percona Community Blog.

Categories: Web Technologies

MySQL Connectors 8.0.16 – Time to Catch up!

Planet MySQL - Mon, 05/06/2019 - 09:59

HI again! It’s been some time since I wrote and shared some of our updates with you. My intention back then was to give you an update with every release — if only all my plans worked out! However we’ve now had 3 releases since last I shared an update with you so it’s time that I updated everyone on what we’ve been working on.

All of our connectors (well except for ODBC) implement our new X DevAPI and so I’ll briefly go over these features and then break out some of the major connector specific features included.


Handling of default schema

We had some inconsistencies regarding how a default schema given on a connection string was handled by some of our connectors. If a user gives a schema on the connection string — such as this – it was expected that myschema would be the default schema set on the session. This would mean that any SQL issued against that session would work against that schema. Not all connectors were handling this the correct way.

var session = mysqlx.getSession("mysqlx://localhost/myschema") Various API standardization efforts

With many of our releases we’ll included various API standardizing efforts. We are always comparing our connectors and trying to make sure that the “core” API is essentially the same among all of them. When we see differences we schedule updates to correct those. Our goal is that if you understand the X DevAPI in one language then you can understand it in all the languages.

Prepared Statement support

With 8.0.16 we released prepared statement support when working with our X DevAPI/Document Store connectors. The first thing you may notice is that there isn’t any specific API for accessing prepared statements. This fits in with our general philosophy of letting the connectors “do the right thing” for the user rather than force the user to always write code. In this case the connector will notice that you are executing a “preparable” query more than once and will automatically make use of prepared statements for you. As of right now there is no way to configure or adjust this behavior. You should see a significant performance improvement from 8.0.15 to 8.0.16 when doing something like a loop of inserts or updates.

Connection Pooling Enhancements

An enhancement was included in 8.0.16 to improve our connection pooling by speeding how our connectors could “reset” the internal connection to the server. This is entirely an internal improvement. Developers will see this as a faster connection pool.

Connection Attributes

Connection attributes are a feature where you can pass in key value pairs when a connection is made. These values will be stored in the server performance schema and can be queried later as a way of “profiling” your application. Think of it simply as “tagging” your connections. These values can be given when the pool is created or when old style sessions are opened. Here is an example code snippet giving some connection attributes when opening an old-style session:

var session = mysqlx.getSession("mysqlx://localhost/schema ?connection-attributes=[key1=value1,key2=value2]") Connector specific Features

Now I’d like to make a quick list of some of the larger changes we made to our connectors that are *not* connected to the X DevAPI. These are not all of the changes in our connectors. Each connector puts out a release announcement giving the entire change list with each release.

  • Connector/CPP
    • Support for MSVC 2017
    • Improved internal string handling
    • Improved our CMaked-based build system
  • Connector/Python
    • Removed support for Django versions less then 1.11
  • Connector/Net
    • Can now use SSL certificates in the PEM file format

As always, we really appreciate your willingness to work with our products and look forward to your feedback.

Categories: Web Technologies

Alejandro Celaya | Blog: Considerations to properly use open source software projects

PHPDeveloper.org - Mon, 05/06/2019 - 09:00

I've been wanting to write this article for a while, but it is a subject complex to approach.

Lately I've had some "conflicts" with users in some of the open source software (OSS) projects I maintain, and I have also seen some of the people I follow on Twitter dealing with the same.

Because of...

Categories: Web Technologies

Laravel News: Laravel Google Translate

PHPDeveloper.org - Mon, 05/06/2019 - 09:00
Laravel Google Translate is a package that provides an artisan console command to translate your localization files with the Google’s translation API. Visit Laravel News for the full post. The post Laravel Google Translate ap...
Categories: Web Technologies

Technical Thoughts, Tutorials, and Musings: PSR-14: Example - Delayed Events, Queues, and Asynchronicity

PHPDeveloper.org - Mon, 05/06/2019 - 09:00

PSR-14: Example - Delayed Events, Queues, and Asynchronicity

One of the long-running debates while designing PSR-14 was how to handle events that were inherently mono-directional. Often, a library will trigger an Event that is, from its point of view, entirely informational. The Eve...
Categories: Web Technologies

A CSS Golfing Exercise

CSS-Tricks - Mon, 05/06/2019 - 07:32

Code golfing is a type of programming where the goal is to accomplish a task using as few bytes as possible. CSSBattle is a code golfing battleground where players complete to recreate target images using CSS and HTML.

The rules are fairly simple:

  • No external resources (sorry, no <img src="the-solution.png">)
  • Your solution must render correctly in Chrome (just for scoring purposes)

This can be a pretty fun departure from day-to-day front-end work. There’s no need to worry about maintainability, semantics, performance, accessibility, or anything other than making a thing really, really small and still render correctly.

A golf solution in 12 attempts

This type of thinking is a pretty dramatic departure from how most of us are writing front-end code for production sites (I hope!), so I’ve been posting all of my solutions on GitHub in an effort to both share some knowledge and learn from others. As a fortunate side effect, it also means there’s a fairly detailed history of my submissions.

Here’s a start-to-finish account of my attempts CSSBattle’s 7th target, which looks like this:

CSSBattle Target #7 — Leafy Trail The "just center the dang thing" method

A reasonable first approach is to simply stick an element in the middle of the page, slap a box shadow and a border radius on it, and call it done. If we were writing this "for real," it might look like this:

<!DOCTYPE html> <html> <head> <style> body { background: #0B2429; margin: 0; } .leaf { width: 150px; height: 150px; border-radius: 67% 0; background: #F3AC3C; margin: 75px 0 75px 175px; box-shadow: -50px 0 #998235, -100px 0 #1A4341 } </style> </head> <body> <div class="leaf"/> </body> </html>

But that’s 423 bytes! That won’t do for CSS golf, so let’s see what we can remove.

Attempt 1: 144 bytes <p style="margin:75 167;height:150;width:150;border-radius:67%0;box-shadow:-50px 0#998235,-100px 0#1A4341,0 0 0 5in #0B2429;background:#F3AC3C">

Here’s a golfed version. There’s definitely some weirdness going on here — no <!DOCTYPE>, no <html>, no <body>, no nothin’. The browser doesn’t need them (and, in fact, inserts them for us), so we save a lot of bytes by leaving them out. We’re using <p> instead of <div> since it’s shorter, and we don’t close the tag at all since it’s not required for things to render.

The CSS itself isn’t much different, aside from the fact that we’ve used a huge box shadow instead of a background on the body element ("background" is long so avoiding it can be beneficial). It’s also inlined in the element since a <style></style> tag costs extra bytes.

You may have noticed that we used 5in for the spread in our last box shadow. Playing with weird units is a huge part of CSS golfing. In this case, we just need the shadow to cover the 400x300 canvas and ‘5in' (480px) is shorter than any pixel value.

Attempt 2: 141 bytes <p style=margin:75+167;height:150;width:150;border-radius:67%0;box-shadow:-50px+0#998235,-100px+0#1A4341,0+0+0+5in#0B2429;background:#F3AC3C>

This introduces a pretty important golfing trick: replacing spaces with plus signs allows us to remove the quotes around attributes, saving a couple bytes. I’m not totally sure why this works. Someone suggested it may be related to this part of the HTML spec. If you have a better answer, please let me know!

This attempt also cleans up a couple of whitespace mistakes from the last attempt.

Attempt 3: 126 bytes <body bgcolor=F3AC3C style=margin:75+75+75+175;border-radius:67%+0;box-shadow:-50px+0#998235,-100px+0#1A4341,0+0+0+5in#0B2429>

Using a <body> tag instead of a <p> means that:

  • We no longer spend bytes setting height or width on a paragraph
  • We get access to bgcolor

bgcolor is a deprecated attribute that comes up often in CSS golf solutions. It only works on a few tags (<body> included), and does two great things:

  • Saves us from spending bytes on "background:"
  • Saves us a byte by allowing us to omit # in hex colors. Additionally, if a color ends in one or two zeros, we can remove them and it will still render correctly. For example, FFFF00 is the same as FFFF.

There’s a golf regression in this iteration! Can you spot it?

The "border" method

By this point, I had spent quite a few hours tinkering on and off with this target and was getting pretty stuck. Fortunately, CSSBattle has a friendly community on Spectrum that is more than willing to lend a hand.

At the time, Praveen held the #1 spot with two bytes fewer than I had managed, so I asked for some help. He suggested leveraging both the <body> and <html> elements to position everything while using borders in place of a background color.

Attempt 4: 126 bytes <style>*{border-radius:67%+0;border:75px solid#F3AC3C;margin:0 50;box-shadow:-50px 0#998235,-100px 0#1A4341,0 0 0 5in#0B2429

This is a pretty huge departure from our last strategy. Our body tag is gone and we’re using <style>*{ to target the <html> and <body> tags that the browser inserts for us. The combination of margin and border nudges our shape exactly where it needs to be, and the box-shadow on <body> covers all the excess stuff you’d see from styling on <html>.

This was tough for me to grok, but Praveen made a diagram that explains things pretty well. Here’s a prettied up version:

Margins and borders on <html> and <body>

a and b are the margin and border on <html>, and c is the margin on <body>. The right margin on <body> doesn’t do anything since there’s no room to push the <body> to the left and it already has zero width.

Once our box shadows are applied, b is covered up and all that’s left is our target image.

There are still some optimizations missing here though. Dorus van den Oord was able to take the border method down to a lean 121 bytes, offering this cryptic bit of advice:

small hint for getting to that 121: What if you could move an element by a quarter of a ...?

Attempts 5 and 6: 122 bytes <style>*{border-radius:67%+0;border:75px solid#F3AC3C;margin:0 50;box-shadow:-53q 0#998235,-25vw 0#1A4341,0 0 0 5in#0B2429

Turns out all we needed was a unit hardly anyone’s ever heard of (q) (and the humble vw). Having to write "px" is rarely correct in CSS golf, so it’s something to be on the lookout for. Here, we can replace 100px with 25vw and 50px with 53q.

A q, or quarter-millimeter, is exactly that — 1/4th of a millimeter, or just under a pixel. The q unit is a staple of CSS golf as one of two values (the other being %) that require just one byte to express. I’ve combined my 5th and 6th attempts here since both were just unit tweaks. We’re still a byte off from 121 though!

Attempt 7: 121 bytes <style>*{border-radius:67%0;border:75px solid#F3AC3C;margin:0 50;box-shadow:-53q 0#998235,-25vw 0#1A4341,0 0 0 5in#0B2429

We finally fixed that regression from the third attempt, thanks to a pull request from Praveen. A percentage doesn’t need a space between it and subsequent values, so we can save a byte in our border-radius. This is a great example of how sharing code can help everyone involved. I had been stuck on this for a pretty dang long time.

The "funky margin" method

Borders aren’t the only approach, though! Enter Rasmus Fløe’s funky margin:

I got 123 chars on #7 by using box-shadow and a funky margin:75 400 75-150 :)

Attempt 8: 120 bytes <body bgcolor=0B2429 style=border-radius:67%0;margin:75+400+75-150;box-shadow:86mm+0#F3AC3C,73mm+0#998235,75vh+0#1A4341>

Here’s how this works, as Rasmus explains it:

positive right margin pushes it off canvas to the left — and negative left margin stretches the element to the wanted width :)

Here it is drawn out:

The Rasmus Fløe "funky margin" method

The right margin (b) pushes the <body> element all the way to the left, collapsing it to zero width. The negative left margin (a) then stretches it back to 150px wide (the width of the leaf shape), and then our box shadow (c) is offset enough to be in view. This is awesome because we no longer have to deal with negative box shadows in order to get everything to layer correctly.

We’re also back to bgcolor and get to leverage a nice quirk of background colors: because <html> doesn’t have its own background color, it inherits one from <body>.

Attempts 9 and 10: 118 bytes <body bgcolor=0B2429 style=border-radius:67%0;margin:75+340+75-90;box-shadow:7cm+0#F3AC3C,57mm+0#998235,55vh+0#1A4341>

With a bit more unit-wrangling we’re able to save ourselves two more bytes (props to Dorus, who was the first to discover this optimization). Adjusting the margins saves a digit (150 becomes 90), and, as a sweet bonus, we get to convert 86mm to 70mm, which becomes 7cm. I’ve again combined two attempts here which were minor unit fixes. (I’m embarrassed to say I initially missed the mm-cm conversion.)

Attempt 11: 117 bytes <body bgcolor=0B2429 style=border-radius:67%0;margin:75+85%75-90;box-shadow:7cm+0#F3AC3C,57mm+0#998235,55vh+0#1A4341>

Romain Deltour was the first to find this 117-byte solution. Changing 340 to 85% means we get to omit a space after one of our values (just like we did with border-radius), saving another byte.

Attempt 12: 115 bytes <body bgcolor=0B2429 style=border-radius:67%0;margin:75+85%75-90;box-shadow:7cm+0#F3AC3C,57mm+0#998235,55vh+0#7fd2>

Two full weeks after Romain’s 117-byte solution, Viacheslav Popov was able to alpha composite his way to 115 bytes via 4-digit hex codes.

I really love this because — not only is it dang clever — but a lot of people (myself included) thought the target had already been fully optimized. Viacheslav’s persistence both sparked a new round of discussion and added another CSS-Trick™ to our arsenal for future targets.

Attempt 13: <115 bytes?

This seems awfully close to optimal to me but that certainly doesn’t mean it can’t be beat — why not give it a shot? There's prior art to get you started, plenty of folks willing to help, and even some tooling. Happy golfing ⛳️

The post A CSS Golfing Exercise appeared first on CSS-Tricks.

Categories: Web Technologies

A Conspiracy to Kill IE6

CSS-Tricks - Mon, 05/06/2019 - 07:22

Chris Zacharias published a few notes about why the team at YouTube added a banner that asked users to switch from IE6 to a more modern browser back in 2009:

The bittersweet consequence of YouTube’s incredible growth is that so many stories will be lost underneath all of the layers of new paint. This is why I wanted to tell the story of how, ten years ago, a small team of web developers conspired to kill IE6 from inside YouTube and got away with it.

I do not recall the exact triggering event that led to our web development team laying out plans to kill IE6 over lunch in the YouTube cafeteria. Perhaps it was the time I pushed out a CSS stylesheet that included an attribute selector on a semi-supported HTML element. Any reasonable web developer would expect this to be ignored by browsers not up to the task. This was not the case with older flavors of IE. Under very specific conditions, an attribute selector on an unsupported HTML element in IE would create an internal recursion that would at best, cause the browser to crash and at worst, trigger a blue screen of death.

There are a lot of interesting things to consider here. IE6 was notoriously difficult for developers to work with and would cause teams to spend a great deal of time fixing game-breaking bugs for what often represented a mere slither of their overall site traffic. However, it’s important to note that as soon as you make a call like this, where do you stop? It suddenly becomes easier to make a Chrome-only website, to ignore basic accessibility principles, to ignore semantic markup, and to make a website optimized for yourself. That leads us to more sticky topics, such as browser diversity and proprietary resources that seem at odds with an open, inclusive web.

I’m reminded here of Jeremy Keith’s book, Resilient Web Design, where he writes:

If a website is built using progressive enhancement then it’s okay if a particular feature isn’t supported or fails to load: Ajax, geolocation, whatever. As long as the core functionality is still available, web designers don’t need to bend over backwards trying to crowbar support for newer features into older browsers.

And Jeremy quotes Mat Marquis, who happened to work on the responsive redesign of The Boston Globe, where he argued that:

Lots of cool features on the Boston Globe don’t work when JS breaks; “reading the news” is not one of them.

Maybe there’s a middle ground here; maybe there’s not. But I find Mat and Jeremy’s approach to be more inspiring and kinder to the overall health of the web.

Direct Link to ArticlePermalink

The post A Conspiracy to Kill IE6 appeared first on CSS-Tricks.

Categories: Web Technologies

What’s New in ProxySQL 2.0

Planet MySQL - Mon, 05/06/2019 - 02:23

ProxySQL is one of the best proxies out there for MySQL. It introduced a great deal of options for database administrators. It made possible to shape the database traffic by delaying, caching or rewriting queries on the fly. It can also be used to create an environment in which failovers will not affect applications and will be transparent to them. We already covered the most important ProxySQL features in previous blog posts:

We even have a tutorial covering ProxySQL showing how it can be used in MySQL and MariaDB setups.

Quite recently ProxySQL 2.0.3 has been released, being a patch release for the 2.0 series. Bugs are being fixed and the 2.0 line seems to start getting the traction it deserves. In this blog post we would like to discuss major changes introduced in ProxySQL 2.0.

Causal Reads Using GTID

Everyone who had to deal with replication lag and struggled with read-after-write scenarios that are affected by the replication lag will definitely be very happy with this feature. So far, in MySQL replication environments, the only way to ensure causal reads was to read from the master (and it doesn’t matter if you use asynchronous or semisynchronous replication). Another option was to go for Galera, which had an option for enforcing causal reads since, like, always (first it used to be wsrep-causal-reads and now it is wsrep-sync-wait). Quite recently (in 8.0.14) MySQL Group replication got similar feature. Regular replication, though, on its own, cannot deal with this issue. Luckily, ProxySQL is here and it brings us an option to define on per-query rule basis with what hostgroup reads which match that query rule should be consistent. The implementation comes with ProxySQL binlog reader and it can work with ROW binlog format for MySQL 5.7 and newer. Only Oracle MySQL is supported due to lack of required functionality in MariaDB. This feature and its technical details have been explained on ProxySQL official blog.

SSL for Frontend Connections

ProxySQL always had support for backend SSL connection but it lacked SSL encryption for the connections coming from clients. This was not that big of a deal given the recommended deployment pattern was to collocate ProxySQL on application nodes and use a secure Unix socket to connect from the app to the proxy. This is still a recommendation, especially if you use ProxySQL for caching queries (Unix sockets are faster than TCP connection, even local ones and with cache it’s good to avoid introducing unnecessary latency). What’s good is that with ProxySQL 2.0 there’s a choice now as it introduced SSL support for incoming connections. You can easily enable it by setting mysql-have_ssl to ‘true’. Enabling SSL does not come with unacceptable performance impact. Contrary, as per results from the official ProxySQL blog, the performance drop is very low.

ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE Native Support for Galera Cluster

Galera Cluster has been supported by ProxySQL almost since beginning but so far it was done through the external script that (typically) has been called from ProxySQL’s internal scheduler. It was up to the script to ensure that ProxySQL configuration was proper, the writer (or writers) has been correctly detected and configured in the writers hostgroup. The script was able to detect the different states Galera node may have (Primary, non-Primary, Synced, Donor/Desync, Joining, Joined) and mark the node accordingly as either available or not. The main issue is that the original script never was intended as anything other than the proof of concept written in Bash. Yet as it was distributed along with ProxySQL, it started to be improved, modified by external contributors. Others (like Percona) looked into creating their own scripts, bundled with their software. Some fixes have been introduced in the script from ProxySQL repository, some have been introduced into Percona version of the script. This led to confusion and even though all commonly used scripts handled 95% of the use cases, none of the popular ones really covered all the different situations and variables Galera cluster may end up using. Luckily, the ProxySQL 2.0 comes with native support for Galera Cluster. This makes ProxySQL support internally MySQL replication, MySQL Group Replication and now Galera Cluster. The way in how it’s done is very similar. We would like to cover the configuration of this feature as it might be not clear at the first glance.

As with MySQL replication and MySQL Group Replication, a table has been created in ProxySQL:

mysql> show create table mysql_galera_hostgroups\G *************************** 1. row *************************** table: mysql_galera_hostgroups Create Table: CREATE TABLE mysql_galera_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL, reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0), offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0), active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1, writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0, max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0, comment VARCHAR, UNIQUE (reader_hostgroup), UNIQUE (offline_hostgroup), UNIQUE (backup_writer_hostgroup)) 1 row in set (0.00 sec)

There are numerous settings to configure and we will go over them one by one. First of all, there are four hostgroups:

  • Writer_hostgroup - it will contain all the writers (with read_only=0) up to the ‘max_writers’ setting. By default it is just only one writer
  • Backup_writer_hostgroup - it contains remaining writers (read_only=0) that are left after ‘max_writers’ has been added to writer_hostgroup
  • Reader_hostgroup - it contains readers (read_only=1), it may also contain backup writers, as per ‘writer_is_also_reader’ setting
  • Offline_hostgroup - it contains nodes which were deemed not usable (either offline or in a state which makes them impossible to handle traffic)

Then we have remaining settings:

  • Active - whether the entry in mysql_galera_hostgroups is active or not
  • Max_writers - how many nodes at most can be put in the writer_hostgroup
  • Writer_is_also_reader - if set to 0, writers (read_only=0) will not be put into reader_hostgroup. If set to 1, writers (read_only=0) will be put into reader_hostgroup. If set to 2, nodes from backup_writer_hostgroup will be put into reader_hostgroup. This one is a bit complex therefore we will present an example later in this blog post
  • Max_transactions_behind - based on wsrep_local_recv_queue, the max queue that’s acceptable. If queue on the node exceeds max_transactions_behind given node will be marked as SHUNNED and it will not be available for the traffic

The main surprise might be handling of the readers, which is different than how the script included in ProxySQL worked. First of all, what you have to keep in mind, is the fact, that ProxySQL uses read_only=1 to decide if node is a reader or not. This is common in replication setups, not that common in Galera. Therefore, most likely, you will want to use ‘writer_is_also_reader’ setting to configure how readers should be added to the reader_hostgroup. Let’s consider three Galera nodes, all of them have read_only=0. We also have max_writers=1 as we want to direct all the writes towards one node. We configured mysql_galera_hostgroups as follows:

SELECT * FROM mysql_galera_hostgroups\G *************************** 1. row *************************** writer_hostgroup: 10 backup_writer_hostgroup: 30 reader_hostgroup: 20 offline_hostgroup: 40 active: 1 max_writers: 1 writer_is_also_reader: 0 max_transactions_behind: 0 comment: NULL 1 row in set (0.00 sec)

Let’s go through all the options:


mysql> SELECT hostgroup_id, hostname FROM runtime_mysql_servers; +--------------+------------+ | hostgroup_id | hostname | +--------------+------------+ | 10 | | | 30 | | | 30 | | +--------------+------------+ 3 rows in set (0.00 sec)

This outcome is different than you would see in the scripts - there you would have remaining nodes marked as readers. Here, given that we don’t want writers to be readers and given that there is no node with read_only=1, no readers will be configured. One writer (as per max_writers), remaining nodes in backup_writer_hostgroup.


mysql> SELECT hostgroup_id, hostname FROM runtime_mysql_servers; +--------------+------------+ | hostgroup_id | hostname | +--------------+------------+ | 10 | | | 20 | | | 20 | | | 20 | | | 30 | | | 30 | | +--------------+------------+ 6 rows in set (0.00 sec)

Here we want our writers to act as readers therefore all of them (active and backup) will be put into the reader_hostgroup.


mysql> SELECT hostgroup_id, hostname FROM runtime_mysql_servers; +--------------+------------+ | hostgroup_id | hostname | +--------------+------------+ | 10 | | | 20 | | | 20 | | | 30 | | | 30 | | +--------------+------------+ 5 rows in set (0.00 sec)

This is a setting for those who do not want their active writer to handle reads. In this case only nodes from backup_writer_hostgroup will be used for reads. Please also keep in mind that number of readers will change if you will set max_writers to some other value. If we’d set it to 3, there would be no backup writers (all nodes would end up in the writer hostgroup) thus, again, there would be no nodes in the reader hostgroup.

Related resources  ClusterControl for ProxySQL  How to Cluster Your ProxySQL Load Balancers  How to Monitor Your ProxySQL with Prometheus and ClusterControl

Of course, you will want to configure query rules accordingly to the hostgroup configuration. We will not go through this process here, you can check how it can be done in ProxySQL blog. If you would like to test how it works in a Docker environment, we have a blog which covers how to run Galera cluster and ProxySQL 2.0 on Docker.

Other Changes

What we described above are the most notable improvements in ProxySQL 2.0. There are many others, as per the changelog. Worth mentioning are improvements around query cache (for example, addition of PROXYSQL FLUSH QUERY CACHE) and change that allows ProxySQL to rely on super_read_only to determine master and slaves in replication setup.

We hope this short overview of the changes in ProxySQL 2.0 will help you to determine which version of the ProxySQL you should use. Please keep in mind that 1.4 branch, even if it will not get any new features, it still is maintained.

Tags:  proxysql MySQL load balancer
Categories: Web Technologies