June 20, 2013

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

… see them in information_scheme database in Percona Server though). The structure of these tables is following: SYS_TABLES: CREATE TABLE `SYS_TABLES` ( `NAME` varchar… Page Header Name Size Description FIL_PAGE_SPACE_OR_CHKSUM 4 /* in < MySQL-4.0.14 space id the page belongs to (== 0… some other table. In this case if ibdata was updated MySQL will fail to start with error: InnoDB: Reading tablespace information from the…

Filed Under: MySQL

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

February 26, 2013 By 1 Comment

… happens: mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select * from information_schema… | Table | Op | Msg_type | Msg_text | +———————-+———-+———-+———-+ | test.dir_test_innodb | optimize | status | OK | +———————-+———-+———-+———-+ 1 row in set (4.60 sec) mysql> select * from information…

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

Post: MySQL EXPLAIN limits and errors.

July 24, 2006 By 6 Comments

… rows by reading data from other tables. No information about using disk for sort or temporary tables MySQL always calls it “filesort” even if… fields can’t work with in memory temporary table. The other thing – since MySQL 4.1 sorting can be done with storing row…. Simple table like output format is good but with growing MySQL Server complexity it is getting impossible to present all relevant information this…

Filed Under: Insight for Developers Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…Records And Background Checks | 4.516233444213867 | …mysql: SHOW CREATE TABLE information_schema.innodb_ft_default_stopword\G *************************** 1. row *************************** Table: INNODB_FT_DEFAULT_STOPWORD Create Table: CREATE TEMPORARY TABLE…

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

Post: Modeling MySQL Capacity by Measuring Resource Consumptions

March 8, 2011 By 1 Comment

…take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat …help. One helpful way to use this information is to compare systems with different memory…# Rows sent 7.15M 0 56.36k 4.19 0.99 360.95 0.99 …0% yes, 99% no # Tmp table 1% yes, 98% no # Tmp table on 1% yes, 98% …

Filed Under: Insight for DBAs, MySQL

Post: MySQL extensions for hosting

April 24, 2008 By 11 Comments

… mysql> SHOW TABLE_STATISTICS LIKE ‘hosting2%’; +——————————–+———–+————–+————————-+ | Table | … 6 | | hosting2.wp_posts | 542 | 4 | 12 | | hosting2.wp_term_relationships …information find active tables which do not have any indexes used meaning table…

Filed Under: Percona Software Tagged With:

Post: MySQL - to use or not to use

May 28, 2007 By 10 Comments

… should say MySQL stability have not much improved (really going up and down) since MySQL 4.0. This even applies to Innodb tables which were already on par with general MySQL level of stability in MySQL 4.0 But… and Partitions and information schema for ease of management but rarely I would see large amount of big post MySQL 4 features used…

Filed Under: Insight for DBAs Tagged With:

Post: MySQL optimizer: ANALYZE TABLE and Waiting for table flush

February 27, 2013 By 3 Comments

The MySQL optimizer makes the decision of what execution plan to use based on the information provided by the storage engines. That information… better execution plan for another query we run ANALYZE TABLE: mysql> analyze table t; +——–+———+———-+———-+ | Table | Op | Msg_type | Msg_text | +——–+———+———-+———-+ | test.t | analyze | status… i=5 | | 11 | Waiting for table flush | select * from test.t where i=4 | | 11 | Waiting for table flush | select * from test.t…

Filed Under: MySQL Tagged With: , , , ,

Post: Logging Foreign Key errors

September 27, 2012 By Leave a Comment

… it: 1- Create the table First we create the table where we are going to store the information: CREATE TABLE foreign_key_errors ( ts… very informative. It’s not very clear where is the error. Let’s see the explanation with perror: # perror 150 MySQL error… to know what the problem is. 4- Let’s check what the tool has logged: mysql> select * from test.foreign_key_errors\G…

Filed Under: MySQL

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…table is blocked for any kind of operation. Let’s see the profiling information…table that is already in the Table Cache, any query to a table that has a DDL operation waiting, will block. Why? Because MySQL…

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