Re: CREATE performance degradation from 4.0.17 - 4.0.20
Hi! On Aug 10, Jeremy Zawodny wrote: On Wed, Aug 04, 2004 at 04:32:42PM +0300, Egor Egorov wrote: 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 -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
On Wed, Aug 04, 2004 at 04:32:42PM +0300, Egor Egorov wrote: Sergei Golubchik [EMAIL PROTECTED] wrote: 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 nasty 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! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
Sergei Golubchik [EMAIL PROTECTED] wrote: 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 nasty 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 ? :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CREATE performance degradation from 4.0.17 - 4.0.20
-Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 04, 2004 8:33 AM To: [EMAIL PROTECTED] Subject: Re: CREATE performance degradation from 4.0.17 - 4.0.20 Sergei Golubchik [EMAIL PROTECTED] wrote: 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 ? :) Setting the sync_frm option to 0 (that was added in 4.0.18) corrected the issue we saw with the test as a temporary solution. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
Hi! On Aug 04, Egor Egorov wrote: Sergei Golubchik [EMAIL PROTECTED] wrote: 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 nasty 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 ? :) There is --skip-sync-frm option. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
On Mon, 2004-08-02 at 14:57, Tinley, Jeremy wrote: 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 nasty 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. Hi, This is the known issue. In MySQL 4.0.17 calling fsync() on frm files was added during table creation. This was done so create table is more durable if used with transactional tables such as Innodb. It however affects all tables at this point. In most cases new tables are created rarely so it is not the problem, if it is for you case --skip-sync-frm option can be used to avoid such behavior. On other hand B-C changes for some tests surprise me. Are the results stable if you repeat the run ? In some cases especially for short tests deviation can be pretty large. Here is an excerpt of sql-bench results: TestABC DE -- alter_table_add6026 88 alter_table_drop 4315 88 create+drop12 11 11 240 223 create_MANY_tables 10 11 10 220 228 create_index111 11 create_key+drop14 15 15 231 221 create_table000 00 select_1_row088 89 select_2_rows 199 99 select_column+column199 99 select_group_when_MANY_tables 59 1110 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 -- Peter Zaitsev, Senior Support Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CREATE performance degradation from 4.0.17 - 4.0.20
On other hand B-C changes for some tests surprise me. Are the results stable if you repeat the run ? In some cases especially for short tests deviation can be pretty large. The results are stable, sadly. The bigger surprise was the select deviation from 3 to 4, but so far, in application testiong, it doesn't seem to be an issue. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
Hi! On Aug 02, Tinley, Jeremy wrote: 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 nasty 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). 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 :( Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CREATE performance degradation from 4.0.17 - 4.0.20
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 nasty 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: TestABC DE -- alter_table_add6026 88 alter_table_drop 4315 88 create+drop12 11 11 240 223 create_MANY_tables 10 11 10 220 228 create_index111 11 create_key+drop14 15 15 231 221 create_table000 00 select_1_row088 89 select_2_rows 199 99 select_column+column199 99 select_group_when_MANY_tables 59 1110 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://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
