Feed Aggregator

SELECT t.table_schema, t.table_name, t.engine
  FROM information_schema.tables AS t
  LEFT JOIN information_schema.key_column_usage AS i ON t.table_schema = i.table_schema AND t.table_name = i.table_name AND i.constraint_name = 'PRIMARY'
 WHERE i.constraint_name IS NULL
   AND t.table_schema NOT IN ('information_schema', 'mysql')
;

On Tuesday, September 4, 2012 the next DOAG SIG MySQL meeting will take place in Hamburg (Germany) with the topic Replication.

Possible presentations are: MySQL Replication, Galera Cluster, Replication with Oracle GoldenGate, Replication with Zimory Scale and Tungsten Replicator.

The event will presumably be located at the Hotel Böttcherhof at Wöhlerstrasse 2.

FromDual is having a talk at Frankfurter Datenbanktage 2013, March 14/15

InnoDB Buffer Pool

The InnoDB Buffer Pool is the memory area where the InnoDB Storage Engine caches its data and index blocks. Each InnoDB data and index block has a size of Innodb_page_size (16384 byte = 16 kbyte). The InnoDB Buffer Pool is configured in bytes with the innodb_buffer_pool_size variable. On a dedicated system the InnoDB Buffer Pool can be configured up to 80% of the systems physical RAM (free).

The innodb_buffer_pool_awe_mem_mb variable is relevant only on 32-bit Windows systems with more than 4 Gbyte of RAM using the so-called Address Windowing Extensions (AWE).

The usage of the InnoDB Buffer Pool can be measured with the SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%' command. The sum of data, misc and free pages is equivalent to total pages. And the number of total pages multiplied by Innodb_page_size corresponds to your innodb_buffer_pool_size.

Innodb_buffer_pool_pages_data       1757
Innodb_buffer_pool_pages_misc     +   10
Innodb_buffer_pool_pages_free     + 2072 …

A bug report for the following problem has been opened: http://support.fromdual.com/bugs/view.php?id=40

A customer asked:

I would like to know if the FromDual Performance Monitor for MySQL works with Zabbix 2.0?

We do not know yet (June 2012). This is a project for this summer… We do not expect any problem if the Zabbix 2.0 interfaces have not changed significantly.

This customer kindly tested fpmmm on Zabbix 2.0 for us.

The customer reported:

In relation to FromDual Performance Monitor for MySQL, I’ve started a test today and I can import the xml files without any problem with Zabbix 2.0
To be continued…

and a bit later:

I can confirm that fpmmm works with Zabbix 2.0!

Great work! A big thank you to our customer. So please feel free to use it with Zabbix 2.0 and report any issue to us.

Hi John,

MPM can work with any MySQL branch/fork. Some of the branches try to make their product unique by adding features or changing things. Because we are parsing the output of SHOW ENGINE INNODB STATUS amongst other things some values can become wrong over time when the output format is changed by the vendors.

The values you mention base most probably on the MySQL status information (SHOW GLOBAL STATUS) Innodb_data_fsyncs and Innodb_data_writes. So those should be correct on any MySQL branch/fork.

But I have to check more in detail…

Oli

hi guys, I have install latest mpm for monitoring a Percona mysql server 5.5

but i found the zabbix web front-end can not display property data got from percona mysql(xtradb)
some fields show a impossiblly large number, and some fields can not got any data

such as “InnoDB Data Writes” show : max value “4.6G”
InnoDB Data Fsyncs show : max value “275.51 M”

Any help ? Thanks a ton in advance !

Daniel the Hazard wrote us:

just wanted to leave this complement to your GLB post: a init script for GLB that supports adding and removing targets

Thanks Daniel!

#!/bin/sh
#
# glbd          Start/Stop the Galera Load Balancer daemon.
#
# processname: glbd
# chkconfig: 2345 90 60
# description: GLB is a TCP load balancer similar to Pen. \
#              It lacks most of advanced Pen features, as \
#              the aim was to make a user-space TCP proxy which is \
#              as fast as possible. It can utilize multiple CPU cores. \
#              A list of destinations can be configured at runtime. \
#              Destination "draining" is supported. It features \
#              weight-based connection balancing (which becomes \
#              round-robin if weights are equal).

### BEGIN INIT INFO
# Provides: glbd
# Required-Start: $local_fs
# Required-Stop: $local_fs
# Default-Start:  2345
# Default-Stop: 90
# Short-Description: run glbd daemon
# Description: GLB is a TCP load …

Its a known problem that changing the Storage Engine from MyISAM to InnoDB can cause some problems
[ 1
] if you have queries of this type:

SELECT COUNT(*) from table;

Luckily this query happens rarely and if, the query can be easily omitted or worked around by guesstimating the amount of rows in the table. For example with:

SHOW TABLE STATUS LIKE 'test';

But in some rare cases customer really needs these values for some reasons. To not exhaust the resources of the server with this query which can be fired quite often in some cases we make use of the materialized views/shadow table technique
[ 2
].

The following example illustrates how to do this.

Our original situation

We have an offer table which is feed by a host system:

CREATE TABLE offer (
  id   int unsigned NOT NULL AUTO_INCREMENT
, `type` CHAR(3) NOT NULL DEFAULT 'AAA'
, data varchar(64) DEFAULT NULL
, PRIMARY KEY (`id`)
, INDEX (type)
) ENGINE=InnoDB;

INSERT INTO offer VALUES (NULL, 'AAA', 'Blablabla');
INSERT INTO offer VALUES (NULL, 'ABC', …

I guess your feed system writes into the table in a serial fashion? Any concurrency is going to kill this method. The summary table will have very few rows, which will create very hot locks when the base table is modified. Deadlocks and lock wait timeout errors are likely.

Hi Geoff,

The mysqldump was done with –master-data(=1) which explicitly set’s binary log file and binary log position.

In short: Yes, it works as expected.

Regards, Oli

You don’t mention the binary log and the position in this post. Will this work as expected (ie regular mysql)?

Also this year we will have a special track for MySQL, Galera, Percona und MariaDB at the FrOSCon in St. Augustin in Germany. The conference is scheduled for August 25 and 26 2012.

Together with the PostgreSQL people we are organizing a sub-conference for Open Source RDBMS there. Now we are looking for interesting talks about MySQL and related techniques like Galera, Percona, MariaDB. The only restriction for the talks is: They must be about an Open Source topic.

We encourage you to send your proposals.

After registering you can Submit a new event. Choose the Databases track. It makes easier to assign the proposal.

Regarding the talks: Please do NOT add talks about NON Open Source solutions. It can be about some new technical things or about some user experience with MySQL technology.

Keep in mind the audience is going to be technical driven. Think about the audience as colleges and not as decision makers.

Please help spreading the word for the Conference by blogging and twittering about it (#froscon)!

And …

See also Byobu.

Often support customers want to see what you do on their databases. For this screen is the ideal tool.

Screen commands typically look like this:

screen> Ctrl+a ?

This means press Ctrl+a, release Ctrl+a, press ? and NOT press Ctrl+a+? together. Often you can find Ctrl+A which is wrong. It is a lower case a!

Start first screen

Start and leave a screen:

shell> screen
screen> exit

or k for kill

screen> Ctrl+a k

Comment: Does not work if done with sudo, typically used with ssh.

A screen can be named as follows:

shell> screen -S support

List available screens

You can find your available screens like this:

user1_shell> screen -ls
There are screens on:
        11212.support   (05/21/2012 03:10:23 PM)        (Attached)
        5358.pts-4.master       (05/21/2012 03:08:08 PM)        (Attached)
2 Sockets in /var/run/screen/S-user1.

User2 cannot see those:

user2_shell> screen -ls
No Sockets found in /var/run/screen/S-user2.

but user2 can see all screens like this: …

Some updates if you want to use bonding on CentOS 6:

Due to the fact that /etc/modprobe.conf has been deprecated in CentOS 6, the process of bonding network interfaces has changed a bit.

Now instead of defining your bond in your /etc/modprobe.conf, you define it in /etc/modprobe.d/bonding.conf [ 1 ].

Dear MySQL User,

With this newsletter we bring you up to date about MySQL and our Services.

If you are not interested in this newsletter or if you are the wrong recipient, you can unsubscribe here or even better send this newsletter to the right person.

There are some news about the following topics:

• High-Availability(HA) with Galera Cluster for MySQL
• MySQL Performance Monitoring (MPM)
• MySQL Training in Essen and Berlin (Germany)
• 3^rd Level Support for MySQL
• Security holes in MySQL
• Technical information about MySQL

High-Availability (HA) with Galera Cluster for MySQL

Last year we have tested the product Galera Cluster for MySQL for you. And our finding was very positive! This is the reason why we warmly recommend you to consider Galera Cluster for MySQL as your next high-availability solution for MySQL.

Galera Cluster for MySQL is the ideal candidate if you are unhappy with consisting solutions like MySQL Cluster, active/passive failover Cluster (DRBD, Heartbeat) or Master/Slave- or …

I just did a test and now understand what you are saying.

I am able to label them in the following:

[Server_label.database_name]

This seems to work.

Thanks heaps

Matthew

Hi Oli,

You are describing exactly what I would like to do with the label. The issue is more about saying which database to monitor without having to rename our databases to the label’s format.

Cheers

Matthew