Keeping It Simple

Tag: mysql

  • 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.