Re: Load problems with 3.23.51

Hi!

>>>>> "Jeremy" == Jeremy Zawodny writes:

Jeremy> On Sat, Jun 22, 2002 at 05:25:59PM -0700, Steven Roussey wrote:
>> Hi all,
>>
>> I have MySQL 3.23.47 running on our sever. I skipped 48 through 50 and
>> tried 51. No dice. It does not handle load, CPU and the load average go
>> through the roof. I'm using Red Hat Linux 7.2 and the official mysql
>> binaries. It appears to be slow to connect, causing 0.5 to 1.0 second
>> delay on connection. Using persistent connections from PHP does not make
>> much of a difference. I thought it might be the hostname lookup changes
>> so I chose skip-grant-tables. This doesn't actually skip the hostname
>> lookup though and had no effect.
>>
>> Most queries are shorter than 1 second so this problem causes
>> catastrophic problems by making queries last a multiple times longer,
>> which make the number of concurrent queries jump exponentially. This is
>> a bad thing. And sadly makes 3.23.51 unusable.
>>
>> Does anyone else note these types of issues?

Jeremy> As another data point for you, I've got 3.23.51 running on our master
Jeremy> quite well. The difference is that I built it from source (to get a
Jeremy> critical InnoDB patch). I don't recall which compiler the MySQL folks
Jeremy> used (and which glibc), but my source build used Debian Woody's gcc
Jeremy> 2.95.4.

We are using gcc 2.95.3 and a patched glibc, the later one that we
used in many builds before.

This is the first email I got that 3.23.51 would be slow.

Steven, could you try to run the MySQL benchmark suite on your machine
and post me the results ?

cd sql-bench
perl run-all-tests --log

The file I am interested in is the summary file named 'output/RUN-*'

Regards,
Monty

------------------------------------------------------------ ---------
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-thread12140@lists.mysql.com
To unsubscribe, e-mail

RE: Load problems with 3.23.51

