tag:blogger.com,1999:blog-72977723099942298652024-02-09T00:46:12.823+05:30Database MySQLMySQL Replication, Optimization, Log AnalyzerManoj K Samtanihttp://www.blogger.com/profile/10523064467243501756noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-7297772309994229865.post-50335677409054012892011-03-16T14:35:00.002+05:302011-03-16T14:37:01.728+05:30MySQL Slow Query Log AnalyzerI found <b>myprofi</b> suitable for my requirement:<br />
<br />
Download <b>myprofi</b> in <b>/opt</b><br />
<br />
wget http://sourceforge.net/projects/myprofi/files/myprofi/MyProfi%200.181%20beta/MyProfi_0.181.zip/download<br />
<br />
<b>unzip MyProfi_0.181.zip<br />
</b><br />
<b>cd myprofi<br />
</b><br />
<b>php parser.php<br />
</b><br />
OUTPUT will be like :<br />
<br />
<br />
<b>MyProfi: mysql log profiler and analyzer<br />
<br />
Usage: php parser.php [OPTIONS] INPUTFILE<br />
<br />
Options:<br />
-top N<br />
Output only N top queries<br />
-type "query types"<br />
Ouput only statistics for the queries of given query types.<br />
Query types are comma separated words that queries may begin with<br />
-sample<br />
Output one sample query per each query pattern to be able to use it<br />
with EXPLAIN query to analyze its performance<br />
-csv<br />
Consideres an input file to be in csv format<br />
Note, that if the input file extension is .csv, it is also considered as csv<br />
-slow<br />
Treats an input file as a slow query log<br />
-sort CRITERIA<br />
Sort output statistics by given CRITERIA.<br />
Works only for slow query log format.<br />
Possible values of CRITERIA: qt_total | qt_avg | qt_max | lt_total | lt_avg | lt_max | rs_total<br />
rs_avg | rs_max | re_total | re_avg | re_max,<br />
where two-letter prefix stands for "Query time", "Lock time", "Rows sent", "Rows executed"<br />
values taken from data provided by sloq query log respectively.<br />
Suffix after _ character tells MyProfi to take total, maximum or average<br />
calculated values.</b><br />
<br />
Example:<br />
<i><b>php parser.php -csv -top 10 -type "SELECT, UPDATE" /va/lib/mysql/slow-query-file.log</b></i>Manoj K Samtanihttp://www.blogger.com/profile/10523064467243501756noreply@blogger.com0tag:blogger.com,1999:blog-7297772309994229865.post-80461228794011339942010-02-08T17:28:00.002+05:302011-03-08T16:01:58.266+05:30MYSQL OPTIMIZATION<div dir="ltr" style="text-align: left;" trbidi="on"><div style="background-color: white; color: black; counter-reset: __goog_page__ 0; font-family: Verdana; font-size: 10pt; line-height: normal; margin-bottom: 6px; margin-left: 6px; margin-right: 6px; margin-top: 6px; min-height: 1100px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;" valign="top"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;" width="100%"><h1 style="font-size: 18pt;"><span style="color: #999999;">Tuning LAMP systems, Part 3: </span>Tuning your MySQL server</h1><div id="subtitle" style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><i>Make your MySQL server fly with these server tuning tips</i></div><img alt="" class="display-img" height="6" src="http://www.ibm.com/i/c.gif" width="1" /></td><td class="no-print" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;" width="192"> </td></tr>
</tbody></table><table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;" valign="top"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;" width="10"><img alt="" height="1" src="http://www.ibm.com/i/c.gif" width="10" /> </td><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;" width="100%"><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Level: Intermediate</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><a href="http://www.ibm.com/developerworks/linux/library/l-tune-lamp-3.html#author">Sean A. Walberg</a> (<a href="mailto:sean@ertw.com?subject=Tuning%20your%20MySQL%20server">sean@ertw.com</a>), Senior Network Engineer</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">07 Jun 2007</div><blockquote style="border-bottom-color: rgb(221, 221, 221); border-bottom-style: dashed; border-bottom-width: 1px; border-left-color: rgb(221, 221, 221); border-left-style: dashed; border-left-width: 1px; border-right-color: rgb(221, 221, 221); border-right-style: dashed; border-right-width: 1px; border-top-color: rgb(221, 221, 221); border-top-style: dashed; border-top-width: 1px; padding-bottom: 10px; padding-left: 10px; padding-right: 10px; padding-top: 10px;">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 <a href="http://www.ibm.com/developerworks/views/linux/libraryview.jsp?topic_by=All+topics+and+related+products&sort_order=desc&lcl_sort_order=desc&search_by=tuning+lamp&search_flag=true&type_by=Articles&show_abstract=true&sort_by=Relevance&end_no=100&show_all=false">series of three articles</a> 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.</blockquote><div id="search_referrer_results" style="margin-bottom: 0px; margin-top: 0px;"><br />
</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="N1005D"><span class="atitle">About MySQL tuning</span></a></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">You can do three things to make your MySQL server faster, from least effective to most effective:</div><ol style="margin-bottom: 0px; margin-top: 0px;"><li style="margin-bottom: 0px; margin-top: 0px;">Throw hardware at the problem.</li>
<li style="margin-bottom: 0px; margin-top: 0px;">Tune the settings of the MySQL process.</li>
<li style="margin-bottom: 0px; margin-top: 0px;">Optimize your queries.</li>
</ol><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 tuning.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 mysqld was consumed with busy-work instead of servicing queries.</div><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><img alt="" height="1" src="http://www.ibm.com/i/v14/rules/blue_rule.gif" width="100%" /> </td></tr>
</tbody></table><table align="right" cellpadding="0" cellspacing="0" class="no-print zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr align="right" style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;" valign="middle"></td><td align="right" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;" valign="top"></td></tr>
</tbody></table></td></tr>
</tbody></table><br />
<br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="N100AD"><span class="atitle">Log slow queries</span></a></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 <i>table scan</i>. 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.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 <i>slow queries</i>.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><br />
<a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="listing1"><b>Listing 1. Enable the MySQL slow query log</b></a><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td class="code-outline" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><pre class="displaycode" style="font-family: 'Courier New'; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; white-space: pre-wrap;">[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</pre></td></tr>
</tbody></table><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">These three settings, used together, log any queries that take longer than 5 seconds and any queries that don't use indexes. Note the caveat about 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 <i>hostname</i>-slow.log. If you'd rather use a different name or path, you can do so with log-slow-queries = /new/path/to/file in my.cnf.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><img alt="" height="1" src="http://www.ibm.com/i/v14/rules/blue_rule.gif" width="100%" /> </td></tr>
</tbody></table><table align="right" cellpadding="0" cellspacing="0" class="no-print zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr align="right" style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;" valign="middle"></td><td align="right" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;" valign="top"> </td></tr>
</tbody></table></td></tr>
</tbody></table><br />
<br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="N100E9"><span class="atitle">Cache queries</span></a></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 <i>query cache</i> 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.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Adding query_cache_size = 32M to /etc/my.conf enables a 32MB query cache.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="N100FC"><span class="smalltitle">Monitor the query cache</span></a></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><br />
<a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="listing2"><b>Listing 2. Display the query cache statistics</b></a><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td class="code-outline" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><pre class="displaycode" style="font-family: 'Courier New'; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; white-space: pre-wrap;">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)</pre></td></tr>
</tbody></table><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">The breakdown of these items is shown in Table 1.</div><br />
<a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="table1"><b>Table 1. MySQL query cache variables</b></a><br />
<table border="0" cellpadding="0" cellspacing="0" class="data-table-1 zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;" summary="Timers"><tbody>
<tr style="text-align: left;"><th style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Variable name</th><th style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Description</th></tr>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Qcache_free_blocks</td><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">The number of contiguous memory blocks in the cache. Higher numbers are a sign of fragmentation. FLUSH QUERY CACHE defragments the cache so there's one free block.</td></tr>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Qcache_free_memory</td><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">The free memory in the cache.</td></tr>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Qcache_hits</td><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Incremented each time a query is served from the cache.</td></tr>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Qcache_inserts</td><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">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.</td></tr>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Qcache_lowmem_prunes</td><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">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 (free_blocks and free_memory, above, tell you which it is).</td></tr>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Qcache_not_cached</td><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">The number of queries that weren't candidates for caching, usually because they weren't SELECT statements.</td></tr>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Qcache_queries_in_cache</td><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">The number of queries (and responses) current cached.</td></tr>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">Qcache_total_blocks</td><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;">The number of blocks in the cache.</td></tr>
</tbody></table><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 FLUSH QUERY CACHE takes a long time, the cache is too large.</div><br />
<table align="right" cellpadding="0" cellspacing="0" class="no-print zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr align="right" style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;" valign="middle"></td><td align="right" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;" valign="top"><b><br />
</b></td></tr>
</tbody></table></td></tr>
</tbody></table><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="N101B3"><span class="atitle">Enforce limits</span></a></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">You should 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.</div><br />
<a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="listing3"><b>Listing 3. MySQL resource settings</b></a><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td class="code-outline" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><pre class="displaycode" style="font-family: 'Courier New'; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; white-space: pre-wrap;">set-variable=max_connections=500
set-variable=wait_timeout=10
max_connect_errors = 100</pre></td></tr>
</tbody></table><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">The maximum connections are governed in the first line. Like MaxClients from 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'.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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!</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><img alt="" height="1" src="http://www.ibm.com/i/v14/rules/blue_rule.gif" width="100%" /> </td></tr>
</tbody></table><br />
<div style="text-align: auto;"><a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="N101E3"><span class="atitle">Buffers and caches</span></a></div><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 SHOW STATUS 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.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">MySQL tunables apply to either the whole mysqld process or each individual client session.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="N101FB"><span class="smalltitle">Server-wide settings</span></a></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><br />
<a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="listing4"><b>Listing 4. Display table-open activity</b></a><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td class="code-outline" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><pre class="displaycode" style="font-family: 'Courier New'; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; white-space: pre-wrap;">mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 5000 |
| Opened_tables | 195 |
+---------------+-------+
2 rows in set (0.00 sec)</pre></td></tr>
</tbody></table><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 Opened_tables increases quickly as you rerun the SHOW STATUS command, you aren't getting enough hits out of your cache. If Open_tables 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 with table_cache = 5000, for example.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Like the table cache, there is also a cache for threads. mysqld 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.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Listing 5 shows how to determine if you have enough threads cached.</div><br />
<a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="listing5"><b>Listing 5. Show thread-usage statistics</b></a><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td class="code-outline" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><pre class="displaycode" style="font-family: 'Courier New'; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; white-space: pre-wrap;">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)</pre></td></tr>
</tbody></table><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">The important value here is Threads_created, which is incremented each time mysqld has to create a new thread. If this number increases quickly between successive SHOW STATUS commands, you should look at increasing your thread cache. You do this with thread_cache = 40, for example, in my.cnf.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><br />
<a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="listing6"><b>Listing 6. Determine key efficiency</b></a><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td class="code-outline" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><pre class="displaycode" style="font-family: 'Courier New'; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; white-space: pre-wrap;">mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads | 98247 |
+-------------------+-----------+
2 rows in set (0.00 sec)</pre></td></tr>
</tbody></table><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Key_reads represents the number of requests that hit disk, and 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.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Temporary tables are used in more advanced queries where data must be stored temporarily before further processing happens, such as in GROUP 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.</div><br />
<a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="listing7"><b>Listing 7. Determine temporary-table usage</b></a><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td class="code-outline" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><pre class="displaycode" style="font-family: 'Courier New'; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; white-space: pre-wrap;">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)</pre></td></tr>
</tbody></table><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Each use of a temporary table increases 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_tables over time shows the rate of created disk tables, and you can determine the effectiveness of the settings. Both tmp_table_size and max_heap_table_size control the maximum size of temporary tables, so make sure you set them both in my.cnf.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="N10298"><span class="smalltitle">Per-session settings</span></a></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><br />
<a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="listing8"><b>Listing 8. Show-sort statistics</b></a><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td class="code-outline" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><pre class="displaycode" style="font-family: 'Courier New'; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; white-space: pre-wrap;">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)</pre></td></tr>
</tbody></table><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">If sort_merge_passes is high, this is an indication that sort_buffer_size needs attention. For example, sort_buffer_size = 4M sets the sort buffer to 4MB.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 many 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.</div><br />
<a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="listing9"><b>Listing 9. Determine table-scan ratio</b></a><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td class="code-outline" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><pre class="displaycode" style="font-family: 'Courier New'; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; white-space: pre-wrap;">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)</pre></td></tr>
</tbody></table><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">The Handler_read_rnd_next / Com_select 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!</div><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><img alt="" height="1" src="http://www.ibm.com/i/v14/rules/blue_rule.gif" width="100%" /> </td></tr>
</tbody></table><br />
<div style="text-align: auto;"><a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="N102E5"><span class="atitle">Three must-have tools</span></a></div><br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 <a href="http://www.ibm.com/developerworks/linux/library/l-tune-lamp-3.html#resources">Resources</a> section.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 problems.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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 mytop 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.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">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.</div><br />
<table border="0" cellpadding="0" cellspacing="0" class="zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"><img alt="" height="1" src="http://www.ibm.com/i/v14/rules/blue_rule.gif" width="100%" /> </td></tr>
</tbody></table><table align="right" cellpadding="0" cellspacing="0" class="no-print zeroBorder" style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-collapse: collapse; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px; font-size: 1em; line-height: inherit;"><tbody>
<tr align="right" style="text-align: left;"><td style="border-bottom-color: gray; border-bottom-style: dotted; border-bottom-width: 1px; border-left-color: gray; border-left-style: dotted; border-left-width: 1px; border-right-color: gray; border-right-style: dotted; border-right-width: 1px; border-top-color: gray; border-top-style: dotted; border-top-width: 1px;"></td></tr>
</tbody></table><br />
<br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><a href="http://www.blogger.com/post-edit.g?blogID=7297772309994229865&postID=8046122879401133994" name="N1032F"><span class="atitle">Summary</span></a></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">This article covered the basics of MySQL tuning and concludes this three-part series on tuning LAMP components. Tuning is largely about understanding how things work, determining if they're working properly, making adjustments, and re-evaluating. Each component -- Linux, Apache, PHP, or MySQL -- has various needs. Understanding them individually helps eliminate the bottlenecks that can slow your application.</div><div><br />
<br />
<br />
<br />
<script type="text/javascript">
<!--</p>
<p>
google_ad_client = "pub-2381651380176034";</p><p>
/* 728x15, created 3/8/11 */</p><p>
google_ad_slot = "4632586614";</p><p>
google_ad_width = 728;</p><p>
google_ad_height = 15;</p><p>
//--></p><p>
</script><br />
<script <="" p="" type="text/javascript">
<p>
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></p><p>
</script><br />
<br />
</div></td></tr>
</tbody></table></div></div>Manoj K Samtanihttp://www.blogger.com/profile/10523064467243501756noreply@blogger.com0tag:blogger.com,1999:blog-7297772309994229865.post-26431442330962567022009-08-24T17:07:00.002+05:302010-02-08T17:34:17.660+05:30MySQL Engines and Optimization<p id="ek732" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in; font-family: verdana;" align="center"> <meta equiv="CONTENT-TYPE" content="text/html; charset=utf-8"> <title></title> <meta name="GENERATOR" content="OpenOffice.org 2.3 (Linux)"> <style type="text/css"> <!-- @page { size: 8.5in 11in; margin: 0.79in } P { margin-bottom: 0.08in } H1 { margin-bottom: 0.08in } H1.western { font-family: "Liberation Serif", "Times New Roman", serif } H1.cjk { font-family: "DejaVu Sans" } H1.ctl { font-family: "DejaVu Sans" } H2 { margin-bottom: 0.08in } H2.western { font-family: "Liberation Serif", "Times New Roman", serif } H2.cjk { font-family: "DejaVu Sans" } H2.ctl { font-family: "DejaVu Sans" } --> </style> </p><h1 style="text-align: center;" class="western"><font style="color: rgb(0, 0, 128);"><u><a href="http://simondownes.co.uk/2008/11/mysql-storage-engines/">MySQL Storage Engines</a></u></font></h1> <p style="text-align: left;">Storage engines are responsible for storing and retrieving all the data stored “in” MySQL. Each storage engine has its own strengths and weaknesses that determine its suitability in a particular situation.</p><div style="text-align: left;"> </div><h2 style="text-align: left;" class="western">MyISAM</h2><div style="text-align: left;"> </div><p style="text-align: left;">MyISAM is the default storage engine in MySQL and provides a good comprimise between performance and features.</p><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Pros:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p style="margin-bottom: 0in;">platform independent </p> </li><li><p style="margin-bottom: 0in;">concurrent inserts </p> </li><li><p style="margin-bottom: 0in;">full-text indexes </p> </li><li><p>compression </p> </li></ul><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Cons:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p style="margin-bottom: 0in;">no transations </p> </li><li><p style="margin-bottom: 0in;">table-level locking </p> </li><li><p>potentially long repair times </p> </li></ul><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Good For:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p>Applications with many reads and few writes. </p> </li></ul><div style="text-align: left;"> </div><hr style="margin-left: 0px; margin-right: auto;"><div style="text-align: left;"> </div><h2 style="text-align: left;" class="western">InnoDB</h2><div style="text-align: left;"> </div><p style="text-align: left;">InnoDB is a transactional storage engine that uses MVCC and row-level locking, and includes automatic crash recovery.</p><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Pros:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p style="margin-bottom: 0in;">ACID transactions </p> </li><li><p style="margin-bottom: 0in;">row-level locking and MVCC </p> </li><li><p style="margin-bottom: 0in;">crash recovery </p> </li><li><p style="margin-bottom: 0in;">clustered indexes </p> </li><li><p>foreign key constraints </p> </li></ul><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Cons:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p>alterations to table structure can be slow on large tables </p> </li></ul><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Good For:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p>Online ordering and other transaction based applications. </p> </li></ul><div style="text-align: left;"> </div><hr style="margin-left: 0px; margin-right: auto;"><div style="text-align: left;"> </div><h2 style="text-align: left;" class="western">Memory</h2><div style="text-align: left;"> </div><p style="text-align: left;">Memory tables store all their data in memory which means they are very fast because there is no waiting for disk I/O. They also use hash indexes which makes them very fast for lookup queries. The table definition of a Memory table will survive a server restart, but all data will be lost.</p><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Pros:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p>Very fast </p> </li></ul><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Cons:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p style="margin-bottom: 0in;">Uses fixed-length rows which can waste memory </p> </li><li><p style="margin-bottom: 0in;">Table-level locking </p> </li><li><p style="margin-bottom: 0in;">No support for TEXT or BLOB datatypes </p> </li><li><p>No transactions </p> </li></ul><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Used for:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p style="margin-bottom: 0in;">Lookup or mapping tables </p> </li><li><p style="margin-bottom: 0in;">Caching results of periodically aggregated data </p> </li><li><p>Intermediate results when analysing data </p> </li></ul><div style="text-align: left;"> </div><hr style="margin-left: 0px; margin-right: auto;"><div style="text-align: left;"> </div><h2 style="text-align: left;" class="western">Archive</h2><div style="text-align: left;"> </div><p style="text-align: left;">The archive engine is optimised for high-speed inserting and data compression. It supports only INSERT and SELECT queries and doesn’t support DELETE, REPLACE, or UPDATE queries, or indexes. Rows are buffered and compressed using zlib as they are inserted which means much less disk I/O than MyISAM tables.</p><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Pros:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p style="margin-bottom: 0in;">Fast INSERTs </p> </li><li><p>Compression </p> </li></ul><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Cons:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p>No support for indexes, SELECTs will perform a full table scan. </p> </li></ul><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Uses:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p>Storing large amounts of rarely accessed data in a very small footprint, e.g. logs and audit records </p> </li></ul><div style="text-align: left;"> </div><hr style="margin-left: 0px; margin-right: auto;"><div style="text-align: left;"> </div><h2 style="text-align: left;" class="western">CSV</h2><div style="text-align: left;"> </div><p style="text-align: left;">The CSV storage engine stores data in text files using comma-seperated values. Other applications can open the table data file directly and read the contents. Likewise, if an application exports a CSV and saves it in the server’s data directory, the server can read the file straight away. CSV tables do not support indexes.</p><div style="text-align: left;"> </div><p style="text-align: left;"><strong>Uses:</strong></p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p>Data interchange and certain types of logging. </p> </li></ul><div style="text-align: left;"> </div><hr style="margin-left: 0px; margin-right: auto;"><div style="text-align: left;"> </div><h2 style="text-align: left;" class="western">Other Engines</h2><div style="text-align: left;"> </div><p style="text-align: left;">There are several other storage engines available.</p><div style="text-align: left;"> </div><ul style="text-align: left;"><li><p style="margin-bottom: 0in;"><strong>Blackhole</strong> - Essentially a no-op storage engine, all INSERTS are discarded although they are recorded in the binary log and can be replayed on slaves. </p> </li><li><p style="margin-bottom: 0in;"><strong>Federated</strong> - Federated tables refer to tables on a remote MySQL server. </p> </li><li><p style="margin-bottom: 0in;"><strong>NDB Cluster</strong> - A specialised storage engine designed for high-performance with redundancy and load-balancing capabilities. </p> </li><li><p style="margin-bottom: 0in;"><strong>Falcon</strong> - A next-generation storage engine designed for todays hardware (64bit CPUs and plenty of memory). </p> </li><li><p><strong>Maria</strong> - A replacement for MyISAM that includes transactions, row-level locking, MVCC, and better crash recovery. </p> </li></ul><div style="text-align: left;"> </div><p style="text-align: left;">Blackhole, Federated and NDB Cluster are suitable only for specific purposes and should only be used after careful consideration. Falcon and Maria are the two modern storage engines although neither are currently considered production-stable.</p> <p id="ek732" class="western" face="verdana" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in; text-align: left;"><font id="ek733" style="color: rgb(35, 35, 220);" size="4"><u id="ek734"><b id="ek735"><font id="ek736" style="background: transparent none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">--------------------------------------------------------------------------------<br /></font></b></u></font></p><span style="font-family: monospace;"><span style="font-weight: bold;"><br /></span></span><font size="5"><span style="font-weight: bold; font-family: arial;"></span></font><div style="text-align: center;"><font size="5"><span style="font-weight: bold; font-family: arial;">Optimization</span></font><br /><span style="font-family: monospace;"><span style="font-weight: bold;"></span></span></div><span style="font-family: monospace;"><span style="font-weight: bold;"><br /></span></span> <meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8"> <title></title> <meta name="GENERATOR" content="OpenOffice.org 2.3 (Linux)"> <style type="text/css"> <!-- @page { size: 8.5in 11in; margin: 0.79in } P { margin-bottom: 0.08in } H1 { margin-bottom: 0.08in } H1.western { font-family: "Liberation Serif", "Times New Roman", serif } H1.cjk { font-family: "DejaVu Sans" } H1.ctl { font-family: "DejaVu Sans" } --> </style> <h1 style="color: rgb(255, 0, 0);" class="western"><font style="font-size: 10pt;" size="2">Optimizing for read performance</font></h1> <meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8"> <title></title> <meta name="GENERATOR" content="OpenOffice.org 2.3 (Linux)"> <style type="text/css"> <!-- @page { size: 8.5in 11in; margin: 0.79in } PRE { font-family: "Liberation Mono", "Courier New", monospace } P { margin-bottom: 0.08in } H2 { margin-bottom: 0.08in } H2.western { font-family: "Liberation Serif", "Times New Roman", serif } H2.cjk { font-family: "DejaVu Sans" } H2.ctl { font-family: "DejaVu Sans" } --> </style> <h2 class="western"><font style="font-size: 10pt;" size="2">Key buffer</font></h2> <p><font style="font-size: 10pt;" size="2">The key buffer stores database indexes in memory. This buffer should be large enough to hold all indexes used by eZ Publish. This should be in the range of hundreds of megabytes. Sites with large amounts of data require larger key buffers. To allocate a buffer of 500MB: </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">key_buffer = 500M</font></pre><p> <font style="font-size: 10pt;" size="2">To find a suitable value for the key buffer, investigate the status variables </font><font style="font-size: 10pt;" size="2"><i>key_read_requests</i></font><font style="font-size: 10pt;" size="2"> and </font><font style="font-size: 10pt;" size="2"><i>key_reads</i></font><font style="font-size: 10pt;" size="2">. The </font><font style="font-size: 10pt;" size="2"><i>key_read_requests</i></font><font style="font-size: 10pt;" size="2"> is the total number of key requests served from the cache while the </font><font style="font-size: 10pt;" size="2"><i>key_reads</i></font><font style="font-size: 10pt;" size="2"> shows the number of times MySQL had to access the filesystem to fetch the keys. </font> </p> <p><font style="font-size: 10pt;" size="2">The lower the number of </font><font style="font-size: 10pt;" size="2"><i>key_reads</i></font><font style="font-size: 10pt;" size="2"> the better. The more memory you allocate to the key buffer the more requests will be served from the cache. There will always be some keys that need to be read from disk (for example when data changes), so the value will never be zero. By comparing the two values you see the hit ratio of your key buffer. The </font><font style="font-size: 10pt;" size="2"><i>key_read_requests</i></font><font style="font-size: 10pt;" size="2"> should be much larger than the </font><font style="font-size: 10pt;" size="2"><i>key_reads</i></font><font style="font-size: 10pt;" size="2">. 99% cached requests is a good number to aim for in a read-intensive environment. </font> </p> <h2 class="western"><font style="font-size: 10pt;" size="2">Table cache</font></h2> <p><font style="font-size: 10pt;" size="2">The table cache tells MySQL how many tables it can have open at any one time. In SQL queries, several tables are typically joined. The rule of thumb is that you should multiply the maximum number of connections (described below) by the maximum number of tables used in joins. For example, if the maximum number of connections is set to 400, the table cache should be at least 400 * 10. The configuration setting below shows a table cache of 4000: </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">table_cache = 4000</font></pre><h2 class="western"> <font style="font-size: 10pt;" size="2">Sort buffers</font></h2> <p><font color="#000080"><u><a href="http://dev.mysql.com/doc/internals/en/filesort.html" target="_blank"><font style="font-size: 10pt;" size="2">MySQL sorts query results</font></a></u></font><font style="font-size: 10pt;" size="2"> before they are returned. The sort buffer is per connection, so you must multiply the size of the sort buffer by the maximum number of connections to predict the server memory requirements. In our case we use a 3MB sort buffer with 400 max connections, which can use a total of 1.2GB of memory. </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">sort_buffer_size = 3M</font></pre><h2 class="western"> <font style="font-size: 10pt;" size="2">Max connections</font></h2> <p><font style="font-size: 10pt;" size="2">MySQL has a limitation on the number of concurrent connections it can keep open. If you are using persistent connections in PHP, each process in Apache will keep a connection to MySQL open. This means that you need to set the number of max connections in MySQL to equal or greater than the number of Apache processes that can connect to the database. In a clustered environment, you must add up the processess on each webserver to determine the maximum. Setting sufficient max connections also ensures that users do not get errors about connecting to the MySQL database. The setting for 400 connections is shown below. </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">max_connections = 400</font></pre><h2 class="western"> <font style="font-size: 10pt;" size="2">Query cache</font></h2> <p><font style="font-size: 10pt;" size="2">MySQL is capable of caching the results of a query. The next time the same query is executed the result is immediately returned, as it is read from the cache rather than the database. For a read-intensive site, this can provide a significant performance improvement. </font> </p> <p><font style="font-size: 10pt;" size="2">To enable the query cache, set the type to "1": </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">query_cache_type = 1</font></pre><p> <font style="font-size: 10pt;" size="2">You can set the maximim size of each query that can be cached. If the query result is larger than the query cache limit, the results will not be cached. This is normally set to 1M: </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">query_cache_limit = 1M</font></pre><p> <font style="font-size: 10pt;" size="2">The amount of memory globally available for query caches is set with the query cache size setting. This should be fairly large, and should be increased in size for large databases. </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">query_cache_size = 100M</font></pre><p> <font style="font-size: 10pt;" size="2">To tune the query cache, use the </font><font style="font-size: 10pt;" size="2"><i>show status</i></font><font style="font-size: 10pt;" size="2"> command. This can be used to determine which settings need to be altered and to see the effect of alterations. The </font><font style="font-size: 10pt;" size="2"><i>show status</i></font><font style="font-size: 10pt;" size="2"> command will show you if the query cache is heavily in use and if you have free memory, which indicates whether the query cache buffer settings should be increased or decreased. </font> </p> <pre><font style="font-size: 10pt;" size="2">mysql> show status like "qcache%";</font> <font style="font-size: 10pt;" size="2">+-------------------------+----------+</font> <font style="font-size: 10pt;" size="2">| Variable_name | Value |</font> <font style="font-size: 10pt;" size="2">+-------------------------+----------+</font> <font style="font-size: 10pt;" size="2">| Qcache_free_blocks | 34 |</font> <font style="font-size: 10pt;" size="2">| Qcache_free_memory | 16466312 |</font> <font style="font-size: 10pt;" size="2">| Qcache_hits | 1313227 |</font> <font style="font-size: 10pt;" size="2">| Qcache_inserts | 78096 |</font> <font style="font-size: 10pt;" size="2">| Qcache_lowmem_prunes | 0 |</font> <font style="font-size: 10pt;" size="2">| Qcache_not_cached | 3328 |</font> <font style="font-size: 10pt;" size="2">| Qcache_queries_in_cache | 140 |</font> <font style="font-size: 10pt;" size="2">| Qcache_total_blocks | 346 |</font> <font style="font-size: 10pt;" size="2">+-------------------------+----------+</font> <font style="font-size: 10pt;" size="2">8 rows in set (0.00 sec)</font></pre> <meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8"> <title></title> <meta name="GENERATOR" content="OpenOffice.org 2.3 (Linux)"> <style type="text/css"> <!-- @page { size: 8.5in 11in; margin: 0.79in } P { margin-bottom: 0.08in } H1 { margin-bottom: 0.08in } H1.western { font-family: "Liberation Serif", "Times New Roman", serif } H1.cjk { font-family: "DejaVu Sans" } H1.ctl { font-family: "DejaVu Sans" } --> </style> <h1 style="color: rgb(255, 0, 0);" class="western"><font style="font-size: 10pt;" size="5">Optimizing for write performance</font></h1><br /> <meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8"> <title></title> <meta name="GENERATOR" content="OpenOffice.org 2.3 (Linux)"> <style type="text/css"> <!-- @page { size: 8.5in 11in; margin: 0.79in } PRE { font-family: "Liberation Mono", "Courier New", monospace } P { margin-bottom: 0.08in } H3 { margin-bottom: 0.08in } H3.western { font-family: "Liberation Serif", "Times New Roman", serif } H3.cjk { font-family: "DejaVu Sans" } H3.ctl { font-family: "DejaVu Sans" } --> </style> <h3 class="western"><font style="font-size: 10pt;" size="2">Disable flush transaction on commit</font></h3> <p><font style="font-size: 10pt;" size="2">When using InnoDB, by default MySQL flushes data to disk when transactions are commited. This means that each transaction is flushed to disk when it occurs. This provides data security in case the database server crashes. </font> </p> <p><font style="font-size: 10pt;" size="2">The default behaviour can be overridden with the following setting: </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">innodb_flush_log_at_trx_commit = 0</font></pre><p> <font style="font-size: 10pt;" size="2">This setting makes MySQL flush the transaction cache every second instead of after each commit. This means transactions are not flushed to disk the moment they happen. While this improves performance, you must decide whether the risk of losing data due to a server crash is acceptable. </font> </p> <h3 class="western"><font style="font-size: 10pt;" size="2">InnoDB buffer pool size</font></h3> <p><font style="font-size: 10pt;" size="2">The InnoDB buffer pool caches table data and indexes. The larger the size of the buffer pool, the more data can be cached and the less disk I/O used. The InnoDB memory buffer pool in MySQL is by default quite low and should be made as large as 70% of the available memory. ("Available memory" means the memory not used by any other application or by another buffer in MySQL.) We increase this to 700MB to increase performance. </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">innodb_buffer_pool_size = 700M</font></pre><h3 class="western"> <font style="font-size: 10pt;" size="2">InnoDB additional mem pool size</font></h3> <p><font style="font-size: 10pt;" size="2">The InnoDB additional mem pool is the buffer used to store internal data structures. The more tables in the database, the more memory is required. If the additional mem pool size is not large enough to store data about the InnoDB tables, MySQL will use system memory and will write warnings to the error log. </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">innodb_additional_mem_pool_size = 50M</font></pre><h3 class="western"> <font style="font-size: 10pt;" size="2">Key buffer</font></h3> <p><font style="font-size: 10pt;" size="2">The key buffer is a memory cache of the indexes in a MySQL database. A large key buffer means that more indexes fit in memory and thus there is a faster execution of queries using indexes. We increase this to 500MB; the default is 16MB. </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">key_buffer = 500M</font></pre><h3 class="western"> <font style="font-size: 10pt;" size="2">Log buffer size</font></h3> <p><font style="font-size: 10pt;" size="2">The log buffer stores the transactions in memory before they are flushed to disk. By making the log buffer size larger, MySQL can wait longer before flushing the transaction log to disk and therefore use less disk I/O. The size recommended by MySQL is between 1MB and 8MB. We used 8MB for our test, which actually made MySQL a bit slower compared to the 1MB default. Therefore, we recommend somewhere in between, for example 4MB. </font> </p> <pre style="margin-bottom: 0.2in;"><font style="font-size: 10pt;" size="2">innodb_log_buffer_size = 4M</font></pre> Manoj K Samtanihttp://www.blogger.com/profile/10523064467243501756noreply@blogger.com0tag:blogger.com,1999:blog-7297772309994229865.post-86196968233371097182008-05-28T14:45:00.001+05:302009-07-09T14:16:13.919+05:30MySQL Replication<p id="ek732" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;" align="center"> <span id="ek733" style="color: rgb(35, 35, 220);font-size:130%;" ><u id="ek734"><b id="ek735"><span id="ek736" style="background: transparent none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">MySQL DataBase Master-Master Replication</span></b></u></span></p> <p id="ek739" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><b id="tnle0"><br /></b></span></p><p id="ek739" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"><b id="tnle0">Required Packages</b></span></p><span style="font-size:130%;"><span style="font-family:verdana;"> mysql</span></span><span style="font-size:130%;"><br /> mysql-server<br /> mysql-devel<br /></span><p id="ek739" class="western" face="verdana" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;"> </p> <p id="ek7311" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek7313" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><b id="ek7314"><span id="ek7315"><span id="ek7316" style="background: transparent none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><span id="ek7317" style="color: rgb(0, 0, 255);">Master1</span></span> server ip: 192.168.0.82</span></b></span></p> <p id="ek7318" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><b id="ek7319"><span id="ek7320"><span id="ek7321" style="background: transparent none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><span id="ek7322" style="color: rgb(0, 174, 0);">Master2</span></span> server ip: 192.168.0.83</span></b></span></p> <p id="ek7323" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span id="ek7324" style="font-size:130%;"><b id="ek7325">Slave username: user</b></span></p> <p id="ek7326" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span id="ek7327" style="font-size:130%;"><b id="ek7328">Slave password: user</b></span></p> <p id="ek7329" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span id="ek7330" style="font-size:130%;"><b id="ek7331">Your data directory is: /var/lib/mysql/</b></span></p> <p id="ek7332" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek7336" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><b id="ek7337"><u id="ek7338">In <span id="ek7339" style="background: rgb(0, 255, 255) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Master1</span> Database machine edit /etc/my.cnf :</u></b></span></p> <p id="ek7340" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek7342" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> # let's make it so auto increment columns behave by having different increments on both servers</span></p> <p id="ek7343" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> auto_increment_increment=2</span></p> <p id="ek7344" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> auto_increment_offset=1</span></p> <p id="ek7345" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> # Replication Master Server</span></p> <p id="ek7346" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> # binary logging is required for replication</span></p> <p id="ek7347" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> log-bin=/var/log/master1-bin</span></p> <p id="ek7348" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> binlog-ignore-db=mysql</span></p> <p id="ek7349" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> binlog-ignore-db=test</span></p> <p id="ek7350" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> # required unique id between 1 and 2^32 - 1</span></p> <p id="ek7351" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> server-id = 1</span></p> <p id="ek7352" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> #following is the slave settings so this server can connect to master2</span></p> <p id="ek7353" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> master-host = 192.168.0.83</span></p> <p id="ek7354" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> master-user = slaveuser</span></p> <p id="ek7355" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> master-password = slavepw</span></p> <p id="ek7356" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> master-port = 3306</span></p> <p id="ek7357" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek7359" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> Save and exit.</span></p> <p id="ek7360" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek7362" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek7364" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><b id="ek7365"><u id="ek7366">In <span id="ek7367" style="background: rgb(0, 255, 0) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Master2</span> Database machine edit /etc/my.cnf :</u></b></span></p> <p id="ek7368" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek7370" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> # let's make it so auto increment columns behave by having different increments on both servers</span></p> <p id="ek7371" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> auto_increment_increment=2</span></p> <p id="ek7372" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> auto_increment_offset=2</span></p> <p id="ek7373" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> # Replication Master Server</span></p> <p id="ek7374" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> # binary logging is required for replication</span></p> <p id="ek7375" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> log-bin=/var/log/master2-bin</span></p> <p id="ek7375" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;">#Ignore database to replicate<br /></span> </p> <p id="ek7376" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> binlog-ignore-db=mysql<br /></span> </p> <p id="ek7377" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> binlog-ignore-db=test</span></p> <p id="ek7378" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> # required unique id between 1 and 2^32 - 1</span></p> <p id="ek7379" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> server-id = 2</span></p> <p id="ek7380" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> #following is the slave settings so this server can connect to master1</span></p> <p id="ek7381" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> master-host = 192.168.0.82</span></p> <p id="ek7382" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> master-user = user</span></p> <p id="ek7383" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> master-password = user</span></p> <p id="ek7384" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> master-port = 3306</span></p> <p id="ek7385" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek7387" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"> Save and exit.</span></p> <p id="ek7388" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek7390" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek7392" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek7393"><span id="ek7394" style="background: rgb(204, 255, 255) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek7395"><span id="ek7396"><span id="ek7397"><span id="ek7398" style="color: rgb(0, 0, 0);">Enter the following command on </span></span></span></b><b id="tyq_0"><span id="ek7396"><span id="ek7397"><span id="ek7398" style="color: rgb(0, 0, 0);">Master1</span></span></span></b><b id="ek7395"><span id="ek7396"><span id="ek7397"><span id="ek7398" style="color: rgb(0, 0, 0);"> </span></span></span></b></span></code><span id="ek7399" style="background: rgb(204, 255, 255) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73100"><span id="ek73101"><span id="ek73102"><span id="ek73103" style="color: rgb(0, 0, 0);">to create/grant user level access on the database to Master2.</span></span></span></b></span></span></p> <p id="ek73104" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73106" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73107"><span id="ek73108">mysql> grant replication slave on *.* to slaveuser@'192.168.0.83' identified by 'slavepw';</span></code></span></p> <p id="ek73109" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73111" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73112"><span id="ek73113">mysql>FLUSH PRIVILEGES;</span></code></span></p> <p id="ek73114" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73116" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73117"><span id="ek73118" style="background: rgb(204, 255, 255) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73119"><span id="ek73120"><span id="ek73121" style="color: rgb(0, 0, 0);">Now, enter the following command on Master2 for create/grant user level access on the database to Master1.</span></span></b></span></code></span></p> <p id="ek73122" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73124" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73125"><span id="ek73126">mysql> grant replication slave on *.* to user@'192.168.0.82' identified by 'user';</span></code></span></p> <p id="ek73127" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73129" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73130"><span id="ek73131">mysql>FLUSH PRIVILEGES;</span></code></span></p> <p id="ek73132" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73134" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73135"><span id="ek73136" style="background: rgb(153, 153, 153) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73137"><span id="ek73138" style="color: rgb(0, 255, 255);">Now make slave both machines to each other.</span></b></span></code></span></p> <p id="ek73139" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73141" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73142"><span id="ek73143" style="background: rgb(230, 230, 230) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73144"><u id="ek73145">On <span id="ek73146" style="color: rgb(0, 0, 255);">Master1</span> enter the following:</u></b></span></code></span></p> <p id="ek73147" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73148"><span id="ek73149">mysql> show master status;</span></code></span></p> <p id="ek73150" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73151"><span id="ek73152"><span id="ek73153">+----------------------+----------+--------------+-----------------------+</span></span></code></span></p> <p id="ek73154" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73155"><span id="ek73156"><span id="ek73157">| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |</span></span></code></span></p> <p id="ek73158" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73159"><span id="ek73160"><span id="ek73161">+----------------------+----------+--------------+-----------------------+</span></span></code></span></p> <p id="ek73162" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73163"><span id="ek73164"><span id="ek73165">| <span id="ek73166" style="color: rgb(0, 0, 255);">mysql-bin-log.000017</span> | <span id="ek73167" style="color: rgb(255, 51, 102);">289</span> | | mysql,test,mysql,test | </span></span></code></span> </p> <p id="ek73168" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73169"><span id="ek73170"><span id="ek73171">+----------------------+----------+--------------+-----------------------+</span></span></code></span></p> <p id="ek73172" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73173"><span id="ek73174"><span id="ek73175">1 row in set (0.00 sec)</span></span></code></span></p> <p id="ek73176" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73178" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73179"><span id="ek73180" style="background: rgb(230, 230, 255) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73181"><span id="ek73182"><span id="ek73183">NOTE:This <span id="ek73184" style="color: rgb(0, 0, 255);">mysql-bin-log</span> file used to read data by the slave servers to replicate Database. Copy file name and position i.e; (<span id="ek73185" style="color: rgb(255, 51, 102);">289</span>).</span></span></b></span></code></span></p> <p id="ek73186" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73188" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73190" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73192" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73193"><span id="ek73194" style="background: rgb(230, 230, 230) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73195"><u id="ek73196"><span id="ek73197">Now make <span id="ek73198" style="color: rgb(51, 204, 102);">Master2</span> the slave of <span id="ek73199" style="color: rgb(0, 0, 255);">Master1.</span></span></u></b></span></code></span></p> <p id="ek73200" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73202" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73203"><span id="ek73204" style="background: rgb(230, 230, 230) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73205"><u id="ek73206"><span id="ek73207">Enter the following command on <span id="ek73208" style="color: rgb(0, 174, 0);">Master2</span>:</span></u></b></span></code></span></p> <p id="ek73209" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73211" class="western" style="margin: 0.02in 0.12in 0.04in 0.09in;font-family:verdana;"><span style="font-size:130%;"> mysql> stop slave;</span></p> <p id="ek73212" class="western" style="margin: 0.02in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73213"><span id="ek73214"><span id="ek73215">mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.82', MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='mysql-bin-log.0000017', MASTER_LOG_POS=98;</span></span></code></span></p> <p id="ek73216" class="western" style="margin: 0.02in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73217"><span id="ek73218"><span id="ek73219">mysql>start slave;</span></span></code></span></p> <p id="ek73220" class="western" style="margin: 0.02in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73221"><span id="ek73222"><span id="ek73223">mysql>show slave status\G;</span></span></code></span></p> <p id="ek73224" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73225"><span id="ek73226" style="background: rgb(0, 174, 0) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73227"><span id="ek73228"><span id="ek73229" style="color: rgb(255, 255, 204);">It will show you something inside the output:</span></span></b></span></code></span></p> <p id="ek73230" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73231"><span id="ek73232"><span id="ek73233"> Master_Log_File: mysql-bin-log.000017</span></span></code></span></p> <p id="ek73234" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><br /><br /></span> </p> <p id="ek73237" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73238"> <span id="ek73239"><span id="ek73240">Read_Master_Log_Pos: 289</span></span></code></span></p> <p id="ek73241" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><br /><br /></span> </p> <p id="ek73244" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73245"> <span id="ek73246"><span id="ek73247">Relay_Log_File: localhost-relay-bin.000026</span></span></code></span></p> <p id="ek73248" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><br /><br /></span> </p> <p id="ek73251" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73252"> <span id="ek73253"><span id="ek73254">Relay_Log_Pos: 239</span></span></code></span></p> <p id="ek73255" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><br /><br /></span> </p> <p id="ek73258" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73259"> <span id="ek73260"><span id="ek73261">Relay_Master_Log_File: mysql-bin-log.000017</span></span></code></span></p> <p id="ek73262" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><br /><br /></span> </p> <p id="ek73265" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73266"><span id="ek73267"><span id="ek73268"> |</span></span></code></span></p> <p id="ek73269" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73270"><span id="ek73271"><span id="ek73272"> |</span></span></code></span></p> <p id="ek73273" class="western" style="margin: 0.04in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73274"><span id="ek73275"><span id="ek73276"> Seconds_Behind_Master: 0</span></span></code></span></p> <p id="ek73277" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73278"><span id="ek73279" style="background: rgb(0, 174, 0) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73280"><span id="ek73281"><span id="ek73282" style="color: rgb(255, 255, 153);">If it is showing Seconds_Behind_Master not “NULL” then this slave is working fine.</span></span></b></span></code></span></p> <p id="ek73283" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73285" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73287" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><b id="ek73288"><span id="ek73289" style="background: rgb(35, 0, 220) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Then for making Master1 slave to Master2, we need Master2 machine's mysql-bin-log and position, for that run the following command for required information:</span></b></span></p> <p id="ek73290" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73292" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73293"><span id="ek73294"><span id="ek73295">mysql> show master status;</span></span></code></span></p> <p id="ek73296" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73298" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73299"><span id="ek73300"><span id="ek73301">+----------------------+----------+--------------+-----------------------+</span></span></code></span></p> <p id="ek73302" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73303"><span id="ek73304"><span id="ek73305">| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |</span></span></code></span></p> <p id="ek73306" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73307"><span id="ek73308"><span id="ek73309">+----------------------+----------+--------------+-----------------------+</span></span></code></span></p> <p id="ek73310" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73311"><span id="ek73312"><span id="ek73313">| <span id="ek73314" style="color: rgb(0, 0, 255);">mysql-bin-log.000002</span> | <span id="ek73315" style="color: rgb(255, 51, 102);">574</span> | | mysql,test,mysql,test | </span></span></code></span> </p> <p id="ek73316" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73317"><span id="ek73318"><span id="ek73319">+----------------------+----------+--------------+-----------------------+</span></span></code></span></p> <p id="ek73320" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73322" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73323"><span id="ek73324"><span id="ek73325">1 row in set (0.00 sec)</span></span></code></span></p> <p id="ek73326" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73328" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73329"><span id="ek73330" style="background: rgb(230, 230, 255) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73331"><span id="ek73332"><span id="ek73333">NOTE:This <span id="ek73334" style="color: rgb(0, 0, 255);">mysql-bin-log</span> file used to read data by the slave servers to replicate database. Copy file name and position i.e; (<span id="ek73335" style="color: rgb(255, 51, 102);">574</span>).</span></span></b></span></code></span></p> <p id="ek73336" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73338" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73340" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73342" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73343"><span id="ek73344" style="background: rgb(230, 230, 230) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73345"><u id="ek73346"><span id="ek73347"><span id="ek73348" style="color: rgb(0, 0, 0);">Now make </span><span id="ek73349" style="color: rgb(0, 0, 255);">Master1</span><span id="ek73350" style="color: rgb(0, 0, 0);"> the slave of </span><span id="ek73351" style="color: rgb(51, 204, 102);">Master2</span></span></u></b></span></code></span></p> <p id="ek73352" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73354" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73355"><span id="ek73356" style="background: rgb(230, 230, 230) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73357"><u id="ek73358"><span id="ek73359"><span id="ek73360" style="color: rgb(0, 0, 0);">Enter the following command on </span><span id="ek73361" style="color: rgb(0, 0, 255);">Master1</span><span id="ek73362" style="color: rgb(0, 0, 0);">:</span></span></u></b></span></code></span></p> <p id="ek73363" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73365" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0.06in;font-family:verdana;"><span style="font-size:130%;"> mysql> stop slave;</span></p> <p id="ek73366" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0.06in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73367"><span id="ek73368"><span id="ek73369">mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.83', MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='mysql-bin-log.000002', MASTER_LOG_POS=98;</span></span></code></span></p> <p id="ek73370" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0.06in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73371"><span id="ek73372"><span id="ek73373">mysql> start slave;</span></span></code></span></p> <p id="ek73374" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0.06in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73375"><span id="ek73376"><span id="ek73377">mysql>show slave status\G;</span></span></code></span></p> <p id="ek73378" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73379"><span id="ek73380" style="background: rgb(51, 204, 102) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73381"><span id="ek73382"><span id="ek73383" style="color: rgb(255, 255, 204);">It will show you something inside the output:</span></span></b></span></code></span></p><p id="ek73378" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"><code id="ek73379"><span id="ek73380" style="background: rgb(51, 204, 102) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73381"><span id="ek73382"><span id="ek73383" style="color: rgb(255, 255, 204);"><br /></span></span></b></span></code></span></p> <p id="ek73384" class="western" style="margin: 0.06in 0.12in 0in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73385"><span id="ek73386"><span id="ek73387"> Master_Log_File: mysql-bin-log.000002</span></span></code></span></p> <p id="ek73390" class="western" style="margin: 0.02in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73391"> <span id="ek73392"><span id="ek73393">Read_Master_Log_Pos: 574</span></span></code></span></p> <p id="ek73397" class="western" style="margin: 0.02in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73398"> <span id="ek73399"><span id="ek73400">Relay_Log_File: mysqld-relay-bin.000003</span></span></code></span></p> <p id="ek73404" class="western" style="margin: 0.02in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73405"> <span id="ek73406"><span id="ek73407">Relay_Log_Pos: 239</span></span></code></span></p> <p id="ek73411" class="western" style="margin: 0.02in 0.12in 0.04in 0.09in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73412"> <span id="ek73413"><span id="ek73414">Relay_Master_Log_File: mysql-bin-log.000002</span></span></code></span></p> <p id="ek73418" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73419"><span id="ek73420"><span id="ek73421"> |</span></span></code></span></p> <p id="ek73422" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73423"><span id="ek73424"><span id="ek73425"> |</span></span></code></span></p> <p id="ek73426" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73427"><span id="ek73428"><span id="ek73429"> Seconds_Behind_Master: 0</span></span></code></span></p> <p id="ek73430" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><br /></span> </p> <p id="ek73432" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"> <span style="font-size:130%;"><code id="ek73433"><span id="ek73434" style="background: rgb(0, 174, 0) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73435"><span id="ek73436"><span id="ek73437" style="color: rgb(255, 255, 0);">If it is showing Seconds_Behind_Master not “NULL” then this slave is working fine.</span></span></b></span></code></span></p> <p id="ek73432" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"><br /><code id="ek73433"></code></span> </p><span style="font-size:130%;"><code id="ek73433"></code></span> <p id="ek73432" class="western" style="margin-left: 0.09in; margin-right: 0.12in; margin-bottom: 0in;font-family:verdana;"><span style="font-size:130%;"><code id="ek73433"><span id="ek73434" style="background: rgb(0, 174, 0) none repeat scroll 0% 50%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"><b id="ek73435"><span id="ek73436"><span id="ek73437" style="color: rgb(255, 255, 0);"><span id="drum0" style="background-color: rgb(255, 255, 255);"><span style="color: rgb(51, 51, 255);">And now do some testing like create/delete database and tables on one machine and check on other, is it showing the changes???? if both are showing same data then that means your replication is working fine</span>.</span></span></span></b></span></code></span></p>Manoj K Samtanihttp://www.blogger.com/profile/10523064467243501756noreply@blogger.com0