You searched for user statistics patch - Page 2 of 3

InnoDB: look after fragmentation

November 5, 2009 By Vadim Tkachenko 12 Comments

One problem made me puzzled for couple hours, but it was really interesting to figure out what’s going on. So let me introduce problem at first. The table is

CREATE TABLE `c` (
  `tracker_id` int(10) unsigned NOT NULL,
  `username` char(20) character set latin1 collate latin1_bin NOT NULL,
  `time_id` date NOT NULL,
  `block_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`tracker_id`,`username`,`time_id`),
  KEY `block_id` (`block_id`)
) ENGINE=InnoDB

CREATE TABLE `c` (

`tracker_id` int(10) unsigned NOT NULL,

`username` char(20) character set latin1 collate latin1_bin NOT NULL,

`time_id` date NOT NULL,

`block_id` int(10) unsigned default NULL,

PRIMARY KEY (`tracker_id`,`username`,`time_id`),

KEY `block_id` (`block_id`)

) ENGINE=InnoDB

Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk The problem is that after restoring table from mysqldump, the query that scans data [...]

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

How (not) to find unused indexes

October 16, 2009 By Morgan Tocker 2 Comments

I’ve seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.Â This method is flawed – here’s the first reason why:

CREATE TABLE `sales` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) DEFAULT NULL,
`status` enum('archived','active') DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `status` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;
mysql> SELECT count(*), status FROM sales GROUP by status;
+----------+---------+
| count(*) | statusÂ  |
+----------+---------+
|Â Â Â  65536 | archived |
|Â Â Â Â Â  154 | activeÂ  |
+----------+---------+
2 rows in set (0.17 sec)
mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | keyÂ Â Â  | key_len | refÂ Â  | rows | ExtraÂ Â Â Â Â Â  |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
|Â  1 | SIMPLEÂ Â Â Â Â  | sales | refÂ  | statusÂ Â Â Â Â Â Â  | status | 2Â Â Â Â Â Â  | const |Â  196 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | keyÂ  | key_len | refÂ  | rowsÂ  | ExtraÂ Â Â Â Â Â  |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|Â  1 | SIMPLEÂ Â Â Â Â  | sales | ALLÂ  | statusÂ Â Â Â Â Â Â  | NULL | NULLÂ Â Â  | NULL | 65690 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)

CREATE TABLE `sales` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`customer_id` int(11) DEFAULT NULL,

`status` enum('archived','active') DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `status` (`status`)

) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1;

mysql> SELECT count(*), status FROM sales GROUP by status;

+----------+---------+

| count(*) | statusÂ |

+----------+---------+

|Â Â Â 65536 | archived |

|Â Â Â Â Â 154 | activeÂ |

+----------+---------+

2 rows in set (0.17 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

1 row in set (0.06 sec)

mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.01 sec)

The cardinality of status index is woeful, but provided that the application [...]

Filed Under: Insight for DBAs, MySQL Tagged With: explain, indexing, MySQL, Tips

SystemTap – DTrace for Linux ?

September 14, 2009 By Vadim Tkachenko 17 Comments

Since DTrace was released for Solaris I am missing it on Linux systems… It can’t be included in Linux by the same reason why ZFS can’t be – it’s licensing issue. Both ZFS and DTrace are under CDDL, which is incompatible with GPL. So you can see DTrace and ZFS on Solaris, FreeBSD, MacOS, but [...]

Filed Under: Insight for DBAs Tagged With: Operating Systems, Tools

XtraDB: The Top 10 enhancements

August 13, 2009 By Morgan Tocker 5 Comments

Note: This post is part 2 of 4 on building our training workshop. Last week I talked about why you don’t want to shard. This week I’m following up with the top 10 enhancements that XtraDB has over the built-in InnoDB included in MySQL 5.0 and 5.1.Â Building this list was not really a scientific [...]

Filed Under: Percona Software Tagged With: improvements, InnoDB, MySQL, Tips, XtraDB

check-unused-keys: A tool to interact with INDEX_STATISTICS

June 26, 2009 By Ryan Lowe 10 Comments

With the growing adoption of Google’s User Statistics Patch**, the need for supporting scripts has become clear. To that end, we’ve created check-unused-keys, a Perl script to provide a nicer interface than directly querying the INFORMATION_SCHEMA database.

Filed Under: Insight for DBAs Tagged With: Community, MySQL Indexes, Percona Patched MySQL, Tips, Tools

Profiling MySQL stored routines

January 19, 2009 By Aurimas Mikalauskas 4 Comments

These days I’m working with a customer who has an application based entirely on stored routines on MySQL side. Even though I haven’t worked much with stored procedures, I though it’s going to be a piece of cake. In the end – it was, but there’s a catch.

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

MySQL Binaries Percona build10

December 11, 2008 By Vadim Tkachenko 12 Comments

We made new binaries for MySQL 5.0.67 build 10 which include next fixes: We addressed concerns about potential logging and statistics overhead, so now you can fully turn on / off query statistics for microslow patch and user statistics in runtime. Next variables were added:

| slow_query_log                  | ON                                           |
| slow_query_log_file             | /var/lib/mysql/slow_query.log                |
for compatibility with MySQL 5.1
and
| userstat_running                | OFF                                          |
to control gathering of user statistics.

| slow_query_log | ON |

| slow_query_log_file | /var/lib/mysql/slow_query.log |

for compatibility with MySQL 5.1

and

| userstat_running | OFF |

to control gathering of user statistics.

With both slow_query_log = OFF and userstat_running = OFF [...]

Filed Under: Percona Software Tagged With: Patches, Percona

How Percona does a MySQL Performance Audit

November 24, 2008 By Baron Schwartz 17 Comments

Our customers or prospective customers often ask us how we do a performance audit (it’s our most popular service). I thought I should write a blog post that will both answer their question, so I can just reply “read all about it at this URL” and share our methodology with readers a little bit. This [...]

Filed Under: Insight for Developers Tagged With: Tuning

Percona build7 with latest patches

November 14, 2008 By Vadim Tkachenko 26 Comments

We made new binaries for MySQL 5.0.67 build 7 which include patches we recently announced. The -percona release includes:

| innodb_check_defrag.patch                        | Session status to check fragmentation of the last InnoDB scan                            | 1.0     | Percona <info@percona.com>       | GPL     | The names are Innodb_scan_*                                                                      |
| userstatsv2.patch                                | SHOW USER/TABLE/INDEX statistics                                                         | V2      | Google                           | GPL     | Added INFORMATION_SCHEMA.*_STATISTICS                                                            |
| show_patches.patch                               | SHOW PATCHES                                                                             | 1.0     | Jeremy Cole                      | N/A     |                                                                                                  |
| innodb_io_patches.patch                          | Cluster of past InnoDB IO patches                                                        | 1.0     | Percona                          | GPL     | This patch contains fixed (control_flush_and_merge_and_read, control_io-threads, adaptive_flush) |
| innodb_show_hashed_memory.patch                  | Adds additional information of InnoDB internal hash table memories in SHOW INNODB STATUS | 1.0     | Percona <info@percona.com>       | GPL     |                                                                                                  |
| innodb_io_pattern.patch                          | Information schema table of InnoDB IO counts for each datafile pages                     | 1.0     | Percona <info@percona.com>       | GPL     | INFORMATION_SCHEMA.INNODB_IO_PATTERN                                                             |
| microsec_process.patch                           | Adds INFOMATION_SCHEMA.PROCESSLIST with TIME_MS column                                   | 1.0     | Percona <info@percona.com>       | GPL     |                                                                                                  |
| innodb_locks_held.patch                          | Add locks held, remove locked records in SHOW INNODB STATUS                              | 1.0     | Baron Schwartz <baron@xaprb.com> | GPL     | Bug #29126 fix                                                                                   |
| microslow_innodb.patch                           | Extended statistics in slow.log                                                          | 1.1     | Percona <info@percona.com>       | GPL     |                                                                                                  |
| mysqld_safe_syslog.patch                         | Patch allows redirect output of error.log to syslog-ng                                   | 1.0     | Percona <info@percona.com>       | GPL     | Ported from Debian                                                                               |
| innodb_fsync_source.patch                        | Information of fsync callers in InnoDB                                                   | 1.0     | Google                           | GPL     |                                                                                                  |
| innodb_show_bp.patch                             | show innodb buffer pool content                                                          | 1.0     | Percona <info@percona.com>       | GPL     |                                                                                                  |

and -percona-highperf release additionaly includes

| split_buf_pool_mutex_fixed_optimistic_safe.patch | InnoDB patch to fix buffer pool scalability                                              | 1.0     | Yasufumi Kinoshita               | BSD     |                                                                                                  |
| innodb_rw_lock.patch                             | Fix of InnoDB rw_locks                                                                   | 1.0     | Yasufumi Kinoshita               | BSD     |

1 2	\| split_buf_pool_mutex_fixed_optimistic_safe.patch \| InnoDB patch to fix buffer pool scalability \| 1.0 \| Yasufumi Kinoshita \| BSD \| \| \| innodb_rw_lock.patch \| Fix of InnoDB rw_locks \| 1.0 \| Yasufumi Kinoshita \| BSD \|

You can download RPMs for RedHat / CentOS 4.x and 5.x for x86_64, binaries, sources and patches there

Filed Under: Percona Software Tagged With: Patches, Percona, Release

Three ways to know when a MySQL slave is about to start lagging

October 8, 2008 By Baron Schwartz 8 Comments

The trouble with slave lag is that you often can’t see it coming. Especially if the slave’s load is pretty uniform, a slave that’s at 90% of its capacity to keep up with the master can be indistinguishable from one that’s at 5% of its capacity. So how can you tell when your slave is [...]

Filed Under: Insight for DBAs Tagged With: Replication, Tips

InnoDB: look after fragmentation

How (not) to find unused indexes

SystemTap – DTrace for Linux ?

XtraDB: The Top 10 enhancements

check-unused-keys: A tool to interact with INDEX_STATISTICS

Profiling MySQL stored routines

MySQL Binaries Percona build10

How Percona does a MySQL Performance Audit

Percona build7 with latest patches

Three ways to know when a MySQL slave is about to start lagging

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA