Professional Web Applications Themes

MySQL 5 on Windoze extremely SLOW compared to MS SQL Server! - MySQL

This is NOT a troll post, and I am long time MySQL user. I suspect that I made some up with configuration. MySQL is a lot slower than MS SQL server, both running on Windows XP. I have a specific task that occurs based on an event and involves about 45 queries. INSERTs, UPDATES, a couple SELECTS. Nothing special. I keep track of how long each query takes, using a low resolution system timer (0.014 sec resolution). The queries take about 1.6 second in MySQL and almost no time at all with MS SQL server (about 0.07 seconds). Our tables ...

  1. #1

    Default MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    This is NOT a troll post, and I am long time MySQL user. I suspect that I
    made some up with configuration. MySQL is a lot slower than MS SQL
    server, both running on Windows XP.

    I have a specific task that occurs based on an event and involves
    about 45 queries. INSERTs, UPDATES, a couple SELECTS. Nothing special.

    I keep track of how long each query takes, using a low resolution
    system timer (0.014 sec resolution).

    The queries take about 1.6 second in MySQL and almost no time at all
    with MS SQL server (about 0.07 seconds).

    Our tables are NOT large, and while some queries are big (a lot of
    fields mentioned in UPDATE), they are nothing out of the ordinary,
    just a few dozen fields.

    I am completely stumped and even EMBARRASSED, as I was the person who
    promoted MySQL.

    Any ideas?

    I will provide any requested information.

    thanks

    i

    Ignoramus8392 Guest

  2. #2

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    Ignoramus8392 <ignoramus8392NOSPAM.8392.invalid> wrote:
    > This is NOT a troll post, and I am long time MySQL user. I suspect that I
    > made some up with configuration. MySQL is a lot slower than MS SQL
    > server, both running on Windows XP.
    >
    > I have a specific task that occurs based on an event and involves
    > about 45 queries. INSERTs, UPDATES, a couple SELECTS. Nothing special.
    >
    > I keep track of how long each query takes, using a low resolution
    > system timer (0.014 sec resolution).
    >
    > The queries take about 1.6 second in MySQL and almost no time at all
    > with MS SQL server (about 0.07 seconds).
    >
    > Our tables are NOT large, and while some queries are big (a lot of
    > fields mentioned in UPDATE), they are nothing out of the ordinary,
    > just a few dozen fields.
    >
    > I am completely stumped and even EMBARRASSED, as I was the person who
    > promoted MySQL.
    >
    > Any ideas?
    >
    > I will provide any requested information.
    >
    > thanks
    >
    > i
    >
    >
    You should be embarrassed! It's been proven in multiple benchmarks on
    different platforms that MySQL performs much faster than SQLServer all other
    factors being equal.

    Send your my.cnf or my.ini file or actually post it right here! I bet there
    is a bunch of stuff that can be done to it to make it run faster.
    --
    -lark

    lark Guest

  3. #3

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    Ignoramus8392 wrote:
    >
    > Any ideas?
    >
    Switch to Oracle.

    Best regards.
    Phil Guest

  4. #4

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    On Thu, 17 Aug 2006 14:04:37 +0200, Phil <localhostlocaldomain> wrote:
    > Ignoramus8392 wrote:
    >>
    >> Any ideas?
    >>
    >
    > Switch to Oracle.
    >
    > Best regards.
    Well... Someone asked me, in response to my original post, to post my
    my.ini and so on.

    I did that and received no reply at all. I know that no one owes me
    anything, but it is disappointing.

    Meanwhile, MySQL on Windows is as slow as it was and we are leaning
    towards MS SQL Server.

    If anyone is willing to take a look at my configuration file, let me
    know what you want and I will post them again.

    i

    Ignoramus23953 Guest

  5. #5

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    > This is NOT a troll post, and I am long time MySQL user. I suspect that I
    > made some up with configuration. MySQL is a lot slower than MS SQL
    > server, both running on Windows XP.
    If you are a long time MySQL user, you probalby know the EXPLAIN
    command. Does the EXPLAIN commands say anything special (such as not
    using indexes)?

    Which MySQL version are you using?

    If you are using joins, do the key fields that make up a join have
    exactly the same data type?
    > I have a specific task that occurs based on an event and involves
    > about 45 queries. INSERTs, UPDATES, a couple SELECTS. Nothing special.
    >
    > I keep track of how long each query takes, using a low resolution
    > system timer (0.014 sec resolution).
    How do you send the commands to the server (ODBC, PHP library, ...)? Do
    you send them one by one or in a batch?
    > The queries take about 1.6 second in MySQL and almost no time at all
    > with MS SQL server (about 0.07 seconds).
    Is there any difference in the tables and/or the queries in MySQL as
    opposed to SQL server? What engine (MyISAM, InnoDB,...) are the tables
    in MySQL?
    > Our tables are NOT large, and while some queries are big (a lot of
    > fields mentioned in UPDATE), they are nothing out of the ordinary,
    > just a few dozen fields.
    >
    > I am completely stumped and even EMBARRASSED, as I was the person who
    > promoted MySQL.
    >
    > Any ideas?
    >
    > I will provide any requested information.
    Don't give up yet... Any database has its preferred way of being
    addressed. Programming, for instance, MS-Access, is totally different
    from programming MySQL. And there are also differences between versions
    of the same database brand. So let's just see what we can do here.

    Best regards
    Dikkie Dik Guest

  6. #6

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    On Thu, 17 Aug 2006 15:05:59 +0200, Dikkie Dik <nospamnospam.org> wrote:
    >> This is NOT a troll post, and I am long time MySQL user. I suspect that I
    >> made some up with configuration. MySQL is a lot slower than MS SQL
    >> server, both running on Windows XP.
    >
    > If you are a long time MySQL user, you probalby know the EXPLAIN
    > command. Does the EXPLAIN commands say anything special (such as not
    > using indexes)?
    >
    > Which MySQL version are you using?
    MySQL 5. I am attaching the SQL statements and my.ini at the bottom of
    my post.

    > If you are using joins, do the key fields that make up a join have
    > exactly the same data type?
    No joins.
    >> I have a specific task that occurs based on an event and involves
    >> about 45 queries. INSERTs, UPDATES, a couple SELECTS. Nothing special.
    >>
    >> I keep track of how long each query takes, using a low resolution
    >> system timer (0.014 sec resolution).
    >
    > How do you send the commands to the server (ODBC, PHP library, ...)? Do
    > you send them one by one or in a batch?
    MySQL C Library.
    >> The queries take about 1.6 second in MySQL and almost no time at all
    >> with MS SQL server (about 0.07 seconds).
    >
    > Is there any difference in the tables and/or the queries in MySQL as
    > opposed to SQL server?
    none whatsoever
    > What engine (MyISAM, InnoDB,...) are the tables in MySQL?
    InnoDB
    >> Our tables are NOT large, and while some queries are big (a lot of
    >> fields mentioned in UPDATE), they are nothing out of the ordinary,
    >> just a few dozen fields.
    >>
    >> I am completely stumped and even EMBARRASSED, as I was the person who
    >> promoted MySQL.
    >>
    >> Any ideas?
    >>
    >> I will provide any requested information.
    >
    > Don't give up yet... Any database has its preferred way of being
    > addressed. Programming, for instance, MS-Access, is totally different
    > from programming MySQL. And there are also differences between versions
    > of the same database brand. So let's just see what we can do here.
    13:41:29.593 Time=0 Sql=SELECT name, id FROM system WHERE ( name = 'trade' )
    13:41:29.640 Time=0.047 Sql=UPDATE system SET name='trade', id=2809 WHERE name = 'trade'
    13:41:29.687 Time=0 Sql=SELECT name, id FROM system WHERE ( name = 'logged' )
    13:41:29.703 Time=0.016 Sql=UPDATE system SET name='logged', id=8896 WHERE name = 'logged'
    13:41:29.734 Time=0.031 Sql=INSERT INTO logged ( id, target, type, visible, externalid, description ) VALUES ( 8896, -120, 80, 1, 2809, 'ACTION=1 VISIBLE=0 TRADE=QDK,-1,C,1.00000000,0.00000000,XYZ2,fakefixbroker,C,200 6,8,19,?,35.00000000,BELZBRG,4,VendorId=47;Externa lId=ExecID13:41:29.265;OrderId=19,2809,,08/10/2006,13:41:29.203,21,0,0,0,0,0,0,1' )
    13:41:29.734 Time=0 Sql=SELECT name, id FROM system WHERE ( name = 'logged' )
    13:41:29.765 Time=0.032 Sql=UPDATE system SET name='logged', id=8897 WHERE name = 'logged'
    13:41:29.796 Time=0.031 Sql=INSERT INTO logged ( id, target, type, visible, externalid, description ) VALUES ( 8897, -121, 80, 1, 2809, 'ACTION=1 TRADE=QDK,-1,C,1.00000000,0.00000000,XYZ2,fakefixbroker,C,200 6,8,19,?,35.00000000,BELZBRG,4,0,1' )
    13:41:29.796 Time=0 Sql=SELECT name, id FROM system WHERE ( name = 'logged' )
    13:41:29.859 Time=0.062 Sql=UPDATE system SET name='logged', id=8898 WHERE name = 'logged'
    13:41:29.890 Time=0.032 Sql=INSERT INTO logged ( id, target, type, visible, externalid, description ) VALUES ( 8898, -122, 80, 1, 2809, 'ACTION=1 VISIBLE=0 TRADE=QDK,-1,C,1.00000000,0.00000000,XYZ2,fakefixbroker,C,200 6,8,19,?,35.00000000,BELZBRG,4' )
    13:41:29.921 Time=0.031 Sql=UPDATE position SET id=1918, account=21, islong=1, quantity=0, qty=127, value=3448.89000000, isoption=1, iscall=1, ident=87647, sid=1503, sname='C', ssymbol='', sdate=13379, sstrike=35.00000000, rdate=0, expid=5529, isdaily=1, settle=0.00000000, position=0, quantities=51, quantity1=0, quantity2=0, quantity3=0, quantity4=0, quantity5=0, quantity6=0, quantity7=0, quantity8=0, quantity9=0, quantity10=0, quantity11=0, quantity12=0, quantity13=0, quantity14=0, quantity15=0, quantity16=0, quantity17=0, quantity18=0, quantity19=0, quantity20=0, quantity21=0, quantity22=0, quantity23=0, quantity24=0, quantity25=0, quantity26=0, quantity27=0, quantity28=0, quantity29=0, quantity30=0, quantity31=0, quantity32=0, quantity33=0, quantity34=0, quantity35=0, quantity36=0, quantity37=0, quantity38=0, quantity39=0, quantity40=0, quantity41=0, quantity42=0, quantity43=0, quantity44=0, quantity45=0, quantity46=0, quantity47=0, quantity48=0, quantity49=0, quantity50=0, quantity51=0, qty1=0, qty2=0, qty3=0, qty4=0, qty5=0, qty6=0, qty7=0, qty8=0, qty9=0, qty10=0, qty11=0, qty12=0, qty13=0, qty14=0, qty15=0, qty16=0, qty17=0, qty18=0, qty19=0, qty20=0, qty21=0, qty22=0, qty23=0, qty24=0, qty25=0, qty26=0, qty27=0, qty28=0, qty29=0, qty30=0, qty31=0, qty32=0, qty33=0, qty34=0, qty35=0, qty36=0, qty37=0, qty38=0, qty39=0, qty40=0, qty41=0, qty42=0, qty43=0, qty44=0, qty45=0, qty46=0, qty47=0, qty48=0, qty49=0, qty50=0, qty51=127, value1=0.00000000, value2=0.00000000, value3=0.00000000, value4=0.00000000, value5=0.00000000, value6=0.00000000, value7=0.00000000, value8=0.00000000, value9=0.00000000, value10=0.00000000, value11=0.00000000, value12=0.00000000, value13=0.00000000, value14=0.00000000, value15=0.00000000, value16=0.00000000, value17=0.00000000, value18=0.00000000, value19=0.00000000, value20=0.00000000, value21=0.00000000, value22=0.00000000, value23=0.00000000, value24=0.00000000, value25=0.00000000, value26=0.00000000, value27=0.00000000, value28=0.00000000, value29=0.00000000, value30=0.00000000, value31=0.00000000, value32=0.00000000, value33=0.00000000, value34=0.00000000, value35=0.00000000, value36=0.00000000, value37=0.00000000, value38=0.00000000, value39=0.00000000, value40=0.00000000, value41=0.00000000, value42=0.00000000, value43=0.00000000, value44=0.00000000, value45=0.00000000, value46=0.00000000, value47=0.00000000, value48=0.00000000, value49=0.00000000, value50=0.00000000, value51=3448.89000000, filler_100_1=0, filler_100_2=0, dfiller_100_1=0.00000000, dfiller_100_2=0.00000000, lfiller_100_1='', lfiller_100_2='' WHERE id = 1918
    13:41:30.062 Time=0.031 Sql=UPDATE logged SET visible=0 WHERE id = 8896
    13:41:30.140 Time=0.016 Sql=SELECT name, id FROM system WHERE ( name = 'logged' )
    13:41:30.187 Time=0.046 Sql=UPDATE system SET name='logged', id=8899 WHERE name = 'logged'
    13:41:30.234 Time=0.047 Sql=INSERT INTO logged ( id, target, type, visible, externalid, description ) VALUES ( 8899, -120, 80, 1, 2809, 'ACTION=3 VISIBLE=0 TRADE=QDK,-1,C,1.00000000,0.00000000,XYZ2,fakefixbroker,C,200 6,8,19,?,35.00000000,BELZBRG,4,VendorId=47;Externa lId=ExecID13:41:29.265;OrderId=19,2809,,08/10/2006,13:41:29.203,21,0,0,0,0,0,0,1' )
    13:41:30.234 Time=0 Sql=SELECT name, id FROM system WHERE ( name = 'logged' )
    13:41:30.390 Time=0.157 Sql=UPDATE system SET name='logged', id=8900 WHERE name = 'logged'
    13:41:30.437 Time=0.046 Sql=INSERT INTO logged ( id, target, type, visible, externalid, description ) VALUES ( 8900, -121, 80, 1, 2809, 'ACTION=3 TRADE=QDK,-1,C,1.00000000,0.00000000,XYZ2,fakefixbroker,C,200 6,8,19,?,35.00000000,BELZBRG,4,0,1' )
    13:41:30.437 Time=0 Sql=SELECT name, id FROM system WHERE ( name = 'logged' )
    13:41:30.484 Time=0.047 Sql=UPDATE system SET name='logged', id=8901 WHERE name = 'logged'
    13:41:30.515 Time=0.032 Sql=INSERT INTO logged ( id, target, type, visible, externalid, description ) VALUES ( 8901, -122, 80, 1, 2809, 'ACTION=3 VISIBLE=0 TRADE=QDK,-1,C,1.00000000,0.00000000,XYZ2,fakefixbroker,C,200 6,8,19,?,35.00000000,BELZBRG,4' )
    13:41:30.546 Time=0.031 Sql=UPDATE position SET id=1918, account=21, islong=1, quantity=0, qty=128, value=3449.89000000, isoption=1, iscall=1, ident=87647, sid=1503, sname='C', ssymbol='', sdate=13379, sstrike=35.00000000, rdate=0, expid=5529, isdaily=1, settle=0.00000000, position=0, quantities=51, quantity1=0, quantity2=0, quantity3=0, quantity4=0, quantity5=0, quantity6=0, quantity7=0, quantity8=0, quantity9=0, quantity10=0, quantity11=0, quantity12=0, quantity13=0, quantity14=0, quantity15=0, quantity16=0, quantity17=0, quantity18=0, quantity19=0, quantity20=0, quantity21=0, quantity22=0, quantity23=0, quantity24=0, quantity25=0, quantity26=0, quantity27=0, quantity28=0, quantity29=0, quantity30=0, quantity31=0, quantity32=0, quantity33=0, quantity34=0, quantity35=0, quantity36=0, quantity37=0, quantity38=0, quantity39=0, quantity40=0, quantity41=0, quantity42=0, quantity43=0, quantity44=0, quantity45=0, quantity46=0, quantity47=0, quantity48=0, quantity49=0, quantity50=0, quantity51=0, qty1=0, qty2=0, qty3=0, qty4=0, qty5=0, qty6=0, qty7=0, qty8=0, qty9=0, qty10=0, qty11=0, qty12=0, qty13=0, qty14=0, qty15=0, qty16=0, qty17=0, qty18=0, qty19=0, qty20=0, qty21=0, qty22=0, qty23=0, qty24=0, qty25=0, qty26=0, qty27=0, qty28=0, qty29=0, qty30=0, qty31=0, qty32=0, qty33=0, qty34=0, qty35=0, qty36=0, qty37=0, qty38=0, qty39=0, qty40=0, qty41=0, qty42=0, qty43=0, qty44=0, qty45=0, qty46=0, qty47=0, qty48=0, qty49=0, qty50=0, qty51=128, value1=0.00000000, value2=0.00000000, value3=0.00000000, value4=0.00000000, value5=0.00000000, value6=0.00000000, value7=0.00000000, value8=0.00000000, value9=0.00000000, value10=0.00000000, value11=0.00000000, value12=0.00000000, value13=0.00000000, value14=0.00000000, value15=0.00000000, value16=0.00000000, value17=0.00000000, value18=0.00000000, value19=0.00000000, value20=0.00000000, value21=0.00000000, value22=0.00000000, value23=0.00000000, value24=0.00000000, value25=0.00000000, value26=0.00000000, value27=0.00000000, value28=0.00000000, value29=0.00000000, value30=0.00000000, value31=0.00000000, value32=0.00000000, value33=0.00000000, value34=0.00000000, value35=0.00000000, value36=0.00000000, value37=0.00000000, value38=0.00000000, value39=0.00000000, value40=0.00000000, value41=0.00000000, value42=0.00000000, value43=0.00000000, value44=0.00000000, value45=0.00000000, value46=0.00000000, value47=0.00000000, value48=0.00000000, value49=0.00000000, value50=0.00000000, value51=3449.89000000, filler_100_1=0, filler_100_2=0, dfiller_100_1=0.00000000, dfiller_100_2=0.00000000, lfiller_100_1='', lfiller_100_2='' WHERE id = 1918
    13:41:30.578 Time=0 Sql=SELECT name, id FROM system WHERE ( name = 'logged' )
    13:41:30.609 Time=0.031 Sql=UPDATE system SET name='logged', id=8902 WHERE name = 'logged'
    13:41:30.656 Time=0.047 Sql=INSERT INTO logged ( id, target, type, visible, externalid, description ) VALUES ( 8902, -120, 80, 1, 2809, 'ACTION=4 VISIBLE=0 TRADE=QDK,-1,C,1.00000000,0.00000000,XYZ2,fakefixbroker,C,200 6,8,19,?,35.00000000,BELZBRG,4,VendorId=47;Externa lId=ExecID13:41:29.265;OrderId=19,2809,35001288,08/10/2006,13:41:29.203,21,0,0,0,0,0,0,1' )
    13:41:30.656 Time=0 Sql=SELECT name, id FROM system WHERE ( name = 'logged' )
    13:41:30.671 Time=0.016 Sql=UPDATE system SET name='logged', id=8903 WHERE name = 'logged'
    13:41:30.703 Time=0.031 Sql=INSERT INTO logged ( id, target, type, visible, externalid, description ) VALUES ( 8903, -122, 80, 1, 2809, 'ACTION=4 VISIBLE=0 TRADE=QDK,-1,C,1.00000000,0.00000000,XYZ2,fakefixbroker,C,200 6,8,19,?,35.00000000,BELZBRG,4' )
    13:41:30.750 Time=0.047 Sql=UPDATE position SET id=1918, account=21, islong=1, quantity=0, qty=127, value=3448.89000000, isoption=1, iscall=1, ident=87647, sid=1503, sname='C', ssymbol='', sdate=13379, sstrike=35.00000000, rdate=0, expid=5529, isdaily=1, settle=0.00000000, position=0, quantities=51, quantity1=0, quantity2=0, quantity3=0, quantity4=0, quantity5=0, quantity6=0, quantity7=0, quantity8=0, quantity9=0, quantity10=0, quantity11=0, quantity12=0, quantity13=0, quantity14=0, quantity15=0, quantity16=0, quantity17=0, quantity18=0, quantity19=0, quantity20=0, quantity21=0, quantity22=0, quantity23=0, quantity24=0, quantity25=0, quantity26=0, quantity27=0, quantity28=0, quantity29=0, quantity30=0, quantity31=0, quantity32=0, quantity33=0, quantity34=0, quantity35=0, quantity36=0, quantity37=0, quantity38=0, quantity39=0, quantity40=0, quantity41=0, quantity42=0, quantity43=0, quantity44=0, quantity45=0, quantity46=0, quantity47=0, quantity48=0, quantity49=0, quantity50=0, quantity51=0, qty1=0, qty2=0, qty3=0, qty4=0, qty5=0, qty6=0, qty7=0, qty8=0, qty9=0, qty10=0, qty11=0, qty12=0, qty13=0, qty14=0, qty15=0, qty16=0, qty17=0, qty18=0, qty19=0, qty20=0, qty21=0, qty22=0, qty23=0, qty24=0, qty25=0, qty26=0, qty27=0, qty28=0, qty29=0, qty30=0, qty31=0, qty32=0, qty33=0, qty34=0, qty35=0, qty36=0, qty37=0, qty38=0, qty39=0, qty40=0, qty41=0, qty42=0, qty43=0, qty44=0, qty45=0, qty46=0, qty47=0, qty48=0, qty49=0, qty50=0, qty51=127, value1=0.00000000, value2=0.00000000, value3=0.00000000, value4=0.00000000, value5=0.00000000, value6=0.00000000, value7=0.00000000, value8=0.00000000, value9=0.00000000, value10=0.00000000, value11=0.00000000, value12=0.00000000, value13=0.00000000, value14=0.00000000, value15=0.00000000, value16=0.00000000, value17=0.00000000, value18=0.00000000, value19=0.00000000, value20=0.00000000, value21=0.00000000, value22=0.00000000, value23=0.00000000, value24=0.00000000, value25=0.00000000, value26=0.00000000, value27=0.00000000, value28=0.00000000, value29=0.00000000, value30=0.00000000, value31=0.00000000, value32=0.00000000, value33=0.00000000, value34=0.00000000, value35=0.00000000, value36=0.00000000, value37=0.00000000, value38=0.00000000, value39=0.00000000, value40=0.00000000, value41=0.00000000, value42=0.00000000, value43=0.00000000, value44=0.00000000, value45=0.00000000, value46=0.00000000, value47=0.00000000, value48=0.00000000, value49=0.00000000, value50=0.00000000, value51=3448.89000000, filler_100_1=0, filler_100_2=0, dfiller_100_1=0.00000000, dfiller_100_2=0.00000000, lfiller_100_1='', lfiller_100_2='' WHERE id = 1918
    13:41:30.812 Time=0.046 Sql=UPDATE logged SET visible=0 WHERE id = 8899
    13:41:30.875 Time=0.063 Sql=UPDATE logged SET visible=0 WHERE id = 8902


    # MySQL Server Instance Configuration File
    # ----------------------------------------------------------------------
    # Generated by the MySQL Server Instance Configuration Wizard
    #
    #
    # Installation Instructions
    # ----------------------------------------------------------------------
    #
    # On Linux you can copy this file to /etc/my.cnf to set global options,
    # mysql-data-dir/my.cnf to set server-specific options
    # (localstatedir for this installation) or to
    # ~/.my.cnf to set user-specific options.
    #
    # On Windows you should keep this file in the installation directory
    # of your server (e.g. C:\Program Files\MySQL\MySQL Server 4.1). To
    # make sure the server reads the config file use the startup option
    # "--defaults-file".
    #
    # To run run the server from the command line, execute this in a
    # command line shell, e.g.
    # mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini"
    #
    # To install the server as a Windows service manually, execute this in a
    # command line shell, e.g.
    # mysqld --install MySQL41 --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini"
    #
    # And then execute this in a command line shell to start the server, e.g.
    # net start MySQL41
    #
    #
    # Guildlines for editing this file
    # ----------------------------------------------------------------------
    #
    # In this file, you can use all long options that the program supports.
    # If you want to know the options a program supports, start the program
    # with the "--help" option.
    #
    # More detailed information about the individual options can also be
    # found in the manual.
    #
    #
    # CLIENT SECTION
    # ----------------------------------------------------------------------
    #
    # The following options will be read by MySQL client applications.
    # Note that only client applications shipped by MySQL are guaranteed
    # to read this section. If you want your own MySQL client program to
    # honor these values, you need to specify it as an option during the
    # MySQL client library initialization.
    #
    [client]

    port=3306

    [mysql]

    default-character-set=latin1


    # SERVER SECTION
    # ----------------------------------------------------------------------
    #
    # The following options will be read by the MySQL Server. Make sure that
    # you have installed the server correctly (see above) so it reads this
    # file.
    #
    [mysqld]

    # The TCP/IP Port the MySQL Server will listen on
    port=3306


    #Path to installation directory. All paths are usually resolved relative to this.
    basedir="C:/Program Files/MySQL/MySQL Server 5.0/"

    #Path to the database root
    datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"

    # The default character set that will be used when a new schema or table is
    # created and no character set is defined
    default-character-set=latin1

    # The default storage engine that will be used when create new tables when
    default-storage-engine=INNODB

    # Set the SQL mode to strict
    sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_E NGINE_SUBSTITUTION"

    # The maximum amount of concurrent sessions the MySQL server will
    # allow. One of these connections will be reserved for a user with
    # SUPER privileges to allow the administrator to login even if the
    # connection limit has been reached.
    max_connections=100

    # Query cache is used to cache SELECT results and later return them
    # without actual executing the same query once again. Having the query
    # cache enabled may result in significant speed improvements, if your
    # have a lot of identical queries and rarely changing tables. See the
    # "Qcache_lowmem_prunes" status variable to check if the current value
    # is high enough for your load.
    # Note: In case your tables change very often or if your queries are
    # textually different every time, the query cache may result in a
    # slowdown instead of a performance improvement.
    query_cache_size=40M

    # The number of open tables for all threads. Increasing this value
    # increases the number of file descriptors that mysqld requires.
    # Therefore you have to make sure to set the amount of open files
    # allowed to at least 4096 in the variable "open-files-limit" in
    # section [mysqld_safe]
    table_cache=256

    # Maximum size for internal (in-memory) temporary tables. If a table
    # grows larger than this value, it is automatically converted to disk
    # based table This limitation is for a single table. There can be many
    # of them.
    tmp_table_size=18M


    # How many threads we should keep in a cache for reuse. When a client
    # disconnects, the client's threads are put in the cache if there aren't
    # more than thread_cache_size threads from before. This greatly reduces
    # the amount of thread creations needed if you have a lot of new
    # connections. (Normally this doesn't give a notable performance
    # improvement if you have a good thread implementation.)
    thread_cache_size=8

    #*** MyISAM Specific options

    # The maximum size of the temporary file MySQL is allowed to use while
    # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
    # If the file-size would be bigger than this, the index will be created
    # through the key cache (which is slower).
    myisam_max_sort_file_size=100G

    # If the temporary file used for fast index creation would be bigger
    # than using the key cache by the amount specified here, then prefer the
    # key cache method. This is mainly used to force long character keys in
    # large tables to use the slower key cache method to create the index.
    myisam_max_extra_sort_file_size=100G

    # If the temporary file used for fast index creation would be bigger
    # than using the key cache by the amount specified here, then prefer the
    # key cache method. This is mainly used to force long character keys in
    # large tables to use the slower key cache method to create the index.
    myisam_sort_buffer_size=35M

    # Size of the Key Buffer, used to cache index blocks for MyISAM tables.
    # Do not set it larger than 30% of your available memory, as some memory
    # is also required by the OS to cache rows. Even if you're not using
    # MyISAM tables, you should still set it to 8-64M as it will also be
    # used for internal temporary disk tables.
    key_buffer_size=100M

    # Size of the buffer used for doing full table scans of MyISAM tables.
    # Allocated per thread, if a full scan is needed.
    read_buffer_size=64K
    read_rnd_buffer_size=256K

    # This buffer is allocated when MySQL needs to rebuild the index in
    # REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
    # into an empty table. It is allocated per thread so be careful with
    # large settings.
    sort_buffer_size=1024k


    #*** INNODB Specific options ***


    # Use this option if you have a MySQL server with InnoDB support enabled
    # but you do not plan to use it. This will save memory and disk space
    # and speed up some things.
    #skip-innodb

    # Additional memory pool that is used by InnoDB to store metadata
    # information. If InnoDB requires more memory for this purpose it will
    # start to allocate it from the OS. As this is fast enough on most
    # recent operating systems, you normally do not need to change this
    # value. SHOW INNODB STATUS will display the current amount used.
    innodb_additional_mem_pool_size=21M

    # If set to 1, InnoDB will flush (fsync) the transaction logs to the
    # disk at each commit, which offers full ACID behavior. If you are
    # willing to compromise this safety, and you are running small
    # transactions, you may set this to 0 or 2 to reduce disk I/O to the
    # logs. Value 0 means that the log is only written to the log file and
    # the log file flushed to disk approximately once per second. Value 2
    # means the log is written to the log file at each commit, but the log
    # file is only flushed to disk approximately once per second.
    innodb_flush_log_at_trx_commit=1

    # The size of the buffer InnoDB uses for buffering log data. As soon as
    # it is full, InnoDB will have to flush it to disk. As it is flushed
    # once per second anyway, it does not make sense to have it very large
    # (even with long transactions).
    innodb_log_buffer_size=1M

    # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
    # row data. The bigger you set this the less disk I/O is needed to
    # access data in tables. On a dedicated database server you may set this
    # parameter up to 80% of the machine physical memory size. Do not set it
    # too large, though, because competition of the physical memory may
    # cause paging in the operating system. Note that on 32bit systems you
    # might be limited to 2-3.5G of user level memory per process, so do not
    # set it too high.
    innodb_buffer_pool_size=147M

    # Size of each log file in a log group. You should set the combined size
    # of log files to about 25%-100% of your buffer pool size to avoid
    # unneeded buffer pool flush activity on log file overwrite. However,
    # note that a larger logfile size will increase the time needed for the
    # recovery process.
    innodb_log_file_size=24M

    # Number of threads allowed inside the InnoDB kernel. The optimal value
    # depends highly on the application, hardware as well as the OS
    # scheduler properties. A too high value may lead to thread thrashing.
    innodb_thread_concurrency=10

    Ignoramus23953 Guest

  7. #7

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    At first glance: you do have an index on system.name, don't you?

    Can you post the output of an EXPLAIN command of a SELECT query?

    So, the output of
    EXPLAIN SELECT name, id FROM system WHERE ( name = 'trade' )

    I also see a lot of duplication. It should be possible to write a stored
    procedure for getting the next id from the system table for a given
    name. In this case, a lot of parsing can be skipped on the server side.

    Taking a look at the my.ini file, nothing seems wrong with that.

    Best regards
    Dikkie Dik Guest

  8. #8

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    On Thu, 17 Aug 2006 15:40:35 +0200, Dikkie Dik <nospamnospam.org> wrote:
    > At first glance: you do have an index on system.name, don't you?
    yes

    CREATE TABLE system ( name CHAR(32) NOT NULL UNIQUE, id INTEGER )
    > Can you post the output of an EXPLAIN command of a SELECT query?
    it says

    1 SIMPLE system ref
    > So, the output of
    > EXPLAIN SELECT name, id FROM system WHERE ( name = 'trade' )
    >
    > I also see a lot of duplication. It should be possible to write a stored
    > procedure for getting the next id from the system table for a given
    > name. In this case, a lot of parsing can be skipped on the server side.
    Yeah. It is not great SQL. (meaning that some queries could be
    eliminated).

    That is beside the point though. Identical SQL runs dramatically
    faster on MS SQL Server.
    > Taking a look at the my.ini file, nothing seems wrong with that.
    I also have several ini files there, how can I be sure that my.ini is the one mysql server looks at?

    /cygdrive/c/program files/MySQL/MySQL Server 5.0 ==>ls -lrt *.ini
    -rwxrwx---+ 1 Administrators SYSTEM 13493 Oct 10 2005 my-template.ini
    -rwxrwx---+ 1 Administrators SYSTEM 2545 Dec 28 2005 my-small.ini
    -rwxrwx---+ 1 Administrators SYSTEM 5044 Dec 28 2005 my-medium.ini
    -rwxrwx---+ 1 Administrators SYSTEM 5039 Dec 28 2005 my-large.ini
    -rwxrwx---+ 1 Administrators SYSTEM 21412 Dec 28 2005 my-innodb-heavy-4G.ini
    -rwxrwx---+ 1 Administrators SYSTEM 5063 Dec 28 2005 my-huge.ini
    -rwx------+ 1 Igor None 9257 Aug 10 13:22 my.ini

    Could it be that I am using a wrong connection method, named pipe vs. socket or whatever?

    i

    Ignoramus23953 Guest

  9. #9

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    Ignoramus23953 <ignoramus23953NOSPAM.23953.invalid> wrote:
    >> How do you send the commands to the server (ODBC, PHP library, ...)? Do
    >> you send them one by one or in a batch?
    >
    > MySQL C Library.
    Do you connect via named pipe or TCP? The pipe should be faster.
    >>> The queries take about 1.6 second in MySQL and almost no time at all
    >>> with MS SQL server (about 0.07 seconds).
    >>
    >> Is there any difference in the tables and/or the queries in MySQL as
    >> opposed to SQL server?
    >
    > none whatsoever
    >
    >> What engine (MyISAM, InnoDB,...) are the tables in MySQL?
    >
    > InnoDB
    [queries]

    Looking at your 32 queries, I observe:

    1. there are 8 queries executing in 0 time (read: less than 1 tick)
    these are all SELECTs

    2. most queries take 1(3), 2(10), 3(8) or 4(2) ticks. Those are all
    INSERT or UPDATE statements. There is one SELECT taking 1 tick,
    however this is not significant because you use a low resolution
    timer.

    3. there is one UPDATE, taking 10 ticks. Not sure whether this is
    significant. The same UPDATE went through in 3 ticks before.


    Conclusions:

    1. there are no exceptionally slow queries. Indexes seem to be fine.
    I'd also say this is not typical database workload. Single threaded,
    simple queries, no JOINs.

    2. the "slow" queries are all of the writing type. Since you use InnoDB
    and no transactions, the transaction log has to be flushed right
    after each such query. Depending on how fast and (otherwise) busy
    your disk is, you pay a penalty of 10-20ms extra delay per query!


    Solutions:

    1. you might want to try the same workload on MyISAM tables. I would
    expect that to be *much* faster.

    2. if you want to stay with InnoDB (I guess you want) - then you should
    turn off AUTO_COMMIT and group your SQL statements in proper
    transactions. Keep in mind that each COMMIT costs you 20ms penalty.

    Alternatively/additionally you could change the
    innodb_flush_log_at_trx_commit=1 parameter in my.cnf. But please
    consult the manual [1] before you do that.

    3. to further speedup MySQL you should consider to connect to the
    server via shared memory [2]. This should be significantly faster
    than named pipes or the network stack. Also you may want to use
    prepared statements to avoid the overhead of the SQL pr.


    [1] [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html[/url]
    [2] [url]http://dev.mysql.com/doc/refman/5.0/en/features.html[/url]


    HTH, XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  10. #10

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    On Thu, 17 Aug 2006 17:07:17 +0200, Axel Schwenke <axel.schwenkegmx.de> wrote:
    > Ignoramus23953 <ignoramus23953NOSPAM.23953.invalid> wrote:
    >
    >>> How do you send the commands to the server (ODBC, PHP library, ...)? Do
    >>> you send them one by one or in a batch?
    >>
    >> MySQL C Library.
    >
    > Do you connect via named pipe or TCP? The pipe should be faster.
    I am sure that I use TCP, I am open to using named pipe, though. How
    would I do it using mysql_real_connect?
    >>>> The queries take about 1.6 second in MySQL and almost no time at all
    >>>> with MS SQL server (about 0.07 seconds).
    >>>
    >>> Is there any difference in the tables and/or the queries in MySQL as
    >>> opposed to SQL server?
    >>
    >> none whatsoever
    >>
    >>> What engine (MyISAM, InnoDB,...) are the tables in MySQL?
    >>
    >> InnoDB
    >
    > [queries]
    >
    > Looking at your 32 queries, I observe:
    >
    > 1. there are 8 queries executing in 0 time (read: less than 1 tick)
    > these are all SELECTs
    Good point
    > 2. most queries take 1(3), 2(10), 3(8) or 4(2) ticks. Those are all
    > INSERT or UPDATE statements. There is one SELECT taking 1 tick,
    > however this is not significant because you use a low resolution
    > timer.
    Yes.
    > 3. there is one UPDATE, taking 10 ticks. Not sure whether this is
    > significant. The same UPDATE went through in 3 ticks before.
    Not sure if it is significant either. I think that possibly, some
    other task interceded. (like my browser etc)
    >
    > Conclusions:
    >
    > 1. there are no exceptionally slow queries. Indexes seem to be fine.
    > I'd also say this is not typical database workload. Single threaded,
    > simple queries, no JOINs.
    Well, yes. The issue is that at some moments in time, we can have a
    lot of such 45 query jobs to get done, all coming at once.

    So it needs to be fast.
    > 2. the "slow" queries are all of the writing type. Since you use InnoDB
    > and no transactions, the transaction log has to be flushed right
    > after each such query. Depending on how fast and (otherwise) busy
    > your disk is, you pay a penalty of 10-20ms extra delay per query!
    >
    >
    > Solutions:
    >
    > 1. you might want to try the same workload on MyISAM tables. I would
    > expect that to be *much* faster.
    > 2. if you want to stay with InnoDB (I guess you want) - then you should
    > turn off AUTO_COMMIT and group your SQL statements in proper
    > transactions. Keep in mind that each COMMIT costs you 20ms penalty.
    Well, you know what, I am open to absolutely anything at this point.

    How can I convert my InnoDB table to ISAM?
    > Alternatively/additionally you could change the
    > innodb_flush_log_at_trx_commit=1 parameter in my.cnf. But please
    > consult the manual [1] before you do that.
    I will check into it. I want the database to flush stuff, in general,
    but maybe not synchronously (ie after it returned success, would be
    fine with me).
    > 3. to further speedup MySQL you should consider to connect to the
    > server via shared memory [2]. This should be significantly faster
    > than named pipes or the network stack. Also you may want to use
    > prepared statements to avoid the overhead of the SQL pr.
    I will peruse these links, thanks.

    I suspect the commit issues first, I think.

    i
    >
    > [1] [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html[/url]
    > [2] [url]http://dev.mysql.com/doc/refman/5.0/en/features.html[/url]
    >
    >
    > HTH, XL
    >
    > Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    > MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Ignoramus23953 Guest

  11. #11

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    Where exactly did you post it... I was watching for it... but did not see
    it...

    Personally, if you already have access to MS SQL Server, then why would you
    use MySQL?

    Cheers!

    "Ignoramus23953" <ignoramus23953NOSPAM.23953.invalid> wrote in message
    news:RNZEg.115906$dA2.9029fe33.usenetserver.com.. .
    > On Thu, 17 Aug 2006 14:04:37 +0200, Phil <localhostlocaldomain> wrote:
    >> Ignoramus8392 wrote:
    >>>
    >>> Any ideas?
    >>>
    >>
    >> Switch to Oracle.
    >>
    >> Best regards.
    >
    > Well... Someone asked me, in response to my original post, to post my
    > my.ini and so on.
    >
    > I did that and received no reply at all. I know that no one owes me
    > anything, but it is disappointing.
    >
    > Meanwhile, MySQL on Windows is as slow as it was and we are leaning
    > towards MS SQL Server.
    >
    > If anyone is willing to take a look at my configuration file, let me
    > know what you want and I will post them again.
    >
    > i
    >

    Duane Phillips Guest

  12. #12

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    On Thu, 17 Aug 2006 10:39:25 -0600, Duane Phillips <duane.phillipsaskme.askme> wrote:
    > Where exactly did you post it... I was watching for it... but did not see
    > it...
    Right here, over a week ago.
    > Personally, if you already have access to MS SQL Server, then why would you
    > use MySQL?
    We want our app to be portable to many databases, so I want MySQL to
    work. Plus we have some linux servers. Plus MS SQL Server is
    proprietary. Plus, I think that MySQL is actually faster, I am just
    not using it right.

    i

    Ignoramus23953 Guest

  13. #13

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    ....
    >> Solutions:
    >>
    >> 1. you might want to try the same workload on MyISAM tables. I would
    >> expect that to be *much* faster.
    >
    >> 2. if you want to stay with InnoDB (I guess you want) - then you should
    >> turn off AUTO_COMMIT and group your SQL statements in proper
    >> transactions. Keep in mind that each COMMIT costs you 20ms penalty.
    >
    > Well, you know what, I am open to absolutely anything at this point.
    >
    > How can I convert my InnoDB table to ISAM?
    ALTER TABLE <table name> ENGINE=MyISAM

    But before you do that, consider a few things:
    Starting the script with "START TRANSACTION" and ending it with "COMMIT"
    works in both SQL server and MySQL. It also switches the autocommit off
    until after the COMMIT. Extra advantage: the whole lot either gets
    stored as a whole, or not at all (but be sure to check for errors,
    otherwise the first error causes a rollback, after which the following
    statements are issued with autocommit on).
    Also, MyISAM tables do not enforce referential integrity (foreign keys).

    Good luck!
    Dikkie Dik Guest

  14. #14

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    On Thu, 17 Aug 2006 21:22:31 +0200, Dikkie Dik <nospamnospam.org> wrote:
    > ...
    >>> Solutions:
    >>>
    >>> 1. you might want to try the same workload on MyISAM tables. I would
    >>> expect that to be *much* faster.
    >>
    >>> 2. if you want to stay with InnoDB (I guess you want) - then you should
    >>> turn off AUTO_COMMIT and group your SQL statements in proper
    >>> transactions. Keep in mind that each COMMIT costs you 20ms penalty.
    >>
    >> Well, you know what, I am open to absolutely anything at this point.
    >>
    >> How can I convert my InnoDB table to ISAM?
    >
    > ALTER TABLE <table name> ENGINE=MyISAM
    OK, I can try that.
    > But before you do that, consider a few things:
    > Starting the script with "START TRANSACTION" and ending it with "COMMIT"
    > works in both SQL server and MySQL. It also switches the autocommit off
    > until after the COMMIT. Extra advantage: the whole lot either gets
    > stored as a whole, or not at all (but be sure to check for errors,
    > otherwise the first error causes a rollback, after which the following
    > statements are issued with autocommit on).
    It would not work, as these individual statements are not called from
    the same routine. We have a few programs exchanging messages, and
    these statements are issued in response to these messages, there is a
    big sequence etc. So these 45 statements are a result of many programs
    exchanging messages and executing complex logic (with one program
    being responsible for issuing SQL statements, the rest are doing other
    things).
    > Also, MyISAM tables do not enforce referential integrity (foreign keys).
    I would not care about it.

    Thanks.

    I will try first to see if changing table parameters related to
    commits, would help.

    i
    > Good luck!
    Ignoramus23953 Guest

  15. #15

    Default Re: MySQL 5 on Windoze extremely SLOW compared to MS SQL Server!

    On Thu, 17 Aug 2006 17:07:17 +0200, Axel Schwenke <de> wrote: 

    That was it. Thanks!

    i
    Ignoramus7272 Guest

Similar Threads

  1. Replies: 1
    Last Post: March 7th, 08:18 PM
  2. Net::SSH::Perl extremely slow?
    By Yon in forum PERL Modules
    Replies: 1
    Last Post: July 15th, 07:11 PM
  3. Extremely slow printing on W2k3 server
    By in forum Windows Server
    Replies: 2
    Last Post: June 8th, 06:26 PM
  4. Extremely Slow WebService
    By Laurel in forum ASP.NET Web Services
    Replies: 1
    Last Post: October 21st, 02:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139