This was principally written for my SQL students but I thought it might be useful to others. SQL calculation are performed row-by-row in the SELECT-list. In its simplest form without even touching a table, you can add two literal numbers like this:

SELECT 2 + 2 AS result;

It will display the result of the addition to the column alias result as a derived table, or the following result:

+--------+
| result |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

Unfortunately, the use of literal values as shown above doesn’t really let you see how the calculation is made row-by-row because it only returns one row. You can rewrite the two literal values into one variable by using a Common Table Expressions (CTEs). The CTE creates an struct tuple with only one x element. Another way to describe what the CTE does would say, it creates a derived table named struct with a single …

Even though I used a dedicated Kubernetes cluster to host my test database, I had this belief that by not explicitly allocating (or requesting, in Kubernetes vocabulary) CPU resources to my Percona XtraDB Cluster (PXC) pods or yet making just a small request, Kubernetes could be delaying access to the free CPU cycles available on […]

Different ways to get the status:

mysqlsh --login-path=icadmin -h$MYROUTER1 -- cluster status

mysqlsh --login-path=icadmin -h${HOSTNAME} --redirect-primary -- cluster status

mysqlsh icadmin:'P4ssw0rD'@db01:3306 -- cluster status --extended=0

mysqlsh icadmin@$MYROUTER1:3306 -- cluster status --extended=1

watch -n 5 “mysqlsh --login-path=icadmin -h$MYROUTER1 -- cluster status”

Set the Primary Instance (switching):

mysqlsh --login-path=icadmin -h${HOSTNAME} --redirect-primary -- cluster set_primary_instance "db01"

Obtaining MySQL InnoDB Cluster basics:

select cluster_id, cluster_name, description, cluster_type, primary_mode, clusterset_id from mysql_innodb_cluster_metadata.clusters;

Members of our cluster:

select * from performance_schema.replication_group_members order by MEMBER_ROLE;

Local & Remote Trans Q’s:

 …

So, we’ve heard that jemalloc is better than malloc for MySQL usage, and in fact, certain versions / forks of mysql already include this, eg. Percona Server (https://github.com/percona/jemalloc).

But, how can I install and configure my system to use it?

Here’s a quick push in the, hopefully, right direction.

First things first, what is “jemalloc” and how does it affect my system? I think it’s quite well explained here: https://www.percona.com/blog/impact-of-memory-allocators-on-mysql-performance/ albeit an old article.

The right place to get the latest …

I love my job. One of the best feelings is when I find an interesting paper and use it to solve a real problem. It feels like I found a cheat code. Instead of having to do a lot of hard thinking, I can just stand on the shoulders of really big people and take a shortcut. Here, I want to share a recent project that I could solve using a public paper.

Don’t have time to read Efficient MySQL Performance? Here’s the book (10 chapters) in one-liners.

1. Performance is query response time.
2. Proper left-most indexing is required for performance.
3. The less data, the better.
4. Access patterns (part of the workload) help or hinder performance.
5. Sharding is how to scale writes when single-node performance is truly reached.
6. Server metrics reflect how the app workload causes MySQL to work.
7. Replication lag is data loss.
8. Locks are held until a transaction commits, so commit quickly.
9. There are many other challenges that you might need to address—sorry.
10. MySQL in the cloud is slower and more expensive, so performance is more important than ever.

Read on to learn about how to connect PHP with a MySQL database using different methods, including mysqli, PDO, and dbForge for MySQL.

The post How to Connect PHP With MySQL appeared first on Devart Blog.

Cloud-based database hosting seems to be an extremely viable solution for modern apps. After all, the entire management-and-maintenance thing is relegated to the hosting provider, scalability is not an issue, and you are free to focus on product development as you see fit.

The post Top MySQL Hosting Providers in 2024 for Reliable Database Management appeared first on Devart Blog.

Introduction # In this blog post, we will discuss an example of a change to the Vitess query planner and how it enhances the optimization process. The new model focuses on making every step in the optimization pipeline a runnable plan. This approach offers several benefits, including simpler understanding and reasoning, ease of testing, and the ability to use arbitrary expressions in ordering, grouping, and aggregations. Vitess distributed query planner # VTGate is the proxy component of Vitess.

We already have blog posts about Dual Password in MySQL from Brian Sumpter – Using MySQL 8 Dual Passwords, and from Marco Tusa – MySQL Dual Passwords – How To Manage Them Programmatically Let’s skip the details about dual passwords and focus on tracking password usage. How can we be sure that we are using […]