List:General Discussion« Previous MessageNext Message »
From:Patrick Thompson Date:October 26 2010 8:25pm
Subject:RE: mySql versus Sql Server performance
View as plain text  
Thanks a lot - I'll try playing around with some of that stuff and at least get the memory
utilization between mySql and Sql Server comparable. At some point I would like to see the
abstraction layer running under mono - but won't get to that for a while - when I do I can
post linux based numbers. If you think the comparisons
(http://cipl.codeplex.com/wikipage?title=Data%20Provider%20Comparison) are misleading, I
would welcome any comments or be happy to edit the page to reflect any concerns - the
point of the numbers is how CIPl performs against the underlying stores in its current
incarnation - so I consider it to be accurate, at least in that respect.

Patrick
myList - everything you could possibly want (to buy)


-----Original Message-----
From: Gavin Towey [mailto:gtowey@stripped] 
Sent: Tuesday, October 26, 2010 3:52 PM
To: Patrick Thompson; mysql@stripped
Subject: [SPAM] RE: mySql versus Sql Server performance
Importance: Low

It's not much, but the dataset is definitely larger than your buffer pool. You could try
this query to show how much data+index is in innodb: SELECT SUM(data_length+index_length)
as data size FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB';  =
Then SET GLOBAL buffer_pool_size= <that number>

I don't have a much experience running mysql on windows; I think much more time is spent
optimizing the server performance on linux based systems rather than windows.


-----Original Message-----
From: Patrick Thompson [mailto:Patrick.Thompson@stripped]
Sent: Monday, October 25, 2010 2:24 PM
To: Gavin Towey; mysql@stripped
Subject: RE: mySql versus Sql Server performance

Here's the innodb stuff - although the largest data set I've used in the stats run is
around 20MB, which doesn't seem like much to me.

'innodb_adaptive_hash_index', 'ON'
'innodb_additional_mem_pool_size', '2097152'
'innodb_autoextend_increment', '8'
'innodb_autoinc_lock_mode', '1'
'innodb_buffer_pool_size', '49283072'
'innodb_checksums', 'ON'
'innodb_commit_concurrency', '0'
'innodb_concurrency_tickets', '500'
'innodb_data_file_path', 'ibdata1:10M:autoextend'
'innodb_data_home_dir', 'C:\MySQL Datafiles\'
'innodb_doublewrite', 'ON'
'innodb_fast_shutdown', '1'
'innodb_file_io_threads', '4'
'innodb_file_per_table', 'OFF'
'innodb_flush_log_at_trx_commit', '1'
'innodb_flush_method', ''
'innodb_force_recovery', '0'
'innodb_lock_wait_timeout', '50'
'innodb_locks_unsafe_for_binlog', 'OFF'
'innodb_log_buffer_size', '1048576'
'innodb_log_file_size', '25165824'
'innodb_log_files_in_group', '2'
'innodb_log_group_home_dir', '.\'
'innodb_max_dirty_pages_pct', '90'
'innodb_max_purge_lag', '0'
'innodb_mirrored_log_groups', '1'
'innodb_open_files', '300'
'innodb_rollback_on_timeout', 'OFF'
'innodb_stats_on_metadata', 'ON'
'innodb_support_xa', 'ON'
'innodb_sync_spin_loops', '20'
'innodb_table_locks', 'ON'
'innodb_thread_concurrency', '8'
'innodb_thread_sleep_delay', '10000'
'innodb_use_legacy_cardinality_algorithm', 'ON'

Patrick
myList - everything you could possibly want (to buy)


-----Original Message-----
From: Gavin Towey [mailto:gtowey@stripped]
Sent: Monday, October 25, 2010 4:50 PM
To: Patrick Thompson; mysql@stripped
Subject: [SPAM] RE: mySql versus Sql Server performance
Importance: Low

So it's a primary key lookup.  That's a rather large primary key though, it's going to
bloat the table size since innodb in mysql uses clustered indexes.

So the explain plan and table structure look pretty straightforward.  It is using the
index to satisfy the query.  The next question is what does the server memory
configuration look like?

SHOW GLOBAL VARIABLES LIKE 'innodb%';

In particular innodb_buffer_pool defines the global set of memory where data and indexes
from your table are cached.  Mysql could be showing slower performance if it is getting
cache misses from the buffer pool and is being forced to read from disk excessively.

On dedicated mysql servers, the buffer pool should be about 80% of available RAM.  The
default value is 8M which is pretty much unusable except for trivial cases.


-----Original Message-----
From: Patrick Thompson [mailto:Patrick.Thompson@stripped]
Sent: Monday, October 25, 2010 12:31 PM
To: Gavin Towey; mysql@stripped
Subject: RE: mySql versus Sql Server performance

Query:

SELECT *
        FROM Item
        WHERE CollectionID = 'a0d3937b-f5a8-0640-dec8-bdd60f7f4775' AND ExternalID =
'fred1'

Explain Extended:

select '17304' AS `ID`,'fred1' AS `ExternalID`,'a0d3937b-f5a8-0640-dec8-bdd60f7f4775' AS
`CollectionID`,NULL AS `ItemTypeVersion`,'<Item
xmlns="http://cipl.codeplex.com/CIPlItem1.xsd"><Valid>1</Valid><ItemStatus>100</ItemStatus><ExternalID>fred1</ExternalID><ModifiedDate>2010-10-25T15:06:55.7188551-04:00</ModifiedDate><PersonType
xmlns="http://cipl.codeplex.com/CIPlOther1.xsd"><Address><USAddressType><City><String>Celebration
1</String></City><Country><String>USA</String></Country><State><String>FL</String></State><Street><String>1170
Celebration blvd
1</String></Street><Zip><Int32>34748</Int32></Zip></USAddressType></Address><AlternateAddresses
Count="2"><USAddressType><City><String>Celebration
1</String></City><Country><String>USA</String></Country><State><String>FL</String></State><Street><String>1170
Celebration blvd
1</String></Street><Zip><Int32>34748</Int32></Zip></USAddressType><USAddressType><City><String>Seattle
1</String></City><Country><String>USA</String></Country><PhoneNumbers
Count="2"><PhoneNumberType><AreaCode><Int32>206</Int32></AreaCode><Number><Int32>7819281</Int32></Number><Tags
Count="1"><String>never answered
1</String></Tags></PhoneNumberType><PhoneNumberType><AreaCode><Int32>206</Int32></AreaCode><Number><Int32>9991971</Int32></Number><Tags
Count="1"><String>cell
1</String></Tags></PhoneNumberType></PhoneNumbers><State><String>WA</String></State><Street><String>12070
Lakeside pl
1</String></Street><Zip><Int32>98126</Int32></Zip></USAddressType></AlternateAddresses><CreateDate><DateTime>2010-10-25T15:06:55.7168549-04:00</DateTime></CreateDate><Name><String>fred1</String></Name><Tags
Count="4"><String>first</String><String>second</String><String>third</String><String>1</String></Tags></PersonType></Item>'
AS `ObjectText`,'2010-10-25 15:06:55' AS `EnteredDate`,'2010-10-25 15:06:55' AS
`LastModDate` from `ciplitemwell0404`.`item` where
(('a0d3937b-f5a8-0640-dec8-bdd60f7f4775' = 'a0d3937b-f5a8-0640-dec8-bdd60f7f4775') and
('fred1' = 'fred1'))

Explain:

1, 'SIMPLE', 'Item', 'const', 'PRIMARY,ItemsByID', 'PRIMARY', '889', 'const,const', 1, ''


Table definition:

CREATE TABLE  `ciplitemwell0404`.`item` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ExternalID` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `CollectionID` varchar(40) CHARACTER SET utf8 NOT NULL,
  `ItemTypeVersion` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `ObjectText` longtext NOT NULL,
  `EnteredDate` datetime NOT NULL,
  `LastModDate` datetime NOT NULL,
  PRIMARY KEY (`CollectionID`,`ExternalID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `ItemsByID` (`CollectionID`,`ID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=29687 DEFAULT CHARSET=latin1;


This is just the retrieve side - which seems to be around 1.5 times slower than the
equivalent Sql Server numbers.

The update is much slower - 3 to 5 times slower depending on the record size. It makes
sense to me to focus on the retrieve, maybe the update is just a reflection of the same
problems.


Patrick
myList - everything you could possibly want (to buy)


-----Original Message-----
From: Gavin Towey [mailto:gtowey@stripped]
Sent: Monday, October 25, 2010 2:00 PM
To: Patrick Thompson; mysql@stripped
Subject: RE: mySql versus Sql Server performance

MySQL and most other databases require adjustment of server settings, and especially of
table structures and indexes to achieve the best performance possible.

If you haven't examined index usage for the queries you're running, or adjusted server
memory settings from defaults, then it's no surprise you would get poor performance.

I don't have the inclination to dig through your code; however, if you extract the actual
queries you are running, then run EXPLAIN <query>; that will show how it's using
indexes.  You can put that information here, along with the SHOW CREATE TABLE
<table> \G output for all tables involved, and someone here should be able to help
diagnose why the queries might be slow.

Regards,
Gavin Towey


-----Original Message-----
From: Patrick Thompson [mailto:Patrick.Thompson@stripped]
Sent: Monday, October 25, 2010 6:38 AM
To: mysql@stripped
Subject: mySql versus Sql Server performance

I am running an open source project that provides an abstraction layer over a number of
different stores. I am puzzled by performance numbers I am seeing between mysql and sql
server - a brief discussion is available here

http://cipl.codeplex.com/wikipage?title=Data%20Provider%20Comparison

The statistics were generated using mySql 5.1 and Sql Server 2008 on a machine with the
following specs:

OS Name             Microsoft Windows 7 Professional
System Model   HP Compaq nc8430 (RB554UT#ABA)
Processor            Intel(R) Core(TM)2 CPU         T7200  @ 2.00GHz, 2000 Mhz, 2 Core(s),
2 Logical Processor(s)
Installed Physical Memory (RAM)             4.00 GB
Total Virtual Memory     6.75 GB
Page File Space 3.37 GB
Disk 120GB SSD with 22GB available

If this isn't the right place to ask this question, can someone point me to somewhere that
is.

Thanks


Patrick
Are you using...
myList<http://www.mylist.com/> - everything you could possibly want (to buy)
Let me know if you can't find something


________________________________
The information contained in this email message is considered confidential and proprietary
to the sender and is intended solely for review and use by the named recipient. Any
unauthorized review, use or distribution is strictly prohibited. If you have received
this message in error, please advise the sender by reply email and delete the message.

This message contains confidential information and is intended only for the individual
named.  If you are not the named addressee, you are notified that reviewing,
disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. 
Please notify the sender immediately by e-mail if you have received this e-mail by
mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed
to be secure or error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not
accept liability for any loss or damage caused by viruses or errors or omissions in the
contents of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com

This message contains confidential information and is intended only for the individual
named.  If you are not the named addressee, you are notified that reviewing,
disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. 
Please notify the sender immediately by e-mail if you have received this e-mail by
mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed
to be secure or error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not
accept liability for any loss or damage caused by viruses or errors or omissions in the
contents of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com

This message contains confidential information and is intended only for the individual
named.  If you are not the named addressee, you are notified that reviewing,
disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. 
Please notify the sender immediately by e-mail if you have received this e-mail by
mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed
to be secure or error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not
accept liability for any loss or damage caused by viruses or errors or omissions in the
contents of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com
Thread
mySql versus Sql Server performancePatrick Thompson25 Oct
  • Re: mySql versus Sql Server performanceJohan De Meersman25 Oct
    • RE: mySql versus Sql Server performancePatrick Thompson25 Oct
  • RE: mySql versus Sql Server performanceGavin Towey25 Oct
    • RE: mySql versus Sql Server performancePatrick Thompson25 Oct
      • RE: mySql versus Sql Server performanceGavin Towey25 Oct
        • RE: mySql versus Sql Server performancePatrick Thompson25 Oct
          • RE: mySql versus Sql Server performanceGavin Towey26 Oct
            • RE: mySql versus Sql Server performancePatrick Thompson26 Oct
  • RE: mySql versus Sql Server performancePatrick Thompson25 Oct