… latest copy of Java JRE for your platform You will need a Java driver for your database — I’m using the MySQL… Percona Server 5.6.10 sandbox: description=MySQL driver=com.mysql.jdbc.Driver connectionSpec=jdbc:mysql://127.0.0.1:5610/schemaspy driverPath… tabs: Tables – Names, number of children, parents, count of columns, row counts, and comments — a great way for a high level…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… fetching rows, thread declared inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id 38, OS thread handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… Locking behavior prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5.5.3… alters a big table can keep executing without any hindrance, copying rows from the table with the old structure to the table… piled up waiting for table metadata lock bringing down the MySQL server or causing all the connections to get depleted. There…
Post: pt-online-schema-change and default values
June 21, 2012 By 1 Comment
… rows… Dropping triggers… Dropped triggers OK. Dropping new table… Dropped new table OK. `sbtest`.`sbtest` was not altered. (in cleanup) Error copying rows from `sbtest`.`sbtest` to `sbtest`.`_sbtest_new`: Copying rows caused a MySQL error 1364: Level: Warning Code: 1364…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… of rows you should stop MySQL, take a copy of the salaries.ibd and start it again. Later, we’ll extract those deleted rows… table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries’; +————+ | ROW…
Post: Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
… the information about a particular user: mysql> select * from user_statistics where user=’root’\G *************************** 1. row *************************** USER: root TOTAL_CONNECTIONS: 25… lost. So if you really need that information you should copy it to another table or export to a csv for…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
….sakila’ doesn’t exist mysql> select count(*) from film; +———-+ | count(*) | +———-+ | 1000 | +———-+ 1 row in set (0.00 sec) mysql> exit And now if you look at the content of the queries.log file, you will see a copy of your session. Conclusion The mysql command line client is not as glossy as…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film_list; 997 rows in set… | removing tmp table | 0.095135 | 55.10 | 3 | 0.0317116667 | | Copying to tmp table | 0.046175 | 26.74 | 1 | …
Post: Load management Techniques for MySQL
May 6, 2012 By 3 Comments
One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though…
Post: Copying InnoDB tables between servers
July 31, 2009 By 11 Comments
…) Copying ./ibdata1 to /data/vadim/mysql/export//ibdata1 >> log scanned up to (3 769009554) …done Copying ./mysql/ibbackup_binlog_marker.ibd is skipped. Copying ./tpcc/stock.ibd is skipped. Copying ./tpcc/warehouse.ibd is skipped. Copying ./tpcc/new… be rolled back or cleaned up InnoDB: in total 107 row operations to undo InnoDB: Trx id counter is 0 1560320…
