You searched for mysql table fragmentation - 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

Just do the math!

July 6, 2009 By Peter Zaitsev 4 Comments

One of the most typical reasons for performance and scalability problems I encounter is simply failing to do the math. And these are typically bad one because it often leads to implementing architectures which are not up for job they are intended to solve. Let me start with example to make it clear. Lets say [...]

Filed Under: Insight for Developers Tagged With: Tips

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

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

Tools

May 30, 2008 By Peter Zaitsev 31 Comments

This page contains links to various tools we found helpful to use in practice. Some tools are written by us, others by third parties, yet another ones may be shipped with your operating system you just need to find they are there. Percona Toolkit Percona Toolkit is a great set of tools for MySQL Performance [...]

Filed Under:

INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine

May 24, 2008 By Baron Schwartz Leave a Comment

Much has been written about the new InnoDB pluggable storage engine, which Innobase released at the MySQL conference last month. We’ve written posts ourselves about its fast index creation capabilities and the compressed row format, and how that affects performance. One of the nice things they added in this InnoDB release is INFORMATION_SCHEMA tables that [...]

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

Quickly preloading Innodb tables in the buffer pool

May 1, 2008 By Peter Zaitsev 9 Comments

In the previous post I mentioned a way I use to preload Clustered Index (data) for Innodb tables. Though I thought this topic would benefit from a bit more information. But lest first start with feature request for Innodb Team: All ways I mention here are hacks and they can’t be as efficient as native [...]

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

Learning about MySQL Table Fragmentation

May 1, 2008 By Peter Zaitsev 20 Comments

Recently I was working with the customer who need quick warmup – to get Innodb table fetched in memory as fast as possible to get good in memory access performance. To do it I run the query: “SELECT count(*) FROM tbl WHERE non_idx_col=0″ I use this particular form of query because it will do full [...]

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

MySQL File System Fragmentation Benchmarks

March 21, 2008 By Peter Zaitsev 28 Comments

Few days ago I wrote about testing writing to many files and seeing how this affects sequential read performance. I was very interested to see how it shows itself with real tables so I’ve got the script and ran tests for MyISAM and Innodb tables on ext3 filesystem. Here is what I found:

Filed Under: Benchmarks Tagged With: InnoDB, MyISAM

Working with many files and file system fragmentation

March 18, 2008 By Peter Zaitsev 11 Comments

Working on performance optimization project (not directly MySQL related) we did a test – creating 100 files writing 4K in the random file for some time and when checking the read speed on the files we end up with, compared to just writing the file sequentially and reading it back. The performance difference was huge [...]

Filed Under: Hardware and Storage Tagged With: Operating Systems

InnoDB: look after fragmentation

Just do the math!

MySQL Binaries Percona build10

Percona build7 with latest patches

Tools

INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine

Quickly preloading Innodb tables in the buffer pool

Learning about MySQL Table Fragmentation

MySQL File System Fragmentation Benchmarks

Working with many files and file system fragmentation

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA