emGee Software Solutions Custom Database Applications

Share this

Web Technologies

Let’s make a form that puts current location to use in a map!

CSS-Tricks - Mon, 08/06/2018 - 06:51

I love shopping online. I can find what I need and get most things for a decent price. I am Nigerian currently working and studying in India, and two things I dread when shopping online are:

  1. Filling out a credit card form
  2. Filling out shipping and billing address forms

Maybe I’m just lazy, but these things are not without challenges! For the first one, thanks to payment processing services like PayPal and e-wallets, I neither have to type in my 12-digit credit card number for every new e-commerce site I visit, nor have to save my credit card details with them.

For the second, the only time-saving option given by most shopping websites is to save your shipping address, but you still have to fill the form (arrghh!). This is where the challenge is. I've had most of my orders returned because my address (which I thought was the right address) could not be located or confirmed by the app for one reason or another.

Address inputs are challenging

Getting a user's address through an input form is a clean solution but can also be a herculean task to the user. Here's why:

  • If the user is new in a particular city, they might not know their full address
  • If the user wants to ship to a new address which isn't saved (e.g shipping to a workplace or a friend’s address instead of the saved home address)
  • If the user resides in a city with very difficult address systems
  • If the user is plain lazy like me
A potential solution: get the address automatically

Getting the user's address by the tap/click of a button. Yup, that's easy! The UX is great as it saves the user both the time and effort of filling out some form. It will also save the store owner time, effort, and even money in some cases, as there'll likely be a reduction in the number of incorrectly placed orders or shipments.

Let’s build a mini app that gets a user's address and shows it on a Google Map interface using vanilla JavaScript. The concept is as follows:

  1. Get the HTML button and listen for a click event
  2. Get the user's location (latitude and longitude) on a button click
  3. Show the user’s location on a Google map
  4. Pass the latitude and longitude to the Google Maps Geocode API URL
  5. Display the returned address (or list of addresses) on the UI for the user to select one
  6. Listen for map events and repeat steps 4 and 5
  7. Pre-fill the form with the address data the user selects
Getting started and setting up

To build this app, we’re going to use the Materialize CSS framework to save us some time fussing with styles. Materialize is a modern responsive front-end framework based on Google’s Material Design system. The beta version works with vanilla JavaScript.

A basic setup using Materialize’s CSS and JavaScript files in a document is like this:

<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Address Locator</title> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0-beta/css/materialize.min.css"> <link rel="stylesheet" href="css/main.css"> </head> <body> <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0-beta/js/materialize.min.js"></script> <script src="js/main.js"></script> </body> </html>

We’re also going to use the Google Maps API for displaying the map and getting the user's human-readable address. We’ll need an API key to do this. Here’s how to get one:

  1. Sign in to your Google Developer's Console Account
  2. Create a new project or select an existing one
  3. Click on "Enable APIs and Services"
  4. Select the “Maps Javascript API” option
  5. Click "Enable" on the new page that comes up. Go back to the previous page and do a search for "Geocoding API," click on it and enable it as well
  6. Then, on the right nav of the page, click on Credentials, copy the API key on the page and save it to a file

Now, let’s update our document to show the map and let the user know it can be used to get their current location as the address. Also, we will add a form that’s pre-filled with the address the user selects.

... <body> <div class="container"> <h3>Shipping Address</h3> <p>You can click the button below to use your current location as your shipping address</p> <div id="map"> </div> <button id="showMe" class="btn">Use My Location</button> <form id="shippingAddress"> <div id="locationList"></div> <br> <div class="input-field"> <textarea class="input_fields materialize-textarea" id="address" type="text"></textarea> <label class="active" for="address">Address (Area and Street)</label> </div> <div class="input-field"> <input class="input_fields" id="locality" type="text"> <label class="active" for="locality">Locality</label> </div> <div class="input-field"> <input class="input_fields" id="city" type="text"> <label class="active" for="city">City/District/Town</label> </div> <div class="input-field"> <input class="input_fields" id="postal_code" type="text"> <label class="active" for="pin_code">Pin Code</label> </div> <div class="input-field"> <input class="input_fields" id="landmark" type="text"> <label class="active" for="landmark">Landmark</label> </div> <div class="input-field"> <input class="input_fields" id="state" type="text"> <label class="active" for="State">State</label> </div> </form> <!-- You could add a fallback address gathering form here --> </div> <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0-beta/js/materialize.min.js"></script> <script src="https://maps.googleapis.com/maps/api/js?key=YOUR_API_KEY"></script> <script src="js/main.js"></script> </body> </html>

While we are here, let's style things a bit to make this look a little better:

.container { width: 50%; max-width: 800px; } #map { height: 50vh; margin-bottom: 10px; display: none; } #locationList .card { padding: 10px; } #toast-container { top: 50%; bottom: unset; } .toast { background-color: rgba(0, 0, 0, 0.8); } @media only screen and (max-width: 768px) { .container { width: 80%; } }

This CSS hides the map until we are ready to view it. Our app should look like this:

Let’s plan this out

Our app will be making use of the HTML5 Geolocation API to determine our user's current location as well as Google's Geocode API with a technique called Reverse Geocoding. The Geocode API takes a human-readable address and changes it into geographical (latitudinal and longitudinal) coordinates and marks the spot in the map.

Reverse Geocoding does the reverse. It takes the latitude and longitude and converts them to human-readable addresses. Geocoding and Reverse Geocoding are well documented.

Here’s how our app will work:

  1. The user clicks on the "Use My Location" button
  2. The user is located with the HTML5 Geolocation API (navigator.geolocation)
  3. We get the user's geographic coordinates
  4. We pass the coordinates to the geocode request API
  5. We display the resulting addresses to the user

Most times, the geocode returns more than one address, so we would have to show the user all the returned addresses and let them choose the most accurate one.

Phew! Finally, we can get to the fun part of actually writing the JavaScript. Let’s go through each of the steps we outlined.

Step 1: Clicking the button

In our main.js file, let's get a reference to the HTML button. While we are at it, we’ll set up some other variables we’ll need, like our API key.

//This div will display Google map const mapArea = document.getElementById('map'); //This button will set everything into motion when clicked const actionBtn = document.getElementById('showMe'); //This will display all the available addresses returned by Google's Geocode Api const locationsAvailable = document.getElementById('locationList'); //Let's bring in our API_KEY const __KEY = 'YOUR_API_KEY'; //Let's declare our Gmap and Gmarker variables that will hold the Map and Marker Objects later on let Gmap; let Gmarker; //Now we listen for a click event on our button actionBtn.addEventListener('click', e => { // hide the button actionBtn.style.display = "none"; // call Materialize toast to update user M.toast({ html: 'fetching your current location', classes: 'rounded' }); // get the user's position getLocation(); });

When the click handler for our button runs, it:

  1. Hides the button
  2. Alerts the user that we are getting their current location (a “toast” in Materialize is like a popup notification)
  3. Calls the getLocation function
Step 2: Get the user's location (latitude and longitude)

When our getLocation function is invoked, we need to do some more work. First, let’s check if we can even use the Geolocation API:

