Category: Uncategorized

Posted on

Comic for March 02, 2022

https://assets.amuniversal.com/22e032c0730b013a9919005056a9545d

Thank you for voting.

Hmm. Something went wrong. We will take a look as soon as we can.

Dilbert Daily Strip

Posted on

Memes that made me laugh 98

https://blogger.googleusercontent.com/img/a/AVvXsEgnnDS-XiJwHSmquVOhmNPxIjeBCNTEWyMYuWsOzgdsZDbBEh2EM3rqkddbXqQvbC_OkNY0xf6kwcYignJZhaQhy2xHyBu-SoEIGlXApGkVa-rjE2EO3Sl5DRrUmOETMCgjfcQul6jskUYyfw0l8tnxGce7DLrkqeHNSb81JOTsoJj8hxU05yKGAhVX=w325-h400

 

Gathered from around the Internet over the past week.  There are fewer than usual, thanks to a three-day road trip to attend the wedding of a friend, during which time I had limited Internet access.  Click any image for a larger view.

More next week.

Peter

Bayou Renaissance Man

Posted on

This is the way

 

Every Red state needs to do this.

Systematically go through and weed out every department and degree program that is just accredited Leftist indoctrination.

Fire the professors.

Spend the money on vocational education.

Gun Free Zone

Posted on

Multitenant Laravel Notifications

https://s0.wp.com/i/blank.jpg

Laravel Notifications are an awesome tool that provides built-in support for sending notifications, in dozens of different channels, like Slack, Telegram, SMS, etc.

In this tutorial, we will see how we can customise Laravel Notifications core to accommodate a multi-tenant setup in a single database.

Note: This tutorial is using Laravel 9.x but should be working for older Laravel versions as well.

Step 1: The Notifications Table

The first thing we need to do is publish the default notifications table using

php artisan notifications:table

You should now have a new migration file under database/migrations which should look like this

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    
    public function up()
    {
        Schema::create('notifications', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->string('type');
            $table->morphs('notifiable');
            $table->text('data');
            $table->timestamp('read_at')->nullable();
            $table->timestamps();
        });
    }
};

Ideally, we would like to have a foreign key to the tenant model.

Schema::create('notifications', function (Blueprint $table) {
     $table->uuid('id')->primary();
     $table->string('type');
     $table->morphs('notifiable');
     $table->text('data');
     $table->timestamp('read_at')->nullable();
     $table->timestamps();

     $table->foreignId('tenant_id')->constrained(); // <- Add this
});

If tenants are supposed to be receiving notifications you might want to make the tenant_id column nullable.

Step 2: Override Laravel’s Database Channel

The next step would be to find a way to fill in that column whenever a notification is being stored in the database. For that, we need to extend the default DatabaseChannel class and replace our version in the Laravel Container.

What we need is a new class called DatabaseChannel which extends Illuminate\Notifications\Channels\DatabaseChannel.

<?php

namespace App\Notifications\Channels;

use Illuminate\Notifications\Notification;

class DatabaseChannel extends \Illuminate\Notifications\Channels\DatabaseChannel
{
    public function buildPayload($notifiable, Notification $notification)
    {
        return [
            'id' => $notification->id,
            'type' => get_class($notification),
            'data' => $this->getData($notifiable, $notification),
            'read_at' => null,
            'tenant_id' => $notification->tenant_id,
        ];
    }
}

Step 3: Create a tenant-aware Notification

Now, whenever we create a new notification we need to inject the tenant_id property so that we can insert it into the database. 

<?php

namespace App\Notifications;

use Illuminate\Notifications\Notification;

class BaseNotification extends Notification
{
    public $tenant_id;

    public function __construct($tenant_id)
    {
        $this->tenant_id = $tenant_id;
    }

    public function via()
    {
        return ['database'];
    }

    public function toDatabase($notifiable)
    {
        return [
             // your payload
        ];
    }
}

Step 4: Use our implementation of the DatabaseChannel

Finally, we need to switch Laravel’s implementation of the DatabaseChannel with ours. To do that we just need to set this up inside the boot method of the AppServiceProvider.

<?php

namespace App\Providers;

use App\Notifications\Channels\DatabaseChannel;
use \Illuminate\Notifications\Channels\DatabaseChannel as BaseDatabaseChannel;

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        $this->app->instance(BaseDatabaseChannel::class, new DatabaseChannel());
    }
}

Ready!

And that’s it!

You now have multi-tenant notifications set up in your Laravel project!

Laravel News Links

Posted on

Laravel Query Log

https://opengraph.githubassets.com/10ff2c561ee9af0da28dbb031cb9b5ad5e961f52b7032cbb54b0d6e3c8ed6c9f/haruncpi/laravel-query-log

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session.
You signed out in another tab or window. Reload to refresh your session.

Laravel News Links

Posted on

Backup/Restore Performance Conclusion: mysqldump vs MySQL Shell Utilities vs mydumper vs mysqlpump vs XtraBackup

https://www.percona.com/blog/wp-content/uploads/2022/02/MySQL-Restore-Backup-Comparison.pngMySQL Restore Backup Comparison

