June 18, 2013

Post: Recovering from a bad UPDATE statement

October 19, 2012 By 5 Comments

…table without a WHERE clause?  Did you know that in MySQL 5.5 that sometimes you can recover from a…1, old version of @2 to the new version of @2, etc. Reverse the entries row entries and create…

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

Post: Beware the Innodb Table Monitor

November 29, 2011 By 2 Comments

…a lot murkier than it is now.   To use it, you create a table (in any database you choose), like this: CREATE TABLE innodb_table_monitor (a…table, at least in the latest Percona Server 5.1 in: storage/innodb_plugin/dict/dict0load.c: line 226, and again in…

Filed Under: Insight for DBAs, MySQL

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

March 4, 2013 By 7 Comments

…in MySQL 5.6: Part 2, The Queries![/caption] This is part 2 in a 3 part series. In part 1, we took a…in the MySQL 5.6 documentation; every place where it talks about creating one of these stopword tables, it simply mentions the table…

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

Comment: MySQL Server Memory Usage

September 1, 2006 By 21 Comments

…0% 0.0% 9.5% 0.0% 0.3% 6.5% 68.4% cpu00 22.1% 0.0% 11.1% 0.1% 1.1% 6.1% 59.1% cpu01 14.9% 0.0% 7.5% 0.0% 0.0% 5.9% 71.5% cpu02 10.5% 0.0% 12.5% 0.0% 0.1% 7.1…

Filed Under: Insight for DBAs Tagged With: , ,

Post: The case for getting rid of duplicate “sets”

May 17, 2011 By 2 Comments

…5.49 sec) Now, what if I structure my data differently? We can “compress” the table in the database by removing the duplicates: mysql> create table…in my set? First, lets create a table to hold the set of numbers (and their desired minimum cardinality) to a “search set” table: CREATE TABLE…

Filed Under: MySQL

Post: MySQL 5.5 and MySQL 5.6 default variable values differences

February 21, 2013 By 8 Comments

… than 200 tables in this system. innodb_stats_on_metadata is disabled by default in MySQL 5.6 Welcome …tables or if you do many create/drop of innodb tables. optimizer_switch is the catch all variable for a…_index_extensions=on 1 row in set (0.00 sec) Summary: MySQL 5.6 comes with quite a few changes …

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

Post: Replication checksums in MySQL 5.6

February 15, 2013 By 3 Comments

…0 rows affected (0,00 sec) Now let’s write something on the master: master [localhost] {msandbox} ((none)) > create database sakila; Query OK, 1…in MySQL 5.6 will ensure data integrity in all scenarios, but they do improve reliability of the whole replication process. This is a…

Filed Under: MySQL Tagged With: , , ,

Post: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE

January 19, 2007 By 17 Comments

…mysql.com/doc/refman/5.0/en/memory-storage-engine.html MEMORY table contents are stored in memory, which is a property that MEMORY tables…table_size system variable to impose a maximum size on MEMORY tables. For individual tables, you can also specify a MAX_ROWS table option in the CREATE TABLE…

Filed Under: Insight for DBAs Tagged With: ,

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

… CHARSET=latin1 CREATE TABLE `orders` ( `o_orderkey` int(11) NOT NULL DEFAULT ‘0‘, `o_custkey` int(11) DEFAULT NULL, `o_orderstatus` char(1) DEFAULT… so MySQL 5.5 prefers to do a PRIMARY index scan of the table orders resulting in sequential IO, while both MySQL 5.6 and MariaDB 5.5 prefer to…

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

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…DATE_FORMAT(order_date,’%Y%m’)) 1 row in set, 1 warning (0.00 sec) The complete … not much different from building a table with CREATE TABLE .. AS SELECT. In fact, this is …created: mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,'dashboard_top_customers’)); Query OK, 0 rows affected (5…

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