List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:April 9 2012 11:14am
Subject:Re: Remote mysql too slow
View as plain text  
You may want to ensure the nic is connected to the network at the
right speed and duplex. It seems that every new server I get now has
to have the speed and duplex explicitly set instead of auto negotiate.

Sent from my iPad

On Apr 9, 2012, at 4:00 AM, "J. Bakshi" <joydeep.bakshi@stripped> wrote:

>
> Hello,
>
> I have been provided a muscular linux server to use as a Mysql server
> in our organization. The server is located just beside the web server
> and within the same network. This dedicated server has 8GB RAM, i5 processors
> and running mysql as service. No apache, php ..... nothing. All resources are
> dedicated to mysql only.
>
> Mysql version - mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64)
>
> The BIG hitch is; when we connect with this box the web sites become too slow.
> I have added the following at my.cnf under [mysqld] section
>
> ` ` ` ` `
> skip_external_locking
> skip_name_resolve
> skip_host_cach
>
> ` ` ` ` ` `
>
>
> The sql connection becomes little faster but still it is considerably
> slow; specially with such a muscular dedicated linx box just for Mysql.
> Is there anything else which I can add/configure to make the network latecy
> small or any such mechanism to make the query fast ?
>
>
> I run the mysqltuner directly on the remote mysql server; and here is the
> result
>
> ```````````````````````````````````````````````````
> -------- General Statistics --------------------------------------------------
> [--] Skipped version check for MySQLTuner script
> [OK] Currently running supported MySQL version 5.1.49-3-log
> [OK] Operating on 64-bit architecture
>
> -------- Storage Engine Statistics -------------------------------------------
> [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
> [--] Data in MyISAM tables: 1G (Tables: 4777)
> [--] Data in InnoDB tables: 3G (Tables: 5543)
> [--] Data in MEMORY tables: 0B (Tables: 136)
> [!!] Total fragmented tables: 5562
>
> -------- Performance Metrics -------------------------------------------------
> [--] Up for: 3d 23h 55m 27s (1M q [4.523 qps], 81K conn, TX: 23B, RX: 469M)
> [--] Reads / Writes: 74% / 26%
> [--] Total buffers: 2.2G global + 20.4M per thread (150 max threads)
> [OK] Maximum possible memory usage: 5.2G (66% of installed RAM)
> [OK] Slow queries: 2% (39K/1M)
> [OK] Highest usage of available connections: 18% (28/150)
> [OK] Key buffer size / total MyISAM indexes: 2.0G/268.5M
> [!!] Key buffer hit rate: 78.2% (5M cached / 1M reads)
> [OK] Query cache efficiency: 42.0% (327K cached / 778K selects)
> [!!] Query cache prunes per day: 1993
> [OK] Sorts requiring temporary tables: 0% (48 temp sorts / 9K sorts)
> [!!] Temporary tables created on disk: 39% (91K on disk / 230K total)
> [OK] Thread cache hit rate: 99% (28 created / 81K connections)
> [!!] Table cache hit rate: 6% (16K open / 248K opened)
> [OK] Open file limit used: 36% (11K/32K)
> [OK] Table locks acquired immediately: 99% (695K immediate / 695K locks)
> [!!] InnoDB data size / buffer pool: 3.6G/8.0M
>
> -------- Recommendations -----------------------------------------------------
> General recommendations:
>    Run OPTIMIZE TABLE to defragment tables for better performance
>    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 (> 128M)
>    tmp_table_size (> 100M)
>    max_heap_table_size (> 100M)
>    table_cache (> 16000)
>    innodb_buffer_pool_size (>= 3G)
>
> ````````````````````````````````````````````````````
>
> Please note, every day mysql optimization as well as repairing is done
> by a cron at night. I have also tried with the suggestion multiple
> times before but every time it gives some new suggestion. I have 8GB
> physical RAM in this server and here is some statistics
>
> `````````````````
> # free -m
>             total       used       free     shared    buffers     cached
> Mem:          7986       7913         73          0        224       6133
> -/+ buffers/cache:       1554       6431
> Swap:         3813          0       3813
> `````````````````````
>
> And here is the vmstat result with 10 count and 5 sec delay
>
> `````````````````````````````
>
> # vmstat 5 10
> procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
> r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
> 0  0      0  56328 230440 6299676    0    0    25    74    6    6  0  0 98  2
> 0  0      0  55700 230440 6299744    0    0     0    22  226  272  0  0 99  1
> 0  0      0  55964 230440 6299856    0    0     0   314  348  388  0  0 94  5
> 0  0      0  55452 230440 6299956    0    0     0    61  304  364  0  0 97  2
> 0  1      0  55592 230440 6300424    0    0     0   271  199  257  0  0 96  4
> 0  0      0  54584 230440 6300908    0    0     0   338  342  428  0  0 92  8
> 0  0      0  54800 230440 6301072    0    0     0    77  119  133  0  0 98  2
> 0  0      0  53964 230440 6301532    0    0     0   617  267  327  0  0 95  4
> 0  0      0  54468 230440 6301544    0    0     0   296  119  116  0  0 99  0
> 0  0      0  54212 230440 6301648    0    0     0   183  361  435  0  0 95  4
>
> `````````````````````````
>
> Is it possible to suggest something to tweak the server / mysql to get
> a fast remote mysql box ?
>
> Thanks
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>
Thread
Remote mysql too slowJ. Bakshi9 Apr
  • Re: Remote mysql too slowReindl Harald9 Apr
    • Re: Remote mysql too slowJ. Bakshi9 Apr
      • Re: Remote mysql too slowReindl Harald9 Apr
        • Re: Remote mysql too slowJ. Bakshi9 Apr
          • Re: Remote mysql too slowReindl Harald9 Apr
            • Re: Remote mysql too slowJ. Bakshi9 Apr
  • Re: Remote mysql too slowJohnny Withers9 Apr
    • Re: Remote mysql too slowNoel Butler9 Apr