MYSQL running 99% of CPU for more that 3min

Folks,

I have Mysql db server (4.0.12-nt) running on Windows 2000 partition that is
actually a VMWare - ESX Server virtual machine.
This VMWare machine has 524Mb of Ram allocated.
The set of the databases on this server is minimal, and my.cnf values are
set to "my.small" file.
Nevertheless the db server stalls a couple of times a week with the system
running up to 99% of CPU resources for mysqld alone for more than 3 min, and
mysql service has to be restarted.
I am trying to determine the cause of this. One of my "guesses" is that the
network connection to this machine is notoriously slow, and there are
network delays.

Does anyone know of mysql system issues that relate to the network outages?
What happens if the query is issued to mysql server and the network
connection terminates at that point - (the result set that suppose to be
sent back to the client does not reach the client)?
Sincerely,
Elena.



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: MYSQL running 99% of CPU for more that 3min

On 10/27/05, Elena C wrote:
> Folks,
>
> I have Mysql db server (4.0.12-nt) running on Windows 2000 partition that=
is

First of all, upgrade to the latest stable version *be careful with
upgrades from 4 to 4.1* this can be a bug fixed long ago, or not.

> actually a VMWare - ESX Server virtual machine.

Emulated? Hmmm, can't expect much from this kind of system.

> This VMWare machine has 524Mb of Ram allocated.
> The set of the databases on this server is minimal, and my.cnf values are
> set to "my.small" file.
> Nevertheless the db server stalls a couple of times a week with the syste=
m
> running up to 99% of CPU resources for mysqld alone for more than 3 min, =
and
> mysql service has to be restarted.

Could be so many things, could you post the configuration of the
virtual machine running it? Its hard to debug errors with a system
like this.

> I am trying to determine the cause of this. One of my "guesses" is that t=
he
> network connection to this machine is notoriously slow, and there are
> network delays.

I don't think so, that would be no reason for such a "hunger" for resources=
..

> Does anyone know of mysql system issues that relate to the network outage=
s?
> What happens if the query is issued to mysql server and the network
> connection terminates at that point - (the result set that suppose to be
> sent back to the client does not reach the client)?

The connection is closed after wait_timeout (variable) and a warning
is issued if you have log_warnings set.

> Sincerely,
> Elena.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

Re: MYSQL running 99% of CPU for more that 3min

Hi guys,

