Re: Question from a new user:MySQL vs MS SQLserver merges
Hi, If you're in a transactionnal env., and have a test env. of mysql with myisam tables, and want to compare, you will not have what you want. Myisam tables have only table locking. sql server 2000 and Yukon have row locking mecanism. So you have to have innodb storage type. Also, if your selects must sort data and do it on disk because the max size of memory tmp table is not enough, you will have very slow queries. Look at mysql parameters, change your table storage to innodb, try to have the same memory requirements for the two databases, and then compare. You can begin by the scripts in the sql-bench directory under mysql installation, play them on sql-server and mysql. When you can compare, do it with your own scripts. And then you will know ! Mathias Selon [EMAIL PROTECTED]: > "jatwood" <[EMAIL PROTECTED]> wrote on 28/04/2005 22:35:45: > > > I am new to MySQL. Please excuse my ignorance if this question has been > > previously discussed. I was not able to > > > > find an answer to my question by searching the archives. > > > > > > > > I have MySQL installed on a dedicated AMD-64 computer with the MS XP Pro > > operating system. > > > > MS-SQLServer is installed on a 32-bit machine with a slower clock speed. > I > > am running the free binary > > > > distribution of MySQL for testing purposes and have been generally > impressed > > with > > > > MySQL's comparative performance in all but one area. > > > > > > > > I am finding that both inner and outer merges take substantially longer > with > > MySQL than with the SQLServer using the same basic code. > > > > In one application a left outer merge between a file with 600,000 > records > > and a file with 4,500,000 records took about 10-15 minutes with > > > > SQLServer and 11 hours with MySQL. I am repeatedly having similar > > experiences with both inner and outer merges. > > > > > > > > Is there any way I can try to improve MySQL's performance with respect > to > > merges? Needless to say, unless I can improve MySQL's > > > > performance, I will not be converting to MySQL at this time. > > You need to post the results of EXPLAIN together > with the structures of your tables, including indexes. > > This sort of performance dropoff is usually due to inappropriate indexes, > and can be solved by adding or changing indexes. > > Alec > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question from a new user:MySQL vs MS SQLserver merges
It sounds like you either have incorrect indexes or you didn't adjust the MySQL configuration variables (i.e. key_buffer, join_buffer_size, read_buffer_size) appropriately for your data set size and what your query is doing (i.e. full table scan). SHOW STATUS can help you figure out what needs to be adjusted, like Key_reads vs. Key_read_requests ratio. On Apr 28, 2005, at 5:35 PM, jatwood wrote: I am new to MySQL. Please excuse my ignorance if this question has been previously discussed. I was not able to find an answer to my question by searching the archives. I have MySQL installed on a dedicated AMD-64 computer with the MS XP Pro operating system. MS-SQLServer is installed on a 32-bit machine with a slower clock speed. I am running the free binary distribution of MySQL for testing purposes and have been generally impressed with MySQL's comparative performance in all but one area. I am finding that both inner and outer merges take substantially longer with MySQL than with the SQLServer using the same basic code. In one application a left outer merge between a file with 600,000 records and a file with 4,500,000 records took about 10-15 minutes with SQLServer and 11 hours with MySQL. I am repeatedly having similar experiences with both inner and outer merges. Is there any way I can try to improve MySQL's performance with respect to merges? Needless to say, unless I can improve MySQL's performance, I will not be converting to MySQL at this time. Thanks -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question from a new user:MySQL vs MS SQLserver merges
"jatwood" <[EMAIL PROTECTED]> wrote on 28/04/2005 22:35:45: > I am new to MySQL. Please excuse my ignorance if this question has been > previously discussed. I was not able to > > find an answer to my question by searching the archives. > > > > I have MySQL installed on a dedicated AMD-64 computer with the MS XP Pro > operating system. > > MS-SQLServer is installed on a 32-bit machine with a slower clock speed. I > am running the free binary > > distribution of MySQL for testing purposes and have been generally impressed > with > > MySQL's comparative performance in all but one area. > > > > I am finding that both inner and outer merges take substantially longer with > MySQL than with the SQLServer using the same basic code. > > In one application a left outer merge between a file with 600,000 records > and a file with 4,500,000 records took about 10-15 minutes with > > SQLServer and 11 hours with MySQL. I am repeatedly having similar > experiences with both inner and outer merges. > > > > Is there any way I can try to improve MySQL's performance with respect to > merges? Needless to say, unless I can improve MySQL's > > performance, I will not be converting to MySQL at this time. You need to post the results of EXPLAIN together with the structures of your tables, including indexes. This sort of performance dropoff is usually due to inappropriate indexes, and can be solved by adding or changing indexes. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question from a new user:MySQL vs MS SQLserver merges
I am new to MySQL. Please excuse my ignorance if this question has been previously discussed. I was not able to find an answer to my question by searching the archives. I have MySQL installed on a dedicated AMD-64 computer with the MS XP Pro operating system. MS-SQLServer is installed on a 32-bit machine with a slower clock speed. I am running the free binary distribution of MySQL for testing purposes and have been generally impressed with MySQL's comparative performance in all but one area. I am finding that both inner and outer merges take substantially longer with MySQL than with the SQLServer using the same basic code. In one application a left outer merge between a file with 600,000 records and a file with 4,500,000 records took about 10-15 minutes with SQLServer and 11 hours with MySQL. I am repeatedly having similar experiences with both inner and outer merges. Is there any way I can try to improve MySQL's performance with respect to merges? Needless to say, unless I can improve MySQL's performance, I will not be converting to MySQL at this time. Thanks