| Bug #10939 | set type=(type+0)|(1<<3),type=(type+0)&~(1<<2) = CS>70000 | ||
|---|---|---|---|
| Submitted: | 28 May 2005 13:20 | Modified: | 2 Feb 2006 12:08 |
| Reporter: | Sergey Frolovichev | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.12 | OS: | Linux (SuSe 9.3) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[28 May 2005 13:34]
Heikki Tuuri
Sergey, can you make a small test case with a much simpler table definition that demonstrates this performance problem? Why do you update the column 'type' twice in the same UPDATE? What does SHOW INNODB STATUS show during the high load? Regards, Heikki
[28 May 2005 13:39]
Sergey Frolovichev
Will be today in the evening! Now I'm removing collation specification for column type. Twice it is specifield because of specific of application (it collects double updates to one but only in this manner - if an error will be in this - I can fix it quickly)
[28 May 2005 15:13]
Sergey Frolovichev
Hi!
I have made very easy test now ;-)
Here comes create for table:
===================
CREATE TABLE `Anketa` (
`oid` int(10) unsigned NOT NULL default '0',
`type` set('PreModeration','Intim','LeaderTry','Leader','Banned','VoiceForward','VoiceWelcome','K') character set utf8 NOT NULL default '',
`created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
===================
Here comes very simple php code which simply replaces 10.000.000 records in this table, and I have started it in background and have run "vmstat 1" on my machine it's about 70.000 CS...
===================
<?
set_time_limit(0);
$dbcon=mysql_connect("localhost","root","");
if ($dbcon)
{
for ($i=0;$i<1000000;$i++)
{
$ins="replace into Anketa.Anketa (oid,type,created) values (".$i.",'',now())";
$inse=mysql_query($ins,$dbcon);
}
}
?>
===================
[29 May 2005 5:22]
Peter Zaitsev
I've look into this case. The problem seems to be general - even simple table with single auto_increment column shows the same problem. Number of context switches is proporsional to number of queries/sec and is 8-10 context switches per query. On my box I get 4 context switches per query in the same case, running test on the same box as MySQL server. This corresponds to my experiments a while back so there is no regressions here. This number is also same for MyISAM and Innodb tables. Number 8-10 could be due to the fact self-compiled binary is used (NPTL ?) or due to usage of multi CPU box - I tested on single CPU box It would be great to give standard MySQL binary a try to see if problem still persists. In general I would not call it a bug, but rather performance suboptimality. Modern Linux systems can do other 1.000.000 context switches/sec so context switching in this case should only take a portion of system resources. If performance of this query is seriously less than expected it should be analyzed separately.
[29 May 2005 7:34]
Heikki Tuuri
Changing the Category to MySQL Server, because the phenomenon of 2 times more context switches than necessary is also for MyISAM. If you do REPLACES, then InnoDB's purge thread will also run, but I do not see how it could cause more than a couple of context switches per second. Also I recall seeing the excessive context switches in Linux a few years ago. Regards, Heikki
[29 May 2005 7:38]
Sergey Frolovichev
I have got the same problem after downloading and installing 4.0, 4.1.11, 4.1.12. This test was on clean mashine without any other load. Mashine: Intel SR1300, Dual Xeon, 4Gb RAM, 2x140Gb 10K RPM, RAID 1.
[29 May 2005 8:23]
Sergey Frolovichev
I have tested 4.0, 4.1.12 RPM on single CPU Xeon. 4.0: 62.000CS per second. So it is about 8 cs per query on 1000000 replaces (~130 sec) to single field primary key table. 4.1.12rpm: 83.000 CS per second. So it is about 7.5 cs per quesry on 1000000 replaces (~89 sec) to single field primary table. Also I want to add more info - operating system is SuSe Linux 9.1 (on the 32 bit mashines) and kernel version is 2.4.28 PS: On 2.6 kernel mysql hangs whole machine like it was few seconds ago working and now is gone - only reset helps :-( It happens usualy in 24 hours from start without any information in logs, system logs, SA and other special logs systems which we tryed to use. This happens with load or without any load on mysql server.
[29 May 2005 21:39]
Heikki Tuuri
Sergey, in which 2.6 distro does the hang happen? --Heikki
[30 May 2005 13:03]
Sergey Frolovichev
2.6.5-0- #7 SMP Mon Apr 25 06:35:11
[7 Sep 2005 9:18]
Vadim Tkachenko
I'm able to repeat it on SUSE LINUX Enterprise Server 9 (x86_64), 2x Opteron 1.6Ghz. (Linux 2.6.5-7.97-smp). I observe big cs even with MyISAM table: CREATE TABLE `Anketa` ( `oid` int(10) unsigned NOT NULL default '0', `type` VARCHAR(12) NOT NULL default '', `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`oid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
[7 Sep 2005 9:30]
Vadim Tkachenko
Solaris 10 / SPARC - stable CS = 23475
[2 Jan 2006 12:08]
Konstantin Osipov
Sergey, could you please try to check whether priority adjustments performed by MySQL server throughout execution of a query are of any harm in your case.
I expect the overall amount of context switches to decrease once these adjustments are removed - they are mostly relevant for long-running queries or many threads with low per-thread load (but high overall load).
To disable priority modifications during execution of a query, please apply the following patch:
kostja@oak:~/mysql/mysql-4.1-root/sql> bk diffs -u
===== init.cc 1.15 vs edited =====
--- 1.15/sql/init.cc 2004-04-07 05:33:56 +04:00
+++ edited/init.cc 2006-01-02 15:07:34 +03:00
@@ -46,5 +46,6 @@
log_10[i]= nr ; nr*= 10.0;
}
specialflag|=options; /* Set options from argv */
+ specialflag|= SPECIAL_NO_PRIOR;
DBUG_VOID_RETURN;
}
Vadim: you could give this patch a try as well.
Thanks!
Thanks!
[20 Jan 2006 11:11]
Vadim Tkachenko
I testes with 5.0.18. I tried specialflag=SPECIAL_SAME_DB_NAME | options; /* Set options from argv */ specialflag|=SPECIAL_NO_PRIOR; /* Set options from argv */ in sql/init.cc. It does not help, CS = 38000 on quadxeon box.
[3 Feb 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: After instalation of 4.1.11 (UTF) I have received very ugly bug with enum & set fields. I have fixed this with setting collation whem, and as was told - I started process of upgrading to4.1.12. On this process I have found one stupid thing which takes about 30% of speed: update Monamour2.Anketa set type=(type+0)|(1<<3),type=(type+0)&~(1<<2) where oid=20921887; I have made a test - i have configured slave with skip-networking, and simply enable replication from master - a lot of such updates and vmstat showing about 70000 CS! And on master CS is about 140.000!! How to repeat: I think content of the table will not be avaliable in gzip it takes about 1Gb... Here comes an update type which couses this "error": update Monamour2.Anketa set type=(type+0)|(1<<3),type=(type+0)&~(1<<2) where oid=20921887; And here creation info: CREATE TABLE `Anketa` ( `oid` int(10) unsigned NOT NULL default '0', `language_id` int(10) unsigned NOT NULL default '0', `partner_id` int(10) unsigned NOT NULL default '0', `user_id` int(10) unsigned NOT NULL default '0', `package_id` int(10) unsigned NOT NULL default '0', `birthdate` date NOT NULL default '0000-00-00', `sign` enum('none','oven','telez','bliznezi','rak','lev','deva','vesi','skorpion','strelez','kozerog','vodoley','ribi') character set utf8 NOT NULL default 'none', `country_id` int(10) unsigned NOT NULL default '0', `region_id` int(10) unsigned NOT NULL default '0', `city_id` int(10) unsigned NOT NULL default '0', `metro_id` int(10) unsigned NOT NULL default '0', `gender` enum('None','M','F','MTF','FTM') character set utf8 NOT NULL default 'None', `orientation` enum('None','Getero','Bi','Gay','Lesbi') character set utf8 NOT NULL default 'None', `couple` enum('None','Along','Getero','Gay','Lesbi') character set utf8 NOT NULL default 'None', `couple_login` varchar(50) collate utf8_unicode_ci NOT NULL default '', `couple_login_status` enum('None','New','Approved','Rejected') character set utf8 NOT NULL default 'None', `couple_login_code` varchar(50) collate utf8_unicode_ci NOT NULL default '', `height` int(11) NOT NULL default '0', `weight` int(11) NOT NULL default '0', `constitution` enum('None','Thin','Normal','Sports','Athletic','Thick','Stout','Other') character set utf8 NOT NULL default 'None', `onbody` set('Pirsing','Tatu') character set utf8 NOT NULL default '', `hairshead` enum('Light','Dark','Red','Melirovanie','Rich','Grey','Clean','None') character set utf8 NOT NULL default 'None', `hairsbody` set('Moustache','Beard','Bristle','Breast','Back','Hands','Legs','Clean','Smootharmpit','Smoothgroin','Smoothall') character set utf8 NOT NULL default '', `routine` enum('None','Owl','Lark') character set utf8 NOT NULL default 'None', `circumstance` enum('None','NonStable','Stable','Average','Huge') character set utf8 NOT NULL default 'None', `languages` set('Russian','English','German','French','Italian','Spain','Portugalian','Ivrit','Other') character set utf8 NOT NULL default '', `home` enum('None','Along','Public','Family','Friend','Couple','Punk') character set utf8 NOT NULL default 'None', `lifepriority` set('Public','Creative','Maxsex','Family','Emotion','Living','Career','Other') character set utf8 NOT NULL default '', `freeday` set('Walk','Read','Along','Guest','Nature','Cinema','Theatre','Museum','Restaurant','Disco','Sport','Computer','Internet','Work','Other') character set utf8 NOT NULL default '', `interests` set('Auto','Business','Animals','Art','Cinema','Computer','Cooking','Literature','Style','Music','Science','Politics','Psychology','Travel','Work','Religion','Sport','Theatre','Photo','Other') character set utf8 NOT NULL default '', `sport` set('Run','Walk','Fitness','Swim','ByCicle','SkateBoard','Roller','Ski','Daiving','Serfing','Fighting','Ioga','Other') character set utf8 NOT NULL default '', `music` set('Russian','Foreign','Bard','RussianRock','Blues','Etnic','Rap','Classic','Dance','Rock','Electronic','Other') character set utf8 NOT NULL default '', `smoke` enum('No','Yes','Sometimes','GiveUp','None') character set utf8 NOT NULL default 'None', `drink` enum('No','Sometimes','Yes','None') character set utf8 NOT NULL default 'None', `drug` enum('No','Never','Sometimes','Light','Yes','GiveUp','Recover','Throw','None') character set utf8 NOT NULL default 'None', `sexgetero` enum('Live','Sex','Small','No','None','Other') character set utf8 NOT NULL default 'None', `sexgomo` enum('Live','Sex','Small','No','None','Other') character set utf8 NOT NULL default 'None', `sexoften` enum('Hourly','Daily','Weekly','Monthly','No','None','Other') character set utf8 NOT NULL default 'None', `sexrole` enum('OnlyActive','PreferActive','PreferPassive','OnlyPassive','Universal','None','Other') character set utf8 NOT NULL default 'None', `sexpenis` enum('10','10-15','16-20','20','None') character set utf8 NOT NULL default 'None', `sexbreast` enum('Small','Middle','Big','None') character set utf8 NOT NULL default 'None', `aids` enum('Yes','No','Unknown','None') character set utf8 NOT NULL default 'None', `sexexcitation` set('Women','Socks','Foots','Clothes','Underclothes','Uniform','Jeans','Skin','Latex','Metall','Smell','Hairiness','Dark','TatuPirsing','Other') character set utf8 NOT NULL default '', `sexpriority` set('Kiss','Massage','Breast','Petting','Masturbation','MinetOffer','MinetGet','KunnilingusGet','KunnilingusOffer','Rimming','Vaginal','AnalGet','AnalOffer','Group','Svingers','Public','Photo','Restling','Bondage','Dominate','Slave','Role','Sado','Mazo','Porka','Izmazivanie','Sperma','Fingering','Fisting','Vampir','Urologania','Pikansizm','Klizma','Porno','Abuse','Toys','Other') character set utf8 NOT NULL default '', `lookfor` set('M','F','T','MF','MM','FF') character set utf8 NOT NULL default '', `age` set('16-20','18-20','21-25','26-30','31-35','36-40','41-50','51-60','61-80') character set utf8 default NULL, `target` set('Friendship','Email','Love','RegSex','SomeSex','Group','Virt','Intim','Family','Child','Fiktive','Rent','Sport','Other','IntimBuy') character set utf8 NOT NULL default '', `iam` enum('Self','MF','MM','FF','None') character set utf8 NOT NULL default 'None', `children` enum('No','Want','Together','Apart','None') character set utf8 NOT NULL default 'None', `whoknows` set('Noone','Parents','Marital','Friends','Known','Colleague') character set utf8 NOT NULL default '', `sponsor` enum('LookFor','Iam','No','None') character set utf8 NOT NULL default 'None', `marital` enum('Together','Apart','Fictive','No','None') character set utf8 NOT NULL default 'None', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `created` timestamp NOT NULL default '0000-00-00 00:00:00', `changed` timestamp NOT NULL default '0000-00-00 00:00:00', `moderator_id` int(10) unsigned NOT NULL default '0', `moderated_ts` timestamp NOT NULL default '0000-00-00 00:00:00', `moderated` enum('New','Updated','Rejected','Approved','AfterRejected') character set utf8 NOT NULL default 'New', `search_mode` enum('Visible','Invisible') character set utf8 default 'Visible', `anketa_mode` enum('Visible','Invisible') character set utf8 NOT NULL default 'Visible', `couple_login_ts` timestamp NOT NULL default '0000-00-00 00:00:00', `status` enum('Active','Blocked','Deleted') character set utf8 NOT NULL default 'Active', `type` set('PreModeration','Intim','LeaderTry','Leader','Banned','VoiceForward','VoiceWelcome','K') character set utf8 NOT NULL default '', `system_status` enum('Active','Blocked','Deleted') character set utf8 NOT NULL default 'Active', `percent` double(9,5) unsigned NOT NULL default '0.00000', `anketa_personal` enum('Empty','On','Off') character set utf8 NOT NULL default 'Empty', `anketa_personal_created` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_type` enum('Empty','On','Off') character set utf8 NOT NULL default 'Empty', `anketa_type_created` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_interests` enum('Empty','On','Off') character set utf8 NOT NULL default 'Empty', `anketa_interests_created` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_favour` enum('Empty','On','Off') character set utf8 NOT NULL default 'Empty', `anketa_favour_created` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_familiarity` enum('Empty','On','Off') character set utf8 NOT NULL default 'Empty', `anketa_familiarity_created` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_self` enum('Empty','On','Off') character set utf8 NOT NULL default 'Empty', `anketa_self_created` timestamp NOT NULL default '0000-00-00 00:00:00', `photos_extended` enum('No','Yes') character set utf8 NOT NULL default 'No', `on_first` enum('Yes','No') character set utf8 NOT NULL default 'No', `anketa_personal_moderator_id` int(10) unsigned NOT NULL default '0', `anketa_personal_moderated_ts` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_personal_moderated` enum('New','Updated','Rejected','Approved','AfterRejected') character set utf8 NOT NULL default 'New', `anketa_personal_moderated_descr` varchar(255) collate utf8_unicode_ci NOT NULL default '', `anketa_type_moderator_id` int(10) unsigned NOT NULL default '0', `anketa_type_moderated_ts` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_type_moderated` enum('New','Updated','Rejected','Approved','AfterRejected') character set utf8 NOT NULL default 'New', `anketa_type_moderated_descr` varchar(255) collate utf8_unicode_ci NOT NULL default '', `anketa_interests_moderator_id` int(10) unsigned NOT NULL default '0', `anketa_interests_moderated_ts` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_interests_moderated` enum('New','Updated','Rejected','Approved','AfterRejected') character set utf8 NOT NULL default 'New', `anketa_interests_moderated_descr` varchar(255) collate utf8_unicode_ci NOT NULL default '', `anketa_favour_moderator_id` int(10) unsigned NOT NULL default '0', `anketa_favour_moderated_ts` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_favour_moderated` enum('New','Updated','Rejected','Approved','AfterRejected') character set utf8 NOT NULL default 'New', `anketa_favour_moderated_descr` varchar(255) collate utf8_unicode_ci NOT NULL default '', `anketa_familiarity_moderator_id` int(10) unsigned NOT NULL default '0', `anketa_familiarity_moderated_ts` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_familiarity_moderated` enum('New','Updated','Rejected','Approved','AfterRejected') character set utf8 NOT NULL default 'New', `anketa_familiarity_moderated_descr` varchar(255) collate utf8_unicode_ci NOT NULL default '', `anketa_self_moderator_id` int(10) unsigned NOT NULL default '0', `anketa_self_moderated_ts` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_self_moderated` enum('New','Updated','Rejected','Approved','AfterRejected') character set utf8 NOT NULL default 'New', `anketa_self_moderated_descr` varchar(255) collate utf8_unicode_ci NOT NULL default '', `anketa_checked` enum('New','No','Yes') character set utf8 NOT NULL default 'New', `leader` enum('No','Try','Yes') character set utf8 NOT NULL default 'No', `anketa_intim` enum('No','Yes') character set utf8 NOT NULL default 'No', `photo_premoderation` enum('Yes','No') character set utf8 NOT NULL default 'No', `rejected_count` int(10) unsigned NOT NULL default '0', `default_photo_id` int(10) unsigned NOT NULL default '0', `anketa_aboutme` enum('Empty','On','Off') character set utf8 NOT NULL default 'Empty', `anketa_aboutme_created` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_aboutme_moderator_id` int(10) unsigned NOT NULL default '0', `anketa_aboutme_moderated_ts` timestamp NOT NULL default '0000-00-00 00:00:00', `anketa_aboutme_moderated` enum('New','Updated','Rejected','Approved','AfterRejected') character set utf8 NOT NULL default 'New', `anketa_aboutme_moderated_descr` varchar(255) collate utf8_unicode_ci NOT NULL default '', PRIMARY KEY (`oid`), KEY `user_id` (`user_id`), KEY `couple_login` (`couple_login`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci