25 Months Later I Again Attend An In Person Computer Conference

 In March 2020 I attended the Southern California Linux Expo in Pasadena, California not knowing that I would not be physically present at a computer conference for over two years later.  When a good part of you job involves speaking at or attending conferences, it is a big shock to suddenly stop.  I did participate in many virtual events but there are things about live events that can not be duplicated over Zoom or the dozens of other similar environments.

The Silicon Valley PostgreSQL conference was my first time in twenty five months since I had been physically at a show.  Many of the other attendees had had similar gaps.  Some of us joked that we worried that our previous ninja-level travel skills were rusty and voiced concerns that they may have forgotten to pack essentials after being a home for a very long time.

The big impact that everyone  recognized was the 'hallway track'.  This is where you see a familiar face or meet a new acquaintance and talk.  Simply talk? Well, yes. Other cases it is a serendipitous exploration of something new.  For example there was a discussion on that get written to the logs during a transaction but before a commit that would survive a system crash.  PostgreSQL internals are not my forte but it was interesting to have a major code committer walk through the various steps of how the various components work within a transaction.  The analog is a homeowner learning how the plumbing in their house  functions.  These things may be mundane to many but I find joy in exploring new things.

The other big impact is meeting co-workers face-to-face.  I am very new in my new position at Percona and had the opportunity to meet a handful of colleagues.  We humans are a social bunch and I do not think video meetings provide the full experience of interacting with others.  I knew my co-workers were an impressive bunch and that idea was more than reinforced at the conference. 

Many virtual conferences had limited ability to ask speakers questions during or after a session.  It was enjoyable to be able to ask speakers questions during a lunch break or while getting coffee.  Presenting to a screen provides minimal feedback to a speaker and having a live audience brings a needed vitality.  Sometimes clarity on a point is needed or a tangent needs to touched. You don't get that from a pre-recorded presentation from either side of the presentation.

I was able to learn a great deal about PostgreSQL and the community at the conference.  After being in the MySQL sphere, there a lot of things that are different and so many things that are similar.  Meeting people passionate about an open source software project is energizing.  PostgreSQL is an impressive database rich with features and is supported by an energetic group of hard working people who are striving to make improvements.  

My next event is Percona Live next May in Austin.  This is an open source database show that is not specific to a single database technology.  Hopefully I can meet up with you there.

Percona Live Needs You to Present!!

 Percona Live is back and in person May 16th to 18th in Austin, Texas and you not only need to be there but you need to present a talk to share your knowledge.  The call for papers closes March 14th. 

Why should you present? 

The Open Source database community is growing.  As such we need those of you help the 'newbies' get up to speed.  We also have a lot of folks leveling up from newbies who need to learn about more advanced nuances.  You have something to contribute to your community.

 And we have some experts who want to share to help you grow. (see below)

But why should you present?

You have something to share! Did you learn something cool about backups? Want to share how you do capacity planning?  Can you explain user account lifecycle management?  Others would love to know how you migrated to the cloud, sped up queries, secure your servers, or how to contribute code.

Never spoken before in public?

Well, this is the event to start at!  There are five minute lightening talks that are perfect for novices or the shy.  The audience at Percona Live is very appreciate and wants you to do well.  Five minutes if five to ten slides.  There is something (or two somethings) that you can share to help others.

Got a lot to say?

There hour tutorials are a great place to do a deep dive on your favorite subject.  

Have less than 180 minutes of information but more than five?

Regular sessions are about fifty minutes (leave a few minutes for questions and answers).  

What can you talk about?

Well, the core material is open source databases or anything tangential to them. 

Kubernetes, cloud, query performance, replication, devops, programming, and anything else in that neighborhood.

Need help?

If you want someone to review, critique, sanity check, kick the tires, taste the sauce, or run things past then contact me.  Leave a comment below, ping me on Twitter (at Stoker), or email me.  

I might set up some Zoom meetings where I walk through the submission process, answer your questions, if you think that would help.  

