All posts by Slayer6

MySQL Configuration (the my. file)

my.cnf (linux)
or my.ini (windows)
/etc/mysql or /etc linux
c:/windows, just c: or mysql installation directory windows

innodb buffer_pool_instances
innodb_buffer_pool_dump_at_shutdown innodb_buffer_pool_load_at_startup

These are the settings that matter most when configuring your InnoDB/XtraDB engine.  There are a lot more available but most not worth mentioning as defaults are usually just fine.  This was all sourced directly from Muhammad Irfan’s blog post and as there would be little value in rewriting the stuff in my own words, I have just copied them here.

innodb_buffer_pool_size: InnoDB relies heavily on the buffer pool and should be set correctly, so be sure to allocate enough memory to it. Typically a good value is 70%-80% of available memory. More precisely, if you have RAM bigger than your dataset setting it bit larger should be appropriate with that keep in account of your database growth and re-adjust innodb buffer pool size accordingly. Further, there is improvement in code for InnoDB buffer scalability if you are using Percona Server 5.1 or Percona Server 5.5 You can read more about it here.

innodb_buffer_pool_instances: Multiple innodb buffer pools introduced in InnoDB 1.1 and MySQL 5.5. In MySQL 5.5 the default value for it was 1 which is changed to 8 as new default value in MySQL 5.6. Minimum innodb_buffer_pool_instances should be lie between 1 (minimum) & 64 (maximum). Enabling innodb_buffer_pool_instances is useful in highly concurrent workload as it may reduce contention of the global mutexes.

Dump/Restore Buffer Pool: This feature speed up restarts by saving and restoring the contents of the buffer pool. This feature is first introduced in Percona Server 5.5 you can read about it here. Also Vadim benchmark this feature You can read more about it in this post. Oracle MySQL also introduced it in version 5.6, To automatically dump the database at startup and shutdown set innodb_buffer_pool_dump_at_shutdown & innodb_buffer_pool_load_at_startup parameters to ON.

innodb_log_file_size: Large enough InnoDB transaction logs are crucial for good, stable write performance. But also larger log files means that recovery process will slower in case of crash. However this is not such big issue since great improvements in 5.5. Default value has been changed in MySQL 5.6 to 50 MB from 5 MB (old default), but it’s still too small size for many workloads. Also, in MySQL 5.6, if innodb_log_file_size is changed between restarts then MySQL will automatically resize the logs to match the new desired size during the startup process. Combined log file size is increased to almost 512 GB in MySQL 5.6 from 4 GB. To get the optimal logfile size please check this blog post.

innodb_log_buffer_size: Innodb writes changed data record into lt’s log buffer, which kept in memory and it saves disk I/O for large transactions as it not need to write the log of changes to disk before transaction commit. 4 MB – 8 MB is good start unless you write a lot of huge blobs.

innodb_flush_log_at_trx_commit: When innodb_flush_log_at_trx_commit is set to 1 the log buffer is flushed on every transaction commit to the log file on disk and provides maximum data integrity but it also has performance impact. Setting it to 2 means log buffer is flushed to OS file cache on every transaction commit. The implication of 2 is optimal and improve performance if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes.

innodb_thread_concurrency: With improvements to the InnoDB engine, it is recommended to allow the engine to control the concurrency by keeping it to default value (which is zero). If you see concurrency issues, you can tune this variable. A recommended value is 2 times the number of CPUs plus the number of disks. It’s dynamic variable means it can set without restarting MySQL server.

innodb_flush_method: DIRECT_IO relieves I/O pressure. Direct I/O is not cached, If it set to O_DIRECT avoids double buffering with buffer pool and filesystem cache. Given that you have hardware RAID controller and battery-backed write cache.

innodb_file_per_table: innodb_file_per_table is ON by default from MySQL 5.6. This is usually recommended as it avoids having a huge shared tablespace and as it allows you to reclaim space when you drop or truncate a table. Separate tablespace also benefits for Xtrabackup partial backup scheme.

Oh by the way…

Don’t cache in linux


vm.swappiness = 1-5

MySQL Optimization Tools my.cnf tuning


Percona Configuration Wizard

