performance problems with mysql 3.23.44

hi everybody,

i've got strange performance problems. even if it is not a bug, someone m=
ight have an idea, what went wrong.

the problem:
i administer a webserver for a german soccer club. part of the homepage i=
s a liveticker.
when the team is having a match, people are able to go to the homepage an=
d have a look at
the current score. the database server works stable for medium load (both=
processors have about=20
70% cpu load), processlist shows all processes sleeping. but when the loa=
d rises, suddenly the server=20
blocks. processlist shows many processes with command 'query' or 'cleanin=
g' and state 'locked' (over 80 %).
the server becomes very, very slow and is no longer accessable, even on c=
onsole
the database server is not swapping. about 1.2gb of ram is used.
the database consists of 47 tables. the type of the database is MyISAM.

i found out that the problem might by=20

How-To-Repeat:

my situation is like the following:

1 server running apache 1.3.22 and php 4.1.2
1 server running the mysql server
hardware:=09dual pentium-III 1.4 GHz with 2GB ram
40 gb ide hdd
software:
mysql:=09mysql 3.23.44 (package from mysql.com)
kernel:=09Linux version 2.4.18-4GB-SMP (gcc version 2.95.3 20010315 (SuSE=
)) #3 SMP Wed May 15 02:17:41 CEST 2002
glibc 2.2.4

webserver and database server are connected via tcpip on a private net


thx in advance

malte


my.cnf:
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
# The following options will be passed to all MySQL clients
[client]
#password =3D your_password
port =3D 3306
socket =3D /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port =3D 3306
socket =3D /var/lib/mysql/mysql.sock
skip-locking
set-variable =3D key_buffer=3D384M
set-variable =3D max_allowed_packet=3D1M
set-variable =3D table_cache=3D2048
set-variable =3D sort_buffer=3D2M
set-variable =3D record_buffer=3D2M
set-variable =3D thread_cache=3D8
# Try number of CPU's*2 for thread_concurrency
set-variable =3D thread_concurrency=3D4
set-variable =3D myisam_sort_buffer_size=3D64M
set-variable =3D max_connections=3D6000

log-bin
server-id =3D 1

# Point the following paths to different dedicated disks
#tmpdir =3D /tmp/
#log-update =3D /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#set-variable =3D bdb_cache_size=3D384M
#set-variable =3D bdb_max_lock=3D100000

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path =3D ibdata1:2000M;ibdata2:2000M
#innodb_data_home_dir =3D /var/lib/mysql/
#innodb_log_group_home_dir =3D /var/lib/mysql/
#innodb_log_arch_dir =3D /var/lib/mysql/
#set-variable =3D innodb_mirrored_log_groups=3D1
#set-variable =3D innodb_log_files_in_group=3D3
#set-variable =3D innodb_log_file_size=3D5M
#set-variable =3D innodb_log_buffer_size=3D8M
#innodb_flush_log_at_trx_commit=3D1
#innodb_log_archive=3D0
#set-variable =3D innodb_buffer_pool_size=3D16M
#set-variable =3D innodb_additional_mem_pool_size=3D2M
#set-variable =3D innodb_file_io_threads=3D4
#set-variable =3D innodb_lock_wait_timeout=3D50

[mysqldump]
quick
set-variable =3D max_allowed_packet=3D16M

[mysql]
#no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable =3D key_buffer=3D256M
set-variable =3D sort_buffer=3D256M
set-variable =3D read_buffer=3D2M
set-variable =3D write_buffer=3D2M

[myisamchk]
set-variable =3D key_buffer=3D256M
set-variable =3D sort_buffer=3D256M
set-variable =3D read_buffer=3D2M
set-variable =3D write_buffer=3D2M

[mysqlhotcopy]
interactive-timeout
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

--=20
Wichmann Internet Services GmbH
Aussigstr. 6
38114 Braunschweig
Tel.: +49 531 5906912
Fax.: +49 531 5906959
mailto: mahrens@wichmann.net

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12364@lists.mysql.com
To unsubscribe, e-mail