I tried 'skip-name-resolve' but it had no impact. :( So it may have
nothing to do with name resolution.

Here are the results in file RUN-mysql-Linux_2.4.16_0.13smp_i686:


I'm going to run the tests on .47 next to see if there is any
difference.


Sincerely,
Steven Roussey
http://Network54.com/?pp=e

> -----Original Message-----
> From: Michael Widenius [mailto:monty@mysql.com]
> Sent: Monday, June 24, 2002 4:24 am
> To: mysql@lists.mysql.com
> Cc: Steven Roussey; bugs@lists.mysql.com
> Subject: Re: Load problems with 3.23.51
>
>
> Hi!
>
> >>>>> "Jeremy" == Jeremy Zawodny writes:
>
> Jeremy> On Sat, Jun 22, 2002 at 05:25:59PM -0700, Steven Roussey
wrote:
> >> Hi all,
> >>
> >> I have MySQL 3.23.47 running on our sever. I skipped 48 through 50
and
> >> tried 51. No dice. It does not handle load, CPU and the load
average go
> >> through the roof. I'm using Red Hat Linux 7.2 and the official
mysql
> >> binaries. It appears to be slow to connect, causing 0.5 to 1.0
second
> >> delay on connection. Using persistent connections from PHP does not
> make
> >> much of a difference. I thought it might be the hostname lookup
changes
> >> so I chose skip-grant-tables. This doesn't actually skip the
hostname
> >> lookup though and had no effect.
> >>
> >> Most queries are shorter than 1 second so this problem causes
> >> catastrophic problems by making queries last a multiple times
longer,
> >> which make the number of concurrent queries jump exponentially.
This is
> >> a bad thing. And sadly makes 3.23.51 unusable.
> >>
> >> Does anyone else note these types of issues?
>
> Jeremy> As another data point for you, I've got 3.23.51 running on our
> master
> Jeremy> quite well. The difference is that I built it from source (to
get
> a
> Jeremy> critical InnoDB patch). I don't recall which compiler the
MySQL
> folks
> Jeremy> used (and which glibc), but my source build used Debian
Woody's
> gcc
> Jeremy> 2.95.4.
>
> We are using gcc 2.95.3 and a patched glibc, the later one that we
> used in many builds before.
>
> This is the first email I got that 3.23.51 would be slow.
>
> Steven, could you try to run the MySQL benchmark suite on your machine
> and post me the results ?
>
> cd sql-bench
> perl run-all-tests --log
>
> The file I am interested in is the summary file named 'output/RUN-*'
>
> Regards,
> Monty



------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Load problems with 3.23.51

I tried the skip-name-resolve and it had no effect. So there goes my
hypothesis.

Here are the results from test:

Benchmark DBD suite: 2.14
Date of test: 2002-06-24 11:19:19
Running tests on: Linux 2.4.16-0.13smp i686
Arguments:
Comments:
Limits from:
Server version: MySQL 3.23.51 log

alter-table: Total time: 134 wallclock secs ( 0.35 usr 0.03 sys + 0.00
cusr 0.00 csys = 0.38 CPU)
ATIS: Total time: 15 wallclock secs ( 3.87 usr 1.62 sys + 0.00 cusr
0.00 csys = 5.49 CPU)
big-tables: Total time: 13 wallclock secs ( 3.39 usr 2.85 sys + 0.00
cusr 0.00 csys = 6.24 CPU)
connect: Total time: 45 wallclock secs (18.00 usr 8.45 sys + 0.00 cusr
0.00 csys = 26.45 CPU)
create: Total time: 119 wallclock secs ( 7.22 usr 2.13 sys + 0.00 cusr
0.00 csys = 9.35 CPU)
insert: Total time: 1162 wallclock secs (361.66 usr 103.25 sys + 0.00
cusr 0.00 csys = 464.91 CPU)
select: Total time: 599 wallclock secs (44.00 usr 8.76 sys + 0.00 cusr
0.00 csys = 52.76 CPU)
wisconsin: Total time: 9 wallclock secs ( 2.01 usr 1.19 sys + 0.00
cusr 0.00 csys = 3.20 CPU)

All 8 test executed successfully

Totals per operation:
Operation seconds usr sys cpu
tests
alter_table_add 75.00 0.24 0.01 0.25
992
alter_table_drop 56.00 0.07 0.00 0.07
496
connect 5.00 2.44 1.00 3.44
10000
connect+select_1_row 8.00 4.51 1.30 5.81
10000
connect+select_simple 7.00 3.55 1.25 4.80
10000
count 19.00 0.02 0.00 0.02
100
count_distinct 19.00 0.44 0.03 0.47
1000
count_distinct_2 20.00 0.33 0.06 0.39
1000
count_distinct_big 52.00 2.41 1.55 3.96
120
count_distinct_group 30.00 0.80 0.19 0.99
1000
count_distinct_group_on_key 18.00 0.29 0.04 0.33
1000
count_distinct_group_on_key_parts 30.00 0.76 0.21 0.97
1000
count_distinct_key_prefix 16.00 0.29 0.02 0.31
1000
count_group_on_key_parts 17.00 0.57 0.12 0.69
1000
count_on_key 171.00 13.22 1.67 14.89
50100
create+drop 6.00 1.70 0.54 2.24
10000
create_MANY_tables 76.00 2.28 0.42 2.70
10000
create_index 2.00 0.00 0.00 0.00
8
create_key+drop 7.00 1.54 0.59 2.13
10000
create_table 0.00 0.00 0.00 0.00
31
delete_all 4.00 0.00 0.00 0.00
12
delete_all_many_keys 22.00 0.01 0.00 0.01
1
delete_big 0.00 0.00 0.00 0.00
1
delete_big_many_keys 22.00 0.01 0.00 0.01
128
delete_key 2.00 0.36 0.26 0.62
10000
drop_index 1.00 0.00 0.00 0.00
8
drop_table 0.00 0.00 0.00 0.00
28
drop_table_when_MANY_tables 3.00 0.39 0.15 0.54
10000
insert 72.00 15.10 8.95 24.05
350768
insert_duplicates 16.00 3.27 2.62 5.89
100000
insert_key 60.00 7.62 2.77 10.39
100000
insert_many_fields 4.00 0.32 0.06 0.38
2000
insert_select_1_key 2.00 0.00 0.00 0.00
1
insert_select_2_keys 3.00 0.00 0.00 0.00
1
min_max 10.00 0.02 0.00 0.02
60
min_max_on_key 83.00 16.11 2.67 18.78
85000
multiple_value_insert 2.00 0.56 0.07 0.63
100000
order_by_big 21.00 6.83 4.43 11.26
10
order_by_big_key 13.00 7.25 4.73 11.98
10
order_by_big_key2 12.00 7.02 4.38 11.40
10
order_by_big_key_desc 13.00 7.29 4.50 11.79
10
order_by_big_key_diff 18.00 7.05 4.27 11.32
10
order_by_big_key_prefix 11.00 6.87 4.55 11.42
10
order_by_key2_diff 1.00 0.79 0.34 1.13
500
order_by_key_prefix 2.00 0.54 0.22 0.76
500
order_by_range 2.00 0.47 0.20 0.67
500
outer_join 20.00 0.00 0.00 0.00
10
outer_join_found 20.00 0.00 0.00 0.00
10
outer_join_not_found 13.00 0.00 0.00 0.00
500
outer_join_on_key 15.00 0.00 0.00 0.00
10
select_1_row 2.00 0.39 0.26 0.65
10000
select_2_rows 2.00 0.18 0.32 0.50
10000
select_big 12.00 7.13 4.47 11.60
80
select_big_str 19.00 6.42 3.65 10.07
10000
select_column+column 1.00 0.29 0.32 0.61
10000
select_diff_key 76.00 0.25 0.03 0.28
500
select_distinct 4.00 0.77 0.23 1.00
800
select_group 21.00 0.90 0.21 1.11
2911
select_group_when_MANY_tables 27.00 1.31 0.43 1.74
10000
select_join 1.00 0.22 0.07 0.29
100
select_key 77.00 44.41 8.29 52.70
200000
select_key2 85.00 42.51 6.77 49.28
200000
select_key2_return_key 82.00 40.64 6.48 47.12
200000
select_key2_return_prim 83.00 41.88 7.19 49.07
200000
select_key_prefix 87.00 43.34 7.64 50.98
200000
select_key_prefix_join 3.00 1.31 0.84 2.15
100
select_key_return_key 76.00 40.18 6.97 47.15
200000
select_many_fields 9.00 3.07 2.79 5.86
2000
select_query_cache 49.00 4.04 0.41 4.45
10000
select_query_cache2 49.00 3.85 0.33 4.18
10000
select_range 88.00 3.09 1.62 4.71
410
select_range_key2 10.00 3.91 0.74 4.65
25010
select_range_prefix 12.00 3.93 0.77 4.70
25010
select_simple 1.00 0.22 0.34 0.56
10000
select_simple_join 1.00 0.24 0.09 0.33
500
update_big 11.00 0.00 0.00 0.00
10
update_of_key 13.00 2.29 1.18 3.47
50000
update_of_key_big 7.00 0.02 0.01 0.03
501
update_of_primary_key_many_keys 11.00 0.03 0.00 0.03
256
update_with_key 58.00 10.90 7.23 18.13
300000
update_with_key_prefix 18.00 4.10 2.43 6.53
100000
wisc_benchmark 2.00 1.06 0.37 1.43
114
TOTALS 2098.00 436.22 126.65 562.87
2667247

Sincerely,
Steven Roussey
http://Network54.com/?pp=e

> -----Original Message-----
> From: Michael Widenius [mailto:monty@mysql.com]
> Sent: Monday, June 24, 2002 4:24 am
> To: mysql@lists.mysql.com
> Cc: Steven Roussey; bugs@lists.mysql.com
> Subject: Re: Load problems with 3.23.51
>
>
> Hi!
>
> >>>>> "Jeremy" == Jeremy Zawodny writes:
>
> Jeremy> On Sat, Jun 22, 2002 at 05:25:59PM -0700, Steven Roussey
wrote:
> >> Hi all,
> >>
> >> I have MySQL 3.23.47 running on our sever. I skipped 48 through 50
and
> >> tried 51. No dice. It does not handle load, CPU and the load
average go
> >> through the roof. I'm using Red Hat Linux 7.2 and the official
mysql
> >> binaries. It appears to be slow to connect, causing 0.5 to 1.0
second
> >> delay on connection. Using persistent connections from PHP does not
> make
> >> much of a difference. I thought it might be the hostname lookup
changes
> >> so I chose skip-grant-tables. This doesn't actually skip the
hostname
> >> lookup though and had no effect.
> >>
> >> Most queries are shorter than 1 second so this problem causes
> >> catastrophic problems by making queries last a multiple times
longer,
> >> which make the number of concurrent queries jump exponentially.
This is
> >> a bad thing. And sadly makes 3.23.51 unusable.
> >>
> >> Does anyone else note these types of issues?
>
> Jeremy> As another data point for you, I've got 3.23.51 running on our
> master
> Jeremy> quite well. The difference is that I built it from source (to
get
> a
> Jeremy> critical InnoDB patch). I don't recall which compiler the
MySQL
> folks
> Jeremy> used (and which glibc), but my source build used Debian
Woody's
> gcc
> Jeremy> 2.95.4.
>
> We are using gcc 2.95.3 and a patched glibc, the later one that we
> used in many builds before.
>
> This is the first email I got that 3.23.51 would be slow.
>
> Steven, could you try to run the MySQL benchmark suite on your machine
> and post me the results ?
>
> cd sql-bench
> perl run-all-tests --log
>
> The file I am interested in is the summary file named 'output/RUN-*'
>
> Regards,
> Monty



------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Load problems with 3.23.51

I will second this.

I attempted an upgrade from 3.23.50 to 3.23.51 and hit immediate
performance problems. I tried for about 20 minutes to see what was
causing it but to no avail. I reverted to 3.23.50.

At first I thought it was my fault so I decided not to report it
until I did some further research.

-M

On Mon, Jun 24, 2002 at 11:52:47AM -0700, Steven Roussey wrote:
> I tried 'skip-name-resolve' but it had no impact. :( So it may have
> nothing to do with name resolution.
>
> Here are the results in file RUN-mysql-Linux_2.4.16_0.13smp_i686:
>
>
> I'm going to run the tests on .47 next to see if there is any
> difference.
>
>
> Sincerely,
> Steven Roussey
> http://Network54.com/?pp=e
>
> > -----Original Message-----
> > From: Michael Widenius [mailto:monty@mysql.com]
> > Sent: Monday, June 24, 2002 4:24 am
> > To: mysql@lists.mysql.com
> > Cc: Steven Roussey; bugs@lists.mysql.com
> > Subject: Re: Load problems with 3.23.51
> >
> >
> > Hi!
> >
> > >>>>> "Jeremy" == Jeremy Zawodny writes:
> >
> > Jeremy> On Sat, Jun 22, 2002 at 05:25:59PM -0700, Steven Roussey
> wrote:
> > >> Hi all,
> > >>
> > >> I have MySQL 3.23.47 running on our sever. I skipped 48 through 50
> and
> > >> tried 51. No dice. It does not handle load, CPU and the load
> average go
> > >> through the roof. I'm using Red Hat Linux 7.2 and the official
> mysql
> > >> binaries. It appears to be slow to connect, causing 0.5 to 1.0
> second
> > >> delay on connection. Using persistent connections from PHP does not
> > make
> > >> much of a difference. I thought it might be the hostname lookup
> changes
> > >> so I chose skip-grant-tables. This doesn't actually skip the
> hostname
> > >> lookup though and had no effect.
> > >>
> > >> Most queries are shorter than 1 second so this problem causes
> > >> catastrophic problems by making queries last a multiple times
> longer,
> > >> which make the number of concurrent queries jump exponentially.
> This is
> > >> a bad thing. And sadly makes 3.23.51 unusable.
> > >>
> > >> Does anyone else note these types of issues?
> >
> > Jeremy> As another data point for you, I've got 3.23.51 running on our
> > master
> > Jeremy> quite well. The difference is that I built it from source (to
> get
> > a
> > Jeremy> critical InnoDB patch). I don't recall which compiler the
> MySQL
> > folks
> > Jeremy> used (and which glibc), but my source build used Debian
> Woody's
> > gcc
> > Jeremy> 2.95.4.
> >
> > We are using gcc 2.95.3 and a patched glibc, the later one that we
> > used in many builds before.
> >
> > This is the first email I got that 3.23.51 would be slow.
> >
> > Steven, could you try to run the MySQL benchmark suite on your machine
> > and post me the results ?
> >
> > cd sql-bench
> > perl run-all-tests --log
> >
> > The file I am interested in is the summary file named 'output/RUN-*'
> >
> > Regards,
> > Monty
>
>
>
> ------------------------------------------------------------ ---------
> 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
> To unsubscribe, e-mail
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>

--
Michael Bacarella | Netgraft Corporation
| 545 Eighth Ave #401
Systems Analysis | New York, NY 10018
Technical Support | 212 946-1038 | 917 670-6982
Managed Services | mbac@netgraft.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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Load problems with 3.23.51

On Mon, Jun 24, 2002 at 03:11:53PM -0400, Michael Bacarella wrote:
> I will second this.
>
> I attempted an upgrade from 3.23.50 to 3.23.51 and hit immediate
> performance problems. I tried for about 20 minutes to see what was
> causing it but to no avail. I reverted to 3.23.50.
>
> At first I thought it was my fault so I decided not to report it
> until I did some further research.

What OS are you using? And did you use the pre-built binaries or
build from source?
--
Jeremy D. Zawodny,
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936

MySQL 3.23.51: up 25 days, processed 549,473,907 queries (246/sec. avg)

------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Load problems with 3.23.51

On Mon, Jun 24, 2002 at 12:43:47PM -0700, Jeremy Zawodny wrote:
> > I will second this.
> >
> > I attempted an upgrade from 3.23.50 to 3.23.51 and hit immediate
> > performance problems. I tried for about 20 minutes to see what was
> > causing it but to no avail. I reverted to 3.23.50.
> >
> > At first I thought it was my fault so I decided not to report it
> > until I did some further research.
>
> What OS are you using? And did you use the pre-built binaries or
> build from source?

Stock Red Hat 7.2. Pre-built mysql-max binaries. InnoDB tables.

--
Michael Bacarella | Netgraft Corporation
| 545 Eighth Ave #401
Systems Analysis | New York, NY 10018
Technical Support | 212 946-1038 | 917 670-6982
Managed Services | mbac@netgraft.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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Load problems with 3.23.51

Hi!

>>>>> "Steven" == Steven Roussey writes:

Steven> I tried the skip-name-resolve and it had no effect. So there goes my
Steven> hypothesis.

Steven> Here are the results from test:

Steven> Benchmark DBD suite: 2.14
Steven> Date of test: 2002-06-24 11:19:19
Steven> Running tests on: Linux 2.4.16-0.13smp i686
Steven> Arguments:
Steven> Comments:
Steven> Limits from:
Steven> Server version: MySQL 3.23.51 log



Steven> TOTALS 2098.00 436.22 126.65 562.87

The above looks quite ok.

On a Intel Xeon 2M cache, 4x700 Mhz, 2G, key_buffer=16M, gcc 3.1
machine I get:

TOTALS 3399.00 664.19 179.00 843.19

(This is a newer benchmark version with some new tests, but still
comparable)

So at least there is nothing strange with the basic MySQL queries on
the machine.

Now there are two different possible problems:

- A problem with the thread library that causes a problem when using
many threads.
- Some query / specific feature that you use that is slower like
before. (For example a query that doesn't use indexes anymore).

Could you by any change check by using the slow query log if there is
some specific query that is causing problems ?

Another option is to run the 3.23.51 server for a while and when you
get a load problem do 'mysqladmin proc ext'. This command should show
us if MySQL is using more table scans than usual.

Regards,
Monty

------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php