getLocation = () => { // check if user's browser supports Navigator.geolocation if (navigator.geolocation) { navigator.geolocation.getCurrentPosition(displayLocation, showError, options); } else { M.toast({ html: "Sorry, your browser does not support this feature... Please Update your Browser to enjoy it", classes: "rounded" }); } }

When we have support, it calls geolocation's getCurrentPosition method. If it doesn't have support, then the user is alerted that there's no browser support.

If there is support, then the getCurrentLocation method is used to get the current location of the device. The syntax is like this:

navigator.geolocation.getCurrentPosition(*success, error, [options]*)
  • success : This is a callback function that takes a position as its only parameter. For us, our success callback function is the displayLocation function.
  • error : [optional] This is a callback function that takes a PositionError as its sole input parameter. You can read more about this here. Our error callback function is the showError function.
  • options : [optional] This is an object which describes the options property to be passed to the getCurrentPosition method. You can read more about this here. Our options parameter is the options object.

Before writing our displayLocation function, let's handle the showError function and options object:

// Displays the different error messages showError = (error) => { mapArea.style.display = "block" switch (error.code) { case error.PERMISSION_DENIED: mapArea.innerHTML = "You denied the request for your location." break; case error.POSITION_UNAVAILABLE: mapArea.innerHTML = "Your Location information is unavailable." break; case error.TIMEOUT: mapArea.innerHTML = "Your request timed out. Please try again" break; case error.UNKNOWN_ERROR: mapArea.innerHTML = "An unknown error occurred please try again after some time." break; } } //Makes sure location accuracy is high const options = { enableHighAccuracy: true }

Now, let’s write the code for our displayLocation function inside our main.js file:

displayLocation = (position) => { const lat = position.coords.latitude; const lng = position.coords.longitude; }

We now have our user's latitude and longitude and we can view them in the console by writing the code below inside displayLocation:

console.log( `Current Latitude is ${lat} and your longitude is ${lng}` ); Step 3: Show the user’s current location on a Google Map

To do this, we will be adding these lines of code to our displayLocation function.

const latlng = {lat, lng} showMap(latlng); createMarker(latlng); mapArea.style.display = "block";

The first line takes our lat and lng values and encapsulates it in the latlng object literal. This makes it easy for us to use in our app.

The second line of code calls a showMap function which accepts a latlng argument. In here, we get to instantiate our Google map and render it in our UI.

The third line invokes a createMarker function which also accepts our object literal (latlng) as its argument and uses it to create a Google Maps Marker for us.

The fourth line makes the mapArea visible so that our user can now see the location.

displayLocation = (position) => { const lat = position.coords.latitude; const lng = position.coords.longitude; const latlng = { lat, lng } showMap(latlng); createMarker(latlng); mapArea.style.display = "block"; }

Now, let's get to creating our functions. We will start with the showMap function.

showMap = (latlng) => { let mapOptions = { center: latlng, zoom: 17 }; Gmap = new google.maps.Map(mapArea, mapOptions); }

The showMap function creates a mapOptions objects that contain the map center (which is the lat and lng coordinates we got from displayLocation) and the zoom level of the map. Finally, we create an instance of the Google Maps class and pass it on to our map. In fewer words, we instantiate the Google Maps class.

To create a map instance, we specify two parameters in the constructor: the div the map will be displayed and the mapOptions. In our case, our div is called mapArea and our mapOptions is called mapOptions. After this, our created map will show up, but without a marker. We need a marker so the user can identify their current position on the map.

Let's create our marker using the createMarker function:

createMarker = (latlng) => { let markerOptions = { position: latlng, map: Gmap, animation: google.maps.Animation.BOUNCE, clickable: true }; Gmarker = new google.maps.Marker(markerOptions); }

A few things to note in this code:

  1. The position property positions the marker at the specified latlng
  2. The map property specifies the map instance where the marker should be rendered (in our case, it’s Gmap)
  3. The animation property adds a little BOUNCE to our marker
  4. The clickable property set to true means our marker can be clicked
  5. Finally, we instantiate the Marker class in our Gmarker instance variable

So far, our user's location has been fetched, the map has rendered and the user can see their current location on the map. Things are looking good! &#x1f57a;

Step 4: Pass the latitude and longitude to the Geocode API

Google's Geocoding API will be used to convert our user's numeric geographical coordinates to a formatted, human-readable address using the reverse geocoding process we covered earlier.

The URL takes this form:

https://maps.googleapis.com/maps/api/geocode/outputFormat?parameters

...where the outputFormat may either be a json or xml which determines the the format used to deliver the data. The parameters part is a list of parameters needed for the request.

Our request URL will look like this:

https://maps.googleapis.com/maps/api/geocode/json?latlng=${latlng}&key=${__KEY}

Let's go ahead and connect to the API. We would do this in a function called getGeolocation.

getGeolocation = (lat, lng) => { const latlng = lat + "," + lng; fetch( `https://maps.googleapis.com/maps/api/geocode/json?latlng=${latlng}&key=${__KEY}` ) .then(res => res.json()) .then(data => console.log(data.results)); }

The getGeolocation function takes two arguments ( lat and lng } concatenates them to form a new latlng variable that is passed to the URL.

Using the Fetch API (more on this here), we add the new latlng and __KEY into the Geocode request URL. Then, on the response object we get back, we pass the .json method to resolve the promise with JSON. Finally, we log the response in our console.

To make use of our newly created function, we have to call it in the displayLocation function. So let's update our displayLocation function to contain the getGeolocation function call:

displayLocation = (position) => { const lat = position.coords.latitude; const lng = position.coords.longitude; const latlng = { lat, lng } showMap(latlng); createMarker(latlng); mapArea.style.display = "block"; getGeolocation(lat, lng)// our new function call }

The returned data should look something like this:

{ "results" : { "address_components": { "long_name": "1600", "short_name": "1600", "types": ["street_number"] }, { "long_name": "Amphitheatre Pkwy", "short_name": "Amphitheatre Pkwy", "types": ["route"] }, { "long_name": "Mountain View", "short_name": "Mountain View", "types": ["locality", "political"] }, { "long_name": "Santa Clara County", "short_name": "Santa Clara County", "types": ["administrative_area_level_2", "political"] }, { "long_name": "California", "short_name": "CA", "types": ["administrative_area_level_1", "political"] }, { "long_name": "United States", "short_name": "US", "types": ["country", "political"] }, { "long_name": "94043", "short_name": "94043", "types": ["postal_code"] } ], "formatted_address": "1600 Amphitheatre Parkway, Mountain View, CA 94043, USA", "geometry": { "location": { "lat": 37.4224764, "lng": -122.0842499 }, "location_type": "ROOFTOP", "viewport": { "northeast": { "lat": 37.4238253802915, "lng": -122.0829009197085 }, "southwest": { "lat": 37.4211274197085, "lng": -122.0855988802915 } } }, "place_id": "ChIJ2eUgeAK6j4ARbn5u_wAGqWA", "types": ["street_address"] } ], "status" : "OK" } Step 5: Display the returned address(es) for the user to select

At this stage, we have made a request to Google's Geocoding API and have gotten our result logged in the console. Now, we have to display the results in a UI for our user. This requires two things:

  1. Create a new function that handles creating HTML elements
  2. Update our getGeolocation function to make the function call

Let's create the function that would take care of creating the HTML elements and updating the DOM.

