June 18, 2013

Post: Modeling MySQL Capacity by Measuring Resource Consumptions

March 8, 2011 By 1 Comment

…You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld…Full scan 0% yes, 99% no # Tmp table 1% yes, 98% no # Tmp table on 1% yes, 98% no In …an understanding how much your queries (at least most important ones) cost you in terms of CPU and IO. …

Filed Under: Insight for DBAs, MySQL

Post: Hidden columns of query_review_history table

August 28, 2012 By Leave a Comment

… to process a MySQL slow query log and store historical values for review trend analysis into query_review_history table. According to its official documentation you can populate many columns in that table but there are other important ones such as…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: A recovery trivia or how to recover from a lost ibdata1 file

June 3, 2011 By 8 Comments

… with the one provided by the customer Import the tablespace At this point I should have access to the table but, although MySQL was…/lib/mysql/recover/ chown mysql.mysql /var/lib/mysql/recover/$CURRENT_TABLENAME.ibd sleep 1 echo “importing tablespace” mysql -u root -e “alter table recover.$CURRENT_TABLENAME import…

Filed Under: MySQL

Post: Copying InnoDB tables between servers

July 31, 2009 By 11 Comments

… remote server you have to run MySQL server ® with XtraDB6 storage engine, you can take one of our binary builds with 5… check mysql> select count(*) from order_line; +———-+ | count(*) | +———-+ | 32093604 | +———-+ 1 row in set (3 min 29.32 sec) New table has been imported!

Filed Under: Percona Software Tagged With: , , , , ,

Post: Should we give a MySQL Query Cache a second chance ?

April 10, 2011 By 64 Comments

…different developers. Supporting different comments is especially important as many application put extra information … We could implement API which would allow one to use local memory, Memcache or …be presented as some INFORMATION_SCHEMA tables. Self Refresh MySQL Query Cache causes not uniform …

Filed Under: Insight for DBAs, MySQL

Post: How to recover deleted rows from an InnoDB Tablespace

February 20, 2012 By 4 Comments

… a single directory with some subdirectories inside, one for every single index in the table: ~/recovery-tool# ls pages-1329221407/FIL_PAGE… is to import it in our database: mysql (employees) > LOAD DATA INFILE ‘/root/recovery-tool/data/salaries.recovery’ REPLACE INTO TABLE `salaries` FIELDS…

Filed Under: Insight for DBAs, MySQL

Post: Why you should ignore MySQL's key cache hit ratio

February 28, 2010 By 28 Comments

…when you start to assign any importance to this ratio. The MySQL manual doesn’t fall into this …(23% miss ratio). Server #2 has one trillion Key_reads, and one hundred quatrillion Key_read_requests (… more important to you, etc) and choose an amount of memory based on how important those tables are to…

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

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

March 4, 2013 By 7 Comments

…”. OK, sounds easy enough: mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE TABLE `innodb_myisam_stopword` ( `value… in the MySQL 5.6 documentation; every place where it talks about creating one of these stopword tables, it simply mentions the table engine… important best practice even if FTS isn’t involved. Always test how your application behaves as a result of a major MySQL…

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

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…1 results in 587MB of raw data, mostly in one  table (lineorder). Each of the 13 queries were executed …Default config for MySQL 5.5 and MySQL 5.6, no tuning at all Config 2: MySQL 5.6 with …to “pollution” by full table scans.  In the classic LRU, a full table scan pushes out important hot pages from …

Filed Under: MySQL Tagged With: , , , , ,

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…behave with query_cache? That is an important question. If Query Cache is enabled and…table that is already in the Table Cache, any query to a table that has a DDL operation waiting, will block. Why? Because MySQL… Now everyday one of the tables was renamed and swapped and that table was the one that …

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