Maximum Memory Warnings Are Rubbish

Many people use MySQLTuner, as a guide to MySQL Server configuration values and they are alarmed when they see things like this:

[!!] Maximum reached memory usage: 86.6G (138.16% of installed RAM)
[!!] Maximum possible memory usage: 5085.9G (8110.25% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory

You should be aware that the “maximum memory” warning from MySQLTuner is rubbish. It is a misleading and impractical estimate, because it has no real chance of happening.

The memory usage they base their calculation on has to do with buffers that are allocated conditionally, depending on certain query behavior. Not all queries allocate all those buffers. Even if they do allocate the buffers, they may not allocate the full size of the buffer.

The chance that a given query will allocate all the possible buffers at their maximum size is remotely small.

In addition, there might be up to max_connections clients connected, but it’s typical in a running system that not all connections are executing a query at the same time. On every MySQL Server I’ve supported, even if there are hundreds of clients connected, most of them are idle (i.e. not running a query). I would expect the number of threads running a query at any given moment to be 10-20 at most. The others are connected, but if you view SHOW PROCESSLIST, they only show “Sleep” as their current state. These are clearly not using query-specific buffers.

The possibility that all threads on a MySQL Server will allocate all the possible query buffers at their maximum size at the same time is so unlikely that you should treat it as complete fiction.

Because of these mistakes in calculating the theoretical maximum memory usage, I ignore those warnings from automatic tuning tools like MySQLTuner. The estimate of “maximum memory” given by MySQLTuner has been many times the size of RAM on every database server I have administered. You don’t need to be alarmed by this.

So how should one estimate memory usage?

By observation!

Monitor the actual memory usage of your mysqld process by using tools like top or ps. Ideally you would have a continuous monitoring system to make graphs over time, so you could observe the trend.

This is much more accurate than relying on MySQL Tuner or any other estimate. Those estimates are not taking into account your database traffic or activity.

Beginner Web Programming Projects

Can someone suggest a web application project for beginners? What would be a good project? Should I follow a how to make a tutorial or figure it out on my own?

This is a frequent question from new programmers. I wrote a post in 2020 about this. Below is a reproduction of my post. It was in response to a question specifically about learning PHP, but similar advice would be true for other programming languages.


The best way to learn PHP, or really any other language, is by practicing writing code. This is probably echoed by many of the other answers.

  • Pick a simple practice project, and implement it. Then pick another one. You don’t have to create a beautiful website to practice. Create an ugly website that serves as your sandbox for trying out code.
  • Learn from mistakes. Try something different.
  • Go outside your comfort zone. Find a new group of functions in the manual and try out each one. For example: Arrays or Strings or SPL.
  • Learn about PHP: The Right Way.
  • Learn about Laravel, the The PHP Framework For Web Artisans. Really. The days of arguing over the best PHP framework are over.
  • Learn about security (see OWASP).
  • Learn about testing code (PHPUnit, the PHP Testing Framework).
  • Subscribe to PHP blogs like Planet PHP. Attend PHP community users’ groups or PHP Conferences around the world.
  • Read other PHP code. Download WordPress or MediaWiki, both widely used and mature PHP projects, and study the code. What’s good about it? What’s lousy about it? There are bits of code in any project that are gems, and some that are garbage.

Finally, there’s a secret way to learn any skill better than you can learn it on your own: teach it to someone else.

As for a suggestion for a project to practice, I would recommend you develop a blog application similar to WordPress.

  1. It’s very simple at first: just a database table that stores blog posts by date, one PHP script to view them, and one PHP script to author them.
  2. Add authentication, so only the blog owner can write posts. Be sure to read You’re Probably Storing Passwords Incorrectly.
  3. Add tags, so the blog owner can categorize posts and users can view posts in that category. You’ll learn what a many-to-many relationship is.
  4. Add comments so readers can post too.
  5. Add captcha support or logins for users. Integrate with OpenID, Facebook, Google authentication APIs. Learn OAuth2.
  6. Add a text search function. Learn about a fulltext search engine like Sphinx Search.
  7. Add some kind of email notification or RSS feed, so users can be notified when a new post appears.
  8. Refactor your comments system to use Disqus, as a way to embed a web service into your application.
  9. Refactor the whole app to an MVC framework, to learn what it takes to do a major overhaul of your code without breaking things.
  10. Write unit tests for your classes that you wrote for the MVC implementation.

I would recommend that you do not follow a tutorial, and instead try to design the application on your own. It will take longer, but I guarantee you’ll learn more about coding. Anyone can follow a tutorial and type in the steps, without knowing what’s happening. If you do not use a tutorial, you’ll have to think harder about how to solve the problems that come up. This is a necessary skill if you want to be a programmer.

But of course you need to read documentation or how-to articles on some of the individual pieces I included in the list above.

Why is Software So Complex?

Q: Why is most modern software so mindbogglingly complex, with multiple layers of abstraction stacked on each other? Why do they not make simple, efficient software like they used to earlier?

This question was asked on Quora recently. I answered that it is due to a few major reasons:

1. Code Maintenance

There’s an old humor article that has circulated online for many years, titled, “If Architects Had to Work Like Programmers.” It’s written as if it’s a letter from a homebuyer who wants an architect to build a house. Here’s an excerpt:

“Please design and build me a house. I am not sure of what I need, you should use your discretion. My house should have between two and forty-five bedrooms. Just make sure the plans are such that the bedrooms can be easily added or deleted. When you bring the blueprints to me, I will make the final decision of what I want. Also bring me the cost breakdown for each configuration so I can arbitrarily pick one.”

This is humorous because it sounds like the way programmers are given their software requirements. Because software can be modified after it is created, the employer assumes it’s easy to do that, and that they don’t need to be specific about what they want.

People in the software development field over many years have tried their best to accommodate this, by creating more and more abstractions so that the pieces of software can be altered, combined, upgraded, or swapped out more easily.

The employer wants this because it enables them to get software on an affordable schedule, without forcing the employer to write detailed specifications that they probably don’t know in advance anyway.

The programmer wants this because they want to remain employed.

2. Code Reusability

A good way to increase code quality without increasing the schedule is to write less bespoke code, but instead use more code that has been written and tested well prior to your project. We call these libraries, frameworks, templates, or code generators.

You’ve probably used Lego toys, where you can build elaborate models using simple reusable bricks or other specialty pieces. You can build practically anything by using enough bricks. There are also some specialized shapes, and lots of guides showing you how to combine them to build the desired models.

Image: Queen Mary model in Lego.

It’s a similar concept to code reusability. Software development then becomes an activity of learning all the different pieces and ways to use them together.

The code is reusable because of abstractions. Like the Lego pieces that use standard dimensions and connecting buttons so they can be fastened to other pieces.

3. Features, Features, Features

I once developed an app for a manager who was very decision-challenged. Every time I would ask him, “do you want the app to behave this way or that way?” I was often asking about two alternatives that were mutually exclusive. For example, do you want the report to arrange categories of data in rows or in columns?

He would always answer, “both.” He didn’t know how to choose, and he was afraid of making the wrong choice. So he asked me to implement both alternatives, and make the software configurable. He wanted to keep his options to change his mind later as often as he wanted to.

This at least doubled the work to implement the code, and doubled the testing needed to assure it works.

But it was worse — every time he said, “both” this doubled the number of test cases, because I had to assure that a new feature worked with every combination of alternatives for the past features.

Programmers can’t say “no” when their employers want some features. They can say, “okay, but here’s what it’ll cost in time and money, do you still want it?”

How do Software Engineers cope with stress?

The typical sources of stress for software engineers are not caused by technology. They’re caused by managers and projects.

Work tasks are not described clearly.

If I’m given unclear work requirements, I ask for more details. I make it clear that I can’t give an estimate for the cost or the time of completion until I know the full scope of work. In a professional software engineering environment, a significant amount of time should be spent estimating the work based on complexity of the task. You can get yourself into a stressful obligation if you agree to a deadline before knowing the requirements of the project.

Be careful about this. Managers often insist that the software engineer decide on the estimate much too early, and then they use that against the engineer later, saying it was an estimate that the engineer had made, so they can’t claim it was imposed on them.

When you are asked for an estimate before you know the scope of work, remember to use this standard response: “I’ll have to get back to you.”

Schedule is too aggressive. Deadlines are impossible.

At one job I joined, I was hired to get a software project back on track after it had fallen behind schedule and the previous team lead had quit. I knew generally what the project was, but I didn’t know how much was done and how much still needed to be done. On my first day, a marketing person told me he wanted to present this software to customers at an annual conference, which was happening in two weeks. He wanted me to promise to finish the software by that time. I told him I would need two weeks just to learn the current state of the project and make an estimate for the completion. It was a little bit stressful to tell him no, but it would have been much worse to make a promise and then fail to deliver.

Finishing on schedule requires long work hours and few breaks.

Don’t fall into the trap of letting management bully you into working until you are exhausted. If you do that, you will make more mistakes, and your code will need to be scrapped and written over. You are not a machine — and even machines need time for maintenance, cleaning, repair, etc. If you keep yourself healthy and your mind fresh, you will be able to concentrate better and produce better quality work. You will have a better chance of finishing the work on time.

New requirements are added late in the project, but the workers are held to the original schedule.

When they want to add more features, tell them you’ll evaluate the new requirements to see if they can be added with minimal interruption to the schedule. Do your best and make an honest effort to do that. But sometimes the new feature is requires major changes to the current code design, which is partially implemented already.

Approach the product manager and let them know this. Present to them the following options:

  • Postpone their new feature idea until “phase 2” (that is, a future revision of the software).
  • Cut some other requirements from the current project that are time-consuming and not yet implemented.
  • Make a compromise to reduce the complexity from one or more features, to make them take less time to implement.
  • Extend the project deadline to give enough time for the extra features.

If they still demand that they want all the features and no change to the schedule, that’s not realistic. Politely tell them that they need to choose one of the options or they will be disappointed. This makes the tough choice their responsibility, which reduces your stress.

Unscheduled work and alerts interrupt and spoil concentration.

This is a great source of stress because it’s difficult to resume work that requires concentration after an interruption. This has been studied a lot. It’s not just the time it takes to do the unscheduled task. It also takes time to shift your focus between tasks. If this happens several times per day, you can lose all your productivity for the whole day.

If software engineers are expected to be oncall or to help with unscheduled analysis or troubleshooting or technical support, then they should make it clear that any schedule estimates are in unpredictable. Your stress comes from uncertainty that you can be productive enough to meet your deadline. You can mitigate this stress by insisting that the deadline must be extended every time you are interrupted.

Software engineers are expected to understand and be productive with any type of technology with no time for training.

The best way to cope with this is to fib a little bit and add some time to every estimate, to allow for research, self-training, debugging, and getting answers from technical support. It’s unfortunately a reality that management can’t justify budget for training time, if they are already paying high salaries to software engineers. So you have to include the necessary training time with engineering estimates.

One way to hide training as engineering is to schedule part of the project to implement a “prototype” or a “proof of concept.” These basically mean you’re going to be practicing, and the result will be an unoptimized implementation, intended to be scrapped and redone before the final deadline.