…MyISAM‘); mysql> begin; mysql> update test2 set names=’mysql’ where id=1; Session 2: mysql> begin; mysql> update… plain select. Behavior shown in this case does not match the one you would expect …where id=496201; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction …
Post: Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
… engine, and how does it compare in performance? Â When would I use PBXT instead of a storage engine like MyISAM, InnoDB or… acquired when updating. Instead, the application can hit a “optimistic lock error” if a record is updated by another user. Now PBXT does acquire… SELECT does not lock at all. In addition, an UPDATE or DELETE only acquires a temporary row-lock. This lock is released when…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… rows) which can help significantly. delay_key_write Delay updating indexes for MyISAM tables. Good for performance but tables will be badly… it is MyISAM related. expire_logs_days This is replication/binlogging related options – when to delete binary logs from master. It does not… use this feature. low_priority_updates If enabled selects will get higher priority than updates and waiting update will not stall all new…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…. Intention locks are very loose IS lock on the table does not conflict with any other locks other than X lock… 12304 lock_mode X When updating the table it also gets “locked”, now with IX lock… this is because update needs to lock… may spotted important difference between MyISAM and Innodb when it comes to Table Level Locks. For MyISAM tables running UPDATE query on the table…
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
…perform certain action (ie write back to the database) when object is removed from the cache, or just …the database which is not replicated) to log updates in heap/myisam/archive table and aggregate it in the …run rather complicated group by query ? Sure it does but it does not have to do it for the …
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
… can see from MyISAM results (above) the insert speeds does not degrade that badly… is because updating index header (most complex part of opening and closing MyISAM table)…updates) and reopened for more than 100 tables. Read performance starts close but degrades less for 10 and 100 tables and when…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
…space limit and MySQL will crash when one of internal memory allocations would…Space For MyISAM tables hosting providers used to use disk quotas for MyISAM tables…and allowing other users to do updates – Innodb only can purge data … functional. General Overload As MySQL does not have much of resource …
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… why Jim does not believe in it. [+/-] Only committed changes go to the disk Innodb modifies data in the database when you change it, Falcon only when it is committed. This has some good… row updates, are they stored in the same position or in another location ? Does row ever split into multiple parts like with MyISAM…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… What kind of outages can happen? Someone runs UPDATE or DELETE and forgets the where clause or…bug that propagates via replication (not common, but does happen) You lose your entire SAN and all… lock times here when non-transactional tables are used Almost no locking, if not using myisam tables Built…
Post: Filtered MySQL Replication
November 7, 2007 By 8 Comments
… There are however some cases when you really do not need events…restriction and running one time update query without selecting proper default …stream of data though it does not help if you …MyISAM tables, because MyISAM can’t be disabled and so if you create tables as MYISAM or ALTER them to MyISAM…
