Get your MySQL table sizes in MB via a SQL query

Figuring out what tables are making your database excessively large can be really helpful when working with production database exports and keeping various environments up to date. This SQL query outputs that information for all databases on a given server:

table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY table_schema, (data_length + index_length) DESC;


Generate MySQL INSERT statements for a few existing records

Occasionally, you’ll want to duplicate a couple of records from your production MySQL database for local use without dumping the entire table (which could be huge). As a solution, the mysqldump command allows you to specify a table and a WHERE query for that table, allowing you to select specific record ID #s and retrieve only those records as INSERT statements:

mysqldump -h {host} -P {port} -u {username} -p{password} {database_name} {table_name} --where="ID = 1" --no-create-info --no-create-db


Upgrade a deprecated AWS Elastic Beanstalk environment in place using the CLI

When a major PHP version change (eg. 5.7 to 7.0, or 7.0 to 8.0) occurs, Amazon AWS Elastic Beanstalk typically does not allow for automated platform version updates but instead requires the cloning of an interface and a CNAME switch. For less available environments, use the following steps to upgrade-in-place using the AWS command line interface:

  1. Take a look at the list of currently supported PHP platforms on Elastic Beanstalk; you’ll want to decide which Solution Stack Name (eg. “64bit Amazon Linux 2 v3.4.1 running PHP 8.1”) corresponds to the platform you wish to upgrade to.
  2. In the Elastic Beanstalk web configuration page, get the environment ID for the environment you wish to upgrade (eg. “e-jwfjvy57r3”).
  3. Run the aws command to upgrade the environment in place:
    aws elasticbeanstalk update-environment --solution-stack-name "64bit Amazon Linux 2 v3.4.1 running PHP 8.1" --environment-id "e-2iuwivbgzc" --region "ca-central-1"


Measure current IOPS usage on a Linux server

This is most easily measured by installing the sysstat package:

sudo yum install sysstat -y

And running the sar command, looking at the value of tps:

sar -d 1

Getting a recursive list of files sorted by last modified date in Linux

Here’s a quick way to get a recursive list of files, sorted in descending order by the last modified date, from any folder in *nix:

find . -type f -printf "%T@ %p\0" | sort -zk1nr | xargs -0 printf "%s\n"

Google’s comprehensive, up-to-date list of mobile phone and device resolutions

The fine folks at Google have a page up on their Material Design site that lists popular device resolutions, including exactly what values you’d want to input into Google Chrome to accurately emulate that device’s screen:

What makes a great product manager?

There’s a terrific post by Brandon Chu on Medium titled MVPM: Minimum Viable Product Manager that does a great job at explaining that while it’s impossible to have in-depth knowledge of the technical, business and user experience legs of the product tripod, knowing key elements in each area makes you so much more able to handle the decision making that you’ll be doing every minute of the day.

Why I mostly hire generalists

I’ve had the pleasure of getting to build the in-house web development team at Rogers Media for a few years now up from just myself and one other developer to a staff of fifteen. I’m of the school of thought that software development teams are best kept small: It requires a smaller web of communication, individual team members maintain a high level of impact on the product they’re building (avoiding a loss of motivation due to the Ringelmann effect), and team stability (that is, people constantly joining or leaving the team) is usually greatly improved.

But there’s a potential downside that has to be addressed: Software is complex, and the requirements of web development often extremely so. How does a small team come through with everything that’s needed to make a project work? My answer has been to build the core of the team around generalists – people who tend to be very good in one area of development, but also knowledgeable and capable in most others as well.

Here’s an excerpt from the Pragmatic Programmer series titled Be A Generalist that speaks to why generalists are the answer to meeting a modern business product’s software needs:

For at least a couple of decades, desperate managers and business owners have been pretending that software development is a manufacturing process at heart. Requirements specifications are created, and architects turn these specifications into a high-level technical vision. Designers fill out the architecture with detailed design documentation, which is handed to robot-like coders, who hold pulp-fiction novels in one hand while sleepily typing in the design’s implementation with the other. Finally, Inspector 12 receives the completed code, which doesn’t receive her stamp of approval unless it meets the original specifications.

It’s no surprise that managers want software development to be like manufacturing. Managers understand how to make manufacturing work. We have decades of experience in how to build physical objects efficiently and accurately. So, applying what we’ve learned from manufacturing, we should be able to optimize the software development process into the well-tuned engine that our manufacturing plants have become.

In the so-called software factory, the employees are specialists. They sit at their place in the assembly line, fastening Java components together or rounding the rough edges of a Visual Basic application on their software lathes. Inspector 12 is a tester by trade. Software components move down the line, and she tests and stamps them in the same way each day. J2EE designers design J2EE applications. C++ coders code in C++. The world is very clean and compartmentalized.

Unfortunately, the manufacturing analogy doesn’t work. Software is at least as malleable as software requirements. Things change in business, and businesspeople know that software is soft and can be changed to meet those requirements. This means architecture, designs, code, and tests must all be created and revised in a fashion more agile than the leanest manufacturing processes can provide.

In this kind of rapidly changing environment, the flexible will survive. When the pressure is on, a smart businessperson will turn to a software professional can solve the problem at hand. So, how do you become that person whose name comes up when they’re looking for a superhero to save the day? The key is to be able to solve the problems that may arise.

What are those problems? That’s right: you don’t know. Neither do I. What I do know is that those problems are as diverse as deployment issues, critical design flaws that need to be solved and quickly reimplemented, heterogenous system integration, and rapid, ad hoc report generation. Faced with a problem set as diverse as this, poor Inspector 12 would be passed over pretty quickly.

The label jack-of-all-trades—master of none is normally meant to be derogatory, implying that the labelee lacks the focus to really dive into a subject and master it. But, when your online shopping application is on the fritz, and you’re losing orders by the hundreds as each hour passes, it’s the jackof-all-trades who not only knows how the application’s code works but can also do low-level UNIX debugging of your web server processes, analyze your RDBM’s configuration for potential performance bottlenecks, and check your network’s router configuration for hard-to-find problems.

And, more important, after finding the problem, the jack-of-all-trades can quickly make architecture and design decisions, implement code fixes, and deploy a new fixed system to production. In this scenario, the manufacturing scenario seems quaint at best and critically flawed at worst.

Another way in which the software factory breaks down is in that, although in an assembly line the work keeps coming in a steady flow, software projects are usually very cyclical. Not only is the actual flow of projects cyclical, but the work inside a project is cyclical. A coder sits on the bench while requirements are being specified, architected, and designed, or the coder multitasks across many projects. The problem with multitasking coders is that, despite the software factory’s intentions, when the rubber meets the road, the coders rely a great deal on context and experience to get their jobs done. Requirements, architecture, and design documents can be a great head start, but ultimately if the programmers don’t understand what the system is supposed to do, they won’t be able to create a good implementation of the system.

Of course, I’m not just picking on coders here. The same is true at nearly every spot on the software assembly line. Context matters, and multitasking doesn’t quite work. As a result, we have an inefficient manufacturing system. There have been various attempts to solve this problem of inefficiency without departing from the manufacturing-inspired system, but we have not yet figured out how to optimize our software factories to an acceptable level.

If you are just a coder or a tester or a designer or an architect, you’re going to find yourself sitting idle or doing busywork during the ebbs of your business’s project flow. If you are just a J2EE programmer or a .NET programmer or a UNIX systems programmer, you’re not going to have much to contribute when the focus of a project or a company shifts, even temporarily, out of your focus area. It’s not about where you sit on the perceived value chain of project work (where the architect holds the highest spot of royalty). It’s about how generally useful you make yourself.



The growth of mobile device usage for the Web isn’t at the expense of desktop usage

From ComScore – Is Mobile Bringing About the Death of the PC? Not Exactly, some interesting data:

Here we can see that mobile consumption has clearly exploded, with a 44% gain in mobile app usage and a 38% gain in mobile web usage. This has contributed to mobile growing its share of digital media time spent from 50% to 60% in the past year and owning a clear majority of time spent.

But importantly, desktop usage has held up in the face of increased competition from mobile for consumers’ attention, with a negligible overall decline in total time spent from 477 billion minutes to 466 billion minutes. The net result of is that we’ve seen a 20% overall increase in time spent on digital in the past year, which means a lot more opportunity to reach consumers and monetize content.

As a web developer, this tells me that websites with analytics that tell me that desktop users are predominant aren’t to be forgotten; mobile Web usage is growing, but native app usage is where people’s attention is.



Downloading more than 5,000 rows from Google Analytics at a time

I’ve been increasing the amount of click tracking I do via Google Analytics, but getting the data back out of that system can be a bit of a time when you’re talking large amounts of data.

Enter Download Analytics, which will ask for input of a URL to the report page you’re viewing, and will automatically prompt to e-mail you (when ready) a link that will allow you to download the entire list of rows in your report. Simple, fast, effective.