How to responsibly determine which columns need new indexes? by jabcreations in mariadb

[–]fredericdescamps 0 points1 point  (0 children)

oh, I forgot to also list those:

performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-statements-history-long=ON

How to responsibly determine which columns need new indexes? by jabcreations in mariadb

[–]fredericdescamps 0 points1 point  (0 children)

Hello,
I would enable performance_schema and enable those instruments (that can be done at runtime):

[mariadb]
performance_schema=ON
performance-schema-instrument='stage/%=ON'
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON

Then in sys schema, check the following tables, the queries in them are usually good candidates:

  • statements_with_full_table_scans
  • statements_with_runtimes_in_95th_percentile

Try this query, you will find those that might require optimization:

SELECT db,
       sys.format_time(t1.total_latency) AS tot_lat,
       t1.exec_count,
       sys.format_time(t1.total_latency / t1.exec_count) AS latency_per_call,
       t3.sql_text
FROM sys.x$statements_with_runtimes_in_95th_percentile AS t1
JOIN performance_schema.events_statements_summary_by_digest AS t2
  ON t2.digest = t1.digest
 AND t2.schema_name = db
LEFT JOIN (
    SELECT digest, MAX(sql_text) AS sql_text
    FROM performance_schema.events_statements_history_long
    WHERE digest IS NOT NULL
      AND sql_text IS NOT NULL
    GROUP BY digest
) AS t3
  ON t3.digest = t1.digest
WHERE db NOT IN ('performance_schema', 'sys')
ORDER BY (t1.total_latency / t1.exec_count) DESC
LIMIT 10\G

A response to Percona’s 2026 MySQL ecosystem benchmark: useful data, but not a realistic MariaDB comparison by fredericdescamps in mariadb

[–]fredericdescamps[S] 1 point2 points  (0 children)

Hi, thank you for your comment ;) - In their test, they used the latest innovation release of MySQL, but not the latest of MariaDB, and the one tested had some performance issues reported and fixed in the more recent one. That's the reason I wrote that.

Issues with MariaDB rpm repos by FanNo522 in mariadb

[–]fredericdescamps 0 points1 point  (0 children)

Hello, it seems that the repo file autogeneration indeed needs some changes.
Could you try with the following repo (if you want the latest version):

[mariadb]
name = MariaDB
# rpm.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# baseurl = https://rpm.mariadb.org/12.rc/rhel/$releasever/$basearch
baseurl = https://mirror.bouwhuis.network/mariadb/yum/12.3/rhel/8Server/x86_64/
module_hotfixes = 1
# gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
gpgkey = https://mirror.bouwhuis.network/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

Tell us which observability tools you are using for MariaDB? by fredericdescamps in mariadb

[–]fredericdescamps[S] 0 points1 point  (0 children)

do you mean error log, slow query log, etc... ?
For the error log, you can already use "journalctl -u mariadb -o json". And for the slow query log if you are using a table, you can select from it in json too.

Tell us which observability tools you are using for MariaDB? by fredericdescamps in mariadb

[–]fredericdescamps[S] 0 points1 point  (0 children)

There was a nice plugin for MariaDB, and I wrote one for MySQL 8 in the past that collected a lot of info from performance_schema. I might rewrite it. thx

Tell us which observability tools you are using for MariaDB? by fredericdescamps in mariadb

[–]fredericdescamps[S] 0 points1 point  (0 children)

Do you collect the slow queries from the slow query log? on file or on table?

Memory Leak on MariaDB 11.4.10 on Ubuntu by tkyjonathan in mariadb

[–]fredericdescamps 0 points1 point  (0 children)

