You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cloudstack.apache.org by benoit lair <ku...@gmail.com> on 2013/04/18 15:16:09 UTC
CS 4.0.0 - mysql server connections aborted
Hello,
I'm using a cs 4.0.0 preproduction server onto centos 5.3.
I have launched a mysqltuner.pl onto the mysql server of the mgmt server.
So it gives me connections aborted.
Have you an explication why it show this ? How can i troubleshoot this ?
Here is my mysqltuner.pl output :
[root@cloudstack-master-server ~]# ./mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden <ma...@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics
--------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.67-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics
-------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 23M (Tables: 166)
[--] Data in MEMORY tables: 125K (Tables: 2)
[!!] Total fragmented tables: 166
-------- Security Recommendations
-------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics
-------------------------------------------------
[--] Up for: 7m 46s (79K q [169.689 qps], 16 conn, TX: 20M, RX: 6M)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 66.0M global + 3.6M per thread (350 max threads)
[OK] Maximum possible memory usage: 1.3G (13% of installed RAM)
[OK] Slow queries: 0% (0/79K)
[OK] Highest usage of available connections: 2% (10/350)
[OK] Key buffer size / total MyISAM indexes: 8.0M/92.0K
[OK] Key buffer hit rate: 100.0% (24 cached / 0 reads)
[OK] Query cache efficiency: 28.7% (7K cached / 25K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 844 sorts)
[!!] Joins performed without indexes: 35
[OK] Temporary tables created on disk: 1% (12 on disk / 936 total)
[!!] Thread cache hit rate: 37% (10 created / 16 connections)
[OK] Table cache hit rate: 92% (128 open / 138 opened)
[OK] Open file limit used: 0% (12/1K)
[OK] Table locks acquired immediately: 100% (7K immediate / 7K locks)
[!!] Connections aborted: 12%
[OK] InnoDB data size / buffer pool: 23.1M/24.0M
-------- Recommendations
-----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Your applications are not closing MySQL connections properly
Variables to adjust:
join_buffer_size (> 1.0M, or always use indexes with joins)
thread_cache_size (> 4)
Thanks for any advice.
Regards, Benoit.
Re: CS 4.0.0 - mysql server connections aborted
Posted by benoit lair <ku...@gmail.com>.
Hello Chiradeep,
Thanks for your response, i'm going to look this more precisely.
I'm going back when got more results.
Regards, Benoit.
2013/4/18 Chiradeep Vittal <Ch...@citrix.com>
> There should be a way for MySQL to log the connections that get aborted.
> At least it will tell you if it was the client that aborted the
> connection.
>
>
> On 4/18/13 6:16 AM, "benoit lair" <ku...@gmail.com> wrote:
>
> >Hello,
> >
> >
> >I'm using a cs 4.0.0 preproduction server onto centos 5.3.
> >
> >I have launched a mysqltuner.pl onto the mysql server of the mgmt server.
> >
> >So it gives me connections aborted.
> >
> >Have you an explication why it show this ? How can i troubleshoot this ?
> >
> >Here is my mysqltuner.pl output :
> >
> >[root@cloudstack-master-server ~]# ./mysqltuner.pl
> >
> > >> MySQLTuner 1.2.0 - Major Hayden <ma...@mhtx.net>
> > >> Bug reports, feature requests, and downloads at
> >http://mysqltuner.com/
> > >> Run with '--help' for additional options and output filtering
> >Please enter your MySQL administrative login: root
> >Please enter your MySQL administrative password:
> >
> >-------- General Statistics
> >--------------------------------------------------
> >[--] Skipped version check for MySQLTuner script
> >[OK] Currently running supported MySQL version 5.1.67-log
> >[OK] Operating on 64-bit architecture
> >
> >-------- Storage Engine Statistics
> >-------------------------------------------
> >[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
> >[--] Data in InnoDB tables: 23M (Tables: 166)
> >[--] Data in MEMORY tables: 125K (Tables: 2)
> >[!!] Total fragmented tables: 166
> >
> >-------- Security Recommendations
> >-------------------------------------------
> >[OK] All database users have passwords assigned
> >
> >-------- Performance Metrics
> >-------------------------------------------------
> >[--] Up for: 7m 46s (79K q [169.689 qps], 16 conn, TX: 20M, RX: 6M)
> >[--] Reads / Writes: 93% / 7%
> >[--] Total buffers: 66.0M global + 3.6M per thread (350 max threads)
> >[OK] Maximum possible memory usage: 1.3G (13% of installed RAM)
> >[OK] Slow queries: 0% (0/79K)
> >[OK] Highest usage of available connections: 2% (10/350)
> >[OK] Key buffer size / total MyISAM indexes: 8.0M/92.0K
> >[OK] Key buffer hit rate: 100.0% (24 cached / 0 reads)
> >[OK] Query cache efficiency: 28.7% (7K cached / 25K selects)
> >[OK] Query cache prunes per day: 0
> >[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 844 sorts)
> >[!!] Joins performed without indexes: 35
> >[OK] Temporary tables created on disk: 1% (12 on disk / 936 total)
> >[!!] Thread cache hit rate: 37% (10 created / 16 connections)
> >[OK] Table cache hit rate: 92% (128 open / 138 opened)
> >[OK] Open file limit used: 0% (12/1K)
> >[OK] Table locks acquired immediately: 100% (7K immediate / 7K locks)
> >[!!] Connections aborted: 12%
> >[OK] InnoDB data size / buffer pool: 23.1M/24.0M
> >
> >-------- Recommendations
> >-----------------------------------------------------
> >General recommendations:
> > Run OPTIMIZE TABLE to defragment tables for better performance
> > MySQL started within last 24 hours - recommendations may be inaccurate
> > Enable the slow query log to troubleshoot bad queries
> > Adjust your join queries to always utilize indexes
> > Your applications are not closing MySQL connections properly
> >Variables to adjust:
> > join_buffer_size (> 1.0M, or always use indexes with joins)
> > thread_cache_size (> 4)
> >
> >
> >Thanks for any advice.
> >
> >
> >Regards, Benoit.
>
>
Re: CS 4.0.0 - mysql server connections aborted
Posted by Chiradeep Vittal <Ch...@citrix.com>.
There should be a way for MySQL to log the connections that get aborted.
At least it will tell you if it was the client that aborted the
connection.
On 4/18/13 6:16 AM, "benoit lair" <ku...@gmail.com> wrote:
>Hello,
>
>
>I'm using a cs 4.0.0 preproduction server onto centos 5.3.
>
>I have launched a mysqltuner.pl onto the mysql server of the mgmt server.
>
>So it gives me connections aborted.
>
>Have you an explication why it show this ? How can i troubleshoot this ?
>
>Here is my mysqltuner.pl output :
>
>[root@cloudstack-master-server ~]# ./mysqltuner.pl
>
> >> MySQLTuner 1.2.0 - Major Hayden <ma...@mhtx.net>
> >> Bug reports, feature requests, and downloads at
>http://mysqltuner.com/
> >> Run with '--help' for additional options and output filtering
>Please enter your MySQL administrative login: root
>Please enter your MySQL administrative password:
>
>-------- General Statistics
>--------------------------------------------------
>[--] Skipped version check for MySQLTuner script
>[OK] Currently running supported MySQL version 5.1.67-log
>[OK] Operating on 64-bit architecture
>
>-------- Storage Engine Statistics
>-------------------------------------------
>[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
>[--] Data in InnoDB tables: 23M (Tables: 166)
>[--] Data in MEMORY tables: 125K (Tables: 2)
>[!!] Total fragmented tables: 166
>
>-------- Security Recommendations
>-------------------------------------------
>[OK] All database users have passwords assigned
>
>-------- Performance Metrics
>-------------------------------------------------
>[--] Up for: 7m 46s (79K q [169.689 qps], 16 conn, TX: 20M, RX: 6M)
>[--] Reads / Writes: 93% / 7%
>[--] Total buffers: 66.0M global + 3.6M per thread (350 max threads)
>[OK] Maximum possible memory usage: 1.3G (13% of installed RAM)
>[OK] Slow queries: 0% (0/79K)
>[OK] Highest usage of available connections: 2% (10/350)
>[OK] Key buffer size / total MyISAM indexes: 8.0M/92.0K
>[OK] Key buffer hit rate: 100.0% (24 cached / 0 reads)
>[OK] Query cache efficiency: 28.7% (7K cached / 25K selects)
>[OK] Query cache prunes per day: 0
>[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 844 sorts)
>[!!] Joins performed without indexes: 35
>[OK] Temporary tables created on disk: 1% (12 on disk / 936 total)
>[!!] Thread cache hit rate: 37% (10 created / 16 connections)
>[OK] Table cache hit rate: 92% (128 open / 138 opened)
>[OK] Open file limit used: 0% (12/1K)
>[OK] Table locks acquired immediately: 100% (7K immediate / 7K locks)
>[!!] Connections aborted: 12%
>[OK] InnoDB data size / buffer pool: 23.1M/24.0M
>
>-------- Recommendations
>-----------------------------------------------------
>General recommendations:
> Run OPTIMIZE TABLE to defragment tables for better performance
> MySQL started within last 24 hours - recommendations may be inaccurate
> Enable the slow query log to troubleshoot bad queries
> Adjust your join queries to always utilize indexes
> Your applications are not closing MySQL connections properly
>Variables to adjust:
> join_buffer_size (> 1.0M, or always use indexes with joins)
> thread_cache_size (> 4)
>
>
>Thanks for any advice.
>
>
>Regards, Benoit.