MySQL Restore Backup ComparisonA little bit ago, I released a blog post comparing the backup performance of different MySQL tools such as mysqldump, the MySQL Shell feature called Instance Dump, mysqlpump, mydumper, and Percona XtraBackup. You can find the first analysis here:

Backup Performance Comparison: mysqldump vs. MySQL Shell Utilities vs. mydumper vs. mysqlpump vs. XtraBackup

However, we know the backups are just the first part of the story. What about the restore time? And which tool performs better for the complete operation (backup+restore)?

Let’s see the results and the conclusion in the following sections.

Benchmark Results

I ran the benchmark on an m5dn.8xlarge instance, with 128GB RAM, 32 vCPU, and two io1 disks of 600GB (one for backup and the other one for MySQL data) with 5000 provisioned IOPS. The MySQL version was 8.0.26 and configured with 89Gb of the buffer pool, 20Gb of redo log, and a sample database of 96 GB (more details below).

When we sum the backup time and the restore time, we can observe the results in the chart below:

MySQL Backup and Restore

And if we analyze the chart without mysqldump to have a better idea of how the other tools performed:


The backup size created by each tool:

MySQL Backup Size

Note that the backup size of XtraBackup (without compression) is the size of the datadir without the binary logs. Next, we can see the backup time:

Time to execute MySQL backup

And the restore time:

Time to restore MySQL

Analyzing The Results

When we sum backup and restore times, we observe that the fastest tool is Percona XtraBackup. The main point of XtraBackup is not even the speed but its capacity to perform PITR backups. Also, the tool supports compression and encryption.

We can also observe that mydumper/myloader and MySQL Shell utilities produce good results in both phases. The difference from Xtrabackup is that both tools perform logical backups, which means that these tools connect to MySQL and extract the data to dump files. Because they have to extract data from MySQL, these tools are more sensitive for the MySQL configuration and backup/restore parametrization. For example, MyDumper/MyLoader has some extra options that can improve the backup and restore performance, such as --rows, --chunk-filesize, and --innodb-optimize-keys.

Note that  XtraBackup, MyDumper, and mysqldump support stream restore, reducing overall timing to perform the backup and restore operation. 

The tool that has the most inconsistent behavior is mysqlpump where the tool can make speedy backups, but the restore performance is terrible since it is single-threaded the same way as mysqldump. 

Based on the tests, we can observe that compression, TLS, socket, or TCP/IP do not significantly impact the time needed to perform the whole operation. Because there is no significant impact, tools that can perform compression and use TLS like MySQL Shell, mydumper/myloader, and XtraBackup have a good advantage since their backups are safer and use less disk space (less disk space = fewer costs). The trade-off between the features of these tools and the time spent to backup and restore the database is something that all DBAs should evaluate.

And to answer some questions/comments about this topic:

The difference you see between MySQL Shell and mydumper can be explained by the use of SSL in one and clear transfer in the other. Encryption has a cost, unfortunately. 

A: Indeed, SSL has a cost. However, when we put the security benefits of the SSL and consider the whole process, it is a small cost (in the same way as compression).

Does XtraBackup support ZSTD? 

A: At this moment, no. However, there is a feature request for this (you can follow the JIRA ticket to receive updates about it):

https://jira.percona.com/browse/PXB-2669

Is there any difference substituting mysqldump | gzip with a different compression tool?

A: The difference is neglectable piping with gzip or sending the uncompressed dump to the disk. The mysqldump tool is the most inefficient option due to its single-thread nature, severely impacting performance. Because of its single-thread nature, the tool cannot extract maximum performance from hardware resources (in particular I/O).

How is the performance impact on MySQL when running the backups?

A: Unfortunately, I did not measure this. Based on my experience, there is a dedicated replica server for backup most of the time. If the MySQL community is interested in this test, I can write another post about this (leave in the comments your opinion). 

It is possible to squeeze more juice from MySQL in the restore phase. We can take some actions like disabling the binary log and making asynchronous writes. You can check the advice (pros and cons) in these two blog posts:

https://www.percona.com/blog/2020/05/14/tuning-mysql-innodb-flushing-for-a-write-intensive-workload/

https://www.percona.com/blog/2014/05/23/improve-innodb-performance-write-bound-loads/

To conclude, this blog post is intended to give an overall idea of how these tools perform. I tried to stick with the default options of each tool (except the number of threads) to keep the test as fair as possible. Also, time is not the only thing that companies consider to adopt a backup method (security, encryption, and data protection are very important). In my daily tasks, I use mydumper/myloader and XtraBackup because I’m more familiar with the commands, and I have used them for a long time. However, I would advise keeping an eye on the MySQL Shell utilities since it is becoming a fascinating tool to perform many tasks (backup and restore have excellent results).

Hardware and Software Specs

These are the specs of the benchmark:

  • 32 CPUs
  • 128GB Memory
  • 2x io1 disks 600 GB with 5000 IOPS each
  • Centos 7.9
  • MySQL 8.0.26
  • MySQL shell 8.0.26
  • mydumper 0.11.5 – gzip
  • mydumper 0.11.5 – zstd
  • Xtrabackup 8.0.26

Useful Resources

Finally, you can reach us through the social networks, our forum, or access our material using the links presented below:

Planet MySQL