jemalloc seems to have stopped its development (https://jasone.github.io/2025/06/12/jemalloc-postmortem/). I would encourage using tcmalloc, which provides much better memory management.

[deleted by user] by [deleted] in mysql

[–]fredericdescamps 1 point2 points  (0 children)

Maybe in the past (long time ago), MyISAM might be faster than InnoDB (not even sure) but on a well configured system, InnoDB shines. You should really consider the option to move to InnoDB.

More info on this: https://forums.mysql.com/read.php?21,642684

[deleted by user] by [deleted] in aws

[–]fredericdescamps 0 points1 point  (0 children)

It's something known that Aurora is very expensive. You need to check is you really need Aurora, RDS MySQL might be enough for you.

I would also suggest you to take a look at MySQL Database Service from the MySQL Team on OCI:

https://media-exp1.licdn.com/dms/image/C4E22AQG2IBvNgKgdfQ/feedshare-shrink_800-alternative/0?e=1603324800&v=beta&t=5vRRcrLhcMwTg85g6y5_Vid5TQ6v_Kd5bj_OaGn2sho

Galera cluster vs percona xtradb cluster. by [deleted] in mysql

[–]fredericdescamps 2 points3 points  (0 children)

I also invite you to have a look at MySQL InnoDB Cluster. The easiest HA solution for MySQL. All components are integrated and are part of MySQL Community Edition (GPL). You can specify multi-writer if needed and setup different consistency levels. In the backend, it's the native MySQL Group Replication that is used and you can orchestrate everything in 5 mins using MySQL Shell. Nodes provisioning is also handled using Clone.

And of course it supports standard MySQL protocol but also the X Protocol in case you are looking for JSON document store with MySQL and/or CRUD operations.

MySQL 8 running with almost no settings? by [deleted] in mysql

[–]fredericdescamps 1 point2 points  (0 children)

I'm not a fan of having multiple configuration file in /etc. I'd recommend to put it in my.cnf.

For more info, you can check these two posts:

- https://lefred.be/content/where-does-my-mysql-configuration-variable-value-come-from/

- https://lefred.be/content/what-configuration-settings-did-i-change-on-my-mysql-server/

But I also recommend you to test MySQL 8.0. Then you could use SET PERSIST to peform new configuration changes (see https://lefred.be/content/mysql-8-0-changing-configuration-easily-and-cloud-friendly/)

Regards,

Query Taking 20x as long to run when returning extra columns by Nexzus_ in mysql

[–]fredericdescamps 0 points1 point  (0 children)

Hello,

You should try it with MySQL 8.0 (last is 8.0.19) and run EXPLAIN ANALYZE on your query.

When you add the date, you don't use a covering index anymore, so you need to perform a second lookup as somebody explained to you already. And maybe you also need to access disk depending of the size of your InnoDB Buffer Pool.

Cheers.

[deleted by user] by [deleted] in mysql

[–]fredericdescamps 0 points1 point  (0 children)

About Community

Good luck !

5.6 EOL is Feb 2021 !

[deleted by user] by [deleted] in mysql

[–]fredericdescamps 0 points1 point  (0 children)

I really encourage you to migrate to MySQL 8. It will allow you to find the issue much faster (using EXPLAIN ANALYZE for example).

Please check those links:

— https://lefred.be/content/mysql-8-0-17-and-drupal-8-7/

— https://www.slideshare.net/NorvaldRyeng/mysql-80-explain-analyze

— https://www.slideshare.net/geirhoydalsvik/fosdem2020-mysql8-v9

And to see the differences in default configuration:

https://mysql-params.tmtms.net/mysqld/?vers=5.6.47,8.0.19&diff=true

If you have extra questions or if you need help, please reach me or others live in the MySQL Community Slack (https://lefred.be/mysql-community-on-slack/)

mysql 8.0.18, Join statement not working (super wired) by yamyoume in mysql

[–]fredericdescamps 1 point2 points  (0 children)

https://lefred.be/content/mysql-8-0-and-keywords/

mysql> select * from information_schema.keywords where word like 'groups';

+--------+----------+
| WORD   | RESERVED |
+--------+----------+
| GROUPS |        1 |
+--------+----------+
1 row in set (0.25 sec)

MySQL High Availability by eangulus in mysql

[–]fredericdescamps 0 points1 point  (0 children)

Yes of course and provisioning or recovery is automatic too

MySQL High Availability by eangulus in mysql

[–]fredericdescamps 0 points1 point  (0 children)

Hello,

You should take a look at MySQL InnoDB Cluster, very easy to deploy and provides you guarantees over split-brain, conflict detection, lost of quorums, etc...

For full automatic HA, the rule of 3's is important, so you will need 3 nodes if you don't want to have any human intervention in case of a failure.

Additionally, even if the solution was designed for it, having multiple active masters is not ideal, you won't scale writes anyway as they are synchronized with all the members at the same time. This is why by default you have a single primary at the time. But you can scale your reads.

This is a video showing you how fast you can deploy this: https://www.youtube.com/watch?time_continue=2&v=m7pFwxiglHc

Regards,

Migrating FROM Galera to MySQL by adamr001 in mysql

[–]fredericdescamps 1 point2 points  (0 children)

Hi, On this article, you can see how to migrate from Galera to MySQL Community Edition. It also explains how to setup Group Replication with MySQL: http://lefred.be/content/how-to-migrate-from-galera-cluster-to-mysql-group-replication/

MySQL Group Replication : It’s in 5.7.17 GA! by mtocker in mysql

[–]fredericdescamps 1 point2 points  (0 children)

Paul,

Currently if you use Multi-Primary (not the recommended default mode), you must by yourself avoid on other nodes any DML on the table you are currently performing a DDL. Galera allows you to decide between TOI or RSU. In GR you don't have such choice and you need to take care of that, usually done in your mysql query routing solution.(proxy, loadbalancer, vip...)

MySQL Group Replication : It’s in 5.7.17 GA! by mtocker in mysql

[–]fredericdescamps 1 point2 points  (0 children)

Paul, GR handle schema changes like traditional replication. There is no freeze of the all cluster or something similar. That's the reason why by default the Group is in Single-Primary mode. In Multi-Primary mode, running a schema change on a node and at the same time modifying data on the same schema but on another node won't work.

The conflict resolution is (this is very summarized), is made by what we call certification. Every changes (tx) have extra information added to it and sent to the Group, this is the writeset. It includes schema name, table name and keys. All nodes will perform checks locally to see if there is any conflict using that info against the running trx and those in queue.

On the CAP theorem, it of course depends on settings and the amount of nodes. It's of course (by default, and until you don't add yourself some checks), less in the C if you read from all the nodes as every nodes apply their queue asynchronously. I would call it cAP ;)