A Little Trick Upgrading to MySQL 5.7

Upgrading to MySQL 5.7In this blog post, I’ll look at a trick we use at Percona when upgrading to MySQL 5.7.

I’ll be covering this subject (and others) in my webinar Learning MySQL 5.7 on Wednesday, July 19, 2017.

We’ve been doing upgrades for quite a while here are Percona, and we try to optimize, standardize and improve this process to save time. When upgrading to MySQL 5.7, more often than not you need to run REPAIR or ALTER via mysql_upgrade to a number of MySQL tables. Sometimes a few hundred, sometimes hundreds of thousands.

One way to cut some time from testing or executing mysql_upgrade is to combine it with mysqlcheck. This identifies tables that need to be rebuilt or repaired. The first step is to capture the output of this process:

This provides a lengthy output of what needs to be done to successfully upgrade our tables. On my test data, I get error reports like the ones below. I’ll need to take the specified action against them:

Before we run through this upgrade, let’s get an idea of how long it would take for a regular mysql_upgrade to complete on this dataset:

On a cold server, my baseline above took about 25 minutes.

The second step on our time-saving process is to identify the tables that need some action (in this case, REPAIR and ALTER … FORCE). Generate the SQL statements to run them and put them into a single SQL file:

My upgrade.sql file will have something like this:

Now we should be ready to run these commands in parallel as the third step in the process:

Getting some parallelization is not bad, and the process improved by about 38%. If we are talking about multi-terabyte data sets, then it is already a big gain.

On the other hand, my dataset has a few tables that are bigger than the rest. Since mysqlcheck processes them in a specific order, one of the threads was processing most of them instead of spreading them out evenly to each thread by size. To fix this, we need to have an idea of the sizes of each table we will be processing. We can use a query from the INFORMATION_SCHEMA.TABLES for this purpose:

Now my table-sizes.sql file will have contents like below, which I can sort and pass to the parallel command again and cut even more time!

This go-around, my total execution time is 8 minutes – a good 65% improvement. To wrap it up, we will need to run mysql_upgrade one last time so that the system tables are also upgraded, the tables are checked again and then restart the MySQL server as instructed by the manual:

The whole process should be easy to automate and script, depending on your preference. Lastly: YMMV. If you have one table that is more than half the size of your total data set, there might not be big gains.

If you want to learn more about upgrading to MySQL 5.7, come to my webinar on Wednesday, July 19: Learning MySQL 5.7. This process is only one of the phases in a multi-step upgrade process when moving to 5.7. I will discuss them in more detail next week. Register now from the link below, and I’ll talk to you soon!

Jervin Real

As Senior Consultant, Jervin partners with Percona’s customers on building reliable and highly performant MySQL infrastructures while also doing other fun stuff like watching cat videos on the internet. Jervin joined Percona in Apr 2010.

via MySQL Performance Blog
A Little Trick Upgrading to MySQL 5.7

Go to Disneyland on a Wednesday Morning in September

Photo: Getty

The Happiest Place on Earth isn’t so happy when you’ve been stuck in line for Space Mountain the past two-and-a-half hours on a sweltering Saturday in July, stress-eating a Mickey-shaped pretzel. Next time, avoid the theme park masses by analyzing line data.

The Los Angeles Times has put together a wildly comprehensive guide to choosing the best time to visit Disneyland and California Adventure based on average wait times for rides. Search for your favorite, can’t-miss rides, check the chart to see when the wait is the shortest, and strategize your trip your trip accordingly.

Los Angeles Times

For instance, Splash Mountain has a peak wait time of 62 minutes at 3 p.m., so do something else around that time. Unsurprisingly, the wait time for this ride is the least miserable when the park opens to the general public at 8 a.m. The Los Angeles Times recommends arriving to the parks at least a half-hour before they open. Do this if you want to maximize that $174 (on peak dates) Park Hopper ticket.

Other takeaways from the Times analysis, which used data from touringplans.com:

Advertisement

Advertisement

  • The best days to visit Disneyland and California Adventure are Wednesday, Thursday and Friday.
  • September and January are the best months to go, as the average wait time per ride is under 15 minutes. Avoid holidays.
  • If you’re willing to break up from your crew and ride solo, the single rider lines are a godsend and can shave off more than an hour of wait time in some cases. The rides that offer them are California Screamin’, Goofy’s Sky School. Grizzly River Run, Indiana Jones Adventure, Matterhorn Bobsleds, Radiator Springs Racers and Splash Mountain.
  • If you really want to go on the new Guardians of the Galaxy — Mission: Breakout! ride (which had a peak wait time of five hours when it first opened over Memorial Day weekend), head over there right at 8 a.m.—or else prepare yourself with a lot of Mickey-shaped snacks.

Don’t waste your time at Disneyland. Here’s how to avoid the lines. | Los Angeles Times


