June 19, 2013

Post: Be productive with the MySQL command line

December 21, 2012 By 14 Comments

…: mysql> pager grep sequence PAGER set to ‘grep sequence‘ mysql> show engine innodb status\Gselect sleep(60);show engine innodb status\G Log sequence number … with -A Database changed mysql> select count(*) from sakila; ERROR 1146 (42S02): Table ‘sakila.sakila’ doesn’t exist mysql> select count(*) …

Filed Under: Insight for DBAs, MySQL

Post: Percona XtraDB Cluster reference architecture with HaProxy

June 20, 2012 By 63 Comments

… sequence. Need state transfer. 120619 13:20:19 [Note] WSREP: Running: ‘wsrep_sst_xtrabackup ‘joiner’ ’10.195.206.117′ ” ‘/var/lib/mysql…-rel25.3 started; log sequence number 246661644 120619 13:21:19 [Note] Recovering after a crash using mysql-bin 120619 13:21…’s create a database and a userfor it. mysql> create database sbtest; Query OK, 1 row affected (0.01 sec) mysql> grant all on…

Filed Under: MySQL, Percona Software, XtraDB Cluster Tagged With:

Post: Recovery deleted ibdata1

August 10, 2012 By 9 Comments

… for ibdconnect, but as long as MySQL isn’t restarted it is possible to recover database fast. Let me illustrate how. Let’s simulate the accident. For that I will delete /var/lib/mysql/ib… checkpoint age is zero, all pages are flushed: — LOG — Log sequence number 363096003 Log flushed up to 363096003 Last checkpoint at…

Filed Under: Insight for DBAs, MySQL Tagged With:

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

February 26, 2013 By 1 Comment

… in our database directory: -rw-rw—-. 1 mysql mysql 8632 Feb 20 15:54 dir_test_innodb.frm -rw-rw—-. 1 mysql mysql 213909504 Feb… defined for the underlying table, or it can be a sequence value generated by InnoDB when the table does not contain… unless you also do something to provide some sort of sequence value for that column, whether as an auto_increment value…

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

Comment: Stored Function to generate Sequences

May 8, 2008 By 17 Comments

[...] Stored Function to generate Sequences | MySQL Performance Blog – You could combine this with an INSERT trigger and get even more auto_increment like functionality. Tags: database mysql sequence [...]

Filed Under: Insight for Developers Tagged With: ,

Post: Evaluating IO subsystem performance for MySQL Needs

March 5, 2008 By 23 Comments

… the system. What I usually look for MySQL is performance in random reads and random … log writes, which are synchronous IO operation database performance which typically have rather close data … scale benchmarks. SysBench by default has repeatable sequence of requests, because it does not initialize …

Filed Under: Benchmarks, Hardware and Storage Tagged With:

Post: Edge-case behavior of INSERT...ODKU

October 27, 2012 By 6 Comments

…a customer issue wherein they were observing database performance that dropped through the floor (to…KEY(username) ) ENGINE=InnoDB; Now consider the following sequence of events. (root@localhost) [test]> INSERT INTO …be considered as being defined first. So, MySQL checks our INSERT, sees that the next…

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

Post: How to recover a single InnoDB table from a Full Backup

January 25, 2012 By 10 Comments

… InnoDB shared tablespace (ibdata) and the transaction IDs and log sequence numbers that are stored in the tablespace files also differ…: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries DISCARD TABLESPACE; Copy the salaries.ibd files from the backup to the database data directory: # cp /tmp/2012-01-22_14-13-20/employees/salaries.ibd /var/lib/mysql/data/employees…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

… thread declared inside InnoDB 442 mysql tables in use 1, locked 0 MySQL thread id 8079, query …so such log sequences can’t be overwritten in log files. By monitoring log sequence number and value…16773888 Buffer pool size 262144 Free buffers 0 Database pages 258053 Modified db pages 37491 Pending …

Filed Under: Insight for DBAs Tagged With:

Post: Lost innodb tables, xfs and binary grep

November 9, 2010 By 12 Comments

… about the system and how data was lost: MySQL had a dedicated partition on XFS file … all had same setting Developer accidentally ran DROP DATABASE X on the wrong machine (production master) All… Instead I had to grep for specific character sequences against all pages. Note however that only works…

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