This series details what happens behind the scenes in response to a user’s click on a link. We’ve already seen the request go down the client machine’s protocol stack, across the Internet, up the server machine’s protocol stack, and into the web server software. If a server-side script was requested, the web server software passed the request off to the script processor. The Service Provider
This need not be the first step. In fact, the entire process can be eliminated (if our application’s design permits it) by caching or memoization within the script. Why even send a request for informaton if the same request was sent awhile ago? Why not just use those results? The only interesting point of discussion is how long awhile can be.
Installing the service provider on the same machine as the script processor may yield the best performance because we avoid all networking. However, this approach can also interfere with the operation of the web server software, so it may not be the best choice. Only after-the-fact measurements can confirm which approach is better, but monitoring the load on the web server can give us a clue. If the web server is already near its limit, it may be best to install the service provider elsewhere.
- on the same machine as the script processor
- on a machine on the same local loop as the script processor
- on a machine on the same network as the script processor
- on a machine within our intranet
- on a machine somewhere out there on the Internet
The service provider then starts up and configures itself (if it hasn’t already).
The service provider shouldn’t be starting up and configuring itself at this point. Why? Because the user is still drumming his fingers on the keyboard, waiting for a response. Startup and configuration should have happened before the request arrived. If the service provider doesn’t do this, consider other alternatives. If the service provider does have this capability, make sure it’s set up to configure itself before requests start arriving.
The proximity of the service provider to the machine running the script is once again important.
Compressing the data as it travels back to the script may or may not yield better performance. Measure, measure, measure. Compression is more for data travelling over a network than for data that is passed from process to process on a local machine. We also note that some data is more compressible than other data.
A Service Provider Example – a DBMS
DBMS’s may not offer the best performance for small, unrelated data sets. Flat files, CSV’s, .ini files, and other formats may be better choices.
Locally-stored data can be accessed faster than data stored on another machine, but this approach is not scalable. As data grows larger and larger, distributing it across multiple machines will allow queries to execute faster.
Data stored in memory can be accessed faster than data stored on a disk, but it vanishes into the ether when the machine crashes or is turned off.
Solid-state drives are faster than hard-disk drives.
Partitioning and clustering may help. The only way to find out for sure is (you guessed it) to try it out.
Know your DBMS’ techniques. Employ the ones that make a difference. See the MySql section below for more performance considerations along this vein.
RDBMSs almost always employ indexing. Indexing is one of the most impactful performance tools out there, but developers often use it poorly. Too much can be just as bad as not enough. Column choices may be inappropriate. Existing indexes may not be used. Tables may be scanned from beginning to end because an index wasn’t created. Etc.
Keep your ear to the ground. This may be a future direction.
A DBMS Example – MySql
Server Installation & Configuration
Provide a generous supply of resources, especially RAM, hard disk space, CPUs (both speed and number), and fast network connections. Configure the operating system and MySql to fully use those resources, but without overusing them. Multiple machines may be a wiser investment than building a powerhouse machine, and it improves scalability options. Use a 64-bit architecture instead of 32.
Anything else installed on this machine will compete for available resources, even if it’s not running. Get rid of it. The windowing system, for example, is not necessary and it competes heavily for CPU cycles and disk space.
Swapping is never allowed on a server. Reconfigure or distribute the database across multiple machines to eliminate swapping.
Revisit this step during and after database installation. Until a database is installed and operational, all settings are merely educated guesses.
Database Design, Installation & Configuration
Normalize first. Denormalize only when absolutely necessary. Redundancy cannot be fully eliminated, but it should be at its minimum. Note that table joins can be faster than using a denormalized design.
Avoid triggers. If you can’t, make sure they are short and fast.
The database may be small now, but it’s hard to predict how big it may eventually become. Plan for partitioning and distributing, even if it’s not needed yet.
For larger databases, use partitions, clustering, multiple disks, RAID, distributed databases, load balancing, or a combination thereof.
Use autoincremented INTs for the primary keys whenever possible.
Compress that which can be compressed. TEXTs and BLOBs are especially good candidates.
Measure; never assume.
Execute ongoing maintenance and admin tasks in background processes that run at a low priority when the database is at its quietest.
Use PROCEDURE ANALYSE to get data type recommendations. It’s not useful when you’re first creating the database, but it’s quite handy when the database is fully populated.
Use consistent data types to avoid type conversions when comparing or joining.
Storage Engines
MySql provides several storage engines, each with its own performance characteristics. All but one are implemented on disk. The other one is implemented in memory, so it’s faster, but all the data disappears when the machine crashes.
The ARCHIVE storage engine is great for logs. The data is compressed, and it can’t be changed after it’s INSERTed. Note that indexes aren’t allowed, which makes the writes faster, but SELECTs can be very slow if the table becomes too large. [Solution: Rotate the tables frequently or when they reach a certain size.]
Use InnoDB rather than MyIsam. It’s faster, especially for large databases with lots of foreign keys. Also note that MyIsam cannot handle transactions.
Masters and slaves don’t have to use the same storage engine. Use whatever performs best.
Configuration Checklist
Whether configuring the MySql server or a database within it, note that the default values for the following may not be appropriate for your system. I could write an article for each one, so at this time, let’s just say that we should not blindly accept these defaults, but should give each one some consideration. Look these up at http://dev.mysql.com/doc/refman/5.6/en/dynindex-sysvar.html
- innodb_additional_mem_pool_size
- innodb_buffer_pool_size
- innodb_file_io_threads
- innodb_flush_commit
- innodb_flush_log_at_trx_commit
- innodb_lock_wait_timeout
- innodb_log_buffer_size
- innodb_log_file_size
- join_buffer_size
- key_buffer
- key_buffer_size
- max_allowed_packet
- max_connections
- max_heap_table_size
- myisam_sort_buffer_size
- net_buffer_length
- open_files_limit
- performance_schema
- performance_schema_events_waits_history_long_size
- performance_schema_events_waits_history_size
- performance_schema_instrument
- query_cache_size
- safe-show-database
- sort_buffer_size
- table_cache
- table_definition_cache
- table_open_cache
- thread_cache
- thread_cache_size
- tmp_table_size
Writing & Testing Queries
Now that the database is installed and configured, we can start using it. We write queries, test them, and embed them in our server-side scripts.
Note the part about testing the queries. Start by doubling your testing budget, then double it again. Now you’re getting close.
Become intimate with EXPLAIN. Know what it does and how it does it. Most importantly, understand its output. This information will identify problems before you run the query the first time. Use it every time you modify a query.
Some people say to wait until later on in the process to optimize queries. I don’t think I can agree with that advice because “later on” never seems to arrive. Besides, continual nitpicking can develop habits that every developer should have. [Feel free to disagree with me on this point. It’s opinion, not fact.]
It has been said and oft repeated that performance can be more dramatically affected by query and index design than by anything else. I don’t know that that is always true, but I’m sure it is more often true than false.
Transport as little data as possible between the database management system and the server-side script. In other words, fetch only what you need.
The biggest offender in this regard is SELECT *, which transports every column of the table. Even if the script truly requires every column, we don’t know what additional columns will be added in the future. When new columns are added, the script will become slower because it is now transporting more data. The asterisk is evil – always list columns by name.
Paging data is now common. Instead of transporting all the data, scripts commonly transport one page at a time. We hope the user will find what he needs with relatively few page accesses, which means less data is transported in total. Note that we’re discussing performance in this article, but usability and the user experience may also need to be assessed before using this technique.
As pointed out in tip #7 in Baron Schwartz’ May 2012 article, data paging can be implemented poorly. He suggests an alternate implementation in a mere three paragraphs, so please take a look at it. [As always, measure the alternatives rather than assuming one is better than the others.]
Two performance tips are direct opposites, but both are valid:
- Replace multiple small queries with one large query.
- Split large queries into multiple small queries.
Both are valid, but not always. The trick is to find out which one, or neither, to use in a given situation. So we’re back to measuring the alternatives again.
Batching INSERTs, REPLACEs, UPDATEs, and DELETEs for offline processing may make sense in some situations. However, batching them on disk may be slower and batching them in memory makes them vulnerable to a crash. To batch or not to batch, and where to batch, must be decided on a case-by-case basis.
Correlated subqueries can be performance nightmares. A JOIN can do the same thing, perhaps faster.
Indexing
Indexes make data retrieval faster. Without indexes, the database management system would have to scan every table from beginning to end for every query, making performance unbearable for medium and large databases.
Building appropriate indexes is both an art and a science. An index can make data retrieval faster, but it also makes the updating process slower. Indexes need to be updated whenever the underlying data is updated. Building an index on every column is just as foolish as building no indexes.
Start by building indexes on primary keys, foreign keys, and columns used in conditions. Then analyze the results and tweak as needed. The slow query log and EXPLAIN will come in handy here.
After some time in production, remove indexes that are never used.
Keep primary keys small, especially if InnoDB is being used. InnoDB includes the primary key in every index, like it or not.
Because data patterns may change over time, index performance should be continually monitored to see if a different set of indexes might now perform better.
Processing Requests
MySql can check for IP spoofing. It resolves the IP address to a host name, then resolves the host name back to an IP address. If this IP address is the same as what it started with, everything’s hunky-dory.
If parts of the website are secured by IP address, this check is important.
If it’s not needed, turn name resolution off with –skip-name-resolve. This will save CPU cycles and network time. If it is necessary, make sure it’s optimized.
Modern DBMS’s can hold much, much more data than most applications need.
However, this slows down all queries. If old data is not likely to be needed, archive it and remove it from the active table.
The database management system goes beyond merely getting and updating data. It also protects the data from corruption that results from concurrent updates. Queries lock the data, do what they came to do, then unlock the data. While one query holds a lock, no other query can access the same data.
Locks can create race conditions – two queries can both hold a lock to the same data, with both queries waiting forever for the other one to release the lock. When this happens, MySql steps in and sends an error to one of the queries, so all queries must be ready to roll back a transaction at any point. [This is one reason why a DBMS needs to support transaction handling.]
The smaller the locked block of data, the fewer delays for other queries. Row-level locking provides better performance than table-level locking. Fine locking granularity is one reason InnoDB is faster than MyIsam.
Shared (read) locks interfere with performance less than exclusive (write) locks because a read lock allows other queries to read at the same time and a write lock blocks all other queries.
The Query Cache
Just as its name implies, MySql’s Query Cache caches the result set of MySql queries. It can be turned off for all queries, turned on for all queries, or used on demand.
Use query_cache_type = 2 for on-demand query caching. Turning it on for all queries (option 1) can result in poorer overall performance than turning it off for all queries (option 0). Use SQL_CACHE in the SELECT statements that should be cached.
The query cache can be configured:
- query_cache_type– turns the query cache off, on for all except SQL_NO_CACHE, or on only if SQL_CACHE is specified
- query_cache_size– amount of memory allocated for the query cache
- –maximum-query_cache_size – stops sessions from increasing query_cache_sizebeyond this value
- query_cache_limit– maximum allowable size of a cached result set
- query_cache_min_res_unit– minimum block size for cache storage
- query_cache_wlock_invalidate – invalidates cached queries immediately when a process requests a write lock
Turning caching off is a two-step process: set query_cache_type to 0 and set query_cache_size to 0. Setting one without the other doesn’t make any sense and it can reduce performance.
Setting query_cache_type to 1 (ON) may make performance worse than setting it to 0 (OFF). Recommended: 2 (DEMAND).
These two conflicting tips:
- Set the query_cache_limitlow to keep the query cache from filling up quickly with large result sets.
- Set the query_cache_limit high because the large result sets may be the ones that benefit most from caching.
are both valid in the general case, but we’re more interested in the specific case – our application. This points to the need for tuning this setting, so be ready to do some experimentation and measurements. The best number is the one that provides the fastest query execution times in production.
query_cache_min_res_unit‘s default is 4KB. If the cache contains mostly small result sets, reducing this value may increase performance. If the cache contains mostly large result sets, increasing this value may be the way to go. In either case, benchmark and compare the Qcache_free_blocks and Qcache_lowmem_prunes status variables.
Keep Qcache_lowmem_prunes as low as possible by increasing query_cache_size. However, larger caches may actually decrease performance, so measure, measure, measure.
If two queries are logically the same, make them byte-by-byte identical so they can be cached once instead of twice. The query cache will fill up less often.
SHOW VARIABLES LIKE ‘%query_cache%’; and SHOW STATUS LIKE ‘%qcache%’; provide information that is useful when tuning query caches.
query_cache_wlock_invalidate invalidates cached result sets immediately when a write lock is granted on the underlying data. If it is set to true, queries on MyISAM tables cannot be served from the query cache when there is a write lock on the table. Setting it to false allows queries to be served from the query cache while the write lock is active. Recommendation: Set query_cache_wlock_invalidate to false if the application permits.
Query caching within a DBMS is not as fast as server-side-script caching. Server-side-script caching is not as fast as web-server caching. Web-server caching is not as fast as HTTP caching. In general, the earlier the caching kicks in, the better.
Queries that create summary tables, pivot tables, or cross tabulations should not be executed live in response to a request. Create the tables with a background process that runs as frequently as necessary, then execute the live queries on the tables so created. The live query will now be simple and will perform much better.
Query Caching With memcached
Should we use query caching or memcached? Today’s answer is easy: Use query caching in production because memcached is still in its infancy on MySql. However, as this technology matures, the answer may change. Keep your ear to the ground on this one.
Logging slow queries in production shows us which queries take the longest to execute, but it can cause its own performance problem while doing so. It must be done with care. Although it may be helpful in the test environment, slow query logging should not be always-on in production.
Ongoing Maintenance
Identify tasks that need to be repeated on a regular basis and build cron jobs for them. Run the cron jobs at low priority at times when the database management system is relatively quiet. Watch out for starvation. If the database is too busy, the cron job may not finish before the next one begins. Keep cron jobs small and fast.
Performance Consideration:
With up-to-the-second live data, the advice to use static HTML instead of dynamic HTML is not appropriate. Live data must be recalculated and regenerated for every live query. There’s no way around that. The trouble with live queries is that the user is waiting while the query is executing.
However, much of the data we consider up-to-the-second need not be up-to-the-second. If the application permits, some data can be up-to-the-hour or even up-to-the-day. Even though it’s not static, it’s not quite as dynamic as it first appears.
Implement up-to-the-day data queries as static HTML. Build a background task that re-creates the static HTML page daily. Run the background task at a low priority level at the quietest time of the day. For live queries, access the static HTML page that was created by the background task. If up-to-the-day isn’t dynamic enough, run the background task more often.
One possible glitch: Use a monitor to make sure the background task fully executes at the proper intervals.
Much data we consider dynamic can actually be a day old, an hour old, etc. If so, it need not be recalculated and regenerated for every live query. It all depends on the application’s needs.
Here’s a checklist of some maintenance tasks your system may need to take care of on a regular basis. Some are executed less often than others. Again, the application’s needs dictate the frequency.
- Look for Corrupted Tables, Indexes, and Views: mysqlcheck –check –databases dbname or CHECK TABLE or ALTER TABLE … CHECK PARTITION
- Ensure Offline Maintenance Operations Don’t Change the Data: CHECKSUM TABLE(before and after maintenance)
- Rebuild Key Distributions: mysqlcheck –analyze –databases dbname or ANALYZE TABLE or ALTER TABLE … ANALYZE PARTITION
- Repair Data and Index Corruption: mysqlcheck –repair –databases dbname or REPAIR TABLE or ALTER TABLE … REPAIR PARTITION
- Defragment and Pack Tables: mysqlcheck –optimize –databases dbname or OPTIMIZE TABLE or ALTER TABLE … OPTIMIZE PARTITION
- Backup Tables: mysqldump–create-options –routines –triggers dbname
- Flush Data, Caches, Locks, Logs, Tables, the Query Cache, and More: FLUSH
- Rotate Logs
Benchmarking & Monitoring
Like all of the subsystems that help process the user’s request, MySql’s performance should be monitored. Uptime is the first and most important thing to monitor, but keeping an eye on specific, oft-used transactions is also advisable.
MySql offers two tools to help identify troublemakers: the slow query log and the performance schema. EXPLAIN, SHOW STATUS, and SHOW VARIABLES also provide important information to help us analyze performance issues.
The MySql Performance Schema provides both coarsely and finely grained instrumentation at a very low level without impacting performance. Because it is so new and has so many options, few people are using it effectively. That is expected to change because nothing else provides this much information.
Take the time to learn about the MySql Performance Schema. Try it out on one of your projects.
Coming Soon
The service provider (DBMS) is now finished serving the request. Now it’s time to send the result back to the client. That journey is described in Part VIII, coming soon to the Monitor.Us blog.
For quick reference, here is the series’ table of contents:
- Part I – an overview of the entire process from beginning to end
- Part II – down the protocol stack (client side)
- Part III – the journey from client to server
- Part IV – up the protocol stack (server side)
- Part V – the web server (software)
- Part VI – the server side script
- Part VII (this one) – the database management system
- Part VIII – down the protocol stack (server side)
- Part IX – the journey from server to client
- Part X – up the protocol stack (client side)
- Part XI – the client-side script
- Part XII – the Document Object Model
- Part XIII – after the document is complete
- Part XIV – concurrency
- Part XV – wrap-up; best practices