performance problems with mysql 3.23.44

Hi!

Malte Ahrens writes:
> hi everybody,
>
> i've got strange performance problems. even if it is not a bug, someone might have an idea, what went wrong.
>
> the problem:
> i administer a webserver for a german soccer club. part of the homepage is a liveticker.
> when the team is having a match, people are able to go to the homepage and have a look at
> the current score. the database server works stable for medium load (both processors have about
> 70% cpu load), processlist shows all processes sleeping. but when the load rises, suddenly the server
> blocks. processlist shows many processes with command 'query' or 'cleaning' and state 'locked' (over 80 %).
> the server becomes very, very slow and is no longer accessable, even on console
> the database server is not swapping. about 1.2gb of ram is used.
> the database consists of 47 tables. the type of the database is MyISAM.

This could be a bug, or a heavy query that appears occassionally.

My suggestion to you is the following:

1. Please upgrade to 3.23.52, if possible. It contains a lot of bug
fixes since 3.23.44. I can't be sure it fixes your problem, but
at least prevents us from searching a possibly fixed bug. I have
seen similar report to this one in the past, but not being reported
with the recent versions of MySQL.

2. If the problem still occurs, please do the following:
Run 'mysqladmin -v processlist' and safe the output. If you
have problems with connecting, try running it with
'mysqladmin -v -i 1 proc >& /tmp/mysqladmin.output'

3. You said that the server becomes unaccessible. Please try
with MySQL user root, maybe you have all connections reserved up?

4. During the problem occurs, include output also from
a. mysqladmin variables
b. mysqladmin extended-status

Examine the output from mysqladmin processlist carefully during the
time the problem occurs. Look for queries which may lock up a
table. Look for 'LOCK TABLES', 'UPDATE ...' and 'DELETE ...' type of
queries and see if there is one thread which prevents other threads
from doing a quick select by locking a table.

You can also try running mysqld with --log-slow-queries enabled and
see if you have any heavy queries that could be optimized away.

Regards,

- Jani

For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Jani Tolonen
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12365@lists.mysql.com
To unsubscribe, e-mail

Re: performance problems with mysql 3.23.44

i found some hints in the mysql documentation, which may solve my problem=
in=20
chapter 2.6.1 it is told that on high load server you have to patch the=20
glibc. but as far as i understand it, the binary distribution uses a patc=
hed=20
glibc. or am i wrong? do i have to compile mysql on my own?

regards

malte

> Hi!
>
> Malte Ahrens writes:
> > hi everybody,
> >
> > i've got strange performance problems. even if it is not a bug, someo=
ne
> > might have an idea, what went wrong.
> >
> > the problem:
> > i administer a webserver for a german soccer club. part of the homepa=
ge
> > is a liveticker. when the team is having a match, people are able to =
go
> > to the homepage and have a look at the current score. the database se=
rver
> > works stable for medium load (both processors have about 70% cpu load=
),
> > processlist shows all processes sleeping. but when the load rises,
> > suddenly the server blocks. processlist shows many processes with com=
mand
> > 'query' or 'cleaning' and state 'locked' (over 80 %). the server beco=
mes
> > very, very slow and is no longer accessable, even on console the data=
base
> > server is not swapping. about 1.2gb of ram is used. the database cons=
ists
> > of 47 tables. the type of the database is MyISAM.
>
> This could be a bug, or a heavy query that appears occassionally.
>
> My suggestion to you is the following:
>
> 1. Please upgrade to 3.23.52, if possible. It contains a lot of bug
> fixes since 3.23.44. I can't be sure it fixes your problem, but
> at least prevents us from searching a possibly fixed bug. I have
> seen similar report to this one in the past, but not being reported
> with the recent versions of MySQL.
>
> 2. If the problem still occurs, please do the following:
> Run 'mysqladmin -v processlist' and safe the output. If you
> have problems with connecting, try running it with
> 'mysqladmin -v -i 1 proc >& /tmp/mysqladmin.output'
>
> 3. You said that the server becomes unaccessible. Please try
> with MySQL user root, maybe you have all connections reserved up?
>
> 4. During the problem occurs, include output also from
> a. mysqladmin variables
> b. mysqladmin extended-status
>
> Examine the output from mysqladmin processlist carefully during the
> time the problem occurs. Look for queries which may lock up a
> table. Look for 'LOCK TABLES', 'UPDATE ...' and 'DELETE ...' type of
> queries and see if there is one thread which prevents other threads
> from doing a quick select by locking a table.
>
> You can also try running mysqld with --log-slow-queries enabled and
> see if you have any heavy queries that could be optimized away.
>
> Regards,
>
> - Jani
>
> For technical support contracts, visit https://order.mysql.com/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Jani Tolonen
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
> <___/ www.mysql.com
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread12365@lists.mysql.com
> To unsubscribe, e-mail