the VMWare machine has 512Mb of RAM and 2.5 Ghz processor and uses SCSI
discs. I do not have an access to the VMWare host - so I do not have an
exact config parameters.
It looks like ESX Server virtual machine allows for 1Gb network, but it's
possible that some packets are lost because of my client's firewall
settings.
Here are my mysql server status params:
**************
| back_log 50
| basedir C:\mysql\
| binlog_cache_size 32768
| bulk_insert_buffer_size 8388608
| character_set latin1
| character_sets latin1 big5 czech euc_kr gb2312 gbk
latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7
cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek
win1250 croat cp1257 latin5 |
| concurrent_insert ON
| connect_timeout 5
| convert_character_set
| datadir C:\mysql\data\
| delay_key_write ON
| delayed_insert_limit 100
| delayed_insert_timeout 300
| delayed_queue_size 1000
| flush OFF
| flush_time 1800
| ft_boolean_syntax + -><()~*:""&|
| ft_min_word_len 4
| ft_max_word_len 254
| ft_max_word_len_for_sort 20
| ft_stopword_file (built-in)
| have_bdb NO
| have_crypt NO
| have_innodb YES
| have_isam YES
| have_raid NO
| have_symlink YES
| have_openssl NO
| have_query_cache YES
| init_file
| innodb_additional_mem_pool_size | 1048576
| innodb_buffer_pool_size 8388608
| innodb_data_file_path ibdata1:10M:autoextend
| innodb_data_home_dir
| innodb_file_io_threads 4
| innodb_force_recovery 0
| innodb_thread_concurrency 8
| innodb_flush_log_at_trx_commit 0
| innodb_fast_shutdown ON
| innodb_flush_method
| innodb_lock_wait_timeout 50
| innodb_log_arch_dir .\
| innodb_log_archive OFF
| innodb_log_buffer_size 1048576
| innodb_log_file_size 5242880
| innodb_log_files_in_group 2
| innodb_log_group_home_dir .\
| innodb_mirrored_log_groups 1
| interactive_timeout 28800
| join_buffer_size 131072
| key_buffer_size 8388600
| language C:\mysql\share\english\
| large_files_support ON
| local_infile ON
| log OFF
| log_update OFF
| log_bin OFF
| log_slave_updates OFF
| log_slow_queries OFF
| log_warnings OFF
| long_query_time 10
| low_priority_updates OFF
| lower_case_table_names ON
| max_allowed_packet 1048576
| max_binlog_cache_size 4294967295
| max_binlog_size 1073741824
| max_connections 100
| max_connect_errors 10
| max_delayed_threads 20
| max_heap_table_size 16777216
| max_join_size 4294967295
| max_sort_length 1024
| max_user_connections 0
| max_tmp_tables 32
| max_write_lock_count 4294967295
| myisam_max_extra_sort_file_size | 268435456
| myisam_max_sort_file_size 2147483647
| myisam_recover_options OFF
| myisam_sort_buffer_size 8388608
| named_pipe OFF
| net_buffer_length 16384
| net_read_timeout 30
| net_retry_count 10
| net_write_timeout 60
| new OFF
| open_files_limit 0
| pid_file C:\mysql\data\SERV.pid
| log_error .\SERV.err
| port 3306
| protocol_version 10
| read_buffer_size 131072
| read_rnd_buffer_size 262144
| rpl_recovery_rank 0
| query_cache_limit 1048576
| query_cache_size 0
| query_cache_type ON
| server_id 0
| slave_net_timeout 3600
| skip_external_locking ON
| skip_networking OFF
| skip_show_database OFF
| slow_launch_time 2
| sort_buffer_size 2097144
| sql_mode 0
| table_cache 64
| table_type MYISAM
| thread_cache_size 0
| thread_stack 196608
| tx_isolation REPEATABLE-READ
| timezone Eastern Daylight Time
| tmp_table_size 33554432
| tmpdir C:\WINNT\TEMP\
| version 4.0.12-nt
| wait_timeout 28800
********************

On 10/27/05, Elena C wrote:
>Folks,
>
>I have Mysql db server (4.0.12-nt) running on Windows 2000 partition that
>is

First of all, upgrade to the latest stable version *be careful with
upgrades from 4 to 4.1* this can be a bug fixed long ago, or not.

>actually a VMWare - ESX Server virtual machine.

Emulated? Hmmm, can't expect much from this kind of system.

>This VMWare machine has 524Mb of Ram allocated.
>The set of the databases on this server is minimal, and my.cnf values are
>set to "my.small" file.
>Nevertheless the db server stalls a couple of times a week with the system
>running up to 99% of CPU resources for mysqld alone for more than 3 min,
>and
>mysql service has to be restarted.

Could be so many things, could you post the configuration of the
virtual machine running it? Its hard to debug errors with a system
like this.

>I am trying to determine the cause of this. One of my "guesses" is that the
>network connection to this machine is notoriously slow, and there are
>network delays.

I don't think so, that would be no reason for such a "hunger" for resources.

>Does anyone know of mysql system issues that relate to the network outages?
>What happens if the query is issued to mysql server and the network
>connection terminates at that point - (the result set that suppose to be
>sent back to the client does not reach the client)?

The connection is closed after wait_timeout (variable) and a warning
is issued if you have log_warnings set.

>Sincerely,
>Elena.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: MYSQL running 99% of CPU for more that 3min

------=_Part_48171_20214737.1130454831410
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Dumb question, but, are you using indices on your tables?