Perhaps the most commonly used my.cnf tuning tool available is MySQLTuner.  This program consists of a simple perl script.  If your server is not currently set up to run perl then you will need to do this.  It can be downloaded or in the case of Ubuntu server, installed directly using apt-get.  Make sure your server has been up and used as it typically is for at least 24 hours.  This use data is important to the script.  Upon running this simple program, a report will be generated suggesting my.cnf configuration changes.  Use this report with caution.  While it will effectively identify some problems and will take care of the capacity estimations you might normally calculate by hand, it’s advice could potentially degrade performance in some cases.  Make sure that you understand the changes you are making regardless of whether or not they have been suggested by an optimization tool.

Another interesting tool is the Percona configuration wizard.  This online tool generates a fairly optimized my.cnf file for your database based on your answers to a sequence of questions.  It could not get any easier than that 🙂


MySQL Database Design for Scalability and Performance

Resource Usage and Size Limits

Operating System File-size Limit
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 file system) 4TB
Solaris 9/10 16TB (who does file systems better than the guys at Sun?)
MacOS X w/ HFS+ 2TB
NetWare w/NSS file system 8TB

In this section I will be discussing database design for large data sets.  First, it is good to be aware of what the actual limits imposed upon your database by the hardware and operating system it is running on are.  MySQL in most cases on modern machines is capable of managing very large data sets, often times, much larger than most applications ever call for.  In general, the hard limits to be aware of are imposed by the file system.  If you are using a 32 bit linux os or a fat file system, table sizes will be limited by this to between 2 and 4 gig.  A newer 64 bit linux os with an ext3-4 file system expands this to 4 terrabytes.  On solaris, table file sizes can reach as much as 16 terrabytes without being limited by the file system.  This is quite a lot of data for a single table.  Be sure to start new projects on a newer 64 bit OS and use a modern file system with higher file size limits if at all possible to minimize the impact of this type of limit.  Even if you do not see a current need, database applications are usually better than most at taking advantage of such newer technologies as 64 bit processors and operating systems as well as multi-core processors and large ram allocations.  In cases in which this hard limit is reached, partitioning data sets in different tables by such logical attributes as date is possible.  Rarely will this be the case in a single node system though.  Having enough disk space is another important limitation to be aware of.  The amount of disk space needed for your particular implementation will depend upon the amount of data you have, indexing schemes, logging needs, backup methods and data population requirements.  In today’s environments, disk space is now inexpensive enough that it is rarely lacking due to its cost but more often due to the administrator’s failure to specify enough.

First, the database engine and tools themselves require space on your node.  This will vary based upon your choice of tools but is minimal relative to the space used for data, index’s, backups etc.  To understand these requirements, check disk usage before installing MySQL and tools(df -h works in linux) and then directly after installation.

The space requirements for you data and indexes a related to the sum of data length and index length.  To look at a particular table try:

SELECT data_length+index_length FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable';

To look at usage across your entire database you can use something like this which I ripped off the web somewhere:

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;

Or you could just check the disk usage in your data partition with df -h again or file manager in windows.  You can also just count bytes per row based on known/specified lengths and expected volume.

Indexes can take a bunch of space too.  Some ways to alleviate this include:

Avoid creating secondary indexes on the really big tables if you can.  The way they are stored, the primary index is duplicated along with them.  This would usually mean one entry per row even if there were only a few distinct values in the second field.

Fragmentation in indexes is bad…worse when using secondary indexes.  Dump and reload tables periodically when index fragmentation is suspected.  I do believe more recent versions of MySQL have done a bit to address this though.

In addition, you must have space available for backing up your entire database and for raw files used in load procedures.  Your particular implementation  will dictate the size of these files.  Logical backups take a lot of room initially as will be discussed later.  These are full backups of your data in text format either using insert statements or delimited rows.  Raw backups or snapshots just keep track of changes so they are small at first but do grow rapidly on a busy system.  Informational and operational logs can also take up a lot of space and vary depending upon your implementation.  This space and managing it will be discussed later in the course and will depend greatly upon your specific requirements.  As noted earlier, informational logs should usually be turned off when not being used to diagnose a problem but there should still be space available to use them when needed.  Operational log size dictates the level of information safety you have relative to your ability to roll back to an earlier state and avoid data loss in the event of a failure.  In most cases it is better to be safe than sorry here.  Leave plenty of space for your binlogs.  This will also be discussed later.  It is a good idea to specify innodb_file_per_table in your config file as separating your tables into individual files allows for future flexibility, especially during disaster recovery and disk space recovery when a table’s size decreases(scaling down). MySQL’s create database command is fairly simple and doesn’t offer a lot of options.  Most configuration is done at the table level as table’s using different engines such as MyISAM vs InnoDB can exist in the same database.  The majority of specification options are at the table level.