--=20
Wichmann Internet Services GmbH
Aussigstr. 6
38114 Braunschweig
Tel.: +49 531 5906912
Fax.: +49 531 5906959
mailto: mahrens@wichmann.net

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12389@lists.mysql.com
To unsubscribe, e-mail

Re: performance problems with mysql 3.23.44

Hi!

>>>>> "Malte" == Malte Ahrens writes:

Malte> i found some hints in the mysql documentation, which may solve my problem. in
Malte> chapter 2.6.1 it is told that on high load server you have to patch the
Malte> glibc. but as far as i understand it, the binary distribution uses a patched
Malte> glibc. or am i wrong? do i have to compile mysql on my own?

Yes, if you are using our binary distributions for Linux x86 then
MySQL uses a patched glibc library and you don't have to compile a
version of your own.

Note that MySQL 3.23.51 used a non optimal glibc version so if you
have this MySQL version you should upgrade to 3.23.52 or downgrade to
3.23.49

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12397@lists.mysql.com
To unsubscribe, e-mail

Re: performance problems with mysql 3.23.44

Malte Ahrens writes:
> i found some hints in the mysql documentation, which may solve my problem. in
> chapter 2.6.1 it is told that on high load server you have to patch the
> glibc. but as far as i understand it, the binary distribution uses a patched
> glibc. or am i wrong? do i have to compile mysql on my own?
>
> regards
>
> malte
>
> --
> Wichmann Internet Services GmbH
> Aussigstr. 6
> 38114 Braunschweig
> Tel.: +49 531 5906912
> Fax.: +49 531 5906959
> mailto: mahrens@wichmann.net

Yes, our binaries use patched glibc.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12404@lists.mysql.com
To unsubscribe, e-mail

Re: performance problems with mysql 3.23.44

Malte Ahrens writes:
> hi everybody,
>
> i've got strange performance problems. even if it is not a bug, someone might have an idea, what went wrong.
>
> the problem:
> i administer a webserver for a german soccer club. part of the homepage is a liveticker.
> when the team is having a match, people are able to go to the homepage and have a look at
> the current score. the database server works stable for medium load (both processors have about
> 70% cpu load), processlist shows all processes sleeping. but when the load rises, suddenly the server
> blocks. processlist shows many processes with command 'query' or 'cleaning' and state 'locked' (over 80 %).
> the server becomes very, very slow and is no longer accessable, even on console
> the database server is not swapping. about 1.2gb of ram is used.
> the database consists of 47 tables. the type of the database is MyISAM.
>

[skip]

> --
> Wichmann Internet Services GmbH
> Aussigstr. 6
> 38114 Braunschweig
> Tel.: +49 531 5906912
> Fax.: +49 531 5906959
> mailto: mahrens@wichmann.net
>

Hi!

What you describe above does not look like a bug, but more like query
waiting for locks set by slow queries.

If you see a state when no query is running and all are waiting for
some lock , then report it to this list, with a repeatable test case.

Otherwise, consider general mailing list or consider getting support.

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi

Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12408@lists.mysql.com
To unsubscribe, e-mail