On 10/27/05, Elena C wrote:
>
> Hi guys,
>
> the VMWare machine has 512Mb of RAM and 2.5 Ghz processor and uses SCSI
> discs. I do not have an access to the VMWare host - so I do not have an
> exact config parameters.
> It looks like ESX Server virtual machine allows for 1Gb network, but it's
> possible that some packets are lost because of my client's firewall
> settings.
> Here are my mysql server status params:
> **************
> | back_log 50
> | basedir C:\mysql\
> | binlog_cache_size 32768
> | bulk_insert_buffer_size 8388608
> | character_set latin1
> | character_sets latin1 big5 czech euc_kr gb2312 gbk
> latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7
> cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek
> win1250 croat cp1257 latin5 |
> | concurrent_insert ON
> | connect_timeout 5
> | convert_character_set
> | datadir C:\mysql\data\
> | delay_key_write ON
> | delayed_insert_limit 100
> | delayed_insert_timeout 300
> | delayed_queue_size 1000
> | flush OFF
> | flush_time 1800
> | ft_boolean_syntax + -><()~*:""&|
> | ft_min_word_len 4
> | ft_max_word_len 254
> | ft_max_word_len_for_sort 20
> | ft_stopword_file (built-in)
> | have_bdb NO
> | have_crypt NO
> | have_innodb YES
> | have_isam YES
> | have_raid NO
> | have_symlink YES
> | have_openssl NO
> | have_query_cache YES
> | init_file
> | innodb_additional_mem_pool_size | 1048576
> | innodb_buffer_pool_size 8388608
> | innodb_data_file_path ibdata1:10M:autoextend
> | innodb_data_home_dir
> | innodb_file_io_threads 4
> | innodb_force_recovery 0
> | innodb_thread_concurrency 8
> | innodb_flush_log_at_trx_commit 0
> | innodb_fast_shutdown ON
> | innodb_flush_method
> | innodb_lock_wait_timeout 50
> | innodb_log_arch_dir .\
> | innodb_log_archive OFF
> | innodb_log_buffer_size 1048576
> | innodb_log_file_size 5242880
> | innodb_log_files_in_group 2
> | innodb_log_group_home_dir .\
> | innodb_mirrored_log_groups 1
> | interactive_timeout 28800
> | join_buffer_size 131072
> | key_buffer_size 8388600
> | language C:\mysql\share\english\
> | large_files_support ON
> | local_infile ON
> | log OFF
> | log_update OFF
> | log_bin OFF
> | log_slave_updates OFF
> | log_slow_queries OFF
> | log_warnings OFF
> | long_query_time 10
> | low_priority_updates OFF
> | lower_case_table_names ON
> | max_allowed_packet 1048576
> | max_binlog_cache_size 4294967295
> | max_binlog_size 1073741824
> | max_connections 100
> | max_connect_errors 10
> | max_delayed_threads 20
> | max_heap_table_size 16777216
> | max_join_size 4294967295
> | max_sort_length 1024
> | max_user_connections 0
> | max_tmp_tables 32
> | max_write_lock_count 4294967295
> | myisam_max_extra_sort_file_size | 268435456
> | myisam_max_sort_file_size 2147483647
> | myisam_recover_options OFF
> | myisam_sort_buffer_size 8388608
> | named_pipe OFF
> | net_buffer_length 16384
> | net_read_timeout 30
> | net_retry_count 10
> | net_write_timeout 60
> | new OFF
> | open_files_limit 0
> | pid_file C:\mysql\data\SERV.pid
> | log_error .\SERV.err
> | port 3306
> | protocol_version 10
> | read_buffer_size 131072
> | read_rnd_buffer_size 262144
> | rpl_recovery_rank 0
> | query_cache_limit 1048576
> | query_cache_size 0
> | query_cache_type ON
> | server_id 0
> | slave_net_timeout 3600
> | skip_external_locking ON
> | skip_networking OFF
> | skip_show_database OFF
> | slow_launch_time 2
> | sort_buffer_size 2097144
> | sql_mode 0
> | table_cache 64
> | table_type MYISAM
> | thread_cache_size 0
> | thread_stack 196608
> | tx_isolation REPEATABLE-READ
> | timezone Eastern Daylight Time
> | tmp_table_size 33554432
> | tmpdir C:\WINNT\TEMP\
> | version 4.0.12-nt
> | wait_timeout 28800
> ********************
>
> On 10/27/05, Elena C wrote:
> >Folks,
> >
> >I have Mysql db server (4.0.12-nt) running on Windows 2000 partition tha=
t
> >is
>
> First of all, upgrade to the latest stable version *be careful with
> upgrades from 4 to 4.1* this can be a bug fixed long ago, or not.
>
> >actually a VMWare - ESX Server virtual machine.
>
> Emulated? Hmmm, can't expect much from this kind of system.
>
> >This VMWare machine has 524Mb of Ram allocated.
> >The set of the databases on this server is minimal, and my.cnf values ar=
e
> >set to "my.small" file.
> >Nevertheless the db server stalls a couple of times a week with the
> system
> >running up to 99% of CPU resources for mysqld alone for more than 3 min,
> >and
> >mysql service has to be restarted.
>
> Could be so many things, could you post the configuration of the
> virtual machine running it? Its hard to debug errors with a system
> like this.
>
> >I am trying to determine the cause of this. One of my "guesses" is that
> the
> >network connection to this machine is notoriously slow, and there are
> >network delays.
>
> I don't think so, that would be no reason for such a "hunger" for
> resources.
>
> >Does anyone know of mysql system issues that relate to the network
> outages?
> >What happens if the query is issued to mysql server and the network
> >connection terminates at that point - (the result set that suppose to be
> >sent back to the client does not reach the client)?
>
> The connection is closed after wait_timeout (variable) and a warning
> is issued if you have log_warnings set.
>
> >Sincerely,
> >Elena.
>
> --
> Daniel da Veiga
> Computer Operator - RS - Brazil
> -----BEGIN GEEK CODE BLOCK-----
> Version: 3.1
> GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
> PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
> ------END GEEK CODE BLOCK------
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=3Drandy.lovin@gmail.co=
m
>
>


--
-Randy Lovin

------=_Part_48171_20214737.1130454831410--

Re: MYSQL running 99% of CPU for more that 3min

Yep, the indexes are in place. And, in the matter of fact, similar
installations are running in various locations for different clients on
various platforms. None of the other clients have this problem. (But none of
the other clients have VMWare machines too...). Because this customer has
VMWare implemented exencively in his data center and uses it internally - we
were bound to use this platform. I need some kind of a strong argument to
get us a dedicated server. But right now I am stuck evaluating the logs
trying to produce some kind of evidence that it's not mysql db server itself
that causes trouble.
Thanks again,
Elena.


>From: Randy Lovin
>To: Elena C
>CC: win32@lists.mysql.com
>Subject: Re: MYSQL running 99% of CPU for more that 3min
>Date: Thu, 27 Oct 2005 16:13:51 -0700
>
>Dumb question, but, are you using indices on your tables?
>
>On 10/27/05, Elena C wrote:
> >
> > Hi guys,
> >
> > the VMWare machine has 512Mb of RAM and 2.5 Ghz processor and uses SCSI
> > discs. I do not have an access to the VMWare host - so I do not have an
> > exact config parameters.
> > It looks like ESX Server virtual machine allows for 1Gb network, but
>it's
> > possible that some packets are lost because of my client's firewall
> > settings.
> > Here are my mysql server status params:
> > **************
> > | back_log 50
> > | basedir C:\mysql\
> > | binlog_cache_size 32768
> > | bulk_insert_buffer_size 8388608
> > | character_set latin1
> > | character_sets latin1 big5 czech euc_kr gb2312 gbk
> > latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7
> > cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek
> > win1250 croat cp1257 latin5 |
> > | concurrent_insert ON
> > | connect_timeout 5
> > | convert_character_set
> > | datadir C:\mysql\data\
> > | delay_key_write ON
> > | delayed_insert_limit 100
> > | delayed_insert_timeout 300
> > | delayed_queue_size 1000
> > | flush OFF
> > | flush_time 1800
> > | ft_boolean_syntax + -><()~*:""&|
> > | ft_min_word_len 4
> > | ft_max_word_len 254
> > | ft_max_word_len_for_sort 20
> > | ft_stopword_file (built-in)
> > | have_bdb NO
> > | have_crypt NO
> > | have_innodb YES
> > | have_isam YES
> > | have_raid NO
> > | have_symlink YES
> > | have_openssl NO
> > | have_query_cache YES
> > | init_file
> > | innodb_additional_mem_pool_size | 1048576
> > | innodb_buffer_pool_size 8388608
> > | innodb_data_file_path ibdata1:10M:autoextend
> > | innodb_data_home_dir
> > | innodb_file_io_threads 4
> > | innodb_force_recovery 0
> > | innodb_thread_concurrency 8
> > | innodb_flush_log_at_trx_commit 0
> > | innodb_fast_shutdown ON
> > | innodb_flush_method
> > | innodb_lock_wait_timeout 50
> > | innodb_log_arch_dir .\
> > | innodb_log_archive OFF
> > | innodb_log_buffer_size 1048576
> > | innodb_log_file_size 5242880
> > | innodb_log_files_in_group 2
> > | innodb_log_group_home_dir .\
> > | innodb_mirrored_log_groups 1
> > | interactive_timeout 28800
> > | join_buffer_size 131072
> > | key_buffer_size 8388600
> > | language C:\mysql\share\english\
> > | large_files_support ON
> > | local_infile ON
> > | log OFF
> > | log_update OFF
> > | log_bin OFF
> > | log_slave_updates OFF
> > | log_slow_queries OFF
> > | log_warnings OFF
> > | long_query_time 10
> > | low_priority_updates OFF
> > | lower_case_table_names ON
> > | max_allowed_packet 1048576
> > | max_binlog_cache_size 4294967295
> > | max_binlog_size 1073741824
> > | max_connections 100
> > | max_connect_errors 10
> > | max_delayed_threads 20
> > | max_heap_table_size 16777216
> > | max_join_size 4294967295
> > | max_sort_length 1024
> > | max_user_connections 0
> > | max_tmp_tables 32
> > | max_write_lock_count 4294967295
> > | myisam_max_extra_sort_file_size | 268435456
> > | myisam_max_sort_file_size 2147483647
> > | myisam_recover_options OFF
> > | myisam_sort_buffer_size 8388608
> > | named_pipe OFF
> > | net_buffer_length 16384
> > | net_read_timeout 30
> > | net_retry_count 10
> > | net_write_timeout 60
> > | new OFF
> > | open_files_limit 0
> > | pid_file C:\mysql\data\SERV.pid
> > | log_error .\SERV.err
> > | port 3306
> > | protocol_version 10
> > | read_buffer_size 131072
> > | read_rnd_buffer_size 262144
> > | rpl_recovery_rank 0
> > | query_cache_limit 1048576
> > | query_cache_size 0
> > | query_cache_type ON
> > | server_id 0
> > | slave_net_timeout 3600
> > | skip_external_locking ON
> > | skip_networking OFF
> > | skip_show_database OFF
> > | slow_launch_time 2
> > | sort_buffer_size 2097144
> > | sql_mode 0
> > | table_cache 64
> > | table_type MYISAM
> > | thread_cache_size 0
> > | thread_stack 196608
> > | tx_isolation REPEATABLE-READ
> > | timezone Eastern Daylight Time
> > | tmp_table_size 33554432
> > | tmpdir C:\WINNT\TEMP\
> > | version 4.0.12-nt
> > | wait_timeout 28800
> > ********************
> >
> > On 10/27/05, Elena C wrote:
> > >Folks,
> > >
> > >I have Mysql db server (4.0.12-nt) running on Windows 2000 partition
>that
> > >is
> >
> > First of all, upgrade to the latest stable version *be careful with
> > upgrades from 4 to 4.1* this can be a bug fixed long ago, or not.
> >
> > >actually a VMWare - ESX Server virtual machine.
> >
> > Emulated? Hmmm, can't expect much from this kind of system.
> >
> > >This VMWare machine has 524Mb of Ram allocated.
> > >The set of the databases on this server is minimal, and my.cnf values
>are
> > >set to "my.small" file.
> > >Nevertheless the db server stalls a couple of times a week with the
> > system
> > >running up to 99% of CPU resources for mysqld alone for more than 3
>min,
> > >and
> > >mysql service has to be restarted.
> >
> > Could be so many things, could you post the configuration of the
> > virtual machine running it? Its hard to debug errors with a system
> > like this.
> >
> > >I am trying to determine the cause of this. One of my "guesses" is that
> > the
> > >network connection to this machine is notoriously slow, and there are
> > >network delays.
> >
> > I don't think so, that would be no reason for such a "hunger" for
> > resources.
> >
> > >Does anyone know of mysql system issues that relate to the network
> > outages?
> > >What happens if the query is issued to mysql server and the network
> > >connection terminates at that point - (the result set that suppose to
>be
> > >sent back to the client does not reach the client)?
> >
> > The connection is closed after wait_timeout (variable) and a warning
> > is issued if you have log_warnings set.
> >
> > >Sincerely,
> > >Elena.
> >
> > --
> > Daniel da Veiga
> > Computer Operator - RS - Brazil
> > -----BEGIN GEEK CODE BLOCK-----
> > Version: 3.1
> > GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
> > PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
> > ------END GEEK CODE BLOCK------
> >
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: http://lists.mysql.com/win32?unsub=randy.lovin@gmail.com
> >
> >
>
>
>--
>-Randy Lovin



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org