June 20, 2013

Comment: MySQL Partitioning - can save you or kill you

April 30, 2013 By 45 Comments

… partition (at least I have seen this happen in a MyIsam environment on similar sized tables). What I am really saying… with the table. Removing partitions is easier and quicker than deleting a few hundred thousand rows… for example, if you need to delete. However, thank you Rick for pointing out my error in…

Filed Under: Insight for DBAs, MySQL Tagged With: , ,

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

…/mysql/test# myisamchk -rq test_concat – check record delete-chain – recovering (with sort) MyISAM-table ‘test_concat’ Data records: 3 – Fixing index…/mysql/test# myisamchk -rq test_concat – check record delete-chain – recovering (with sort) MyISAM-table ‘test_concat’ Data records: 3 – Fixing index…

Filed Under: MySQL

Post: MyISAM mmap feature (5.1)

May 26, 2006 By 10 Comments

…: memory mapping for the datafiles. That can be enabled by –myisam_use_mmap=1 startup option. In this case instead of systems call MyISAM will use memcpy function. There is a memory addressing limit… non key column (updaye-nokey) OLTP rw queries, SELECT/INSERT/DELETE/UPDATE queries (OLTP rw) batch select queries – scan of 20…

Filed Under: Insight for DBAs Tagged With: ,

Post: Concurrent inserts on MyISAM and the binary log

May 14, 2008 By 7 Comments

… I had an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements… Locked status. My first thought was that the customer had deleted from the table, which leaves “holes” in the middle of…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by…_checkpoint_limit | 180 | | synced_doc_id | 1028261 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…commanual Com_delete_multiblogpercona….myisam_recoverblogpercona.commanual myisam_recover_optionsblogpercona.commanual myisam_recover_optionsblogpercona.commanual myisam_repair_threadsblogpercona.commanual myisam_sort_buffer_sizeblogpercona.commanual myisam_stats_methodblogpercona.commanual myisam…

Filed Under: Insight for DBAs, MySQL

Post: Performance gotcha of MySQL memory tables

February 1, 2008 By 5 Comments

… random deletes by primary key (DELETE FROM test WHERE id=N) and get just about 80 deletes per second while converting table to MyISAM I get about 600 deletes per second and about 4000 deletes per second…

Filed Under: Benchmarks, Insight for Developers

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

…(10) unsigned NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`) -> ) ENGINE=MYISAM; Query OK, 0 rows affected (0.03 sec) mysql> insert… the table until first corrupted row and get result in MyISAM table ? Unfortunately test2 ended up to be empty after the… is also helpful in cases you’ve want to recover deleted rows or dropped table. I should also mention at Percona…

Filed Under: Insight for DBAs Tagged With:

Post: MySQL Server Variables - SQL layer or Storage Engine specific.

June 8, 2006 By 14 Comments

… if tables have “holes” (deleted rows) which can help significantly. delay_key_write Delay updating indexes for MyISAM tables. Good for performance… in practice it is MyISAM related. expire_logs_days This is replication/binlogging related options – when to delete binary logs from master… FullText search related variables. As FullText currently works with MyISAM tables these are MyISAM related. join_buffer_size Buffer used for joins…

Filed Under: Insight for DBAs Tagged With: ,

Post: Recovering CREATE TABLE statement from .frm file

December 17, 2008 By 5 Comments

…Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the … | test_myisam | CREATE TABLE `test_myisam` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM…

Filed Under: Insight for DBAs Tagged With: , ,
« Older Posts