You searched for latin1 utf 8 - MySQL Performance Blog

How to recover table structure from InnoDB dictionary

April 22, 2013 By Aleksandr Kuzminsky 4 Comments

To recover a dropped or corrupt table with Percona Data Recovery Tool for InnoDB you need two things: media with records(ibdata1, *.ibd, disk image, etc.) and a table structure. Indeed, there is no information about the table structure in an InnoDB page. Normally we either recover the structure from .frm files or take it from [...]

Filed Under: Insight for DBAs, MySQL Tagged With: InnoDB, kuzminsky, Recovery, SYS_COLUMNS, SYS_FIELDS, SYS_INDEXES, SYS_TABLES

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

March 4, 2013 By Ernie Souhrada 7 Comments

This is part 2 in a 3 part series. In part 1, we took a quick look at some initial configuration of InnoDB full-text search and discovered a little bit of quirky behavior; here, we are going to run some queries and compare the result sets. Our hope is that the one of two things [...]

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: Ernie Souhrada, FTS, FullText Search, InnoDB, MyISAM, MySQL 5.6

CentOS 5.8 users: your UTF-8 data is in peril with Perl MySQL

February 22, 2013 By Daniel Nichter 3 Comments

CentOS 5.8 and earlier use Perl module DBD::mysql v3.0007 which has a bug that causes Perl not to flag UTF-8 data as being UTF-8. Presuming that the MySQL table/column is using UTF-8, and the Perl MySQL connection is also using UTF-8, then a correct system returns: PV = 0x9573840 "\343\203\213 \303\250"\0 [UTF8 "\x{30cb} \x{e8}"] That’s a Devel::Peek inside a Perl scalar variable which [...]

Filed Under: Insight for Developers, Percona Toolkit Tagged With: CentOS 5, Daniel Nichter, DBD-mysql, MySQL, Perl, UTF-8

Best kept MySQLDump Secret

March 23, 2012 By Peter Zaitsev 13 Comments

Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it works, but did you know there are some cases when you can get table entirely missing from the backup if you use this technique ? The problem comes from the fact how MySQL’s [...]

Filed Under: Insight for DBAs, MySQL

Connecting orphaned .ibd files

May 13, 2011 By Aleksandr Kuzminsky 19 Comments

There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in [...]

Filed Under: MySQL

Where does HandlerSocket really save you time?

March 16, 2011 By Ryan Lowe Leave a Comment

HandlerSocket has really generated a lot of interest because of the dual promises of ease-of-use and blazing-fast performance. The performance comes from eliminating CPU consumption. Akira Higuchi’s HandlerSocket presentation from a couple of months back had some really good profile results for libmysql versus libhsclient (starting at slide 15). Somebody in the audience at Percona [...]

Filed Under: Insight for DBAs, MySQL Tagged With: HandlerSocket, MySQL, NoSQL, oprofile

Converting Character Sets

March 17, 2009 By Ryan Lowe 21 Comments

The web is going the way of utf8.Â Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8.Â Googling for “mysql convert charset to utf8″ results in a plethora of sites, [...]

Filed Under: Insight for Developers Tagged With: Community, Tips, Tools

JOIN Performance & Charsets

October 21, 2008 By Ryan Lowe 2 Comments

We have written before about the importance of using numeric types as keys, but maybe you’ve inherited a schema that you can’t change or have chosen string types as keys for a specific reason. Either way, the character sets used on joined columns can have a significant impact on the performance of your queries. Take [...]

Filed Under: Uncategorized Tagged With: Character Sets, JOIN Performance, Tips, Tuning

Testing InnoDB “Barracuda” format with compression

April 23, 2008 By Vadim Tkachenko 3 Comments

New features of InnoDB – compression format and fast index creation sound so promising so I spent some time to research time and sizes on data we have on our production. The schema of one of shards is

CREATE TABLE `article87` (
  `id` bigint(20) unsigned NOT NULL,
  `ext_key` varchar(32) NOT NULL,
  `site_id` int(10) unsigned NOT NULL,
  `forum_id` int(10) unsigned NOT NULL,
  `thread_id` varchar(255) CHARACTER SET latin1 NOT NULL,
  `published` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `crawled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `subject` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `num_links` smallint(6) NOT NULL,
  `links_in` int(10) unsigned NOT NULL,
  `cache_author` varchar(255) NOT NULL,
  `cache_site` varchar(255) DEFAULT NULL,
  `anchor` varchar(255) NOT NULL,
  `isthread` tinyint(3) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  `inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fromfile` varchar(255) NOT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `encoding` varchar(255) NOT NULL,
  `warning` mediumtext NOT NULL,
  `is_thread_start` tinyint(3) unsigned NOT NULL,
  `source` mediumint(8) unsigned NOT NULL,
  `hash` char(32) NOT NULL,
  `mod_is` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `is_adult` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `bodyuc` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
 KEY `ext_key` (`ext_key`),
 KEY `forum_id` (`forum_id`,`thread_id`,`published`),
 KEY `site_id` (`site_id`,`published`),
 KEY `hash` (`hash`),
 KEY `forum_id_2` (`forum_id`,`is_thread_start`,`published`),
 KEY `published` (`published`),
 KEY `inserted` (`inserted`),
 KEY `forum_id_3` (`forum_id`,`thread_id`,`is_thread_start`),
 KEY `site_id_2` (`site_id`,`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `article87` (

`id` bigint(20) unsigned NOT NULL,

`ext_key` varchar(32) NOT NULL,

`site_id` int(10) unsigned NOT NULL,

`forum_id` int(10) unsigned NOT NULL,

`thread_id` varchar(255) CHARACTER SET latin1 NOT NULL,

`published` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`crawled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`subject` varchar(255) NOT NULL,

`title` varchar(255) NOT NULL,

`url` varchar(255) NOT NULL,

`num_links` smallint(6) NOT NULL,

`links_in` int(10) unsigned NOT NULL,

`cache_author` varchar(255) NOT NULL,

`cache_site` varchar(255) DEFAULT NULL,

`anchor` varchar(255) NOT NULL,

`isthread` tinyint(3) unsigned NOT NULL,

`author_id` int(10) unsigned NOT NULL,

`inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`fromfile` varchar(255) NOT NULL,

`language_id` tinyint(3) unsigned NOT NULL,

`encoding` varchar(255) NOT NULL,

`warning` mediumtext NOT NULL,

`is_thread_start` tinyint(3) unsigned NOT NULL,

`source` mediumint(8) unsigned NOT NULL,

`hash` char(32) NOT NULL,

`mod_is` tinyint(3) unsigned NOT NULL DEFAULT '0',

`is_adult` tinyint(3) unsigned NOT NULL DEFAULT '0',

`bodyuc` mediumtext NOT NULL,

PRIMARY KEY (`id`),

KEY `ext_key` (`ext_key`),

KEY `forum_id` (`forum_id`,`thread_id`,`published`),

KEY `site_id` (`site_id`,`published`),

KEY `hash` (`hash`),

KEY `forum_id_2` (`forum_id`,`is_thread_start`,`published`),

KEY `published` (`published`),

KEY `inserted` (`inserted`),

KEY `forum_id_3` (`forum_id`,`thread_id`,`is_thread_start`),

KEY `site_id_2` (`site_id`,`author_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Filed Under: Insight for DBAs Tagged With: InnoDB, Production

Using CHAR keys for joins, how much is the overhead ?

June 18, 2007 By Peter Zaitsev 18 Comments

I prefer to use Integers for joins whenever possible and today I worked with client which used character keys, in my opinion without a big need. I told them this is suboptimal but was challenged with rightful question about the difference. I did not know so I decided to benchmark. The results below are for [...]

Filed Under: Insight for Developers Tagged With: Bugs, InnoDB, MyISAM, Tips

How to recover table structure from InnoDB dictionary

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

CentOS 5.8 users: your UTF-8 data is in peril with Perl MySQL

Best kept MySQLDump Secret

Connecting orphaned .ibd files

Where does HandlerSocket really save you time?

Converting Character Sets

JOIN Performance & Charsets

Testing InnoDB “Barracuda” format with compression

Using CHAR keys for joins, how much is the overhead ?

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA