Archive for March, 2012

Simple Query to identify Foreign Key references on Deadlocked Tables

Wednesday, March 21st, 2012

The other day, I was troubleshooting a deadlock, and I wondered if any of the table’s columns were referenced by any foreign keys (fks) from any other tables in the instance.

Well, this is actually very simple with information_schema (I_S):

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME='parent';

Where `parent` is the name of the table you’re searching for.

Note this query does not restrict on the database, or schema, name, but that could easily be added (or any other number of conditions). Here is an example where I only return the most useful columns (which could be useful for determining said conditions):

SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA,
	TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA,
	REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME='parent';

If there are any fks, this will return something like:

+-------------------+-----------------+--------------+------------+-------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |
+-------------------+-----------------+--------------+------------+-------------+
| test              | child_ibfk_1    | test         | child      | parent_id   |
+-------------------+-----------------+--------------+------------+-------------+
+-------------------------+-----------------------+------------------------+
| REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-------------------------+-----------------------+------------------------+
| test                    | parent                | id                     |
+-------------------------+-----------------------+------------------------+

Hope this helps.

 

Save 10% on Percona Live MySQL Conference & Expo

Tuesday, March 20th, 2012

If you still have not signed up for the Percona Live MySQL Conference & Expo (week of April 9th), use the following discount code from SkySQL to receive 10% off when registering:

SKY-PL12

And while you’re there, don’t forget to attend the following events on the day-after (4/13/2012) the Expo:

 

MariaDB 5.3 is now GA

Tuesday, March 20th, 2012

This is another, in-case-you-missed-it, but it is definitely worth mentioning again:

MariaDB 5.3 is now GA

So, if you’re using MariaDB 5.1 or 5.2, I’d definitely recommend upgrading to the new MariaDB 5.3.

Also, if you’re a Windows user using MySQL 5.1, I’d highly consider making the switch to MariaDB 5.3 due to all of the Windows-specific improvements in it.

MariaDB 5.3 Enhancements:

  • Subquery optimizations
  • Semi-join subquery optimizations
  • Non-semi-join optimizations
  • Subquery Cache
  • Subquery Optimizations Map
  • Optimizations for derived tables and views
  • Disk access optimization
  • Join optimizations
  • Index Merge improvements
  • Optimizer control
  • NoSQL-style interfaces
  • Group commit for the binary log
  • Replication and binary logging enhancements
  • Microsecond support for NOW() and timestamp, time, and datetime columns
  • Windows performance improvements
  • And a number of other general improvements

MariaDB 5.3 Windows-Specific Performance improvements include:

  • Backported Windows performance patches from MySQL 5.5
  • Asynchronous IO in XtraDB is redesigned and is now faster, due to the use of IO completion ports
  • Additional durability option for XtraDB : innodb_flush_method can now be O_DSYNC, like on Unixes. (The effect of using this option is that the log file is opened with FILE_FLAG_WRITETHROUGH, and FlushFileBuffers() is not done. This may improve speed in write-heavy scenarios.)
  • A new Windows MSI installer
  • Includes a GUI-tool, HeidiSQL

All of the above, and more are documented in further detail here:

http://kb.askmonty.org/en/what-is-mariadb-53

You can download it here:

http://downloads.askmonty.org/mariadb/5.3/

(And just for reference, if running MariaDB you should probably keep your eye on the new MariaDB 5.5 too (5.5.20 was alpha, 5.5.21 is now beta), benchmarks here).

Hope this helps.

MariaDB 5.5 has been Released

Thursday, March 8th, 2012

In case you didn’t notice it last week, MariaDB 5.5 (5.5.20, specifically) has been released.

It’s a release I’ve been looking forward to for a while now, so I’m very excited that it’s now available! :)

Note that it is currently ‘alpha’ since it is the very first release of MariaDB 5.5. However, it is not your typical new ‘alpha’, so I would expect it to be GA in the not-so-distant future (please don’t quote me on this though, as I have no idea of the true time table – but the goal is for the next 5.5 release to be ‘beta’).

You can download MariaDB 5.5 from the following location:

http://downloads.askmonty.org/mariadb/5.5/

There are also more details on this 5.5.20 MariaDB version here:

What is MariaDB 5.5
Release Notes
Changelog
General Info

 

SkySQL Training schedule is Now Available

Tuesday, March 6th, 2012

I’m happy to announce the new SkySQL Training schedule is now available.

In fact, some classes began this week.

Here is the upcoming Performance Tuning class schedule:

  • Mar 05, 2012 – Mar 08, 2012 :: San Francisco
  • Mar 27, 2012 – Mar 30, 2012 :: Paris
  • Apr 02, 2012 – Apr 05, 2012 :: Boston
  • May 15, 2012 – May 18, 2012 :: Paris
  • Jun 18, 2012 – Jun 21, 2012 :: Paris

More details on the Performance Tuning Class are here:

http://www.skysql.com/services/training/courses/performance-tuning-mysql-database

And note the Performance Tuning class is not the only available class at the moment. The full SkySQL Training Schedule can be found here:

http://www.skysql.com/services/training/schedule


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty