Kalasha,

Since autocommit is disabled, did you issue “COMMIT” after inserting those records ? If not, then you have to either enable autocommit or commit your DML statements manually (issue “COMMIT” or execute DDL statement)

Obyis,

Try to load “Digest::SHA1” instead of “Digest::SHA” at the beginning of “InnoDbStatus.pm” file by replacing “use Digest::SHA” with “use Digest::SHA1”. It should work after that.

It’s not usual to find an easy source on how to setup MySQL replication, I thought it might be useful at least for the beginners to write a direct and simple howto blog on setting up Master/Slave replication in MySQL using the classic method (binary log information). Check out my post GTID In Action for information about transaction-based replication using GTID.

Before going through the replication setup steps, I think it’s better to explain first how Replication works in MySQL.

MySQL …

One of our customers had a question related to the right value of Galera Cache size (gcache.size) in Galera Cluster for MySQL which I would like to share with you.

The question was: My maintenance window takes 4 hours for my 5TB DB. How can I avoid an SST ?!

Basically, having too small GCache size will lead to SST (Snapshot State Transfer) instead of IST (Incremental State Transfer), thus we can avoid the SST by setting the GCache to the appropriate value.

To check the current value of the GCache size: …

Sometimes, it is required to enable the General Query Log (which is disabled by default). If the General Query Log is enabled the server writes to this log information when clients connect or disconnect, and each SQL statement received from the client.

The question is, does enabling the General Query Log affects the MySQL performance ?
Also, it is possible to record the output of this log into either file or table in the mysql database (mysql.general_log), what is the performance impact of each one? …

Are you using FILE or TABLE as log output ?
Try “show global variables like’log_output’;”

Kalasha,
You might need to execute the SQL statement (FLUSH LOGS;) and then check the log files.

Hi Przemek,

I do agree with you that the replication will be blocked until the slaves finish executing the alter statement, but the table being changed on the slaves themselves wont be blocked during the alter statement the same like the master. I agree also that this could be considered as an advantage of pt-online-schema-change over Online DDL. I’ll add that to the blog. Thanks Przemek for the hint …

Hi Shlomi

Can you please explain more on how did you produce your results?
Because Online DDL are DDL statements anyway and will be written to the binary log as statements (even if RBR is being used) which means that it will be executed on the slave the same like it was on the master.

By the way, I've tested it in a replication environment and the table being changed on the slave was not blocked during the statement execution and it took approximately the same or even less time than it was on the master. …

One of the most expensive database operations is performing Data Definition Language (DDL, e.g. CREATE, DROP, ALTER, etc.) statements, specially, the ALTER statements because MySQL blocks the entire table for both reads and writes while modifying the table.

For the huge tables, this might take hours to get the table changed which affects the application, so that, a good planning is required for such operations in order to avoid doing these changes during the peak times. For those people who have 24/7 …

Will you be at a loss having a MySQL or MariaDB database problem? Will you need urgently help from experienced FromDual staff when your production MySQL or MariaDB database stops working? Or do you need a third party opinion for one of your MySQL or MariaDB solutions?

FromDual offers you vendor independent database support for MySQL (community and enterprise edition), MariaDB (community and enterprise edition) and Percona Server for all versions starting with 4.0.

How we provide support?

Our FromDual …

Hello,

there are companies which earn tens of thousands of Euros per hour with their MySQL databases. Other companies operate their ERP system on MySQL, to which 1000 employees are attached to. Is the database down 1000 people are not working any more until the system is working again! Downtime costs starting at EUR 30'000.- per hour upwards.

Support through the MySQL specialists?

These companies have properly planned operations of their MySQL databases, designed their production systems redundantly and …

Thanks, Morgan!

MySQL single query performance - the truth!

As suggested by morgo I did a little test for the same query and the same data-set mentioned in Impact of column types on MySQL JOIN performance but looking into an other dimension: the time (aka MySQL versions).

The answer

To make it short. As a good consultant the answer must be: “It depends!” :-)

The test

The query was again the following:

SELECT *
  FROM a
  JOIN b ON b.a_id = a.id
 WHERE a.id BETWEEN 10000 AND 15000
;

The Query Execution Plan was …

Hi Joffrey,

I think NDB tables use 4-byte alignment not InnoDB:

NDB tables use 4-byte alignment; all NDB data storage is done in multiples of 4 bytes. Thus, a column value that would typically take 15 bytes requires 16 bytes in an NDB table. For example, in NDB tables, the TINYINT, SMALLINT, MEDIUMINT, and INTEGER (INT) column types each require 4 bytes storage per record due to the alignment factor.

Check this manual page for reference.

Thanks,
Abdel-Mawla

Hi Morgan,

Thanks!

I have planed such a test already a while ago but did not find the time to do it yet...

In my mind I have some numbers showing that since MySQL 4.0 single query performance has decreased significantly...

This leads to the question: Where are all the MySQL branches and forks heading to and is this in the interest of the majority of the MySQL users or just in the interest of a few ones like Facebook, LinkeId, Google, Booking.com etc...?

Regards,
Oli

In our MySQL trainings and consulting engagements we tell our customers always to use the smallest possible data type to get better query performance. Especially for the JOIN columns. This advice is supported as well by the MySQL documentation in the chapter Optimizing Data Types:

Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better …

If you are not operating MySQL databases yourself, but you develop MySQL based software, our MySQL development support is exactly fitting to your needs!

Are your developers hitting technical MySQL problems from time to time or have they a technical MySQL question, our support team is pleased to answer your questions after having talked to the FromDual consulting team.

This support contract is ticket- and not server-based and thus ideally fitting to your software development team. Any number of your MySQL …

Features

Galera is a synchronous multi-master replication Cluster for MySQL/InnoDB databases, having features like:

• 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 replication, on row level
• Direct client connections, native MySQL look & feel

Benefits

The benefits of these features are:

• No slave lag
• No lost transactions
• Read scalability and write …

Hello csmanioto,

As mentioned above this is a PoC and should never be used on system with critical data because it is an evil hack!!!

If you would like to see this comming in production soon you should vote fore this feature request.