MySQL Table Design for Scalability and Performance

Table Specification
Foreign Keys
Star Schema(pre-join)
Temp Tables

Table specification is done, as you are probably aware, through the create table command.  Usually you will want your scripts to contain specification of fields, options, indexes and foreign keys all in one place.  Check online for syntactical guides.  Personally, I usually handle this process through an ide.  Many people prefer to start with a db design tool such as DBVisualizer.  In this case, you create a picture of your database design with tables connected at foreign keys by lines.  Then, when satisfied you reverse engineer a create script through the software.  I am not a big fan of this method as it seems slow to me however for others who are more visual it seems to work well.  My MySQL client tool of choice is Navicat.  It is fairly easy to use relative to the other options out there.  To create a table, I will fill out a form with Navicat’s create table option which in turn generates the create sql for me.  There are many free options out there as well, however Navicat essentials offers ssh tunneling, the ability to directly access a database that is not directly accessible to the internet, and this is important enough to me to warrant the 40$ a Navicat essentials license costs.

In the past, specification of foreign keys has been a requirement of good database design.  Many db’s feel the same way about this now, especially those that have been around for a while.  For most MySQL implementations, i agree that this is a helpful way of ensuring data integrity and wise to use.  In the case of really large data sets, however, it is important to understand the cost of using foreign keys.  In many cases, it may be better to rely upon the application for this integrity.  For example, when an insert happens on a table with a foreign key, MySQL has to go check the key in a recursive fashion to make sure all required rows exist in the database according to the key.  While this is usually more efficient than handling this check yourself(what a mess), this check can be costly performance wise.  A happy medium can be achieved by creating foreign keys to control small inserts coming from the application but then disabling them using foreign_key_checks=0 when doing bulk loads.  Of course you as the administrator are much less likely to make mistakes than the application programmers (haha).  The performance gain by disabling these checks in a rigorously keyed database can be huge.  We will discuss more about this when we address database loading and ETL.

Another feature available in newer versions of MySQL is views.  Views are selections from the data than can be used to offer apparently pre-joined tables for use by application programmers.  While some sense can be seen in this as it may help in consistent and efficient caching of queries, this is probably of minimal importance.  MySQL creates views on the fly for the most part either using a merge or temp tables depending upon the terms of the query.  When tuning queries for performance, it is usually better to manage these joins per query at the application level as the path taken by the view function may not be optimal.  Basically, like foreign keys, views are a luxury that should be avoided when optimal performance is desired.
A more effective way of providing a true pre-join to application developers is through the use of star schema tables.  These are tables that contain required data from 2 or more standard tables populated usually directly after the base tables are loaded.  A star schema table can be separately indexed and optimized for a particular need and can prevent costly joins from being preformed over and over at the application level through performing them one time during your ETL process.  Your particular application will dictate how effective this technique is in increasing performance.  In the past, space requirements would often limit the use of this technique however given the low cost of disk space now, this usually a better choice than a view for increasing the performance of often used joins.
Use of temporary tables is also a technique that may or may not be effective in your situation but can have its uses in large MySQL databases.  Temporary tables are only available for the life of a connection.  More information about the use of these is available online.  More recent releases of MySQL handle temporary tables very effectively.  In some cases, especially those in which a costly join produces a very small set of data needed for part of a query, memory usage can be optimized through the use of a temporary table.  When building data transforms, you should consider temporary table use when this type of situation arises as ETL performance like all query performance quickly becomes unacceptable when disk paging is required.

MySQL Database Access and Population

Client Tools
Partitioning Jobs
Transaction Scope
Query Cache

In this section I will discuss different methods of accessing your database to perform maintenance and data tasks.  Your connection choices when accessing your MySQL database have a significant bearing upon the performance of the job you are running.  In addition, limiting the scope of the transactions you pass through these connections can be a very effective means by which to achieve acceptable performance for inserts.

