|Home||Switchboard||Unix Administration||Red Hat||TCP/IP Networks||Neoliberalism||Toxic Managers|
May the source be with you, but remember the KISS principle ;-)
Bigger doesn't imply better. Bigger often is a sign of obesity, of lost control, of overcomplexity, of cancerous cells
|Securing the Initial MySQL Accou|
|Apache||MS Access to mySql conversion||PHP integration||
|Comparison with other databases||Resetting root password on MySQL||Humor||Etc|
September 29, 2006 | www.mysqlperformanceblog.comMy favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.
I'm surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.
Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.
key_buffer_size - Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload - remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time - it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you'll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.
innodb_buffer_pool_size This is very important variable to tune if you're using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply - if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.
innodb_additional_mem_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.
innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.
innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory - it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.
innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.
table_cache - Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I've seen values over 100.000 used.
thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.
query_cache_size If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.
Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.
P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.
Make your MySQL server fly with these server tuning tips
07 Jun 2007
Applications using the LAMP (Linux®, Apache, MySQL, PHP/Perl) architecture are constantly being developed and deployed. But often the server administrator has little control over the application itself because it's written by someone else. This series of three articles discusses many of the server configuration items that can make or break an application's performance. This third article, the last in the series, focuses on tuning the database layer for maximum efficiency.
About MySQL tuning
You can do three things to make your MySQL server faster, from least effective to most effective:
Throwing hardware at a problem is often the first thought, especially because databases are resource hogs. This solution can only take you so far, though. In practical terms, you can usually double your central processing unit (CPU) or disk speed, and maybe increase your memory by a factor of 4 or 8.
The second best thing to do is to tune the MySQL server, also called
mysqld. Tuning the process means allocating memory to the right
places and giving
mysqld an idea of what type of load to expect.
Rather than make the disks faster, it's better to reduce the number of disk
accesses needed. Similarly, making sure the MySQL process is operating correctly
means it can spend more time servicing queries than taking care of background
tasks like temporary disk tables and opening and closing files. Tuning
mysqld is the focus of this article.
The best thing you can do is make sure your queries are optimized. This
means the proper indexes are applied to tables, and queries are written
in such a way that they take advantage of MySQL's strengths. Even though
this article doesn't cover query tuning (books have been written on the
subject), it configures
mysqld to report queries that may need
Just because these tasks have been assigned an order doesn't mean you
can ignore the hardware and
mysqld settings in favor of properly
tuned queries. A slow machine is a slow machine, and I've seen fast machines
with well-written queries fail under load because
consumed with busy-work instead of servicing queries.
Log slow queries
In a SQL server, the data tables sit on disk. Indexes provide a means for the server to find a particular row of data in the table without having to search the entire table. When the entire table has to be searched, it's called a table scan. Most often, you want only a small subset of the data in the table, so a full table scan wastes a lot of disk I/O and therefore time.This problem is compounded when data must be joined, because many more rows must be compared between the two sides of the join.
Of course, table scans aren't always a sign of a problem; sometimes it's more efficient to read the whole table than it is to pick through it (making these decisions is the job of the query planner in the server process). Inefficient use of indexes, or not being able to use indexes at all, slows the queries, and this issue becomes more pronounced as the load on the server and the size of the tables increases. Queries that take more than a given amount of time to execute are called slow queries.
You can configure
mysqld to log slow queries in the aptly
named slow query log. Administrators then look at this log to help them
determine which parts of the application need further investigation. Listing
1 shows the configuration required in my.cnf to enable the slow query log.
[mysqld] ; enable the slow query log, default 10 seconds log-slow-queries ; log queries taking longer than 5 seconds long_query_time = 5 ; log queries that don't use indexes even if they take less than long_query_time ; MySQL 4.1 and newer only log-queries-not-using-indexes
log-queries-not-using-indexes: You must have MySQL 4.1 or newer. The slow query log is in your MySQL data directory and is called hostname-slow.log. If you'd rather use a different name or path, you can do so with
log-slow-queries = /new/path/to/filein my.cnf.
Reading through the slow query log
is best done with the
mysqldumpslow command. Specify the path
to the logfile, and you're given a sorted list of the slow queries, along
with how many times they're found in the log. One helpful feature is that
mysqldumpslow removes any user-specified data before collating
the results, so different invocations of the same query are counted as one;
this helps point out queries in need of the most work.
Many LAMP applications rely heavily on the database but make the same queries over and over. Each time the query is made, the database must do the same work -- parse the query, determine how to execute it, load information from disk, and return it to the client. MySQL has a feature called the query cache that stores the result of a query in memory, should it be needed again. In many instances, this increases performance drastically. The catch, though, is that the query cache is disabled by default.
query_cache_size = 32M to /etc/my.conf enables a
32MB query cache.
Monitor the query cache
After you enable the query cache, it's important to understand whether it's being used effectively. MySQL has several variables you can watch to see how things are going in the cache. Listing 2 shows the status of the cache.
mysql> SHOW STATUS LIKE 'qcache%'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | Qcache_free_blocks | 5216 | | Qcache_free_memory | 14640664 | | Qcache_hits | 2581646882 | | Qcache_inserts | 360210964 | | Qcache_lowmem_prunes | 281680433 | | Qcache_not_cached | 79740667 | | Qcache_queries_in_cache | 16927 | | Qcache_total_blocks | 47042 | +-------------------------+------------+ 8 rows in set (0.00 sec)
Table 1. MySQL query cache variables
||The number of contiguous memory blocks in the cache. Higher
numbers are a sign of fragmentation.
||The free memory in the cache.|
||Incremented each time a query is served from the cache.|
||Incremented each time a query is inserted. Divide the number of inserts by the hits to get your miss rate; subtract this value from 1 to get your hit rate. In the previous example, approximately 87% of the queries are getting served from cache.|
||How many times the cache ran out of memory and had to be cleaned
up to make room for more queries. This number is best looked at
over time; if it's increasing, it's a sign that either fragmentation
is serious or memory is low (
||The number of queries that weren't candidates for caching, usually
because they weren't
||The number of queries (and responses) current cached.|
||The number of blocks in the cache.|
Often, showing the variables several seconds apart indicates change,
which helps determine whether the cache is being used effectively. Running
FLUSH STATUS resets some of the counters, which is helpful
if the server has been running for a while.
It's tempting to make an excessively large query cache in the hopes of
caching everything. Because
mysqld must perform maintenance
on the cache, such as pruning when memory becomes low, the server can get
bogged down trying to manage the cache. As a rule, if
CACHE takes a long time, the cache is too large.
enforce a few limits in
mysqld to ensure that the system load
doesn't cause resource starvations. Listing 3 shows some important resource-related
settings from my.cnf.
set-variable=max_connections=500 set-variable=wait_timeout=10 max_connect_errors = 100
MaxClientsfrom Apache, the idea is to make sure only the number of connections you can serve are allowed. To determine the maximum number of connections your server has seen so far, execute
SHOW STATUS LIKE 'max_used_connections'.
The second line tells
mysqld to terminate any connections that
have been idle for more than 10 seconds. In LAMP applications, the connection
to the database is usually only as long as the Web server takes to process
the request. Sometimes, under load, connections hang around and take up
connection table space. If you have many interactive users or use persistent
connections to the database, then setting this low isn't a good idea!
The final line is a safety measure. If a host has problems connecting
to the server and ends up aborting the request too many times, the host
is locked until
FLUSH HOSTS can be run. By default, 10 failures
are enough to cause blocking. Changing this value to 100 gives the server
enough time to recover from whatever problems it has. Using a higher value
doesn't help you much because if the server can't connect once in 100 tries,
chances are it's not going to connect at all.
Buffers and caches
MySQL supports well over 100 tunable settings; but luckily, mastering
a small handful will take care of most needs. Finding the right value for
these settings involves looking at status variables via the
command and, from that, determining whether
mysqld is behaving
as you wish. You can't allocate more memory to buffers and caches than exists
in the system, so tuning often involves making compromises.
MySQL tunables apply to either the whole
or each individual client session.
Each table is represented as a file on disk and must be opened before
it can be read. To speed up the process of reading from the file,
mysqld caches these open files up to the limit specified by
table_cache in /etc/mysqld.conf. Listing 4 shows how to display the
activity associated with opening tables.
mysql> SHOW STATUS LIKE 'open%tables'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Open_tables | 5000 | | Opened_tables | 195 | +---------------+-------+ 2 rows in set (0.00 sec)
Listing 4 shows that 5,000 tables are currently open and that 195
tables had to be opened because there was no available file descriptor in
the cache (the statistics were cleared earlier, so it's feasible to have
5,000 open tables with a history of only 195 opens). If
increases quickly as you rerun the
SHOW STATUS command, you
aren't getting enough hits out of your cache. If
is much lower than your
table_cache setting, you have too many
(some room to grow is never a bad thing, though). Adjust your table cache
table_cache = 5000, for example.
Like the table cache, there is also a cache for threads.
spawns threads as needed when receiving connections. On a busy server where
connections are torn up and down quickly, caching threads for use later
speeds up the initial connection.
Listing 5 shows how to determine if you have enough threads cached.
mysql> SHOW STATUS LIKE 'threads%'; +-------------------+--------+ | Variable_name | Value | +-------------------+--------+ | Threads_cached | 27 | | Threads_connected | 15 | | Threads_created | 838610 | | Threads_running | 3 | +-------------------+--------+ 4 rows in set (0.00 sec)
Threads_created, which is incremented each time
mysqldhas to create a new thread. If this number increases quickly between successive
SHOW STATUScommands, you should look at increasing your thread cache. You do this with
thread_cache = 40, for example, in my.cnf.
The key buffer stores index blocks for MyISAM tables. Ideally, requests for these blocks should come from memory instead of disk. Listing 6 shows how to determine how many blocks were read from disk versus those from memory.
mysql> show status like '%key_read%'; +-------------------+-----------+ | Variable_name | Value | +-------------------+-----------+ | Key_read_requests | 163554268 | | Key_reads | 98247 | +-------------------+-----------+ 2 rows in set (0.00 sec)
Key_reads represents the number of requests that hit
Key_read_requests is the total number. Dividing the
reads by the read requests gives the miss rate -- in this case, 0.6 misses
per 1,000 requests. If you're missing more than 1 per 1,000 requests, you
should consider increasing your key buffer.
key_buffer = 384M,
for example, sets the buffer to 384MB.
Temporary tables are used in more advanced queries where data must be
stored temporarily before further processing happens, such as in
BY clauses. Ideally, such tables are created in memory; but if a
temporary table gets too large, it's written to disk. Listing 7 shows the
statistics associated with temporary-table creation.
mysql> SHOW STATUS LIKE 'created_tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 30660 | | Created_tmp_files | 2 | | Created_tmp_tables | 32912 | +-------------------------+-------+ 3 rows in set (0.00 sec)
Created_tmp_tables; disk-based tables also increment
Created_tmp_disk_tables. There is no hard-and-fast rule for the ratio because it depends on the queries involved. Watching
Created_tmp_disk_tablesover time shows the rate of created disk tables, and you can determine the effectiveness of the settings. Both
max_heap_table_sizecontrol the maximum size of temporary tables, so make sure you set them both in my.cnf.
The following settings are per session. Take care when you set these numbers, because when multiplied by the number of potential connections, these options represent a lot of memory! You can change these numbers in the session through code or for all sessions in my.cnf.
When MySQL must perform a sort, it allocates a sort buffer to store the
rows as they're read from disk. If the size of the data to sort is too large,
the data must go to temporary files on disk and be sorted again. If the
sort_merge_passes status variable is high, this is an indication
of this disk activity. Listing 8 shows some of the sort-related status counters.
mysql> SHOW STATUS LIKE "sort%"; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | Sort_merge_passes | 1 | | Sort_range | 79192 | | Sort_rows | 2066532 | | Sort_scan | 44006 | +-------------------+---------+ 4 rows in set (0.00 sec)
sort_merge_passes is high, this is an indication
sort_buffer_size needs attention. For example,
= 4M sets the sort buffer to 4MB.
MySQL also allocates memory to read tables. Ideally, the indexes provide
enough information to read in only the needed rows, but sometimes queries
(through poor design or the nature of the data) require large chunks of
the table to be read. To understand this behavior, you need to know how
SELECT statements were run and the number of times you
had to read the next row in the table (rather than a direct access through
an index). The commands to do so are shown in Listing 9.
mysql> SHOW STATUS LIKE "com_select"; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Com_select | 318243 | +---------------+--------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE "handler_read_rnd_next"; +-----------------------+-----------+ | Variable_name | Value | +-----------------------+-----------+ | Handler_read_rnd_next | 165959471 | +-----------------------+-----------+ 1 row in set (0.00 sec)
gives your table-scan ratio -- in this case, 521:1. Anything over 4000,
and you should look at your
read_buffer_size, such as
read_buffer_size = 4M. If you're growing this number beyond 8M, it's
time to talk to your developers about tuning those queries!
Three must-have tools
Even though the
SHOW STATUS commands are helpful when you're
drilling down into specific settings, you need some tools to help you interpret
the vast amounts of data provided by
mysqld. I've found three
tools to be indispensable; you can find links in the
Most sysadmins are familiar with the
top command, which
provides a constantly updated view of the CPU and memory consumed by tasks.
mytop is modelled after
top; it provides a view
of all the connected clients along with any queries they're currently running.
mytop also provides real-time and historical data about key-buffer
and query-cache efficiency, and statistics about the queries being run.
It's a useful tool to see what's going on -- within 10 seconds, you can
get a view of the server's health and display any connections that are causing
mysqlard is a daemon that connects to the MySQL server and
collects data every 5 minutes, storing it in a Round Robin Database backend.
A Web page displays the data, such as table-cache usage, key efficiency,
connected clients, and temporary-table usage. Whereas
provides a snapshot of server health,
mysqlard provides long-term
health information. As a bonus,
mysqlard uses some of the information
it collects to make suggestions about how to tune your server.
Another tool for collecting
SHOW STATUS information is
mysqlreport. It's far more verbose in its reporting than
mysqlard because it analyzes every facet of the server. It's
an excellent tool for tuning a server because it performs the appropriate
calculations on the status variables to help you determine what needs fixing.
Get products and technologies
SHOW STATUSvariables on your behalf.
Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers : Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism : The Iron Law of Oligarchy : Libertarian Philosophy
War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda : SE quotes : Language Design and Programming Quotes : Random IT-related quotes : Somerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose Bierce : Bernard Shaw : Mark Twain Quotes
Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 : Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law
Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds : Larry Wall : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOS : Programming Languages History : PL/1 : Simula 67 : C : History of GCC development : Scripting Languages : Perl history : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history
The Peter Principle : Parkinson Law : 1984 : The Mythical Man-Month : How to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite
Most popular humor pages:
Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor
The Last but not Least Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D
Copyright © 1996-2018 by Dr. Nikolai Bezroukov. www.softpanorama.org was initially created as a service to the (now defunct) UN Sustainable Development Networking Programme (SDNP) in the author free time and without any remuneration. This document is an industrial compilation designed and created exclusively for educational use and is distributed under the Softpanorama Content License. Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.
FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available to advance understanding of computer science, IT technology, economic, scientific, and social issues. We believe this constitutes a 'fair use' of any such copyrighted material as provided by section 107 of the US Copyright Law according to which such material can be distributed without profit exclusively for research and educational purposes.
This is a Spartan WHYFF (We Help You For Free) site written by people for whom English is not a native language. Grammar and spelling errors should be expected. The site contain some broken links as it develops like a living tree...
|You can use PayPal to make a contribution, supporting development of this site and speed up access. In case softpanorama.org is down you can use the at softpanorama.info|
The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the author present and former employers, SDNP or any other organization the author may be associated with. We do not warrant the correctness of the information provided or its fitness for any purpose.
Last modified: September 12, 2017