You searched for mysql show status table overhead - Page 2 of 2

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

Efficient Boolean value storage for Innodb Tables

April 23, 2008 By Peter Zaitsev 6 Comments

Sometimes you have the task of storing multiple of boolean values (yes/now or something similar) in the table and if you get many columns and many rows you may want to store them as efficient way as possible. For MyISAM tables you could use BIT(1) fields which get combined together for efficient storage:

CREATE TABLE `bbool` (
  `b1` bit(1) NOT NULL,
  `b2` bit(1) NOT NULL,
  `b3` bit(1) NOT NULL,
  `b4` bit(1) NOT NULL,
  `b5` bit(1) NOT NULL,
  `b6` bit(1) NOT NULL,
  `b7` bit(1) NOT NULL,
  `b8` bit(1) NOT NULL,
  `b9` bit(1) NOT NULL,
  `b10` bit(1) NOT NULL
) ENGINE=MyISAM
mysql> show table status like 'bbool' \G
*************************** 1. row ***************************
           Name: bbool
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 10
 Avg_row_length: 7
    Data_length: 70
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2008-04-24 00:41:01
    Update_time: 2008-04-24 00:45:40
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

CREATE TABLE `bbool` (

`b1` bit(1) NOT NULL,

`b2` bit(1) NOT NULL,

`b3` bit(1) NOT NULL,

`b4` bit(1) NOT NULL,

`b5` bit(1) NOT NULL,

`b6` bit(1) NOT NULL,

`b7` bit(1) NOT NULL,

`b8` bit(1) NOT NULL,

`b9` bit(1) NOT NULL,

`b10` bit(1) NOT NULL

) ENGINE=MyISAM

mysql> show table status like 'bbool' \G

*************************** 1. row ***************************

Name: bbool

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 10

Avg_row_length: 7

Data_length: 70

Max_data_length: 1970324836974591

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2008-04-24 00:41:01

Update_time: 2008-04-24 00:45:40

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

Filed Under: Insight for Developers Tagged With: InnoDB

UNION vs UNION ALL Performance

October 5, 2007 By Peter Zaitsev 15 Comments

When I was comparing performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my benchmarks, and he was right. Numbers would be different but it should not change general point of having optimization of moving LIMIT inside of union clause being [...]

Filed Under: Benchmarks, Insight for Developers Tagged With: Optimizer

How fast can you sort data with MySQL ?

August 18, 2007 By Peter Zaitsev 26 Comments

I took the same table as I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result set which is the most typical way sorting is used in practice. I tested full table scan of the table completes in 0.22 [...]

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

How much overhead is caused by on disk temporary tables

August 16, 2007 By Peter Zaitsev 26 Comments

As you might know while running GROUP BY and some other kinds of queries MySQL needs to create temporary tables, which can be created in memory, using MEMORY storage engine or can be created on disk as MYISAM tables. Which one will be used depends on the allowed tmp_table_size and also by the data which [...]

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

Using LVM for MySQL Backup and Replication Setup

August 21, 2006 By Peter Zaitsev 53 Comments

If someone asks me about MySQL Backup advice my first question would be if they have LVM installed or have some systems with similar features set for other operation systems. Veritas File System can do it for Solaris. Most SAN systems would work as well. What is really needed is ability to create atomic snapshot [...]

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

MySQL Query Cache

July 27, 2006 By Peter Zaitsev 70 Comments

MySQL has a great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know. First let me clarify what MySQL Query Cache is – I’ve seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache [...]

Filed Under: Insight for DBAs Tagged With: Production

SHOW INNODB STATUS walk through

July 17, 2006 By Peter Zaitsev 40 Comments

Many people asked me to publish a walk through SHOW INNODB STATUS output, showing what you can learn from SHOW INNODB STATUS output and how to use this info to improve MySQL Performance. To start with basics SHOW INNODB STATUS is command which prints out a lot of internal Innodb performance counters, statistics, information about [...]

Filed Under: Insight for DBAs Tagged With: InnoDB

How much memory Innodb locks really take ?

July 13, 2006 By Peter Zaitsev 11 Comments

After playing yesterday a bit with INSERT … SELECT I decided to check is Innodb locks are relly as efficient in terms of low resource usage as they are advertised. Lets start with a bit of background – in Innodb row level locks are implemented by having special lock table, located in the buffer pool [...]

Filed Under: Insight for DBAs Tagged With: InnoDB

MySQL Binaries Percona build10

Efficient Boolean value storage for Innodb Tables

UNION vs UNION ALL Performance

How fast can you sort data with MySQL ?

How much overhead is caused by on disk temporary tables

Using LVM for MySQL Backup and Replication Setup

MySQL Query Cache

SHOW INNODB STATUS walk through

How much memory Innodb locks really take ?

Search

Top Searches

Percona Webinars

ABOUT PERCONA

Percona MySQL Training

Percona Careers

SOFTWARE FROM PERCONA

SERVICES FOR MYSQL USERS

ABOUT THIS BLOG AND PERCONA