And if I can not help you with a subjects, odds are I can find someone -- probably I have met at a past Percona live.

Do I have to attend?

Well, not really. There is an option to virtually attend for those who can not make it in person.

Please attend!

Austin is an amazing city.  Lots of great food, amazing museums, and 6th Street is the live music capital of Texas.  Nearby you can float the Comal or Guadalupe rivers, see the Alamo (also see the Buckhead Saloon), dance in Gruene Hall (the oldest in Texas), eat TexMex in the Elvis room Chuy's, or savor the many BBQ joints. 

Plus at the show you get to participate in the hallway track! Often times at conferences, the most valuable information or contacts you will make will be between sessions.  You will make friends, learn fascinating things that may not be database related, and have a good time visiting the expo hall where vendors show off their wares.\

So sign up today!

MySQL 8.0.28 Release Note Commentary

 MySQL Server version  8.0.28 was launched a few days ago and without a lot a fanfare. Which is a shame and I wish we had more of a splash for every release.  The release notes are available here. 

I would like to highlight some of the changes.

There are over a hundred bug fixes and big thanks to Hope Lee, Casa Zhang, Øystein Grøvlen, Song Zhibai, Facebook, Hobert Lu, Brian Yue, Zhai Weixiang, Murakami Kohei,  Venkatesh Prasad Venugopal, and the Tencent team.

The Highlights

This release does not have a lot of 'wow' new features but shows a lot of solid work in evolving the product. 

• The new audit_log_disable system variable permits disabling audit logging for all connecting and connected sessions.
• The output from EXPLAIN FORMAT=TREE was ex-encoded ranges for multi-valued indexes Now, hex-encoding is used only for string types having a binary character set. Strings with non-binary character sets are now printed  as plain text, with escaping for any special characters. 
• Now, hex-encoding is used only for string types having a binary character set. Strings with non-binary character sets are now printed in EXPLAIN FORMAT=TREE output as plain text, with escaping for any special characters. 
• The shortcuts ASCII for CHARACTER SET latin1 and UNICODE for CHARACTER SET ucs2 are now deprecated, and you should expect their removal in a future version of MySQL. Using either of these now raises a warning; use CHARACTER SET instead.
• The character sets ucs2, macoman, macce, dec, and hp4  along with all of their collations, are now deprecated, and subject to removal in a subsequent release of MySQL. You should use utf8mb4 instead of any of the character sets just listed.
• The GnuPG build key used to sign MySQL downloadable packages has been updated. The previous GnuPG build key is set to expire on 2022-02-16. For information about verifying the integrity and authenticity of MySQL downloadable packages using GnuPG signature checking, or to obtain a copy of our public GnuPG build key, (Yup, it took a little while to get the new key out there when '28 was first published -- appologies!)
• A new Performance Schema statement metric, CPU_TIME, is now available, enabling you to measure the CPU time spent on a query.
• The number of distinct windows which can appear in a given SELECT is now limited to 127. The number of distinct windows is the sum of the named windows and the implicit windows specified as part of any window function's OVER clause. In order to use a large number of windows, it may be necessary to increase the value of the thread_stack server system variable. (Not sure just how you count the number of implicit windows!)    UPDATE from Justin Swanhart - I was reading your latest blog post.  In a query each window function that uses OVER which does not use a named window is an implicit window.  If all of your window functions are framed the same they can share a named window, and you can have basically unlimited window functions.  But if you use OVER for each of them, then you are limited to 127.

So, please download this new version ASAP.

MySQL Track at SCaLE 19x

 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 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 Features

MySQL 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 2021

In 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 Bottleneck

The 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 MySQL

Disasters 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 2022

Backups 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 Cluster

InnoDB 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 Heatwave

HeatWave 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 Indexes

MySQL 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!

Fun With Functional Indexes

    Functional indexes are way to build indexes on values derived from rather than values in columns, or parts there of.  Pretend your very price sensitive customers want to buy products where the combined price of the product and the shipping on that product are below a certain amount.

CREATE index cost_and_shipping ON products((cost + shipping));

    Please note the extra () around the calculation on the value to be indexed.  You will need them to create the functional index.  So lets run a quick query and then look at the output from EXPLAIN to determine if the new index was used;

select *

from products

where cost + shipping = 3; +----+------+----------+----------+ | id | cost | shipping | name | +----+------+----------+----------+ | 2 | 2 | 1 | Dohickey | | 3 | 2 | 1 | Whatsits | +----+------+----------+----------+ 2 rows in set (0.0009 sec)

EXPLAIN FORMAT=TREE

select *

from products

where cost + shipping = 3\G *************************** 1. row *************************** EXPLAIN: -> Index lookup on products using cost_and_shipping ((cost + shipping)=3) (cost=0.70 rows=2) 1 row in set (0.0011 sec)

    And yes, the optimizer can use the cost_and_shipping index.

However, please beware that you have work with the index as it is designed. In this case it works for cost plus shipping, not shipping plus cost. Mix the order and your query will not be able to utilize the index.

EXPLAIN FORMAT=TREE

select *

from products

where shipping + cost= 3\G

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

EXPLAIN: -> Filter: ((products.shipping + products.cost) = 3)  (cost=0.65 rows=4)

    -> Table scan on products  (cost=0.65 rows=4)

1 row in set (0.0013 sec)

    So, please take advantage of MySQL's functional index ability but make sure you use your indexes as you define them.

Don't Forget to Check Your Accounts

    The start of fall in the northern hemisphere is a time for many of use to prepare for the approaching winter. Part of that is clearing away and storing summer things and pulling out of storage the winter things. And one of those things I suggest to you do is look at the accounts on your MySQL instances.

    It is very easy, especially with multiple folks creating and alter doing user administration, to have redundant accounts.  You may also have  unneeded accounts.  Or you can have accounts that expired and nobody asked to have them reset.  And you may have accounts that you are not sure who or what uses them.

Start with this simple query to get a first look at your user data.

use mysql;
select User, Host, password_expired, account_locked, User_attributes from user;  

    Examine which accounts are expired and locked.  Do you know why they are in this state? 

    Next look at the User and Host data sorted by User. 

select User, Host from user order by User, Host;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| Foo              | %         |
| bar              | localhost |
| bill             | localhost |
| davetest         | localhost |
| demo             | localhost |
| demo2            | localhost |
| dstokes          | %         |
| dstokes          | localhost |
| foo              | localhost |
| foobar           | localhost |
| jack             | localhost |
| mary             | localhost |
| mike             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| myuser           | %         |
| root             | localhost |
+------------------+-----------+

    In this case notice that there are two separate and distinct accounts where the User column is 'dstokes'.  Someone might have wanted one account or maybe there are two different users to justify the account.  Always look at the account as the pair of the User and Host columns.  If this was supposed to be one account, save yourself some future headaches and merge the accounts.  Remember the MySQL authentication system is a wee bit promisors and lets the first matching account into the server.  This is why dstokes@localhost can not read the data that is granted to dstokes@%! 

    And when you discover that 'dstokes'@'%' is Dave Stokes and dstokes@'localhost is Dilbert Stokes please annotate this information.

ALTER USER 'dstokes'@'%' COMMENT 'This is remote account for Dave Stokes x1234';

ALTER USER 'dstokes'@'localhost' COMMENT 'Dilbert Stokes debug account';

Histograms and Faster MySQL Queries

     Histograms were introduced with MySQL 8.0 and are a valuable way of speeding up queries.  The MySQL optimizer assumes that data in a column has evenly distributed values. Even distribution of data probably does not reflect much of the data sitting right now in your database.  

    The optimizer wants to find the most efficient way to return the data requested in a query.  If it has poor information on that data, then the optimizer will make a 'guesstimate' that will will result in a query plan that will not perform well.  But if the optimizer has good information, in this case provided by a histogram, then it can produce a better query plan.

    In the following example a able is filled with data that is not evenly distributed.  In the histogram image following, the data is represented in what looks like a rollercoaster side view. 

create table dist (id serial not null primary key, 
                            x int unsigned not null);

insert into dist (x) value (1),(1),(1),(1),(1),
                                        (2),(3),(3),(3),(3),(3),(3),
                                        (4),(4),(5),(6),(6),(6),(6),
                                        (6),(6),(6),(8),(9),(9),(9),(9);

select x, count(x) from dist group by x;
+---+----------+
| x | count(x) |
+---+----------+
| 1 |        5 |
| 2 |        1 |
| 3 |        6 |
| 4 |        2 |
| 5 |        1 |
| 6 |        7 |
| 8 |        1 |
| 9 |        4 |
+---+----------+

    There are 22 values of x that have a value less than seven.  If we examine output of a query where we are looking for the those values, the optimizer estimates, as seen in the EXLAIN output below,  it will need to roughly a third of the 27or 9 rows in the table. Here the optimizer has made a guess from assuming an even distribution, a third of 27 is 9.  It is easy to see that 9 is no where close to 22.

    Imagine a contractor estimates that it will take $9 to make you a widget but the final bill is $22.  Or your GPS application in your phone informs you that you are nine blocks from your destination but in reality is a much longer 22 blocks away.  In these two cases there may be valid reasons for the cost and distance 'overruns' but they are still frustrating to have to come up with the extra money of walk the extra distance.  Likewise this query generates a poorly performing  query plan.

 EXPLAIN select x, count(x) from dist where x < 7\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dist
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 27
     filtered: 33.32999801635742
        Extra: Using where
1 row in set, 1 warning (0.0007 sec)
Note (code 1003): /* select#1 */ select `fk`.`dist`.`x` AS `x`,count(`fk`.`dist`.`x`) AS `count(x)` from `fk`.`dist` where (`fk`.`dist`.`x` < 7)

    In this case a histogram provides a a better query plan. Creating a histogram is easy and in this case ten buckets will be used to store the values.

ANALYZE TABLE dist UPDATE HISTOGRAM ON x WITH 10 BUCKETS;

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

| Table   | Op        | Msg_type | Msg_text                                     |

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

| fk.dist | histogram | status   | Histogram statistics created for column 'x'. |

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

    And rerun EXPLAIN.

EXPLAIN select x, count(x) from dist where x < 7\G

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

           id: 1

  select_type: SIMPLE

        table: dist

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 27

     filtered: 81.48148345947266

        Extra: Using where

1 row in set, 1 warning (0.0046 sec)

Note (code 1003): /* select#1 */ select `fk`.`dist`.`x` AS `x`,count(`fk`.`dist`.`x`) AS `count(x)` from `fk`.`dist` where (`fk`.`dist`.`x` < 7)

    81% of 27 is 22 which is the value of the number of rows where x is less than 7.  If the cumulative frequency of the bucket values is examined it is easy to see that the values less than 7 is indeed 81%.

SELECT (SUBSTRING_INDEX(v, ':', -1)) value,        

                concat(round(c*100,1),'%') cumulfreq,             

                CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') freq     

FROM information_schema.column_statistics,         

            JSON_TABLE(histogram->'$.buckets','$[*]'                 

                COLUMNS(v VARCHAR(60) PATH '$[0]',                    

            c double PATH '$[1]')) hist            

WHERE  table_name = 'dist'  and column_name = 'x';

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

| value | cumulfreq | freq  |

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

| 1     | 18.5%     | 18.5% |

| 2     | 22.2%     | 3.7%  |

| 3     | 44.4%     | 22.2% |

| 4     | 51.9%     | 7.4%  |

| 5     | 55.6%     | 3.7%  |

| 6     | 81.5%     | 25.9% |

| 8     | 85.2%     | 3.7%  |

| 9     | 100%      | 14.8% |

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

    Histograms are great for data that does not change frequently and unlike an index there is no ongoing maintenance overhead to impact performance.  Of course as the data changes, the value of the histogram degrades but they are easily updated.