It takes time to open a connection to a database, make a query, have the database find the requested data, and send it back to the file that made the request. That’s true even for very simple queries. The time depends on how the database is configured, the hardware on the server, how the data is stored in the tables being searched, and of course, how well the queries have been written.
For much of the summer, I’ve been talking about performance and dynamic websites, specifically those driven by PHP. I talked about the Zend Engine that interprets PHP code and I shared tips for optimizing the code you write. Then I offered reasons why you should upgrade to PHP 7 if you haven’t already done so.
A few times while talking about PHP, I mentioned databases and said I would talk about them soon. Soon is now I’d like to begin talking about SQL databases, specifically MySQL.
Today I want to talk about a little in general about SQL databases and then talk about MySQL’s two schemas, MyISAM and InnoDB. Over the next few weeks I’ll talk more about things like MySQL configuration, making use of indexes, and how to find slow queries and write better ones.
I covered server hardware in an earlier series and don’t want to spend time covering it again, but I do want to point out or remind you that database-driven sites generally want more CPU, more RAM, and faster storage I/O. Dynamic sites need to read and write to one or more databases (I/O), store data in temporary memory (RAM), and sometimes make calculations or manipulate data (CPU) before returning it to the requesting code.
SQL Databases
Odds are whatever database you currently use, assuming you run a dynamic site, you interact with it through SQL (structured query language). It’s possible you use some other type of database, but my guess is it’s some flavor of SQL. Maybe MySQL or MSSQL or PostgreSQL, all of which are relational databases that talk to your code via SQL statements inside PHP or another programming language.
The main difference is really in the programming language and how it specifically connects to the database and the functions in the language that will include your query. The SQL should be the same.
While I want to focus on MySQL, I want to call attention to the MySQLi extension and MariaDB. The first is a database driver mainly used by PHP and the latter is a fork of MySQL by the original creators of MySQL.
The MySQLi extension is an improved version of the driver PHP uses to interact with the database. I mention it because I’ve seen a number of hosts list MySQLi as the database that comes with their plan. If you see it, know your database will be MySQL and the “i” is an improved version of the MySQL extension for PHP with a different set of functions to connect to the database.
MariaDB is a fork of MySQL. Once Oracle took control of MySQL proper, some were concerned it might not remain as open as it had always been. The lead developer of MariaDB was one of the founders of MySQL. MariaDB offers a drop-in MySQL replacement and you shouldn’t even notice any difference between it and MySQL. It’s the database currently offered with my hosting plan.
Let me also briefly mention phpMyAdmin. Odds are any interaction you have with your MySQL database will be through phpMyAdmin, which is an interface to handle MySQL administration. You can add and remove tables, view the data currently stored, run SQL commands directly, repair and optimize tables, and most of what you’ll want to do with your database.
MySQL Schemas
A database schema refers to the set of rules that determine how the database is structured and organized. MySQL databases typically use one of two schemas, MyISAM or InnoDB.
MySQL used to use the MyISAM schema by default, but InnoDB has been the default since version 5.5.5.
The main advantage of MyISAM is the way it stores data. It can fit more data into the same amount of space so it requires less storage space overall. However, InnoDB is the better option for most things and is likely the option you want if you have the choice. Three specific strengths of InnoDB are that it has:
- Better crash recovery.
- Row-level locking as opposed to table level locking.
- Support for transactions, foreign keys, and relationship constraints.
You can’t optimize and repair InnoDB tables through phpMyAdmin the way you can MyISAM tables, but you can through other means. If your site runs on WordPress, here’s an article showing how you can optimize and repair tables by making a change in your wp-config.php file. But know that optimizing InnoDB tables doesn’t have the same effect it does for MyISAM tables and probably isn’t necessary.
Again, InnoDB is the likely default, but if not and you have access to phpMyAdmin, you can select it when you create a new database. If it isn’t the default and you want it to be, you can set the default_storage_engine configuration variable so it is by changing the value in your my.cnf file.
[code type=”mysql”]
default_storage_engine = InnoDB
[/code]
Don’t worry if you don’t know what a my.cnf file is or where you might find it. I’ll talk more about configuration variables next week and you shouldn’t have to change the default as long as you’re using MySQL 5.5.5 or above.
Despite it being next week’s topic, I would like to call out one more variable before closing things this week.
MySQL reverse DNS
The MySQL server maintains a host cache in memory that contains information about clients that access the server. When a connection is made, MySQL does a DNS lookup and compares the hostname and IP address to what’s in the cache.
These lookups take time, possibly up to a few seconds each and you may want to disable the lookup using another configuration variable, skip-name-resolve. You set the variable in your my.cnf file under the mysqld configuration block and then restart MySQL.
[code type=”mysql”]
skip-name-resolve
[/code]
Adding skip-name-resolve will skip the hostname part of the lookup and MySQL will only use IP addresses to match connecting hosts. If your DNS is slow and you allow multiple hosts to connect to your database skipping name resolve could speed up your database.
However, it also means connections to the database need to be made via IP address and not hostname, which is something you might need to change elsewhere in your code. For that reason, you may not want to set skip-name-resolve unless you know the DNS lookup is taking a long time and causing performance issues.
If making that configuration change went by a little fast, hang in there as I’ll be talking about MySQL configuration next week.
Closing Thoughts
The content for a dynamic site is often stored in some kind of database. Odds are if your site uses a database, it’s some flavor of SQL, likely MySQL, though it’s possible you use another SQL database or even something non-SQL.
MySQL offers two different database schemas, MyISAM and InnoDb. The former will usually take up less space on disk, but the latter offers some performance enhancements and should be the default selection when you create a new database, and the option you likely want.
Next week I’ll show you how to set the database schema if the default isn’t your preferred option and I’ll talk about a handful of other configuration variables you may want to change from their defaults.c
Download a free sample from my book, Design Fundamentals.
Some interesting points made here I shall show this post to my Dev guy to see what we can test.