populateCard = (geoResults) => { geoResults.map(geoResult => { // first create the input div container const addressCard = document.createElement('div'); // then create the input and label elements const input = document.createElement('input'); const label = document.createElement('label'); // then add materialize classes to the div and input addressCard.classList.add("card"); input.classList.add("with-gap"); // add attributes to them label.setAttribute("for", geoResult.place_id); label.innerHTML = geoResult.formatted_address; input.setAttribute("name", "address"); input.setAttribute("type", "radio"); input.setAttribute("value", geoResult.formatted_address); input.setAttribute("id", geoResult.place_id); addressCard.appendChild(input); addressCard.appendChild(label) return ( // append the created div to the locationsAvailable div locationsAvailable.appendChild(addressCard) ); }) }

In this function, we iterate through our results and create some HTML elements (div , input and a label), append the input and the label to the div and finally append the new div to a parent div (which is locationsAvailable). Once we get the result from our API call, our DOM will be created and displayed to the user.

Next, we update our getGeolocation function to call our populateCard function by replacing the last line of getGeolocation with this:

.then(data => populateCard(data.results));

...which means our updated function should look this:

getGeolocation = (lat, lng) => { const latlng = lat + "," + lng; fetch( `https://maps.googleapis.com/maps/api/geocode/json?latlng=${latlng}&key=${__KEY}` ) .then(res => res.json()) .then(data => populateCard(data.results)); }

At this point, everything should be working fine. Our user clicks a button, gets a location displayed on the map along with a list of addresses that match the current location.

Step 6: Listen for map events and repeat steps 4 and 5

If our user decides to move the map or marker, nothing happens to the UI — new addresses aren’t not displayed and everything remains static. We’ve got to fix this, so let's make our app dynamic by listening for map events. You can read all about Google Map Events here.

There are three events we want to listen for:

  1. drag: This is fired once the user starts dragging and continues to drag the map
  2. dragend: This is fired once the user stops dragging the map
  3. idle: This is fired once every event has been fired and the map is idle

Quick Question: Why are these events best suited for our app?

Quick Answer: The first two events will make sure that our map marker stays in the center of the map during the drag event while the idle event will make a geocoding request with the new coordinates.

To listen for these events we have to update the showMap function with the following:

Gmap.addListener('drag', function () { Gmarker.setPosition(this.getCenter()); // set marker position to map center }); Gmap.addListener('dragend', function () { Gmarker.setPosition(this.getCenter()); // set marker position to map center }); Gmap.addListener('idle', function () { Gmarker.setPosition(this.getCenter()); // set marker position to map center if (Gmarker.getPosition().lat() !== lat || Gmarker.getPosition().lng() !== lng) { setTimeout(() => { updatePosition(this.getCenter().lat(), this.getCenter().lng()); // update position display }, 2000); } });

As explained above, the first two event listeners simply ensure that the marker remains in the center of our map. Pay closer attention to the idle event listener because that is where the action is.

Once the idle event is fired, the marker goes to the center, then a check is done to find out if the current position of the marker is the same with the lat or lng values received by the displayLocation function. If it is not the same, then we call the updatePosition function after two seconds of idleness.

Having said that, we have to make a few updates to the showMap function. First, on the function header, we have to include more parameters and on the showMap function call. We need to add the new arguments there, too. Our showMap function should look like this:

showMap = (latlng, lat, lng) => { let mapOptions = { center: latlng, zoom: 17 }; Gmap = new google.maps.Map(mapArea, mapOptions); Gmap.addListener('drag', function () { Gmarker.setPosition(this.getCenter()); // set marker position to map center }); Gmap.addListener('dragend', function () { Gmarker.setPosition(this.getCenter()); // set marker position to map center }); Gmap.addListener('idle', function () { Gmarker.setPosition(this.getCenter()); // set marker position to map center if (Gmarker.getPosition().lat() !== lat || Gmarker.getPosition().lng() !== lng) { setTimeout(() => { updatePosition(this.getCenter().lat(), this.getCenter().lng()); // update position display }, 2000); } }); }

And our displayLocation function should look like this:

displayLocation = (position) => { const lat = position.coords.latitude; const lng = position.coords.longitude; const latlng = { lat, lng } showMap(latlng, lat, lng); //passed lat and lng as the new arguments to the function createMarker(latlng); mapArea.style.display = "block"; getGeolocation(lat, lng); }

Having listened for the map events, let's repeat Step 4 and Step 5.

We start by writing our updatePosition function. This function will perform only one action for now, which is to pass the new lat and lng values to the getGeolocation function:

updatePosition = (lat, lng) => { getGeolocation(lat, lng); }

After getting the new position and fetching addresses, our DOM should re-render for the user, right? Well, it doesn't. And to fix that, we create a function that will force the DOM to re-render:

// check if the container has a child node to force re-render of dom function removeAddressCards(){ if (locationsAvailable.hasChildNodes()) { while (locationsAvailable.firstChild) { locationsAvailable.removeChild(locationsAvailable.firstChild); } } }

It checks if the locationsAvailable div has any childNodes, and if it does, it deletes them before creating new address cards. The populateCard function is now updated to this:

populateCard = (geoResults) => { // check if a the container has a child node to force re-render of dom removeAddressCards(); geoResults.map(geoResult => { // first create the input div container const addressCard = document.createElement('div'); // then create the input and label elements const input = document.createElement('input'); const label = document.createElement('label'); // then add materialize classes to the div and input addressCard.classList.add("card"); input.classList.add("with-gap"); // add attributes to them label.setAttribute("for", geoResult.place_id); label.innerHTML = geoResult.formatted_address; input.setAttribute("name", "address"); input.setAttribute("type", "radio"); input.setAttribute("value", geoResult.formatted_address); input.setAttribute("id", geoResult.place_id); addressCard.appendChild(input); addressCard.appendChild(label); return ( locationsAvailable.appendChild(addressCard); ); }) }

We are done and are now able to fully get and display the user's address!

Step 7: Pre-fill the form with the address data the user selects

The final step is to fill the form with the address the user selects. We need to add a click event listener to the address card and pass the address as argument to the callback function.

Here’s how we add the event listener in the populateCard function:

input.addEventListener('click', () => inputClicked(geoResult));

You should note that the geoResult argument in the above callback is the selected address object from the results array. That said, update the populateCard function to accommodate our new line of code.

The inputClicked function uses a series of if statements to assign values to our form elements. so before working on it, let’s bring our form elements into the equation:

const inputAddress = document.getElementById('address'), inputLocality = document.getElementById('locality'), inputPostalCode = document.getElementById('postal_code'), inputLandmark = document.getElementById('landmark'), inputCity = document.getElementById('city'), inputState = document.getElementById('state');

Having done this, let us now work on pre-filling the form with the address_components in the inputClicked function.

inputClicked = result => { result.address_components.map(component => { const types = component.types if (types.includes('postal_code')) { inputPostalCode.value = component.long_name } if (types.includes('locality')) { inputLocality.value = component.long_name } if (types.includes('administrative_area_level_2')) { inputCity.value = component.long_name } if (types.includes('administrative_area_level_1')) { inputState.value = component.long_name } if (types.includes('point_of_interest')) { inputLandmark.value = component.long_name } }); inputAddress.value = result.formatted_address; // to avoid labels overlapping pre-filled input contents M.updateTextFields(); // removes the address cards from the UI removeAddressCards(); }

The above block of code iterates over the clicked (or selected) address component, checks the types of components and finally assigns them to the input fields if they match.

M.updateTextFields() function is from Materialize and it ensures that the label does not overlap with the input fields values and the removeAddressCards() function removes the address cards from the UI.

With this, we are done with our app and have saved our users lots of typing and headaches! Surely they will thank us for implementing such a hassle free solution.

This whole UX experiment can be seen as a shortcut that will help the user pre-fill the shipping address form. We should clearly state here that the returned addresses isn’t always 100% accurate. But that’s why we allow the address to be manually edited in the UI.

Demo!

See the Pen Prefill Address Form with Geolocation and Google Maps by CSS-Tricks (@css-tricks) on CodePen.

The post Let’s make a form that puts current location to use in a map! appeared first on CSS-Tricks.

Categories: Web Technologies

How to setup PhpStorm with Xdebug on Docker [Tutorial Part 2] - Pascal Landau

Planet PHP - Mon, 08/06/2018 - 05:00

In the second part of this tutorial series on developing PHP on Docker we're taking a good hard look at PhpStorm, Xdebug and how to run and debug scripts from within PhpStorm on Docker.

And just as a reminder, the first part is over at Setting up PHP, PHP-FPM and NGINX for local development on Docker.

Note: The setup that I am going to use is for demonstration purposes only! I do not recommend that you use it "as is" as your development setup. Some problems that I won't solve here include:

  • everything is owned by root (no dedicated user; that will in particular be problematic for linux users)
  • SSH login credentials are hard-coded in the container (inherently insecure)
  • host.docker.internal will only exist for Windows and Mac users, NOT for unix users

There will be a another part of this series that will deal with all of those (and some more common) problems and aims at providing a consistent development environment for all developers in a team (regardless of the OS they are using). Please subscribe to the RSS feed to get automatic notifications when that part comes out :)

Table of contents Setup: The docker containers

We will only need the php-cli container for this part. Luckily, we already have a good understanding on how to create the container, although we'll need to make some adjustments to make everything work smoothly with PhpStorm. I'm gonna walk you through all the necessary changes, but I'd still recommend to clone the corresponding git repository docker-php-tutorial (unless you've already done that in part 1), checkout branch part_2 and build the containers now.

As in part 1, I'm assuming your codebase lives at /c/codesbase:

cd /c/codebase/ git clone https://github.com/paslandau/docker-php-tutorial.git cd docker-php-tutorial git checkout part_2 docker-compose docker-compose build

Further, make sure to open /c/codebase/docker-php-tutorial as a project in PhpStorm.

In general, there are two ways to run PHP from PhpStorm using Docker:

  1. via the built-in Docker setup
  2. via Deployment Configuration (treating docker more or less like a VM)
Run PHP via built-in Docker setup

This is the "easier" way and should mostly work "out of the box". When you run a PHP script using this method, PhpStorm will start a docker container and configure it automatically (path mappings, network setup, ...). Next, the script in question is executed and the container is stopped afterwards.

Enable docker to communicate on port 2375

Open the Docker Setting in tab "General" and activate the checkbox that says "Expose daemon on tcp://localhost:2375 without TLS".

Configure Docker Server in PhpStorm

In PhpStorm, navigate to File |

Truncated by Planet PHP, read more at the original (another 21610 bytes)

Categories: Web Technologies

How to setup PhpStorm with Xdebug on Docker [Tutorial Part 2] - Pascal Landau

Planet PHP - Mon, 08/06/2018 - 05:00

In the second part of this tutorial series on developing PHP on Docker we're taking a good hard look at PhpStorm, Xdebug and how to run and debug scripts from within PhpStorm on Docker.

And just as a reminder, the first part is over at Setting up PHP, PHP-FPM and NGINX for local development on Docker.

Note: The setup that I am going to use is for demonstration purposes only! I do not recommend that you use it "as is" as your development setup. Some problems that I won't solve here include:

  • everything is owned by root (no dedicated user; that will in particular be problematic for linux users)
  • SSH login credentials are hard-coded in the container (inherently insecure)
  • host.docker.internal will only exist for Windows and Mac users, NOT for unix users

There will be a another part of this series that will deal with all of those (and some more common) problems and aims at providing a consistent development environment for all developers in a team (regardless of the OS they are using). Please subscribe to the RSS feed to get automatic notifications when that part comes out :)

Table of contents Setup: The docker containers

We will only need the php-cli container for this part. Luckily, we already have a good understanding on how to create the container, although we'll need to make some adjustments to make everything work smoothly with PhpStorm. I'm gonna walk you through all the necessary changes, but I'd still recommend to clone the corresponding git repository docker-php-tutorial (unless you've already done that in part 1), checkout branch part_2 and build the containers now.

As in part 1, I'm assuming your codebase lives at /c/codesbase:

cd /c/codebase/ git clone https://github.com/paslandau/docker-php-tutorial.git cd docker-php-tutorial git checkout part_2 docker-compose docker-compose build

Further, make sure to open /c/codebase/docker-php-tutorial as a project in PhpStorm.

In general, there are two ways to run PHP from PhpStorm using Docker:

  1. via the built-in Docker setup
  2. via Deployment Configuration (treating docker more or less like a VM)
Run PHP via built-in Docker setup

This is the "easier" way and should mostly work "out of the box". When you run a PHP script using this method, PhpStorm will start a docker container and configure it automatically (path mappings, network setup, ...). Next, the script in question is executed and the container is stopped afterwards.

Enable docker to communicate on port 2375

Open the Docker Setting in tab "General" and activate the checkbox that says "Expose daemon on tcp://localhost:2375 without TLS".

Configure Docker Server in PhpStorm

In PhpStorm, navigate to File |

Truncated by Planet PHP, read more at the original (another 21610 bytes)

Categories: Web Technologies

Negative architecture, and assumptions about code - Matthias Noback

Planet PHP - Mon, 08/06/2018 - 01:25

In "Negative Architecture", Michael Feathers speaks about certain aspects of software architecture leading to some kind of negative architecture. Feathers mentions the IO Monad from Haskell (functional programming) as an example, but there are parallel examples in object-oriented programming. For example, by using layers and the dependency inversion principle you can "guarantee" that a certain class, in a certain layer (e.g. domain, application) won't do any IO - no talking to a database, no HTTP requests to some remote service, etc.

[...] guarantees form a negative architecture - a set of things that you know can’t happen in various pieces of your system. The potential list is infinite but that doesn’t stop this perspective from being useful. If you’re using a particular persistence technology in one part of your system, it is valuable to be able to say that it is only used in that place and no place else in the system. The same can be true of other technologies. Knowing what something is not able to do reduces the number of pitfalls. It puts architecture on solid ground.

I find that this is a great advantage of making any kind of agreement between team members: about design principles being applied, where to put certain classes, etc. Even making a clear distinction between different kinds of tests can be very useful. It's good to know that if a test is in the "unit test" directory, it won't do any IO. These tests are just some in-memory verifications of specified object behavior. So we should be able to run them in a very short amount of time, with no internet connection, no dependent services up and running, etc.

A negative architecture is what's "between the lines". You can look at all the code and describe a positive architecture for it, but given certain architecture or design rules, there's also the background; things we can be sure will never happen. Like a User entity sending an email. Or an html template doing a database migration. Or an HTTP client wiping out the entire disk drive. And so on and so on; I'm sure you can think of some other funny stuff that could happen (and that would require instant, nightmarish fixing).

It all sounds really absurd, but tuned down a little, these examples are actually quite realistic if you work with a legacy project that's in a bad shape. After all, these scenarios are all possible. Developers could've implemented them if they wanted to. We can never be certain that something is definitely not happening in our system. In particular if we don't have tests for that system. We can only have a strong feeling that something won't be happening.

The legacy project I'm working on these days isn't quite as bad as the ones we're fantasizing about now. But still, every now and then I stumble upon an interesting class, method, statement, which proves that my picture of the project's "negative architecture" isn't accurate enough. This always comes with the realization that "apparently, this can happen". Let's take a look at a few examples.

"An object does nothing meaningful in its constructor"

I've learned over time to "do nothing" in my constructor; just to accept constructor arguments and assign them to properties. Like this:

final class SomeService { private $foo; private $bar; public function __construct($foo, $bar) { $this->foo = $foo; $this->bar = $bar; } }

Recently, I replaced some static calls to a global translator function by calls to an injected translator service, which I added as a new constructor argument. At the last line of the constructor I assigned the translator service to a new attribute (after all, that's what I've learned to do with legacy code - add new things at the end):

final class SomeService { // ... private $translator; public function __construct(..., Translator $translator) { // ... $this->translator = $translator; } }

However, it turned out that the method that needed the (now injected) translator, was already called in the constructor:

public function __construct(..., Translator $translator) { // ... $this->aMethodThatNeedsTheTranslator(...); $this->translator = $translator; }

So before I assigned the translator to its dedicated attribute, another method attempted to use it already. This resulted in a nasty, fatal runtime error. It made me realize that my assumption was that in this project, a constructor would never do something. So even though th

Truncated by Planet PHP, read more at the original (another 8202 bytes)

Categories: Web Technologies

Vitess Weekly Digest

Planet MySQL - Sun, 08/05/2018 - 01:33
This week, we kick off our new weekly blog updates — bringing you the best of Vitess questions and topics on our Slack discussions. The goal is to show the most interesting topics and requests so those of you just getting started can see highlights of what has been covered.
Since this is our first ever digest, we’re going to go back in time and publish a little more than what happened last week.
Large result sets Alejandro [Jul 2nd at 9:54 AM] Good morning, we are trying to move away from interacting with Vitess through gRPC and instead using the MySQL binary protocol and I was just wondering if anyone here could let me know if Vitess supports unbuffered/streaming queries (returning more than 10K rows) over the MySQL binary protocol, or if that is just a gRPC feature? Thanks in advance. (edited)
sougou [29 days ago] @Alejandro set workload='olap' should do it (for mysql protocol)
Naming shards Deepak [9:36 AM] Hello everyone, I have a small doubt can someone please help me with it? I have four shards in Vitess cluster but all the data are going in just one shard. One of the difference in the setup is shard nomenclature. Instead of giving -80, 80- etc as shard names (params to -init_shard flag to vttablet) I have given names 0, 1, 2, 3. Can this be the cause of all data going to just one shard?
sougou [10:11 AM] @Deepak yeah. shards have to be named by their keyrange
DB config parameters simplified
sougou [10:06 AM] yay. no more long list of db command line options for vttablet: https://github.com/vitessio/vitess/pull/4088
all db-config-XXX options are now deprecated.
acharis [10:08 AM] but will still work?
sougou [10:08 AM] yes
acharis [10:08 AM] great
sougou [10:08 AM] till 3.0, then I'll delete them
MySQL 8.0 support hrishy [7:33 PM] does vitess support mysql 5.8 and the native json in particular
sougou [7:41 PM] @hrishy I assume you mean 8.0. Although it hasn't been tried, we think it should work.

derekperkins [7:49 PM] the new 8.0 JSON functions won’t work yet, those have to get added to the parser, but if that’s a blocker for you, it’s pretty easy to add
derekperkins [8 days ago] https://github.com/vitessio/vitess/issues/4099
Replica chicken-and-egg faut [1:01 AM] Hello. I need some help migrating to vitess. I have a baremetal master running mysql 5.6 and vttablet pointing to that master. I can query the master through vttablet and vtgate. The replicas however are still empty and cannot replicate because they don’t have the correct schema. I thought I could try make it a rdonly tablet and do the copy but that says no storage method implemented (assuming because it is not managed by mysqlctld) I’m not sure what the best approach from here is.
sougou [6:21 AM] @faut you can setup the replicas independent of vitess: manually copy the schema and point them at the master, just like you would to a regular mysql replica mysqlctl is good only if you want vitess to manage the mysqls from the ground up. but if you want to spin up a brand new replica, you first need to take a backup through vtctl then spinning up a vttablet against an empty mysql will restore from that backup and automatically point it to the master
faut [6:24 AM] yea, I’m stuck at the point of making the backup through vtctl. I want to try get away from managed the sql servers.  If I had a replica/rdonly with the schema manually copied would it manage to copy all the data from the existing master? I am able to do a GetSchema off the master but cannot just restore that schema to the rdonly/replica.
sougou [6:26 AM] there is a chicken-and-egg problem here because you can't take a backup from a master
faut [6:26 AM] Yes :joy:
sougou [6:26 AM] so, you need to manually bring up one rdonly replica and take a backup using that after that you can spin up more (we need to fix that limitation). there's an issue for it
Vitess auto-fixes replication sjmudd [5:52 AM] Hi all. I was a bit surprised by something I saw today in Vitess. I had a master which was configured on “the wrong port”, so to simplify things I adjusted the configuration and restarted vttablet and mysqld.  Clearly changing the port the master was listening on broke replication so I went to fix the replica configurations by doing a stop slave and was going to do a change master to master_port = xxx only to find it had given me an error: replication was already running. It looks like vttablet will “fix” the master host:port configuration if replication is stopped and restart replication. Wasn’t bad but was unexpected.
sjmudd [5:52 AM] Is this expected? (I’d guess so). What other magic type behaviour does vitess do and when? @sougou?
sougou [6 days ago] vttablet will try to fix replication by default. You can disable it with the -disable_active_reparents flag. If you're managing mysql externally to vitess, this should be specified for vttablet as well as vtctld.
skyler [6:08 AM] Is there a document anywhere that lists the kinds of SQL queries that are incompatible with Vitess?
derekperkins [6:24 AM] @skyler it depends whether your queries are single shard or not single shard supports most any query since it is just passed through to the underlying MySQL instance cross-shard is much more limited
sjmudd [7.12 AM] also there are several things even a single shard won’t handle well: e.g. setting/querying session/global variables
use of query hints. many of these things may not be a concern. but if you use them can require code changes.
Sougou: while it is configurable I think you should probably show the default vitess version as 5.7.X as 5.5.X is rather antique right now.
I’ve changed my setup to advertise as 5.7.X. Is there a need to show such a low version?  iirc this can lead replication to behave differently as the i/o thread sometimes takes the remote version into account. I’d guess most other things may not care.
sougou [8:09 AM] yeah. we can change the default now. most people are on 5.7 now
acharis [9:17 AM] i think vtexplain might be what you're looking for
skyler [6 days ago] nice! Thank you. I was unaware of that.
derekperkins [6 days ago] Thanks, I couldn't remember for sure what the current iteration was called
Categories: Web Technologies

What Does I/O Latencies and Bytes Mean in the Performance and sys Schemas?

Planet MySQL - Sat, 08/04/2018 - 23:18

The Performance Schema and sys schema are great for investigating what is going on in MySQL including investigating performance issues. In my work in MySQL Support, I have a several times heard questions whether a peak in the InnoDB Data File I/O – Latency graph in MySQL Enterprise Monitor (MEM) or some values from the corresponding tables and view in the Performance Schema and sys schema are cause for concern. This blog will discuss what these observations means and how to use them.

The Tables and Views Involved

This blog will look into three sources in the Performance Schema for I/O latencies, so let’s first take a look at those tables. The three Performance Schema tables are:

  • events_waits_summary_global_by_event_name: with the event name set to wait/io/table/sql/handler or wait/io/file/%. This is the table used for the waits_global_by_latency and wait_classes_global_by_% views in the sys schema.
  • table_io_waits_summary_by_table: this is the table used for the schema_table_statistics% views in the sys schema.
  • file_summary_by_instance: this is the table used for the io_global_by_file_by% views in the sys schema.

These are also the sources used in MySQL Enterprise Monitor for the InnoDB Data File I/O graphs shown above. Let’s take a look at an example of each of the tables.

events_waits_summary_global_by_event_name

The events_waits_summary_global_by_event_name has aggregate data for wait events grouped by the event name. For the purpose of this discussion it is the table and file wait/io events that are of interested. An example of the data returned is:

mysql> SELECT * FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME = 'wait/io/table/sql/handler' OR EVENT_NAME LIKE 'wait/io/file/%' ORDER BY SUM_TIMER_WAIT DESC LIMIT 5; +--------------------------------------+------------+----------------+----------------+----------------+----------------+ | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | +--------------------------------------+------------+----------------+----------------+----------------+----------------+ | wait/io/file/innodb/innodb_log_file | 4003029 | 97371921796204 | 5371684 | 24324412 | 180878537324 | | wait/io/table/sql/handler | 4049559 | 43338753895440 | 494128 | 10702072 | 138756437188 | | wait/io/file/innodb/innodb_data_file | 25850 | 11395061934996 | 0 | 440814508 | 43029060332 | | wait/io/file/sql/binlog | 20041 | 1316679917820 | 0 | 65699088 | 25816580304 | | wait/io/file/sql/io_cache | 2439 | 68212824648 | 1448920 | 27967360 | 628484180 | +--------------------------------------+------------+----------------+----------------+----------------+----------------+ 5 rows in set (0.00 sec) mysql> SELECT EVENT_NAME, COUNT_STAR, sys.format_time(SUM_TIMER_WAIT) AS SumTimerWait, sys.format_time(MIN_TIMER_WAIT) AS MinTimeWait, sys.format_time(AVG_TIMER_WAIT) AS AvgTimerWait, sys.format_time(MAX_TIMER_WAIT) AS MaxTimerWait FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME = 'wait/io/table/sql/handler' OR EVENT_NAME LIKE 'wait/io/file/ %' ORDER BY SUM_TIMER_WAIT DESC LIMIT 5; +--------------------------------------+------------+--------------+-------------+--------------+--------------+ | EVENT_NAME | COUNT_STAR | SumTimerWait | MinTimeWait | AvgTimerWait | MaxTimerWait | +--------------------------------------+------------+--------------+-------------+--------------+--------------+ | wait/io/file/innodb/innodb_log_file | 4003029 | 1.62 m | 5.37 us | 24.32 us | 180.88 ms | | wait/io/table/sql/handler | 4049559 | 43.34 s | 494.13 ns | 10.70 us | 138.76 ms | | wait/io/file/innodb/innodb_data_file | 25853 | 11.40 s | 0 ps | 440.78 us | 43.03 ms | | wait/io/file/sql/binlog | 20041 | 1.32 s | 0 ps | 65.70 us | 25.82 ms | | wait/io/file/sql/io_cache | 2439 | 68.21 ms | 1.45 us | 27.97 us | 628.48 us | +--------------------------------------+------------+--------------+-------------+--------------+--------------+ 5 rows in set (0.01 sec) mysql> SELECT * FROM sys.waits_global_by_latency WHERE events = 'wait/io/table/sql/handler' OR events LIKE 'wait/io/file/%' LIMIT 5; +--------------------------------------+---------+---------------+-------------+-------------+ | events | total | total_latency | avg_latency | max_latency | +--------------------------------------+---------+---------------+-------------+-------------+ | wait/io/file/innodb/innodb_log_file | 4003029 | 1.62 m | 24.32 us | 180.88 ms | | wait/io/table/sql/handler | 4049559 | 43.34 s | 10.70 us | 138.76 ms | | wait/io/file/innodb/innodb_data_file | 25874 | 11.43 s | 441.88 us | 43.03 ms | | wait/io/file/sql/binlog | 20131 | 1.32 s | 65.66 us | 25.82 ms | | wait/io/file/sql/io_cache | 2439 | 68.21 ms | 27.97 us | 628.48 us | +--------------------------------------+---------+---------------+-------------+-------------+ 5 rows in set (0.01 sec)

These three queries show the same data, just obtained and displayed in different ways.

In the result there are two groups of events. The wait/io/table events (the wait/io/table/sql/handler is the only event of this group which is why it can be listed explicitly) and the wait/io/file group.

The table events are for accessing data in tables. It does not matter whether the data is cached in the buffer pool or is accessed on disk. In this table and view, there is no distinguishing between different types of access (read, write, etc.).

The file events are, as the name suggest, for actually accessing files. There is one file event per file type. For example, in he output there are the wait/io/file/innodb/innodb_log_file event for accessing the InnoDB redo log files, the wait/io/file/innodb/innodb_data_file event for accessing the InnoDB data files themselves, the wait/io/file/sql/binlog event for the binary log files, etc.

In the second query, all of the timings are wrapped in the sys.format_time() function. The timings returned by the Performance Schema are in picoseconds (10^-12 second) which are somewhat hard for us humans to read. The sys.format_time() function converts the picoseconds to human readable strings. When you sort, however, make sure you sort by the original non-converted values.

Tip: Use the sys.format_time() function to convert the picoseconds to a human readable value, but do only so for the returned row; sort and filter by the original values. The Performance Schema always returns timings in picoseconds irrespective of the timer used internally for the event.

The sys schema by default returns the timing as human readable values. If you need the values in picoseconds prefix the table name with x$, for example sys.x$waits_global_by_latency. The sys schema includes an ORDER BY clause in most views. For the waits_global_by_latency view, the default ordering is by the total latency, so there is no need to add an ORDER BY clause in this example.

table_io_waits_summary_by_table

The table_io_waits_summary_by_table Performance Schema table and schema_table_statistics% sys schema views are related to the wait/io/table/sql/handler event just discussed. These provide information about the amount of time spent per table. Unlike querying the wait/io/table/sql/handler in the wait_events_% tables, it split the time spent into whether it is used for reads, writes, fetch, insert, update, or delete. The read and write columns are the aggregates for the corresponding read and write operations, respectively. Since fetch is the only read operation, the read and fetch columns will have the same values.

The table and view show the table I/O, i.e. the access to table data irrespective of whether the data is accessed in-memory or on disk. This is similar to the wait/io/table/sql/handler event. An example of the result of querying the table and view for the employees.salaries table is:

mysql> SELECT * FROM performance_schema.table_io_waits_summary_by_table WHERE OBJECT_SCHEMA = 'employees' AND OBJECT_NAME = 'salaries'\ *************************** 1. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: employees OBJECT_NAME: salaries COUNT_STAR: 2844047 SUM_TIMER_WAIT: 31703741623644 MIN_TIMER_WAIT: 4975456 AVG_TIMER_WAIT: 11147072 MAX_TIMER_WAIT: 138756437188 COUNT_READ: 0 SUM_TIMER_READ: 0 MIN_TIMER_READ: 0 AVG_TIMER_READ: 0 MAX_TIMER_READ: 0 COUNT_WRITE: 2844047 SUM_TIMER_WRITE: 31703741623644 MIN_TIMER_WRITE: 4975456 AVG_TIMER_WRITE: 11147072 MAX_TIMER_WRITE: 138756437188 COUNT_FETCH: 0 SUM_TIMER_FETCH: 0 MIN_TIMER_FETCH: 0 AVG_TIMER_FETCH: 0 MAX_TIMER_FETCH: 0 COUNT_INSERT: 2844047 SUM_TIMER_INSERT: 31703741623644 MIN_TIMER_INSERT: 4975456 AVG_TIMER_INSERT: 11147072 MAX_TIMER_INSERT: 138756437188 COUNT_UPDATE: 0 SUM_TIMER_UPDATE: 0 MIN_TIMER_UPDATE: 0 AVG_TIMER_UPDATE: 0 MAX_TIMER_UPDATE: 0 COUNT_DELETE: 0 SUM_TIMER_DELETE: 0 MIN_TIMER_DELETE: 0 AVG_TIMER_DELETE: 0 MAX_TIMER_DELETE: 0 1 row in set (0.00 sec) mysql> SELECT * FROM sys.schema_table_statistics WHERE table_schema = 'employees' AND table_name = 'salaries'\G *************************** 1. row *************************** table_schema: employees table_name: salaries total_latency: 31.70 s rows_fetched: 0 fetch_latency: 0 ps rows_inserted: 2844047 insert_latency: 31.70 s rows_updated: 0 update_latency: 0 ps rows_deleted: 0 delete_latency: 0 ps io_read_requests: 493 io_read: 7.70 MiB io_read_latency: 611.04 ms io_write_requests: 8628 io_write: 134.91 MiB io_write_latency: 243.19 ms io_misc_requests: 244 io_misc_latency: 2.50 s 1 row in set (0.05 sec)

In this case it shows that there has been mostly writes – inserts – for the table. The sys schema view effectively joins on the performance_schema.file_summary_by_instance for the read columns, so for the schema_table_statistics view fetch and read are not synonyms.

So, what is it the file_summary_by_instance table shows that is different table the “table I/O” that has been the topic of the first two tables? Let’s see.

file_summary_by_instance

Unlike the two previous tables, the file_summary_by_instance shows how much time is spent on actual file I/O and how much data is accessed. This makes the file_summary_by_instance table and the corresponding sys schema views very useful for determining where time is spent doing disk I/O and which files have the most data accesses on disk.

An example of using the Performance Schema and two of the sys schema views is:

mysql> SELECT * FROM performance_schema.file_summary_by_instance WHERE FILE_NAME LIKE '%\\\\employees\\\\salaries.ibd'\G *************************** 1. row *************************** FILE_NAME: C:\ProgramData\MySQL\MySQL Server 8.0\Data\employees\salaries.ibd EVENT_NAME: wait/io/file/innodb/innodb_data_file OBJECT_INSTANCE_BEGIN: 2230271392896 COUNT_STAR: 9365 SUM_TIMER_WAIT: 3351594917896 MIN_TIMER_WAIT: 11970500 AVG_TIMER_WAIT: 357885020 MAX_TIMER_WAIT: 40146113948 COUNT_READ: 493 SUM_TIMER_READ: 611040652056 MIN_TIMER_READ: 65421052 AVG_TIMER_READ: 1239433224 MAX_TIMER_READ: 16340582272 SUM_NUMBER_OF_BYTES_READ: 8077312 COUNT_WRITE: 8628 SUM_TIMER_WRITE: 243186542696 MIN_TIMER_WRITE: 11970500 AVG_TIMER_WRITE: 28185588 MAX_TIMER_WRITE: 1984546920 SUM_NUMBER_OF_BYTES_WRITE: 141459456 COUNT_MISC: 244 SUM_TIMER_MISC: 2497367723144 MIN_TIMER_MISC: 154910196 AVG_TIMER_MISC: 10235113564 MAX_TIMER_MISC: 40146113948 1 row in set (0.00 sec) mysql> SELECT * FROM sys.io_global_by_file_by_latency WHERE file = '@@datadir\\employees\\salaries.ibd'\G *************************** 1. row *************************** file: @@datadir\employees\salaries.ibd total: 9365 total_latency: 3.35 s count_read: 493 read_latency: 611.04 ms count_write: 8628 write_latency: 243.19 ms count_misc: 244 misc_latency: 2.50 s 1 row in set (0.09 sec) mysql> SELECT * FROM sys.io_global_by_file_by_bytes WHERE file = '@@datadir\\employees\\salaries.ibd'\G *************************** 1. row *************************** file: @@datadir\employees\salaries.ibd count_read: 493 total_read: 7.70 MiB avg_read: 16.00 KiB count_write: 8628 total_written: 134.91 MiB avg_write: 16.01 KiB total: 142.61 MiB write_pct: 94.60 1 row in set (0.10 sec)

This example is from Microsoft Windows, and as always when backslashes are in play, it is fun to try to determine the appropriate number of backslashes to use. When specifying the file name with LIKE, you need four backslashes per backslash in the file name; when using = you need two backslashes.

Again, the values are split into reads and writes (though not as detailed as before with fetch, insert, update, and delete – that is not known at the level where the file I/O happens). The miscellaneous values include everything that is not considered reads or write; this includes opening and closing the file.

The sys schema queries not only have formatted the timings, but also the path and the bytes. This has been done using the sys.format_path() and sys.format_bytes() functions, respectively.

From the output, it can be seen that despite no rows were ever fetched (read) from the employees.salaries table (that was found in the previous output), there has still been some read file I/O. This was what the sys.schema_table_statistics view reflected.

So, what does all of this mean? The graph in MySQL Enterprise Monitor showed that there was six seconds file I/O latency for the InnoDB data files. Is that bad? As often with these kinds of questions, the answer is: “it depends”.

What to Make of the Values

In this case we have a case where the graphs in MySQL Enterprise Monitor show a peak for the latency and bytes used doing I/O on the InnoDB data files. This is actually disk I/O. But what exactly does that means and should the alarm sound?

Let’s first refresh our memory on how the graphs looked:
If you are not using MySQL Enterprise Monitor, you may have similar graphs from your monitoring solution, or you have obtained latency and bytes values from the tables and views discussed in this blog.

The latency graph shows that we have done six seconds if I/O. What does that mean? It is the aggregate I/O during the period the data was collected. In this case, the data is plotted for every minute, so in the one minute around 12:51, of the 60 seconds a total of six seconds was spent doing I/O. Now, the six seconds suddenly do no sound so bad. Similar for the bytes, around 4.6MiB of data was read or written.

In general, the values obtained either from the monitoring graphs or from the underlying tables cannot be used to conclude whether the is a problem or not. They just show how much I/O was done at different times.

Similar for the values from the Performance Schema. On their own they do not tell much. You can almost say they are too neutral – they just state how much work was done, not whether it was too much or too little.

A more useful way to use this data is in case a problem is reported. This can be that system administrator reports the disks are 100% utilized or that end users report the system is slow. Then, you can go and look at what happened. If the disk I/O was unusually high at that point in time, then that is likely related, and you can continue your investigation from there.

There are more reports in MySQL Enterprise Monitor both as time series graphs and as point-in-time snapshots. The point-in-time snapshots are often using the sys schema views but allows sorting. An example is the Database File I/O reports:
MySQL Workbench also provides performance reports based on the sys scheme. The equivalent to the previous report is the Top File I/O Activity Report:
The MySQL Workbench performance reports also allows you to export the report or copy the query used to generate the report, so you can execute it manually.

With respect to the wait/io/table/sql/handler events, then remember that I/O here does not mean disk I/O, but table I/O. So, all it means that time is accumulating for these events – including when looking at the per table is that the data in the table is used. There are also per index values in table_io_waits_summary_by_index_usage Performance Schema table and the schema_index_statistics sys view which have the same meaning. Whether a given usage is too high depends on many things. However, it can again be useful to investigate what is causing problems.

For the example data from this blog, it was triggered by loading the employees sample database. So, there was no problem. If you want to put data into your database, it must necessarily be written to the data file, otherwise it will not be persisted. However, if you think the data import took too long, you can use the data as part of your investigation on how to make the import faster.

The conclusion is that you should not panic if you see the I/O latencies peak. On their own they just mean that more work was done during that period that usual. Without context a peak is no worse than a dip (which could indicate something is preventing work from being done or that there is a natural slow period). Instead use the I/O latencies and bytes together with other observations to understand where MySQL is spending time and for which files data is being read and written.

References

I will recommend you look at the following references, if you want to understand more about the Performance Schema tables and sys schema views discussed in this blog:

The GitHub repository for the sys schema includes all the definitions of the views (and other objects). Since these are written in plain SQL, they are very useful to see in more depth where the data is coming from. The GitHub website allows you to browse through each of the files. Each sys schema object is defined in its own file.

Categories: Web Technologies

5 Easy Ways To Improve Your Database Performance

Planet MySQL - Sat, 08/04/2018 - 14:33

In many cases, developers, DBAs and data analysts struggle with bad application performance and are feeling quite frustrated when their SQL queries are extremely slow, which can cause the entire database to perform poorly.

Luckily, there is a solution to this problem! In this article, we will briefly cover a few ways you can use to improve the overall database performance. In many cases, you’ll need to use one or more of these paths to resolve database performance issues.

Optimize Queries

In most cases, performance issues are caused by poor SQL queries performance. When trying to optimize those queries, you’ll run into many dilemmas, such as whether to use IN or EXISTS, whether to write a subquery or a join. While you can pay good dime on consulting services, you can also speed up SQL queries using query optimizers such as EverSQL Query Optimizer, which will both speed up the query and explain the recommendations, so you could learn for the future.

Essentially, EverSQL is one of the most effective online SQL query optimizers currently available; it is capable of optimizing not just MySQL, but MariaDB and PerconaDB queries as well – and you can try it out entirely for free!

Create optimal indexes

Indexing, when done properly, can help to optimize your query execution duration and increase overall database performance. Indexes accomplish this by implementing a data structure that helps to keep things organized and makes locating information easier; basically, indexing speeds up the data retrieval process and makes it more efficient, thereby saving you (and your system) time and effort.

Get a stronger CPU

The better your CPU, the faster and more efficient your database will be. Therefore, if your database underperforms, you should consider upgrading to a higher class CPU unit; the more powerful your CPU is, the less strain it will be under when tasked with multiple applications and requests. Also, when assessing CPU performance it’s important to keep track of all aspects of CPU performance, including CPU ready times (which can tell you about the times your system attempted to use the CPU but couldn’t because all of the CPU’s resources were too busy or otherwise occupied).

Allocate more memory

Similar to how having a CPU that’s not powerful enough can impact the efficiency of a database, so too can lack of memory. After all, when there is not enough memory available in the database to perform the work that is being asked of, database performance is understandably going to take a hit. Basically, having more memory available will help to boost the system’s efficiency and overall performance. A good way to check if you need more memory is to look at how many page faults your system has; if the number of faults is high (in the thousands, for example) it means that your hosts are running low on (or potentially entirely out of) available memory space. Therefore, when trying to improve database performance it’s important to both look at how much memory you have total as well as page faults (to determine if you need additional memory to improve efficiency).
In addition, you can consider increasing the amount of memory used by MySQL. Our recommendation is allow it to allocate 70% of the total memory (assuming the database is the only application on that server). You can modify the amount of memory allocated to the database using the innodb_buffer_pool_size key in MySQL’s configuration file, my.cnf.

Data defragmentation

If you’re having trouble with a slow database, another possible solution is data defragmentation. When many records are written to the database and time goes by, the records are fragmented in MySQL’s internal data files and on the disk itself. The defragmentation of the disk will allow grouping the relevant data together, so I/O related operations will run faster, which will directly impact on overall query and database performance. Also, on a somewhat related note it’s also important to have enough disk space in general when running a database; if you’re looking to truly optimize database performance, make sure to utilize disk defragmentation while also keeping plenty of free disk space available for your database.

Disk Types

Fetching the results of even a single query can require millions of i/o operations from the disk, depending on the amount of data the query needs to access for processing, and depending on the amount of data returned from the query. Therefore, the type of disks in your server can greatly impact the performance of your SQL queries. Working with SSD disks can significantly improve your overall database performance, and specifically your SQL query performance.

Database version

Another major factor in database performance is the version of MySQL you’re currently deploying. Staying up to date with the latest version of your database can have significant impact on overall database performance. It’s possible that one query may perform better in older versions of MySQL than in new ones, but when looking at overall performance, new versions tend to perform better.
Credits: Simon Mudd

Summary

Ultimately, whether you choose to utilize one or more of these methods, you can rest assured that there are plenty of options for improving your database performance. Test them one by one to see which one will have the largest impact on your database.

Categories: Web Technologies

Fun with Bugs #70 - On MySQL Bug Reports I am Subscribed to, Part VIII

Planet MySQL - Sat, 08/04/2018 - 06:53
More than 2 months passed since my previous review of active MySQL bug reports I am subscribed to, so it's time to describe what I was interested in this summer.

Let's start with few bug reports that really surprised me:
  • Bug #91893 - "LOAD DATA INFILE throws error with NOT NULL column defined via SET". The bug was reported yesterday and seem to be about a regression in MySQL 8.0.12 vs older versions. At least I have no problem to use such a way to generate columns for LOAD DATA with MariaDB 10.3.7.
  • Bug #91847 - "Assertion `thread_ids.empty()' failed.". As usual, Roel Van de Paar finds funny corner cases and assertion failures of all kinds. This time in MySQL 8.0.12.
  • Bug #91822 - "incorrect datatype in RBR event when column is NULL and not explicit in query". Ernie Souhrada found out that the missing column is given the datatype of the column immediately preceding it, at least according to mysqlbinlog output.
  • Bug #91803 - "mysqladmin shutdown does not wait for MySQL to shut down anymore". My life will never be the same as before after this. How can we trust anything when even shutdown command is no longer works as expected? I hope this bug is not confirmed after all, it's still "Open".
  • Bug #91769 - "mysql_upgrade return code is '0' even after errors". Good luck to script writers! The bug is still "Open".
  • Bug #91647 - "wrong result while alter an event". Events may just disappear when you alter them. Take care!
  • Bug #91610 - "5.7 or later returns an error on strict mode when del/update with error func". Here Meiji Kimura noted a case when the behavior of strict sql_mode differs in MySQL 5.6 vs never versions.
  • Bug #91585 - "“dead” code inside the stored proc or function can significantly slow it down". This was proved by Alexander Rubin from Percona.
  • Bug #91577 - "INFORMATION_SCHEMA.INNODB_FOREIGN does not return a correct TYPE". This is a really weird bug in MySQL 8.
  • Bug #91377 - "Can't Initialize MySQl if internal_tmp_disk_storage_engine is set to MYISAM". It seems Oracle tries really hard to get rid of MyISAM by all means in MySQL 8 :)
  • Bug #91203 - "For partitions table, deal with NULL with is mismatch with reference guide". All version affected. maybe manual is wrong, but then we see weird results in information_schema as well. So, let's agree for now that it's a "Verified" bug in partitioning...
As usual, I am interested in InnoDB-related bugs:
  • Bug #91861 - "The buf_LRU_free_page function may leak some memory in a particular scenario". This is a very interesting bug report about the memory leak that happens when tables are compressed. It shows how to use memory instrumentation in performance_schema to pinpoint the leak. This bug report is still "Open".
  • Bug #91630 - "stack-use-after-scope in innobase_convert_identifier() detected by ASan". Yura Sorokin from Percona had not only reported this problem, but also contributed a patch.
  • Bug #91120 - "MySQL8.0.11: ibdata1 file size can't be more than 4G". Why nobody tries to do anything about this "Verified" bug reported 2 months ago?
  • Bug #91048 - "ut_basename_noext now dead code". This was reported by Laurynas Biveinis.
Replication problems are also important to know about:
  • Bug #91744 - "START SLAVE UNTIL going further than it should." This scary bug in cyclic replication setup was reported by  Jean-François Gagné 2 weeks ago and is still "Open" at the moment.
  • Bug #91633 - "Replication failure (errno 1399) on update in XA tx after deadlock". On top of all other problems with XA transactions we have in MySQL, it seems that replication may break upon executing a row update immediately after a forced transaction rollback due to a deadlock being detected while in an XA transaction.
Some optimizer bugs also caught my attention:
  • Bug #91486 - "Wrong column type , view , binary". We have a "Verified" regression bug here without a "regression" tag or exact versions checked. Well done, Sinisa Milivojevic!
  • Bug #91386 - "Index for group-by is not used with primary key for SELECT COUNT(DISTINCT a)". Yet another case where a well known bug reporter, Monty Solomon, had  to apply enormous efforts to get it "Verified" as a feature request.
  • Bug #91139 - "use index dives less often". A "Verified" feature request from Mark Callaghan.
The last but not the least, documentation bugs. We have one today (assuming I do not care that much about group replication):
  • Bug #91074 - "INSTANT add column undocumented". It was reported by my former colleague in Percona, Jaime Crespo. The bug is still "Verified" as of now, but since MySQL 8.0.12 release I think it's no longer valid. I see a lot of related details here, for example. But nobody cares to close this bug properly and provide the links to manual that were previously missing.
That's all for today, folks! Stay tuned.
Categories: Web Technologies

The Cost Of JavaScript In 2018

Echo JS - Fri, 08/03/2018 - 19:48
Categories: Web Technologies

What is new in ES2018?

Echo JS - Fri, 08/03/2018 - 19:48
Categories: Web Technologies

Firefox removes RSS support - Evert Pot

Planet PHP - Fri, 08/03/2018 - 12:45

gHacks recently reported that Mozilla plans to remove support for RSS & Atom related features. Specifically Live Bookmarks and their feed reader.

The reasons cited are the lack of usage. This doesn’t completely surprise me. Both of the features don’t fit that well with modern workflows. There’s not a lot of people using bookmarks actively except through auto-complete, and the reader interface never really was as effective as just looking at a website. Honestly the day that Mozilla didn’t show the feed-discovery button by default in the address bar is the day they killed it.

Firefox feed reader:

The 2000’s vs Today

I can’t help being sad about this though. For a little while feeds were a very prominent and exciting feature of the web. More than an effective tool to replace mailing lists, they represented an idea that anyone can start a website and become part of the “blogosphere”. The dreams were pretty big in the 2000’s. Aside from standard protocols for getting updates from websites, many platforms supported standard API’s for authoring blogs, and API’s for federated commenting and even API’s for federated identity (OpenID).

At its height, even Microsoft Word got integration with these standard blogging (Metaweblog) APIs for easy authoring.

Today it feels like a lot of that dream has died, and we’ve moved back to censored corporate-controlled content-silos such as Facebook and Twitter. Each with their proprietary API’s, incompatable with anything besides themselves and no interest to open up their networks to federation with other providers. These large vendors are worried about losing control of where content lives and where your firends are, because once they lose the network effect the only thing that’s left is a steaming pile of garbage where nobody really wants to hang out anyway.

So I’m sad to see this feature being removed from Firefox. It wasn’t a really a fleshed out or well maintained feature. It’s recoverable with add-ons, but what I really care about, is what the feature represents.

Mozilla’s mission is:

Our mission is to ensure the Internet is a global public resource, open and accessible to all. An Internet that truly puts people first, where individuals can shape their own experience and are empowered, safe and independent.

I think it’s important to say that I don’t necessarily advocate for preserving this feature. I want Mozilla to be a catalyst for taking control from corporate silos back to the individual. RSS & Atom might not be the key to this goal, but without a good replacement this doesn’t feel good. To me it goes against the mission that Mozilla set out to accomplish.

Categories: Web Technologies

Congrats to Duo Security!

Planet MySQL - Fri, 08/03/2018 - 09:35


Congratulations to Duo Security, which announced that it is to be acquired by Cisco Systems for $2.35b. This is a great outcome for all involved, and I'm very proud of what the team has accomplished.

I worked with Duo for about three years, initially as an advisor and ultimately as Chief Operating Officer running Sales, Marketing, Products, Engineering and Services. I helped grow the company from around $7m in Annual Recurring Revenue (ARR) to about $100m. The company has continued to grow to 700 employees, 12,000 customers and revenues that I estimate could exceed $200m ARR by year end, based on prior published numbers.

Duo is the fastest growing company I've been a part of; faster even than Zendesk or MySQL. When a company grows this quickly, it becomes a different organization every year. The early sub-$10m revenue company is quite different from where Duo is today. I would always tell new employees to be prepared for change. What remained constant was an underlying set of midwestern values of hard work, customer care and innovation that made Duo special. (Also we had a really good fun band called "Louder Than Necessary.")

It's a testament to the founders' vision and the management skills of the leaders we recruited that the company scaled so well. I remain especially proud of the many people we hired, promoted and developed to become the future leaders in the company. As news of the acquisition came out, many people have asked me about the deal, so here are my thoughts...

First of all, this should be recognized as an absolute success for the management team. To grow a company to this size and value is very rare. Less than 1% of venture-backed companies get to a valuation of $1 billion. It's also one of the biggest software successes in the midwest and proves that you don't have to be in Silicon Valley to win big. (Duo has in fact expanded into San Mateo, Austin, London and Detroit. Part of Duo's success is due to these multiple locations, but that's a another story.) 

Secondly, this deal creates a larger force in the industry. There is no doubt that Duo could have proceeded towards an IPO in 2019; they had in fact hired several new executives to lead these efforts in recent months. But the combination of Cisco and Duo together is more significant than Duo on its own. There has been a large amount of consolidation in the security space in the last few years and I believe Cisco will emerge as a leader. They have the respect and attention of Global 2000 CISOs and CIOs, a strong worldwide sales machine and a large number of related products. In a few years time, it will be clear that Microsoft isn't the only company that is capable of reinvention. 

Thirdly, this represents an opportunity for further growth for the team at Duo. Cisco plays on a larger global stage than Duo could on its own. But Duo's executives, managers, engineers, security experts, sales people, support engineers, product team and marketing organization have a lot of mojo to contribute. Duo has become one of the fastest growing SaaS companies on the planet and they know a thing or two about making security easy. The company has a Net Promoter Score (NPS) of 68, one of the highest in the industry!

And that is the key to why this deal makes sense to me. The combination gives Duo scale. But it also injects speed and innovation into an industry that needs it. The old approach to security, locking down your network with a VPN and using old-fogey security tokens doesn't work when your applications are in the cloud, employees are mobile and hackers are targeting everyone and every thing. I believe Duo is well positioned to lead with a modern new approach to security.

There's also a fourth point, which in the long term could become even more significant. Duo's success injects a large amount of capital in the Ann Arbor / Detroit area. The company has also developed tremendous expertise in building a SaaS company at scale. That combination of capital and talent will result in the creation of additional startups in coming years. Duo's investors (Benchmark, GV, Index, Redpoint,Rennaissance, True Ventures...) did very well and are likely to be open to investing in new startups in the region alongside other firms focused on the midwest such as Drive Capital, eLab Ventures, Steve Case's Revolution, RPM Ventures and others. This acquisition shines a spotlight on Michigan's growing tech scene and that will have all kinds of positive impact on investment, innovation and job creation.

To all my friends at Duo, this is a vote of confidence in all that you have created. I wish you congratulations on achieving this milestone. Now there's an even bigger opportunity to take this product line, security expertise and company culture to a bigger audience than we ever thought possible.

Go Duo!  

 

Categories: Web Technologies

Pages