Board index MyODBC

CREATE performance degradation from 4.0.17 -> 4.0.20

CREATE performance degradation from 4.0.17 -> 4.0.20

Post by Tinley, Jerem » Wed, 04 Aug 2004 06:59:19



We're upgrading from 3.23.58 to 4.0.20 and found that that although the
ALTER test results of sql-bench had been greatly improved, CREATE has
shown * performance degradation.  Just before needing to make the
decision to revert back to 3.23.58, we found a post here where someone
had a similar problem when using SAN storage.  We see the problem using
hardware RAID, shared storage or local SCSI disks.

The machine in question is a 3ghz, 4GB RAM, reiserfs.  The data and
application reside on local SCSI disks, 10k rpm. All installations are
the MySQL provided linux-binary (x86), Standard releases.

Here is an excerpt of sql-bench results:

Test                            A    B    C     D    E
------------------------------------------------------
alter_table_add                60    2    6     8    8
alter_table_drop               43    1    5     8    8
create+drop                    12   11   11   240  223
create_MANY_tables             10   11   10   220  228
create_index                    1    1    1     1    1
create_key+drop                14   15   15   231  221
create_table                    0    0    0     0    0
select_1_row                    0    8    8     8    9
select_2_rows                   1    9    9     9    9
select_column+column            1    9    9     9    9
select_group_when_MANY_tables   5    9   11    10   10

Column A is MySQL 3.23.58
Column B is MySQL 4.0.15
Column C is MySQL 4.0.16
Column D is MySQL 4.0.17
Column E is MySQL 4.0.20

The biggest problem is the create set.  That's a HUGE difference in the
exact same hardware.  Thoughts?

-J

--
MySQL General Mailing List
For list archives: http://www.veryComputer.com/

 
 
 
Top

CREATE performance degradation from 4.0.17 -> 4.0.20

Post by Jeremy Zawod » Thu, 12 Aug 2004 10:59:03




> >> We're upgrading from 3.23.58 to 4.0.20 and found that that although the
> >> ALTER test results of sql-bench had been greatly improved, CREATE has
> >> shown * performance degradation.  Just before needing to make the
> >> decision to revert back to 3.23.58, we found a post here where someone
> >> had a similar problem when using SAN storage.  We see the problem using
> >> hardware RAID, shared storage or local SCSI disks.

> > Yes.
> > Since 4.0.17 MySQL sync()'s after it created an .frm file (in
> > CREATE/ALTER TABLE).

> And note that the sync() call not only physically writes .frm file to disk, but
> also everything else which is in write cache. If the server is under load, sync()
> call may take seconds, tens of seconds or even hundreds of seconds.

> > As one usually doesn't create tables at the huge rate, it is not a
> > problem.
> > Unfortunately, it is apparently a problem for sql-bench :(

> Time to add a NO_SYNC option to CREATE TABLE, Sergei ? :)

Wouldn't it make more sense to use fsync() on just the .frm file?  Or
am I missing something here?

Jeremy
--
Jeremy D. Zawodny     |  Perl, Web, MySQL, Linux Magazine, Yahoo!

[book] High Performance MySQL -- http://www.veryComputer.com/

--
MySQL General Mailing List
For list archives: http://www.veryComputer.com/


 
 
 
Top

CREATE performance degradation from 4.0.17 -> 4.0.20

Post by Sergei Golubch » Thu, 12 Aug 2004 15:45:29


Hi!



> > > Since 4.0.17 MySQL sync()'s after it created an .frm file (in
> > > CREATE/ALTER TABLE).

> Wouldn't it make more sense to use fsync() on just the .frm file?  Or
> am I missing something here?

Nope, you are right.
MySQL does fsync(), not sync().

It was my mistake - I didn't know all these flavours of syncs so I
wrote sync() in my email :(

Regards,
Sergei

--
   __  ___     ___ ____  __

 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/  www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

 
 
 
Top

1. Solaris install of 4.0.20 - Only 20 connections allowed

All

Hardware: Solaris 4 cpu, 16G ram, 900MHz, ver 5.8
MySQL: 4.0.20 binary install, 32 bit version

Admin install, I maintain the my.cnf, and do db admin.

Installed a production db (10 million rows) with no
problems. However, can only open 20 or so connections
to mysqld. The error is:

  ERROR 1135: Can't create a new thread (error 11).
  If you are not out of available memory, you can
  consult the manual for a possible OS-dependent bug

The command "perror 11" returns "Resource temporarily
unavailable".

The my.cnf config is based on my-huge.cnf with the
key-buffer set to 4000m. The max number of connections
is set to 100. That's standard.

Looked at file descriptors using command "pfiles pid"
and it has over 1k on that process.

I get the error while using client mysql, or using the
normal client (java jdbc).

I have an older development solaris box with 4.0.18
installed (64 bit version) and it can do connections
up to 100 with no problems. This was confirmed with
by creating a java program and doing connections in
a loop.

Does anyone have any ideas as to what to check or what
else to do?

Kinda desperate

David

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

2. Please help

3. Stateless search from database (Return 20 and 20 records)

4. Custom Controls and databinding to a own class

5. 4.0.20 -> 4.1.9 Can't open file: 'Autor.ibd'

6. Slim Dual Stylus Released

7. <<<<<<<<<<< ODBC datasources with Web application >>>>>>>>>>>>><

8. NHL-Hockey 49,90 DM!!

9. problems compiling mysql 4.0.20 (master.pid was not created in 400 seconds)

10. Performance degradation over time

11. Performance Degradation

12. Performance degradation with InnoDB as compared to MyISAM


All times are UTC
Board index