Queries such as selects and inserts can be passed to your database in several different ways.  Each of these has performance implications which need to be considered.  For the sake of this discussion let us consider a database residing on a remote machine which you are maintaining from a desktop at work or home.  For the most part, costly queries and inserts will perform far better when passed directly to the engine command line than when passed across the network.  Network latency and likely other concerns can cut the performance of your job nearly in half and in some cases make something un-runnable through your client that works perfectly fine when submitted command line.  This does not mean database clients have no use for querying and of course, most application designs require database access over a network.  What needs to be understood is that access from a client tool such as Navicat from your desktop utilizes drivers just as the application you are creating does to enable this access.  The pattern that has worked best for me is to use the ide to facilitate the creation of sql code and running this against a subset of data to test and debug.  Once the sql I am creating is ready for prime time, it can be moved to the server and executed command line or through a script on the entire data set.  In this way you can take advantage of the ease of the idea in the design process while getting optimal performance when the full job is executed.  While engaging in this process, you will quickly be made aware of the operation of the query cache.  This is a cache used by MySQL to store the results of particular often run queries.  As you iterate through runs of a particular piece of code, you will notice performance after the first run increases dramatically.  This is due to the operation of the query cache.  Specifying a larger query cache through the my.cnf configuration file using the query_cache_size option will increase the effectiveness of this cache with large data sets.  Be sure to keep this in mind and take advantage of this feature to save time.

As noted before, the performance of your job can be greatly effected by whether or not it can be executed in memory without having to swap parts of the data out to disk.  This can be managed through the use of transactions and in sql partitioning of your job.  Below I have provided an example which does both to speed up query execution.  Use of transaction isolation (using start transaction, begin and commit in your query syntax) and using looping to process small groups of records at a time can turn an impossible job that would otherwise take days to run into something that completes nicely in under 20 minutes.  While there are other approaches to this, this particular pattern has worked amazingly for me permitting ETL operations on data sets that due to size would have been otherwise unmanageable.  Here is some code to get you started.  Just replace the insert with yours.

 drop procedure if exists load_prv_destination_table;
 delimiter $$
 create procedure load_prv_destination_table()
 declare row_max int unsigned default 3000000;
 declare row_increment int unsigned default 500000;
 declare row_position int unsigned default 1;
 declare row_count int unsigned default 0;
#set row_max = (select count(*) from raw.source_table);
 set row_max = (select max(provider_no) from raw.source_table);
 start transaction;
 while row_position <= row_max do
 insert into prv_destination_table (first_name, last_name, address_1, address_2, zip_code_no, destination_table_type_no, dbs_vshard_no)
 select substring(raw.source_table.first_name,1,40), substring(raw.source_table.last_name,1,40), substring(raw.source_table.address_1,1,50), substring(raw.source_table.address_2,1,50), -1, -1, 10000
 from raw.source_table
 where raw.source_table.category != 'HS' and raw.source_table.category != 'RX'
 and raw.source_table.category != 'AC' and raw.source_table.category != 'CLI'
 and raw.source_table.category != 'AM' and raw.source_table.category != 'IL'
 and raw.source_table.category != 'CM' and raw.source_table.category != 'unk'
 and provider_no between row_position and row_position+row_increment
 group by raw.source_table.first_name, raw.source_table.last_name, raw.source_table.address_1, raw.source_table.address_2
 order by raw.source_table.provider_no asc;
 #limit row_position, row_increment;
set row_position=row_position+row_increment;
 end while;
#set end_time = now();
 #select time_to_sec(timediff(end_time,start_time));
 #select start_time, end_time;
 end $$
call load_prv_destination_table();

You can un-comment the time stuff to run benchmarks.  You will want to optimize total run time by selecting the right number of records to loop through.  This is of course most related to the amount of ram available.  Watching the process monitor for your particular operating system can also be helpful.  A properly sized chunk to loop through will nearly max out your innodb_buffer_pool memory.  Too large of a loop and you are back to swapping to disk again and performance will really suffer.   When looking at a series of queries in an ETL process (for example) it will be fairly clear which queries are ending up in swap space.  While this can be minimized with good query design in a lot of cases, sometimes, it is unavoidable.  In these instances, try the approach above.