The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Help Optimizing MySQL and Apache - Server been running for 8 days now

Discussion in 'Workarounds and Optimization' started by djblamire, Jul 28, 2013.

  1. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    I have just purchased a new dedicated server and currently using the defaults for MySQL and Apache. I'm looking to try and optimize the setup as best I can.

    Here is the spec of the machine:

    Intel Core i7-2600 Quad Core
    8GB DDR3 RAM
    CentOS 6.4 - 64-Bit

    The spec is better than my previous machine, but the server load seems to be generally higher - I did do some tweaks on the previous machine, but I no longer have access to that machine to compare.

    I'm not entirely sure what files I should be providing with regards to Apache - in order to optimize, but here are the ones for MySQL.

    Here is the contents of /etc/my.cnf

    Code:
    [mysqld]
    innodb_file_per_table=1
    local-infile=0
    open_files_limit=2090
    
    I have run /usr/bin/mysqltuner.pl and this is what is showing - Everything has been running as it is for a tleast 8 days now:

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 67M (Tables: 569)
    [--] Data in InnoDB tables: 44M (Tables: 935)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 19
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 10d 19h 41m 26s (6M q [7.023 qps], 968K conn, TX: 21B, RX: 1B)
    [--] Reads / Writes: 94% / 6%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (7% of installed RAM)
    [OK] Slow queries: 0% (0/6M)
    [OK] Highest usage of available connections: 8% (13/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/21.1M
    [OK] Key buffer hit rate: 99.5% (7M cached / 40K reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (4K temp sorts / 1M sorts)
    [!!] Joins performed without indexes: 4801
    [!!] Temporary tables created on disk: 27% (60K on disk / 215K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (400 open / 141K opened)
    [OK] Open file limit used: 13% (279/2K)
    [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
    [OK] InnoDB data size / buffer pool: 44.4M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 400)
    
    I don't quite understand exactly what it is recommending.

    Any help would really be appreciated with this.

    Thanks in advance,

    Daniel
     
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    add in the end of my.cnf
    and change open_files_limit=2090 to open_files_limit=10000
    myisam_use_mmap=1
    connect_timeout = 2
    join_buffer_size=2M
    read_rnd_buffer_size=4M
    query_cache_type = 1
    query_cache_size = 30M
    query_cache_limit = 1M
    max_heap_table_size = 50M
    tmp_table_size = 50M
    thread_cache_size = 50
    table_open_cache = 1000
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    key_buffer_size = 500M
    concurrent_insert=2

    and restart mysql
     
  3. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    Thank you very much for your help. - I've now applied those changes.

    Is there anything I should now be monitoring to check for improvement ? - Log files, re-running the mysqltuner command ?

    Looking at the mysql-slow.log file seems to show the majority of queries are being logged. Most so far seem to be between about 0.1066 and 0.1366.

    Also, in terms of optimizing Apache, what files do I need to provide for anyone to be able to provide a bit of assistance with that ?

    Thanks in advance,

    Daniel
     
    #3 djblamire, Jul 28, 2013
    Last edited: Jul 28, 2013
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    you can install munin monitoring in cpanel/whm

    For mysql checks, mysqltuner.pl

    to review slow queries
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

    and for apache - httpd.conf
     
  5. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    648
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    You are welcome to update this thread with the outcome after you have implemented these changes.

    Thank you.
     
  6. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    After running the server for over 24 hours since the changes were implemented, here are the results of running the mysqltuner command again now:

    Code:
     >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.32-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 70M (Tables: 569)
    [--] Data in InnoDB tables: 44M (Tables: 935)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 19
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 2h 21m 1s (1M q [10.871 qps], 141K conn, TX: 2B, RX: 201M)
    [--] Reads / Writes: 90% / 10%
    [--] Total buffers: 724.0M global + 8.4M per thread (151 max threads)
    [OK] Maximum possible memory usage: 1.9G (26% of installed RAM)
    [!!] Slow queries: 15% (162K/1M)
    [OK] Highest usage of available connections: 7% (11/151)
    [OK] Key buffer size / total MyISAM indexes: 500.0M/21.8M
    [OK] Key buffer hit rate: 98.1% (212K cached / 4K reads)
    [OK] Query cache efficiency: 60.6% (352K cached / 581K selects)
    [!!] Query cache prunes per day: 47990
    [OK] Sorts requiring temporary tables: 0% (361 temp sorts / 175K sorts)
    [!!] Joins performed without indexes: 1077
    [!!] Temporary tables created on disk: 27% (4K on disk / 16K total)
    [OK] Thread cache hit rate: 99% (11 created / 141K connections)
    [!!] Table cache hit rate: 5% (1K open / 19K opened)
    [OK] Open file limit used: 7% (711/10K)
    [OK] Table locks acquired immediately: 99% (309K immediate / 310K locks)
    [OK] InnoDB data size / buffer pool: 44.4M/128.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (> 30M)
        join_buffer_size (> 2.0M, or always use indexes with joins)
        tmp_table_size (> 50M)
        max_heap_table_size (> 50M)
        table_cache (> 1000)
    
    Can you recommend any other improvements, or does that look a lot better now ?

    With regards to the Apache 'tweaking', here is part of the httpd.conf that I think may be the useful bit ?

    Code:
    # These can be set in WHM under 'Apache Global Configuration'
    Timeout 60
    TraceEnable Off
    ServerSignature Off
    ServerTokens ProductOnly
    FileETag None
    StartServers 5
    <IfModule prefork.c>
    MinSpareServers 5
    MaxSpareServers 10
    </IfModule>
    ServerLimit 256
    MaxRequestWorkers 150
    MaxConnectionsPerChild 10000
    KeepAlive Off
    KeepAliveTimeout 5
    MaxKeepAliveRequests 100
    
    RewriteEngine on
    RewriteMap LeechProtect prg:/usr/local/cpanel/bin/leechprotect
    Mutex file:/usr/local/apache/logs rewrite-map
    
    Thanks in advance for any assistance,

    Daniel
     
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Apache is good, you can lower Timeout, and start a bit more servers at once

    Timeout 30
    StartServers 10
    MinSpareServers 5
    MaxSpareServers 10

    about mysql, it's good,
    now you need to optimize the queries from mysql-slow.log
    describe what script you use (is it custom etc)

    run pt-query-digest to see slow queries review
     
  8. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    Thanks again for your help - I'll take a look at the apache.

    The script was custom built (several years ago), and I've lost track how we put it all together.

    There are only two types of queries being used (one each of two pages), but here is the page code for one of them:

    I believe it is probably very badly written, but with only knowing a small amount of MySQL/PHP it is hard for me to improve on.

    Code:
    <?php require_once('Connections/databaseconnect.php'); ?>
    <?php require('functionformat.php'); ?>
    <?php
    
    /*
    The following strips out the use of the % and _ wildcard characters, before running the query.
    */
    
    $pattern = array('/%/','/_/','/!/','/$/','/^/','/{/','/}/','/,/','/=/','/"/','/|/','/:/','/;/');
    $replace = array('','');
    $search_name = preg_replace($pattern, $replace, $_POST[search_name]);
    
    $search_name = mysql_real_escape_string($search_name);
    
    $unconfirmed_header = 0;
    $confirmed_header = 0;
    
    if (strlen($search_name) < "2") {
    
    }
    
    else {
    
    $maxRows_Recordset1 = 1250;
    $pageNum_Recordset1 = 0;
    
    if (isset($_GET['pageNum_Recordset1'])) {
      $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
    }
    $startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
    
    mysql_select_db($database_stemel, $stemel);
    $query_Recordset1 = "SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1 WHERE (confirmed = '0' OR confirmed = '1') AND (company_name LIKE '%$search_name%' OR other_info 
    
    LIKE '%$search_name%' OR url LIKE '%$search_name%') ORDER BY confirmed DESC, company_name ASC, company_number ASC";
    $query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
    $Recordset1 = mysql_query($query_limit_Recordset1, $stemel) or die(mysql_error());
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
    
    if (isset($_GET['totalRows_Recordset1'])) {
      $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
    } else {
      $all_Recordset1 = mysql_query($query_Recordset1); 
      $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
    }
    $totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
    
    }
    ?>
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
    <META NAME="ROBOTS" CONTENT="NOINDEX, NOFOLLOW">
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
    
    <?php
    /* This section passes on received values as hidden fields after validating null entries. */
    include ('header.php');
    ?>
    
    <link rel="stylesheet" href="search.css" type="text/css" />
    
      <?php
    if (($totalRows_Recordset1) > 0) { 
    
    		if ($row_Recordset1['confirmed'] & 1) { 
    		
    		?>
    <table width="100%" align="center" border="0">
      <tr>
        <td><font size="2">
    
    <div class="seperator"></div>
    
    <div class="boardcontainer">
    <table cellpadding="4" cellspacing="1" border="0" width="100%">
    <tr><td colspan="6" class="catbg" height="18" >Main Database</td></tr>
    
    <tr>
    	<td class="windowbg" width="28%" align="center">Company Name</td>
    	<td class="windowbg" width="12%" align="center">Phone No</td>
    	<td class="windowbg" width="12%" align="center">Mobile No</td>
    	<td class="windowbg" width="12%" align="center">Home No</td>
    	<td class="windowbg" width="12%" align="center">Personal Mobile</td>
    	<td class="windowbg" width="24%" align="center">Other Info</td>
    </tr>
    
    <?php 
    $confirmed_header=1;
    } 
    
    /*
    Message that is displayed when no CONFIRMED numbers are returned from the database
    */
    
    				{				
    
    
    if ($confirmed_header < 1)
    {
    
    			?>	
    			<table width="100%" align="center" border="0">
    			<tr>
    		    <td><font size="2">
    
    			<div class="seperator"></div>
    
    			<div class="boardcontainer">
    			<table cellpadding="4" cellspacing="1" border="0" width="100%">
    			<tr><td colspan="6" class="catbg" height="18" >Main Database</td></tr>
    
    			<tr>
    				<td class="windowbg" width="28%" align="center">Company Name</td>
    				<td class="windowbg" width="12%" align="center">Phone No</td>
    				<td class="windowbg" width="12%" align="center">Mobile No</td>
    				<td class="windowbg" width="12%" align="center">Home No</td>
    				<td class="windowbg" width="12%" align="center">Personal Mobile</td>
    				<td class="windowbg" width="24%" align="center">Other Info</td>
    			</tr>
    
    
      <tr>
        <td class=windowbg2 colspan="6" align=center BGCOLOR=#FFFFCC><B>No confirmed numbers have been found that match the criteria you entered.  One or more have been found in the unconfirmed list, and are listed below.</B></td>
      </tr>
    
    
    </table>
    </div>
    				<?php
    				}{}
    				} ?>
      <?php do { ?>
      <tr>
    <?php
         if ($row_Recordset1['confirmed'] & 1) { 
    
    				if (strlen($row_Recordset1['url']) > "2") {
    						 	 echo '<td class=windowbg2 width=28% align=center BGCOLOR=#FFFFCC><a href=http://www.mywebsite.com/external.php?site='.$row_Recordset1['url'].' target="_blank">'.$row_Recordset1
    
    ['company_name'].'</a></td>';   } else {
    		 
    		   echo '<td class=windowbg2 width=28% align=center BGCOLOR=#FFFFCC>'.$row_Recordset1['company_name'].'</a></td>';		 
    		 }
          echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['phone_no']).'</td>';
         echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['mobile_no']).'</td>';
         echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['home_no']).'</td>';
         echo '<td class=windowbg2 width=12% align=center BGCOLOR=#FFFFCC>*'.format_phone($row_Recordset1['mob2_no']).'</td>';
         echo '<td class=windowbg2 width=24% align=center BGCOLOR=#FFFFCC>*'.$row_Recordset1['other_info'].'</td></tr>';
            } else { 
    	if ($unconfirmed_header < 1) {
    ?>
    
    </tr>
    </table>
    </div><br />	
    
    <table width="100%" align="center" border="0">
      <tr><td><font size="2">
    <div class="seperator"></div>
    
    <div class="boardcontainer">
    <table cellpadding="4" cellspacing="1" border="0" width="100%">
    
    <tr><td colspan="6" class="catbg" height="18" >Unconfirmed Numbers</td></tr>
    
    <tr>
    	<td class="windowbg" width="28%" align="center">Company Name</td>
    	<td class="windowbg" width="12%" align="center">Phone No</td>
    	<td class="windowbg" width="12%" align="center">Mobile No</td>
    	<td class="windowbg" width="12%" align="center">Home No</td>
    	<td class="windowbg" width="12%" align="center">Personal Mobile</td>
    	<td class="windowbg" width="24%" align="center">Other Info</td>
    </tr>
    
    <?php
    	$unconfirmed_header = 1;
    				}
    
    				if (strlen($row_Recordset1['url']) > "2") {
    						 	 echo '<td class=windowuv width=28% align=center BGCOLOR=#CCFFFF> '.$row_Recordset1['company_name'].'</td>';   } else {
    		 
    		   echo '<td class=windowuv width=28% align=center BGCOLOR=#CCFFFF>'.$row_Recordset1['company_name'].'</a></td>';
    		 		 
    		 }
    
         echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['phone_no']).'</td>';
         echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['mobile_no']).'</td>';
         echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['home_no']).'</td>';
         echo '<td class=windowuv width=12% align=center BGCOLOR=#CCFFFF>*'.format_phone($row_Recordset1['mob2_no']).'</td>';
         echo '<td class=windowuv width=24% align=center BGCOLOR=#CCFFFF>*'.$row_Recordset1['other_info'].'</td></tr>';
            } 
    ?>
    
      <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
    
    </tr>
    </table>
    </div><br />
    
    <div class="seperator">
    <table cellpadding="4" cellspacing="1" border="0" width="100%">
    <tr>
    	<td class="titlebg" align="center" colspan="2">
    		Info Centre
    	</td>
    </tr>
    
    	<td class="windowbg2">
    		<div style="float: left; width: 59%; text-align: left;">
    		<span class="small">Please update us with any corrections as soon as possible.</span><br />
    		</div>
    		<div style="float: left; width: 40%; text-align: left;">
    		<div class="small" style="float: left; width: 49%;"><span style="color: red;"><b>lllll</b></span></div>
    		</div>
    	</td>
    </tr>
    </table>
    </div>
        </font></td>
      </tr>
    </table>
    
    <?php } else { ?>
    
    <body bgcolor="#FFFFCC">
    	<tr><td colspan=10>
    	 <center><table border=1 bordercolor=navy cellpadding=0 cellspacing=0><tr><td bgcolor="#CCCCFF"><div class=TableTitle>
    		<p align="center"><b><font face="Tahoma">NO RESULTS FOUND</font></b></div></td></tr><tr><td BGCOLOR=#FFFFCC>
    <div align="center">
    	<h3>&nbsp;</h3>
    </div>
    <div align="center">
    	<h3><font face="Tahoma" size="3">Your search didn't match any records.</B></font></h3>
    
    	<h3>&nbsp;</h3>
    </div>
    </td></tr></table></center>
    </td></tr></table><br>
    
    <?php
    }
    ?>
    <br>
    <?php
    require ('footer.php');
    
    if (($totalRows_Recordset1) > 0) { 
    mysql_free_result($Recordset1);
    mysql_close();
    }else{ 
    } 
    ?>
    
    Thanks
    Daniel
     
  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    LIKE %key% can't utilize indexes, its slow row by row scan, full text search will be better

    btw. you can run this
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest
    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

    and in slow.txt will be the detailed review of slow queries which are consuming the most time
     
  10. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    Hi,

    Thanks - I'll have a look into 'Full Text' and see how easy it would be to change the current setup.

    With regards to 'pt-query-digest', I've just installed and run that, but not entirely sure what the output is trying to tell me.

    Code:
    
    # 28.8s user time, 110ms system time, 33.29M rss, 186.64M vsz
    # Current date: Tue Jul 30 09:40:43 2013
    # Hostname: server.myserver.com
    # Files: /var/lib/mysql/mysql-slow.log
    # Overall: 176.44k total, 34 unique, 1.20 QPS, 0.14x concurrency _________
    # Time range: 2013-07-28 16:44:19 to 2013-07-30 09:40:43
    # Attribute          total     min     max     avg     95%  stddev  median
    # ============     ======= ======= ======= ======= ======= ======= =======
    # Exec time         20576s   100ms      7s   117ms   122ms    20ms   116ms
    # Lock time            18s       0    78ms   101us   138us   234us    93us
    # Rows sent          9.38M       0  87.80k   55.76  246.02  582.64    1.96
    # Rows examine      14.39G       0 120.50k  85.55k  83.83k   3.54k  83.83k
    # Query size        86.04M      14   1.40k  511.31  621.67  147.90  621.67
    
    # Profile
    # Rank Query ID           Response time   Calls R/Call V/M   Item
    # ==== ================== =============== ===== ====== ===== =============
    #    1 0x63458FE4D3477429 7458.0904 36.2% 62485 0.1194  0.00 SELECT contact?
    #    2 0x0E1182D2854678D1 5007.8278 24.3% 42941 0.1166  0.00 SELECT contact?
    #    3 0x8AA02BDCA2A87600 4908.9373 23.9% 42763 0.1148  0.00 SELECT contact?
    #    4 0x503B590421C28943 3141.6332 15.3% 27919 0.1125  0.00 SELECT contact?
    # MISC 0xMISC               59.6004  0.3%   335 0.1779   0.0 <30 ITEMS>
    
    # Query 1: 0.42 QPS, 0.05x concurrency, ID 0x63458FE4D3477429 at byte 39730472
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-07-28 16:44:26 to 2013-07-30 09:40:43
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         35   62485
    # Exec time     36   7458s   106ms   558ms   119ms   128ms     7ms   116ms
    # Lock time     40      7s    41us    12ms   117us   144us    67us   131us
    # Rows sent      5 553.61k       0     500    9.07   24.84   40.34    0.99
    # Rows examine  35   5.10G  85.61k  86.18k  85.66k  83.83k       0  83.83k
    # Query size    44  38.12M     618     649  639.77  621.67    1.38  621.67
    # String:
    # Databases    server5_contactdb
    # Hosts        localhost
    # Users        server5_contactdb
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `server5_contactdb` LIKE 'contact1'\G
    #    SHOW CREATE TABLE `server5_contactdb`.`contact1`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1
    WHERE
    ((phone_no LIKE '09653601000') OR
    (mobile_no LIKE '09653601000') OR
    (home_no LIKE '09653601000') OR
    (mob2_no LIKE '09653601000') OR
    (other_info LIKE '%09653601000%') OR
    (other_info LIKE '%0965 3601000%') OR
    (other_info LIKE '%0965 360 1000%') OR
    (other_info LIKE '%09653 601000%') OR
    (other_info LIKE '%09653 601 000%') OR
    (other_info LIKE '%096 53601000%') OR
    (other_info LIKE '%096 5360 1000%')) AND (confirmed = '0' OR confirmed = '1')
    ORDER BY confirmed DESC, company_name ASC, company_number ASC LIMIT 0, 500\G
    
    # Query 2: 0.29 QPS, 0.03x concurrency, ID 0x0E1182D2854678D1 at byte 39729993
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-07-28 16:44:19 to 2013-07-30 09:40:43
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         24   42941
    # Exec time     24   5008s   100ms   593ms   117ms   128ms    10ms   110ms
    # Lock time     23      4s    32us    11ms    96us   119us    86us   103us
    # Rows sent     43   4.08M       0   1.22k   99.65  685.39  236.75    7.70
    # Rows examine  24   3.51G  85.61k  86.91k  85.75k  83.83k       0  83.83k
    # Query size    15  13.75M     305     545  335.65  363.48   20.02  329.68
    # String:
    # Databases    server5_contactdb
    # Hosts        localhost
    # Users        server5_contactdb
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `server5_contactdb` LIKE 'contact1'\G
    #    SHOW CREATE TABLE `server5_contactdb`.`contact1`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1
    WHERE (confirmed = '0' OR confirmed = '1') AND (company_name LIKE '%Apple%' OR other_info LIKE '%Apple%' OR url LIKE '%Appl$
    
    # Query 3: 0.51 QPS, 0.06x concurrency, ID 0x8AA02BDCA2A87600 at byte 39732255
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-07-28 16:44:26 to 2013-07-29 15:49:27
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         24   42763
    # Exec time     23   4909s   104ms   551ms   115ms   122ms     7ms   110ms
    # Lock time     22      4s    38us    37ms    94us   131us   190us    89us
    # Rows sent      4 419.93k       0     658   10.06   28.75   46.31    0.99
    # Rows examine  24   3.49G  85.61k  86.31k  85.65k  83.83k       0  83.83k
    # Query size    29  25.56M     605     636  626.77  621.67    5.64  621.67
    # String:
    # Databases    server5_contactdb
    # Hosts        localhost
    # Users        server5_contactdb
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `server5_contactdb` LIKE 'contact1'\G
    #    SHOW CREATE TABLE `server5_contactdb`.`contact1`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1
    WHERE
    ((phone_no LIKE '09772001632') OR
    (mobile_no LIKE '09772001632') OR
    (home_no LIKE '09772001632') OR
    (mob2_no LIKE '09772001632') OR
    (other_info LIKE '%09772001632%') OR
    (other_info LIKE '%0977 2001632%') OR
    (other_info LIKE '%0977 200 1632%') OR
    (other_info LIKE '%09772 001632%') OR
    (other_info LIKE '%09772 001 632%') OR
    (other_info LIKE '%097 72001632%') OR
    (other_info LIKE '%097 7200 1632%')) AND (confirmed = '0' OR confirmed = '1')
    ORDER BY confirmed DESC, company_name ASC, company_number ASC\G
    
    # Query 4: 0.34 QPS, 0.04x concurrency, ID 0x503B590421C28943 at byte 39729533
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.00
    # Time range: 2013-07-28 16:44:19 to 2013-07-29 15:45:58
    # Attribute    pct   total     min     max     avg     95%  stddev  median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count         15   27919
    # Exec time     15   3142s   100ms   630ms   113ms   122ms    11ms   110ms
    # Lock time     12      2s    28us    33ms    80us   108us   210us    73us
    # Rows sent     40   3.85M       0  34.89k  144.46  755.64  672.87    8.91
    # Rows examine  15   2.28G  85.61k 120.50k  85.78k  83.83k  683.98  83.83k
    # Query size     9   8.55M     291     531  321.07  346.17   19.89  313.99
    # String:
    # Databases    server5_contactdb
    # Hosts        localhost
    # Users        server5_contactdb
    # Query_time distribution
    #   1us
    #  10us
    # 100us
    #   1ms
    #  10ms
    # 100ms  ################################################################
    #    1s
    #  10s+
    # Tables
    #    SHOW TABLE STATUS FROM `server5_contactdb` LIKE 'contact1'\G
    #    SHOW CREATE TABLE `server5_contactdb`.`contact1`\G
    # EXPLAIN /*!50100 PARTITIONS*/
    SELECT company_number, company_name, url, phone_no, mobile_no, home_no, mob2_no, other_info, confirmed FROM contact1 WHERE (confirmed = '0' OR confirmed = '1') AND (company_name LIKE '%microsoft%' OR other_info LIKE '%microsoft%' OR url LIKE '%microsoft%') O$
    
    I really appreciate all the help you have provided so far. Any help and guidance on the above would really be appreciated.

    Thanks
    Daniel
     
  11. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    The simplest way to do this is adding simple indexes on phone_no, mobile_no, home_no, mob2_no etc
    and full text index on other_info
    and another full index on company name

    then add full text search in your query instead of like %key%

    MySQL will pick the best index for the use

    this is the simplest optimization that can be done without working on the database (checking the data and it's structure)

    and instead of using WHERE (confirmed = '0' OR confirmed = '1')
    use WHERE confirmed IN (0, 1)
     
  12. djblamire

    djblamire Well-Known Member

    Joined:
    May 3, 2003
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    Thanks again for the recommended changes.

    When you say 'adding simple indexes' is this done in the structure of the MySQL database ?

    Will using "WHERE confirmed IN (0, 1)" actually be more efficient than using "WHERE (confirmed = '0' OR confirmed = '1')", or is it just more about the right way of doing it ?

    Thanks again,
    Daniel
     
  13. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page