June 19, 2013

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

February 26, 2013 By 1 Comment

… first part (this one) will be a very quick overview of FTS in InnoDB and some observations that I’ve made while… two, delete some data, and see what happens: mysql> set global innodb_ft_aux_table=’test/dir_test_innodb‘; mysql> set global innodb_optimize…) mysql> delete from dir_test_innodb LIMIT 200000; Query OK, 200000 rows affected (8.65 sec) mysql> optimize table dir_test_innodb; +———————-+———-+———-+———-+ | Table…

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

Post: Tuning InnoDB Concurrency Tickets

May 24, 2010 By 5 Comments

…_id), -> FOREIGN KEY (parent_id) REFERENCES parent(id) -> ON DELETE CASCADE -> ) ENGINE=INNODB; — 0 Tickets Used Query OK, 0 rows affected (0… larger (important) select statements. A quick sysbench run gives us the following results (X-axis is innodb_concurrency_tickets, Y-axis is…=1900M innodb_thread_concurrency=16 innodb_flush_method = O_DIRECT innodb_write_io_threads=8 innodb_read_io_threads=8 innodb_io_capacity=500 innodb_max…

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

Post: Lost innodb tables, xfs and binary grep

November 9, 2010 By 12 Comments

… case I worked on yesterday, here’s a quick tip – having a database backup does not mean …copy of the data The important tables were all InnoDB Having a backup, customer has first attempted to …through various data loss scenarios. One of them was deleted files on xfs – we all agreed on few things…

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

Post: Can Innodb Read-Ahead reduce read performance ?

June 26, 2007 By 2 Comments

… open inside InnoDB Main thread process no. 3956, id 1157658976, state: sleeping Number of rows inserted 60790248, updated 11571576, deleted 0, read…, 0.00 deletes/s, 24860.96 reads/s The stats are rather interesting. There are 8 queries inside Innodb and we can… Innodb read-ahead works by having more overlap between requests so thread would rarely need to stall unable to perform any quick…

Filed Under: Insight for DBAs Tagged With: ,

Post: Innodb locking and Foreign Keys

December 12, 2006 By 2 Comments

… with foreign keys in Innodb. I’ve only done a quick check so could be missing some details. All Innodb Foreign Key related… will try to delete row in PARENT table lockup in CHILD table will be performed with row lock performed. Innodb is smart… happen as it is internally implemented as special form of DELETE+INSERT. But this is something you should not be doing…

Filed Under: Insight for DBAs Tagged With:

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

… that with something like insert into MyISAM_table… select * from Innodb_table… would take about 10 days. The bottleneck was clearly… 1 myisamchk: error: Couldn’t fix table with quick recovery: Found wrong number of deleted records myisamchk: error: Run recovery again without… sec) So varchar columns are supported without any issue but, deleted rows prevent the use of the quick option for myisamchk.

Filed Under: MySQL

Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5

May 20, 2011 By 11 Comments

…looks something like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN (62715996, 62716592, 62717660,…::read _multi_range_next,QUICK_RANGE_SELECT::get_next,rr_quick,sub_select,do_select,JOIN::…things a lot worse in this workload and innodb_adaptive_hash_index_partitions=16 available for …

Filed Under: MySQL

Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs

February 23, 2012 By 3 Comments

…e. updates on CentOS. If you are using InnoDB, it is also important that your transaction logs…was able to pinpoint the position of the delete statement, it is at 336797160. [root@sb logs]#…or server upgrade, this method can be good quick rollback procedure in case something fails during the operation…

Filed Under: Insight for DBAs

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…this column. It is used to prevent wide innodb primary keys on the MV. mysql> select…lines table, three orders of magnitude more quickly than COUNT(*). mysql> select count(*) cnt from order… aggregate functions. Now I delete some line items from orders: mysql> delete -> from order_lines -> where order…

Filed Under: Insight for DBAs, MySQL

Post: Paul McCullagh answers your questions about PBXT

November 20, 2009 By 2 Comments

…both MyISAM and InnoDB/XtraDB. Tests show that PBXT’s performance is similar to InnoDB but, depending …which record versions are no longer needed and deletes them from the database. The Checkpointer flushes … The affect is that the init function returns quickly, but the first queries that access PBXT …

Filed Under: Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts