RE: mySql versus Sql Server performance
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:gto...@ffn.com] Sent: Tuesday, October 26, 2010 3:52 PM To: Patrick Thompson; mysql@lists.mysql.com 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.thomp...@channelintelligence.com] Sent: Monday, October 25, 2010 2:24 PM To: Gavin Towey; mysql@lists.mysql.com 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', '1' 'innodb_use_legacy_cardinality_algorithm', 'ON' Patrick myList - everything you could possibly want (to buy) -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Monday, October 25, 2010 4:50 PM To: Patrick Thompson; mysql@lists.mysql.com 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.thomp...@channelintelligence.com] Sent: Monday, October 25, 2010 12:31 PM To: Gavin Towey; mysql@lists.mysql.com 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;Valid1/ValidItemStatus100/ItemStatusExternalIDfred1/ExternalIDModifiedDate2010-10-25T15:06:55.7188551-04:00/ModifiedDatePersonType xmlns=http://cipl.codeplex.com/CIPlOther1.xsd;AddressUSAddressTypeCityStringCelebration 1/String/CityCountryStringUSA/String/CountryStateStringFL/String/StateStreetString1170 Celebration blvd 1/String
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) ProcessorIntel(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... myListhttp://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.
RE: mySql versus Sql Server performance
That's true for the deletes - but not for save and get. The ddl is available here http://cipl.codeplex.com/SourceControl/changeset/view/2460#57689 The code that accesses it is here http://cipl.codeplex.com/SourceControl/changeset/view/2460#57729 Patrick myListhttp://www.mylist.com/ - everything you could possibly want (to buy) From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Monday, October 25, 2010 9:55 AM To: Patrick Thompson Cc: mysql@lists.mysql.com Subject: Re: mySql versus Sql Server performance I merely skimmed it, but your comment that you pay the query compilation cost on every request suggests to me that you're not using prepared statements. If you can, you should :-) Also, MySQL *does* support SPs, from 5.0 onwards or something. You could split into separate modules for pre- and post-5. On Mon, Oct 25, 2010 at 3:38 PM, Patrick Thompson patrick.thomp...@channelintelligence.commailto:patrick.thomp...@channelintelligence.com wrote: 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) ProcessorIntel(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... myListhttp://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. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
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;Valid1/ValidItemStatus100/ItemStatusExternalIDfred1/ExternalIDModifiedDate2010-10-25T15:06:55.7188551-04:00/ModifiedDatePersonType
xmlns=http://cipl.codeplex.com/CIPlOther1.xsd;AddressUSAddressTypeCityStringCelebration
1/String/CityCountryStringUSA/String/CountryStateStringFL/String/StateStreetString1170
Celebration blvd
1/String/StreetZipInt3234748/Int32/Zip/USAddressType/AddressAlternateAddresses
Count=2USAddressTypeCityStringCelebration
1/String/CityCountryStringUSA/String/CountryStateStringFL/String/StateStreetString1170
Celebration blvd
1/String/StreetZipInt3234748/Int32/Zip/USAddressTypeUSAddressTypeCityStringSeattle
1/String/CityCountryStringUSA/String/CountryPhoneNumbers
Count=2PhoneNumberTypeAreaCodeInt32206/Int32/AreaCodeNumberInt327819281/Int32/NumberTags
Count=1Stringnever answered
1/String/Tags/PhoneNumberTypePhoneNumberTypeAreaCodeInt32206/Int32/AreaCodeNumberInt329991971/Int32/NumberTags
Count=1Stringcell
1/String/Tags/PhoneNumberType/PhoneNumbersStateStringWA/String/StateStreetString12070
Lakeside pl
1/String/StreetZipInt3298126/Int32/Zip/USAddressType/AlternateAddressesCreateDateDateTime2010-10-25T15:06:55.7168549-04:00/DateTime/CreateDateNameStringfred1/String/NameTags
Count=4Stringfirst/StringStringsecond/StringStringthird/StringString1/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:gto...@ffn.com]
Sent: Monday, October 25, 2010 2:00 PM
To: Patrick Thompson; mysql@lists.mysql.com
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.thomp...@channelintelligence.com]
Sent: Monday, October 25, 2010 6:38 AM
To: mysql@lists.mysql.com
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)
ProcessorIntel(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
RE: [SPAM] RE: mySql versus Sql Server performance
Thanks Martin, though I'm somewhat confused by your message - there are no
joins in the query (unless the longtext s thought of that way) and the Explain
seems to indicate the query is using the ItemsById primary index (which is what
I would expect).
Patrick
myListhttp://www.mylist.com/ - everything you could possibly want (to buy)
From: Martin Gainty [mailto:mgai...@hotmail.com]
Sent: Monday, October 25, 2010 3:53 PM
To: Patrick Thompson; gto...@ffn.com; mysql@lists.mysql.com
Subject: [SPAM] RE: mySql versus Sql Server performance
Importance: Low
Patrick-
you'll want to determine if your predicates are implementing indexes or FTS
with EXPLAIN statement
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html
you'll need a plan to determine if the join-type of resultsets returned from
the Queries are NL, Hashed or sort-merge
http://en.wikipedia.org/wiki/Join_%28SQL%29
Each one has pros/cons dependening on the cardinality of the inner resultset vs
the outer resultset
hth
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
From: patrick.thomp...@channelintelligence.com
To: gto...@ffn.com; mysql@lists.mysql.com
Date: Mon, 25 Oct 2010 15:31:26 -0400
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;Valid1/ValidItemStatus100/ItemStatusExternalIDfred1/ExternalIDModifiedDate2010-10-25T15:06:55.7188551-04:00/ModifiedDatePersonType
xmlns=http://cipl.codeplex.com/CIPlOther1.xsd;AddressUSAddressTypeCityStringCelebration
1/String/CityCountryStringUSA/String/CountryStateStringFL/String/StateStreetString1170
Celebration blvd
1/String/StreetZipInt3234748/Int32/Zip/USAddressType/AddressAlternateAddresses
Count=2USAddressTypeCityStringCelebration
1/String/CityCountryStringUSA/String/CountryStateStringFL/String/StateStreetString1170
Celebration blvd
1/String/StreetZipInt3234748/Int32/Zip/USAddressTypeUSAddressTypeCityStringSeattle
1/String/CityCountryStringUSA/String/CountryPhoneNumbers
Count=2PhoneNumberTypeAreaCodeInt32206/Int32/AreaCodeNumberInt327819281/Int32/NumberTags
Count=1Stringnever answered
1/String/Tags/PhoneNumberTypePhoneNumberTypeAreaCodeInt32206/Int32/AreaCodeNumberInt329991971/Int32/NumberTags
Count=1Stringcell
1/String/Tags/PhoneNumberType/PhoneNumbersStateStringWA/String/StateStreetString12070
Lakeside pl
1/String/StreetZipInt3298126/Int32/Zip/USAddressType/AlternateAddressesCreateDateDateTime2010-10-25T15:06:55.7168549-04:00/DateTime/CreateDateNameStringfred1/String/NameTags
Count=4Stringfirst/StringStringsecond/StringStringthird/StringString1/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
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', '1'
'innodb_use_legacy_cardinality_algorithm', 'ON'
Patrick
myList - everything you could possibly want (to buy)
-Original Message-
From: Gavin Towey [mailto:gto...@ffn.com]
Sent: Monday, October 25, 2010 4:50 PM
To: Patrick Thompson; mysql@lists.mysql.com
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.thomp...@channelintelligence.com]
Sent: Monday, October 25, 2010 12:31 PM
To: Gavin Towey; mysql@lists.mysql.com
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;Valid1/ValidItemStatus100/ItemStatusExternalIDfred1/ExternalIDModifiedDate2010-10-25T15:06:55.7188551-04:00/ModifiedDatePersonType
xmlns=http://cipl.codeplex.com/CIPlOther1.xsd;AddressUSAddressTypeCityStringCelebration
1/String/CityCountryStringUSA/String/CountryStateStringFL/String/StateStreetString1170
Celebration blvd
1/String/StreetZipInt3234748/Int32/Zip/USAddressType/AddressAlternateAddresses
Count=2USAddressTypeCityStringCelebration
1/String/CityCountryStringUSA/String/CountryStateStringFL/String/StateStreetString1170
Celebration blvd
1/String/StreetZipInt3234748/Int32/Zip/USAddressTypeUSAddressTypeCityStringSeattle
1/String/CityCountryStringUSA/String/CountryPhoneNumbers
Count=2PhoneNumberTypeAreaCodeInt32206/Int32/AreaCodeNumberInt327819281/Int32/NumberTags
Count=1Stringnever answered
1/String/Tags/PhoneNumberTypePhoneNumberTypeAreaCodeInt32206/Int32/AreaCodeNumberInt329991971/Int32/NumberTags
Count=1Stringcell
1/String/Tags/PhoneNumberType/PhoneNumbersStateStringWA/String/StateStreetString12070
Lakeside pl
1/String/StreetZipInt3298126/Int32/Zip/USAddressType/AlternateAddressesCreateDateDateTime2010-10-25T15:06:55.7168549-04:00/DateTime/CreateDateNameStringfred1/String/NameTags
Count=4Stringfirst/StringStringsecond/StringStringthird/StringString1/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
RE: mySql versus Sql Server performance
Sorry, that should be 200MB not 20MB (still doesn't seem like much to me)
Patrick
myList - everything you could possibly want (to buy)
-Original Message-
From: Patrick Thompson
Sent: Monday, October 25, 2010 5:24 PM
To: 'Gavin Towey'; mysql@lists.mysql.com
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', '1'
'innodb_use_legacy_cardinality_algorithm', 'ON'
Patrick
myList - everything you could possibly want (to buy)
-Original Message-
From: Gavin Towey [mailto:gto...@ffn.com]
Sent: Monday, October 25, 2010 4:50 PM
To: Patrick Thompson; mysql@lists.mysql.com
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.thomp...@channelintelligence.com]
Sent: Monday, October 25, 2010 12:31 PM
To: Gavin Towey; mysql@lists.mysql.com
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;Valid1/ValidItemStatus100/ItemStatusExternalIDfred1/ExternalIDModifiedDate2010-10-25T15:06:55.7188551-04:00/ModifiedDatePersonType
xmlns=http://cipl.codeplex.com/CIPlOther1.xsd;AddressUSAddressTypeCityStringCelebration
1/String/CityCountryStringUSA/String/CountryStateStringFL/String/StateStreetString1170
Celebration blvd
1/String/StreetZipInt3234748/Int32/Zip/USAddressType/AddressAlternateAddresses
Count=2USAddressTypeCityStringCelebration
1/String/CityCountryStringUSA/String/CountryStateStringFL/String/StateStreetString1170
Celebration blvd
1/String/StreetZipInt3234748/Int32/Zip/USAddressTypeUSAddressTypeCityStringSeattle
1/String/CityCountryStringUSA/String/CountryPhoneNumbers
Count=2PhoneNumberTypeAreaCodeInt32206/Int32/AreaCodeNumberInt327819281/Int32/NumberTags
Count=1Stringnever answered
1/String/Tags/PhoneNumberTypePhoneNumberTypeAreaCodeInt32206/Int32/AreaCodeNumberInt329991971/Int32/NumberTags
Count=1Stringcell
1/String/Tags/PhoneNumberType/PhoneNumbersStateStringWA/String/StateStreetString12070
Lakeside pl
1/String/StreetZipInt3298126/Int32/Zip/USAddressType/AlternateAddressesCreateDateDateTime2010-10-25T15:06:55.7168549-04:00/DateTime/CreateDateNameStringfred1/String/NameTags
Count=4Stringfirst/StringStringsecond/StringStringthird/StringString1/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
