Neuigkeiten

Angemeldet haben sich: Volker, Erkan, Mario, Oli, Ronny, (Norbert), …

Es ist um 18 Uhr im El Encanto unter meinem Namen reserviert…

Hallo zusammen,

Einige von Euch haben sicher vor, an die DOAG Conference 2011 nach Nürnberg zu kommen, welche vom 15. bis 17. November statt findet.

Neben zahlreichen interessanten Vorträgen über MySQL soll auch das Gesellschaftliche nicht zu kurz kommen. Daher planen wir am Dienstag, 15. November einen MySQL Community Abend mit gemeinsamem Nachtessen in einer netten Lokalität in Nürnberg. Ca. 18:30 - 19:00

Ihr seid alle herzlich eingeladen, daran teilzunehmen (auch wenn Ihr es nicht auf die DOAG Conference …

Michal Hrušecký did a Survey of the usage of different MySQL branches on OpenSuSE Linux. The results can be found in his article: MySQL survey results (Oct 17, 2011).

We have seen a customer running a MySQL instance with about 1 Mio InnoDB tables. He had some problems (hick-ups) but basically it worked…

This depends a bit on version and distribution. Debian/Ubuntu for example sets bind-address to localhost/127.0.0.1 afaik.

Look for my.cnf in /etc/ or /etc/mysqld.

Other locations you can find with:

mysqld --help --verbose | grep my.cnf

Hello BBlue,

Can you please elaborate a bit more what the problem is? It is not clear to me where you stuck.

Regards, Oli

Galera features

Galera provides the following features:

• Synchronous replication
• Active/active multi-master topology
• Read and write to any cluster node
• Automatic membership control, failed nodes drop from the cluster
• Automatic node joining
• True parallel row level replication
• Direct client connections
• Drop-in replacement for native MySQL

Galera benefits

Benefits using Galera Replication:

• High Availability
• No slave lag
• No lost transactions
• No more data inconsistency
• Smaller client latencies
• Read scalability …

Table of Contents

• Reasons to Upgrade MariaDB or MySQL
• How to Upgrade to MariaDB or MySQL
• MariaDB and MySQL Reserved Keywords
• MariaDB and MySQL Problems we hit in real life
  • Upgrade MySQL 4.1 to MySQL 5.1
  • Upgrade MySQL 4.1 to MySQL 5.6
  • Upgrade MySQL 5.0 to MySQL 5.1
  • Upgrade MySQL 5.0 to MySQL 5.6
  • Upgrade MySQL 5.0 to MariaDB 10.3
  • Upgrade MySQL 5.1 to MySQL 5.5
  • Upgrade MySQL 5.5 to MySQL 5.6
  • Upgrade MySQL 5.6 to MySQL 5.7
  • Upgrade MySQL 5.7.12 to MySQL 5.7.21
  • Upgrade MySQL 5.7 to MySQL 8.0
  • Upgrade MariaDB 5.5 …

With a partner we are planning and Advanced MySQL DBA workshop. As the name says it should be a workshop. Its planned duration is 2 days. So within 2 days we can cover 4 to 8 topics more deeply. Requirements: VirtualBox, VMware, own Laptop?

Possible exercises during the workshop

• Set-up a Master-Master replication with 2 Slaves
• Load balance on master with MySQL Proxy and on Slaves with LVS.
• Design a little schema and load with data from foodmart
• Do a backup with XtraDB and LVM
• Do a PITR and and InnoDB crash …

Workshop topics

Duration: 3 days, Exercises

• Partitioning
  • Partitioning by time (temporal data)
  • Optimizer and Partitioning
  • Limitations
  • New 5.5 and 5.6 features of Partitioning
• InnoDB Architecture
  • Clustered PK
  • InnoDB Internals
  • InnoDB Online features
  • New Performance Features in 5.5 and 5.6
  • Operations of InnoDB
• Performance Tuning and Benchmarking
  • Basics, Latency and Throughput
  • Profiling
  • Benchmarking
  • Benchmarking Tools
• Memcached
  • Caching strategies
  • Operations
  • ding
• HA and replication solutions …

Hi Partha Dutta,

What you are describing is mentioned in Literature reference [3].

Hi Baron,

90% of the MySQL installations have 1 or 2 concurrent running queries. And most of the MySQL users do not have the time or the capabilities to tune queries, applications or add indexes.

I know that you, Domas and others think the Query Cache should NOT be used at all. However from what we see on the market it is for more MySQL users useful l than it harms.

Regards, Oli

After further investigations with MariaDB developers we found out, that the Problem can be solved when an explicit BEGIN or START TRANSACTION is set in front of the SELECT.

For example:

SET autocommit=0; BEGIN; SELECT * FROM test; COMMIT;

This is similar to other problems we have seen earlier this year where a customers got back several rows on a PK lookup (which IMHO is a Bug)!

As a result: Always use BEGIN or START TRANSACTION when you set autocommit=0!

It looks really evil as you said. I could reproduce it with the following sequence:

SHOW GLOBAL STATUS
WHERE variable_name = 'Qcache_hits'
   OR variable_name = 'Qcache_inserts'
   OR variable_name = 'Qcache_not_cached'
   OR variable_name = 'Qcache_queries_in_cache'
   OR variable_name = 'Com_select';

SET autocommit=0; SELECT * FROM test; COMMIT;
SHOW GLOBAL STATUS
WHERE variable_name = 'Qcache_hits'
   OR variable_name = 'Qcache_inserts'
   OR variable_name = 'Qcache_not_cached'
   OR …

Hi luke,

Great! Thank you for the hint. I somehow missed this bug… :(

Oli

It was just a complex Join Query as far as I can remember:

SELECT * FROM a
JOIN b ON ...
JOIN c ON ...
...
WHERE ...

Possibly it had some GROUP BY or DISTINCT in it.

Regards, Oli

MySQL 5.6

The focus of this release seems to be on Replication, Partitioning and InnoDB/Performance.

• New InnoDB-related INFORMATION_SCHEMA tables and more information in the PERFORMANCE_SCHEMA.
• Persistent Optimizer statistics for InnoDB tables.
• InnoDB table information can be limited when many tables are opened.
• Several internal performance enhancements for InnoDB.
• InnoDB deadlocks can be written to the error log.
• Delayed Replication
• ALTER TABLE ... EXCHANGE PARTITION.
• Binlog shipping with the mysqlbinlog …

In our opinion remote Consulting is non optimal for the customer because tight interaction is difficult remotely.

Please visit Remote-DBA or Consulting…

Workshop topics

Duration: 3 days, register here.

• Partitioning
  • Partitioning by time (temporal data)
  • Optimizer and Partitioning
  • Limitations
  • New 5.5 and 5.6 features of Partitioning
• InnoDB Architecture
  • Clustered PK
  • InnoDB Internals
  • InnoDB Online features
  • New Performance Features in 5.5 and 5.6
  • Operations of InnoDB
• Performance Tuning and Benchmarking
  • Basics, Latency and Throughput
  • Profiling
  • Benchmarking
  • Benchmarking Tools
• Memcached
  • Caching strategies
  • Operations
  • ding
• HA and replication solutions …

Dear MySQL and MariaDB User,

With this Newsletter you receive the News about FromDual for Spring 2011:

Topics

• Technical Information
• Support and Remote-DBA services for MySQL and MariaDB
• Open dates
• News about FromDual
• News about MySQL

Technical Information

We have compiled the following technical information for you:

• Configuration of MySQL for Shared Hosting: Operating MySQL in a Shared Hosting environment is one of the trickiest things you can do with MySQL. Some hints and information about it you can …