MySQL ODBC Connector - Regarding the cpu utilization of mysqld

Author

Regarding the cpu utilization of mysqld

2005-09-26, 11:23 am

Hi,

When running a process which is a part of my apllication,
mysqld.exe is using 90%-95% of cpu utilization. Here I used mysqld for
starting the mysql server. For other processes in the application, CPU
utilization is very minimum. I used optimization (for order by, select
and like) in building the sql queries used by the process and modified
the following parameters in the my.ini file as

key_buffer =3D 64M
max_allowed_packet =3D 1M
sort_buffer_size =3D 4M
read_buffer_size =3D 4M
query_cache_size=3D 16M

I am using 256MB RAM. Are my above modifications are correct/useful for
minimizing the CPU utilization?. Even after this also mysqld is using
around 85% of CPU.

Are there any other ways for minimizing the cpu utilization for mysql
server.

Could you please suggest me the ways for decreasing the CPU utilization
for mysqld.exe to minimum.

Please help me in this.

Thanks,
Narasimha

Confidentiality Notice=0D

The information contained in this electronic message and any attachments to=
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or=
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or=
Mailadmin@wipro.com immediately
and destroy all copies of this message and any attachments.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2005-09-26, 11:23 am

--=_alternative 0058C67D85257088_=
Content-Type: text/plain; charset="US-ASCII"

<lakshmi.narasimharao@wipro.com> wrote on 09/26/2005 11:50:11 AM:

>
> Hi,
>
> When running a process which is a part of my apllication,
> mysqld.exe is using 90%-95% of cpu utilization. Here I used mysqld for
> starting the mysql server. For other processes in the application, CPU
> utilization is very minimum. I used optimization (for order by, select
> and like) in building the sql queries used by the process and modified
> the following parameters in the my.ini file as
>
> key_buffer = 64M
> max_allowed_packet = 1M
> sort_buffer_size = 4M
> read_buffer_size = 4M
> query_cache_size= 16M
>
> I am using 256MB RAM. Are my above modifications are correct/useful for
> minimizing the CPU utilization?. Even after this also mysqld is using
> around 85% of CPU.
>
>
> Are there any other ways for minimizing the cpu utilization for mysql
> server.
>
> Could you please suggest me the ways for decreasing the CPU utilization
> for mysqld.exe to minimum.
>
> Please help me in this.
>
> Thanks,
> Narasimha
>

Is it conceivable that the MySQL server could actually be that busy? How
many SQL statements are you processing per second? How much data is
transferring into and out of your MySQL server? What are some of the
queries appearing in your slow query log? What do the EXPLAINs if those
queries tell you? Besides MySQL, what else does that server host? What
operating system is on that server? How much memory have you allocated for
MySQL usage (some OS's allow for per-appication memory tuning)?

Depending on what else is going on or how much memory all of your other
applications/daemons have taken up, MySQL could be spending all of your
CPU time just paging data. 256MB is not very much memory to install for a
database server, especially a shared database server. May I suggest that
you add more RAM (at least upgrade to 1GB RAM, more if you can afford it).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 0058C67D85257088_=--

2005-09-27, 7:23 am

------ _=_NextPart_001_01C5
C350.B56C403F
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hi,
=0D
We are using the same set of query's what we re using in some
other process, where the mysqld CPU utilization is minimum. But for this
process particularly, mysqld is utilising more than 90 % of CPU. Even in
2GB RAM machine also, behaving same. We are using Windows XP/ 2000/ 2003
server. In all these it is behaving like this. Could any one of you
suggest me the way to handle this situation.
=0D
Thanks,
Narasimha

-----Original Message-----
From: SGreen@unimin.com & #91;mailto:SGreen@un
imin.com]=0D
Sent: Monday, September 26, 2005 9:43 PM
To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)
Cc: gleb.paharenko@ensita.net; mysql@lists.mysql.com
Subject: Re: Regarding the cpu utilization of mysqld

<lakshmi.narasimharao@wipro.com> wrote on 09/26/2005 11:50:11 AM:

>=0D
> Hi,
>=0D
> When running a process which is a part of my apllication,
> mysqld.exe is using 90%-95% of cpu utilization. Here I used mysqld for
> starting the mysql server. For other processes in the application, CPU
> utilization is very minimum. I used optimization (for order by, select
> and like) in building the sql queries used by the process and modified
> the following parameters in the my.ini file as
>=0D
> key_buffer =3D 64M
> max_allowed_packet =3D 1M
> sort_buffer_size =3D 4M
> read_buffer_size =3D 4M
> query_cache_size=3D 16M
>=0D
> I am using 256MB RAM. Are my above modifications are correct/useful
for
> minimizing the CPU utilization?. Even after this also mysqld is using
> around 85% of CPU.
>=0D
>=0D
> Are there any other ways for minimizing the cpu utilization for mysql
> server.
>=0D
> Could you please suggest me the ways for decreasing the CPU
utilization
> for mysqld.exe to minimum.
>=0D
> Please help me in this.
>=0D
> Thanks,
> Narasimha
>=0D

Is it conceivable that the MySQL server could actually be that busy? How
many SQL statements are you processing per second? How much data is
transferring into and out of your MySQL server? What are some of the
queries appearing in your slow query log? What do the EXPLAINs if those
queries tell you? Besides MySQL, what else does that server host? What
operating system is on that server? How much memory have you allocated
for MySQL usage (some OS's allow for per-appication memory tuning)?=0D

Depending on what else is going on or how much memory all of your other
applications/daemons have taken up, MySQL could be spending all of your
CPU time just paging data. 256MB is not very much memory to install for
a database server, especially a shared database server. May I suggest
that you add more RAM (at least upgrade to 1GB RAM, more if you can
afford it).=0D

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine=0D

Confidentiality Notice=0D

The information contained in this electronic message and any attachments to=
this message are intended
for the exclusive use of the addressee(s) and may contain confidential or=
privileged information. If
you are not the intended recipient, please notify the sender at Wipro or=
Mailadmin@wipro.com immediately
and destroy all copies of this message and any attachments.
------ _=_NextPart_001_01C5
C350.B56C403F--

SGreen@unimin.com

2005-09-27, 9:23 am

--=_alternative 00491B6485257089_=
Content-Type: text/plain; charset="US-ASCII"

<lakshmi.narasimharao@wipro.com> wrote on 09/27/2005 06:47:50 AM:

>
> Hi,
>
>
> We are using the same set of query's what we re using in some
> other process, where the mysqld CPU utilization is minimum. But for this
> process particularly, mysqld is utilising more than 90 % of CPU. Even in
> 2GB RAM machine also, behaving same. We are using Windows XP/ 2000/ 2003
> server. In all these it is behaving like this. Could any one of you
> suggest me the way to handle this situation.
>
>
> Thanks,
> Narasimha
>
> -----Original Message-----
> From: SGreen@unimin.com & #91;mailto:SGreen@un
imin.com]
>
> Sent: Monday, September 26, 2005 9:43 PM
> To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)
> Cc: gleb.paharenko@ensita.net; mysql@lists.mysql.com
> Subject: Re: Regarding the cpu utilization of mysqld
>
>
>
> <lakshmi.narasimharao@wipro.com> wrote on 09/26/2005 11:50:11 AM:
>
>
>
>
>
> for
>
>
>
> utilization
>
>
>
>
> Is it conceivable that the MySQL server could actually be that busy? How
> many SQL statements are you processing per second? How much data is
> transferring into and out of your MySQL server? What are some of the
> queries appearing in your slow query log? What do the EXPLAINs if those
> queries tell you? Besides MySQL, what else does that server host? What
> operating system is on that server? How much memory have you allocated
> for MySQL usage (some OS's allow for per-appication memory tuning)?
>
>
> Depending on what else is going on or how much memory all of your other
> applications/daemons have taken up, MySQL could be spending all of your
> CPU time just paging data. 256MB is not very much memory to install for
> a database server, especially a shared database server. May I suggest
> that you add more RAM (at least upgrade to 1GB RAM, more if you can
> afford it).
>
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>

If your MySQL statement load on the problem server is the same as the load
on another, similarly configured server and that the other server is not
loading the CPU, you should probably look outside of MySQL.

Check for anti-virus software, firewall packet screeners, disk change
monitors, or any other programs running on the problem box that are either
not running or are not configured the same way as they are on the "well
behaved" server. Your last response said that MySQL can handle the load
without maxing out the CPU, which tells me that MySQL is probably not the
problem this time but that something that is reacting to MySQL and
interfering with it probably is.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 00491B6485257089_=--

Sponsored Links