via Lifehacker
Go to Disneyland on a Wednesday Morning in September

Here’s a sneak peek of what Disney’s Star Wars Land will look like

This weekend is Disney’s D23 expo, where the company often unveils the first glimpses into what it has in store for the years ahead.

Our own Editor-and-Disney-aficionado-in-Chief Matthew Panzarino is down in LA for the event’s early access press day. Disney just pulled back the curtain on a bunch of stuff that’ll appear in the Star Wars Land parks that are set to open at Disneyland and Disneyworld in 2019, and he’s got pics of everything.

I expect we’ll see quite a bit more about Star Wars Land (and, hopefully, the tech that’ll power it) throughout the weekend, but here’s a little sneak peek of what we’ve seen so so far. Tap that right arrow to proceed through the slideshow — or if you’re on mobile, just scroll.

via TechCrunch
Here’s a sneak peek of what Disney’s Star Wars Land will look like

MySQL EXTRACT Function

Summary: in this tutorial, you will learn how to use the MySQL EXTRACT() function to extract part of a DATE or DATETIMEvalue. Introduction to the MySQL EXTRACT function The EXTRACT() function extracts part of a date. The following illustrates the syntax of the EXTRACT() function. [crayon-596848704a2f5006580142/] The EXTRACT() function requires two arguments unit and date. […]

The post MySQL EXTRACT Function appeared first on MySQL Tutorial.

via MySQL Tutorial
MySQL EXTRACT Function

Dilbert 2017-07-14

Transcript

Robot: Hello. I am a bluetooth speaker and your digital assistant. How may I help you? Man: Just shut up and sit there until I need something. Robot: Did you say” Give the CIA access to your microphone?” Man: Please, no. I’ll do anything you want.

via Dilbert
Dilbert 2017-07-14

Disney’s ‘Star Wars’ theme park is taking shape

Universal Studios Japan recently released the first trailer for its in-construction Super Nintendo World attraction, and now Disney has gone one better by building an actual physical model showing off its upcoming Star Wars Land (unofficial title). Due to open in 2019 at both Disneyland in California and Disney World in Florida, the new area "will transport guests to a never-before-seen planet" — which just happens to look like every weathered, forgettable world characters from the films briefly touch down on to get a ship part or intel or what have you.

There should be plenty to do at the "remote trading port" once you’ve filled your boots with expensive merchandise and your bellies with Yoda burgers. Ride specifics are pretty hazy, but Disney has revealed that visitors will control the Millennium Falcon on a secret mission as part of one signature attraction, while the other main draw will drop guests "in the middle of a climactic battle between the First Order and the Resistance."

Star Tours, a Star Wars-inspired flight simulator, and several other attractions were up and running at various Disney parks long before the company waved a check in front of George Lucas he couldn’t pass up. Now Disney owns the rights to the extremely popular franchise, it makes sense to create a more elaborate live experience for fans to visit — especially after the shot-in-the-arm that was the last two movies. Though a few years off completion, anyone visiting Disney World in the future will be able to kill two film-inspired birds with one stone, diving into the mind of James Cameron through the new World Of Avatar experience.

Via: TechCrunch

Source: Disney

via Engadget
Disney’s ‘Star Wars’ theme park is taking shape

Net neutrality supporters sent over 5 million emails to the FCC

Yesterday, activists advocating for a free and open internet rallied individuals to submit over two million comments and millions of emails and phone calls to the FCC in support of Net Neutrality. Popular sites across the web, from Yelp to Pornhub (and Engadget!), urged users to contact the federal agency and Congress with a singular message: Don’t let internet service providers create faster and slower connections to boost their profit margins.

Internet advocacy nonprofit Fight For The Future tallied the participation numbers, which are still climbing: Five million emails and 124,000 calls to Congress, while protestors actually visited twenty Congressional offices. The two million comments made to the FCC almost tripled the previous record made during the "internet slowdown" advocacy day on September 10th, 2014.

Even if they didn’t take action, tens of millions of users saw the messages plastered across the web. Big sites and services like Reddit, Twitter, Netflix, Spotify, Airbnb and Amazon all either urged advocacy or pointed users to sites like BattleForTheNet.com, which supplied premade form letters to fire off to the FCC and routed users to their Congressional representative’s phone number.

The stock message to the agency, echoed by statements from Google, Facebook, Dropbox, YouTube and internet advocates, is simple: Don’t do anything. During the Obama administration, the FCC officially classified the internet a utility, meaning ISPs (in theory) can’t limit users’ speed or access to sites. Shortly after he was appointed by Trump, FCC Chairman Ajit Pai started vocalizing his disdain for Net Neutrality, calling his agency’s previous support of it a "mistake." Whether yesterday’s activism changes his mind is a question for tomorrow.

Source: Fight For The Future

via Engadget
Net neutrality supporters sent over 5 million emails to the FCC