https://www.percona.com/blog/wp-content/uploads/2023/09/Dont-Start-Using-Your-MySQL-Server-Until-Youve-Configured-Your-OS-150×150.png

Whenever you install your favorite MySQL server on a freshly created Ubuntu instance, you start by updating the configuration for MySQL, such as configuring buffer pool, changing the default datadir director, and disabling one of the most outstanding features – query cache. It’s a nice thing to do, but first things first. Let’s review the best practices we usually follow in Managed Services before using your MySQL server in production and stage env, even for home play purposes.

Memory

Our usual recommendation is to use specific memory parameters, which we suggest to ensure optimal performance.

• To prevent out-of-memory (OOM) episodes, the OOM Score has to be set to -800.
• vm.swappiness = 1
• Disable Transparent Huge Pages
• Install and enable jemalloc. Let’s briefly go through each setting to understand why adjustments are needed. Afterward, we will see how to configure these settings on your OS.

OOM

The OOM killer checks oom_score_adj to adjust its final calculated score. This file is present in /proc/$pid/oom_score_adj. You can add a sizable negative score to this file to ensure that OOM killer is less likely to pick up and terminate your process. The oom_score_adj can vary from -1000 to 1000. If you assign -1000 to it, it can use 100% memory and avoid getting terminated by OOM killer. On the other hand, if you assign 1000 to it, the Linux kernel will keep killing the process even when it uses minimal memory.

Swappiness

Swappiness is a Linux kernel parameter determining how aggressively the Linux virtual machine swaps pages between memory and the swap space on the system’s disk. The default value of vm.swappiness is 60, representing the percentage of free memory before activating the swap. Lower values reduce swapping and keep more memory pages in physical memory. Changing the value directly influences the performance of the Linux system. These values are defined as:

• 0: swap is disabled
• 1: Minimum amount of swapping without disabling it entirely
• 10: recommended value to improve performance when sufficient memory exists in a system
• 100: aggressive swapping

Transparent Huge Pages and Jemalloc

When it comes to Transparent Huge Pages (THP), they can take up more memory. The kernel’s memory allocation function allocates the requested page size, and sometimes more, rounded up to fit within the available memory. In other words, even if your application requires a small amount of memory, it will still be allocated at least a full page.

Additionally, pages must be contiguous in memory, which applies to ‘huge pages.’ This means that if the server cannot find a full page available in a row, it will defragment the memory before allocating it. This can negatively impact performance and cause delays.

InnoDB is built on a B*-Tree of indices, meaning that its workload will usually have sparse rather than contiguous-memory access, and, as such, it will likely noticeably perform worse with THP.

If you use jemalloc in conjunction with THP, the server may run out of memory over time because unused memory cannot be freed. Therefore, disabling Transparent Huge Pages for database servers is advisable to avoid this situation.

Using jemalloc instead of glibc memory allocator for MySQL results in less memory fragmentation and more efficient resource management. This is especially true when Transparent Huge Pages are disabled.

Action steps for memory settings

Before we change what needs to be adjusted, we need to know the current situation on our DB instance. By the way, I assume you installed the pt-toolkit and your favorite MySQL server to make your life easier. If you haven’t, please install it (Percona Toolkit documentation).

echo " -- THP check";cat /sys/kernel/mm/transparent_hugepage/enabled;  cat /sys/kernel/mm/transparent_hugepage/defrag;echo " --Swappiness";sysctl vm.swappiness ; cat /etc/sysctl.conf | grep -i swap;echo " -- OOM for MySQL";cat /proc/$(pidof mysqld)/oom_score ; cat /proc/$(pidof mysqld)/oom_score_adj;echo " -- jemalloc"; sudo pt-mysql-summary | grep -A5 -i "memory management" ; sudo grep -i jem /proc/$(pidof mysqld)/maps

We want to see something like below, but I am sure we are not.

> echo " -- THP check";cat /sys/kernel/mm/transparent_hugepage/enabled;  cat /sys/kernel/mm/transparent_hugepage/defrag;echo " --Swappiness";sysctl vm.swappiness ; cat /etc/sysctl.conf | grep -i swap;echo " -- OOM for MySQL";cat /proc/$(pidof mysqld)/oom_score ; cat /proc/$(pidof mysqld)/oom_score_adj;echo " -- jemalloc"; sudo pt-mysql-summary | grep -A5 -i "memory management" ; sudo grep -i jem /proc/$(pidof mysqld)/maps  -- THP check always madvise [never] always defer defer+madvise madvise [never]  --Swappiness vm.swappiness = 1 vm.swappiness = 1  -- OOM for MySQL 0 -800  -- jemalloc # Memory management library ################################## jemalloc enabled in mysql config for process with id 29584 Using jemalloc from /usr/lib/x86_64-linux-gnu/libjemalloc.so.1 # The End #################################################### 7f3456ac1000-7f3456af4000 r-xp 00000000 08:01 63812                      /usr/lib/x86_64-linux-gnu/libjemalloc.so.1 7f3456af4000-7f3456cf3000 ---p 00033000 08:01 63812                      /usr/lib/x86_64-linux-gnu/libjemalloc.so.1 7f3456cf3000-7f3456cf5000 r--p 00032000 08:01 63812                      /usr/lib/x86_64-linux-gnu/libjemalloc.so.1 7f3456cf5000-7f3456cf6000 rw-p 00034000 08:01 63812                      /usr/lib/x86_64-linux-gnu/libjemalloc.so.1

Let’s quickly fix it.

Disable THP

Let’s create a service which will disable THP for us:

sudo su - cat <<EOF > /usr/lib/systemd/system/disable-thp.service [Unit] Description=Disable Transparent Huge Pages (THP) [Service] Type=simple ExecStart=/bin/sh -c "echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled && echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag" [Install] WantedBy=multi-user.target EOF

And the below command to enable this service:

sudo systemctl daemon-reload sudo systemctl start disable-thp sudo systemctl enable disable-thp

vm.swappiness = 1

Change swappiness at runtime.

echo 1 > /proc/sys/vm/swappiness

And let’s persist it in the config file:

echo "# Swappiness" >> /etc/sysctl.conf echo "vm.swappiness = 1" >> /etc/sysctl.conf

And enable this change.

sudo sysctl -p

OOM and Jemalloc

We are halfway through improving things, but let’s keep pushing for better memory usage. Let’s install jemalloc.

sudo apt-get install libjemalloc1

Please confirm that we have it on the correct path:

ls -l /usr/lib/x86_64-linux-gnu/libjemalloc.so.1

And the last thing we need to push our MySQL service to use our magic jemalloc library, let’s create an override for systemd:

Note: Depending on the system, it can be shown as mysql or mysqld. You can use systemctl | grep mysql to get the proper mysql service name.

sudo systemctl edit mysql

Add the specified content to the file below immediately.

[Service] Environment= "LD_PRELOAD=/usr/lib64/libjemalloc.so.1" OOMScoreAdjust=-800

To apply this change, we need to reload daemon and mysql service.

sudo systemctl daemon-reload sudo systemctl restart mysql

The optimization of our memory settings has been completed successfully. You can verify it by executing the same check above.

echo " -- THP check";cat /sys/kernel/mm/transparent_hugepage/enabled;  cat /sys/kernel/mm/transparent_hugepage/defrag;echo " --Swappiness";sysctl vm.swappiness ; cat /etc/sysctl.conf | grep -i swap;echo " -- OOM for MySQL";cat /proc/$(pidof mysqld)/oom_score ; cat /proc/$(pidof mysqld)/oom_score_adj;echo " -- jemalloc"; sudo pt-mysql-summary | grep -A5 -i "memory management" ; sudo grep -i jem /proc/$(pidof mysqld)/maps

Mount point option for disk

Another thing I want to address in this article is how to reduce IO stress on our disks. It’s one of the most straightforward tasks we have, but it will give us a lot of performance for our powerful disks, which keeps our databases healthy and durable.

By default, when most disks are mounted using the relatime option, the system updates the metadata statistics for files each time they are accessed or changed on the mount point. This process can result in a significant amount of IO usage, which can be particularly problematic when running a database on that mount point. Given that MySQL typically accesses and writes numerous files concurrently, we must prioritize IO for more critical processes within the database rather than for updating metadata. Therefore, it is advisable to refrain from using the relatime option by default in such scenarios. To make this happen, we need to update it to noatime,nodiratime.

How to check the current options we have: I assume that you are using a separate mount point for the MySQL database attached to /var/lib/mysql path.

sudo mount | grep "/var/lib/mysql"

The result you will more likely get is:

/dev/sdb on /var/lib/mysql type ext4 (rw,relatime)

Action steps to apply best practices for disk settings

Let’s find out where we have these disk settings for that fstab coming to help.

> cat /etc/fstab |  grep "/var/lib/mysql" /dev/sdb      /var/lib/mysql     ext4 defaults     0   0

So it’s easy to update the fstab file and add the required options for mount point = noatime, nodiratime.

sudo vim  /etc/fstab /dev/sdb         /var/lib/mysql     ext4 defaults,noatime,nodiratime    0   0

From that moment, we are almost done, but we can’t apply these changes until our MySQL server is running, so we need to stop our mysql service, umount datadir directory, and mount it with new options.

sudo systemctl stop mysql

Once MySQL service is stopped, we can unmount our /mysql directory,

sudo umount /var/lib/mysql

and mount it again using updated /etc/fstab settings:

sudo mount -av

At that point, disk settings should be good, but it’s worth verifying that we have the desired mount point options. Afterward, we can start the MySQL service:

> sudo mount | grep  grep "/var/lib/mysql" /dev/sdb on /var/lib/mysql type ext4 (rw,noatime,nodiratime)

We see the options are correct, so we can start the mysql service.

sudo systemctl start mysql

Conclusion

Optimizing memory and disk settings for MySQL can greatly improve the performance and stability of your database. Following the steps outlined in this article, you can reduce IO stress on your disks, prioritize IO for critical processes within the database, and improve memory usage. Remember always to verify your changes and consult with a professional if you have any questions or concerns. With these optimizations in place, your MySQL database will be better equipped to handle the demands of your applications and users.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Try Percona Distribution for MySQL today!

Planet MySQL

https://kongulov.dev/assets/images/posts/building-efficient-applications-with-mysql-stored-procedures.png

DELIMITER //
CREATE PROCEDURE GetUser(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

Laravel News Links

https://ahmedash.dev/images/thumbs/laravel/laravel-where.png

1public static function __callStatic($method, $parameters)
2{
3    return (new static)->$method(...$parameters);
4}

 1public function __call($method, $parameters)
 2{
 3    if (in_array($method, ['increment', 'decrement', 'incrementQuietly', 'decrementQuietly'])) {
 4        return $this->$method(...$parameters);
 5    }
 6
 7    if ($resolver = $this->relationResolver(static::class, $method)) {
 8        return $resolver($this);
 9    }
10
11    if (Str::startsWith($method, 'through') &&
12        method_exists($this, $relationMethod = Str::of($method)->after('through')->lcfirst()->toString())) {
13        return $this->through($relationMethod);
14    }
15
16    return $this->forwardCallTo($this->newQuery(), $method, $parameters);
17}

1/**
2 * Get a new query builder for the model's table.
3 *
4 * @return \Illuminate\Database\Eloquent\Builder
5 */
6public function newQuery()
7{
8    return $this->registerGlobalScopes($this->newQueryWithoutScopes());
9}

1User::where('email', 'root@admin.com')->first()

1SELECT * FROM users WHERE `email` = 'root@admin.com' LIMIT 1

1User::where('email', 'root@admin.com')
2	->where('is_active', true)->first()

1SELECT * FROM users WHERE `email` = 'root@admin.com' AND `is_active` = true LIMIT 1

1User::where([
2	'email' => 'root@admin.com',
3	'is_active' => true
4])->first()

1SELECT * FROM users WHERE `email` = 'root@admin.com' AND `is_active` = true LIMIT 1

1User::where('email', 'root@admin.com')
2	->orWhere('username', 'admin')->first()

1User::where(function($q) {
2  $q->where('email', 'root@admin.com')
3	  ->orWhere('username', 'admin');
4})
5->where('is_active', true)
6->first()

1SELECT
2*
3FROM `users`
4WHERE (`email` = 'root@admin.com' or `username` = 'admin')
5AND `is_active` = 1
6
7LIMIT 1

1$onlyActive = true;
2
3User::where('email', 'root@admin.com')
4->when($onlyActive, function($q) {
5	$q->where('is_active', true);
6})->first()

1User::whereEmail('root@admin.com')->first()

1SELECT * FROM `users` WHERE `email` = 'root@admin.com' LIMIT 1

1User::whereEmailAndStatus('root@admin.com','active')->first()

1SELECT * FROM `users` WHERE `email` = 'root@admin.com' AND `status` = 'active' LIMIT 1

1User::whereEmailOrUserName('root@admin.com','admin')->first()

1SELECT * FROM `users` WHERE `email` = 'root@admin.com' OR `user_name` = 'admin' LIMIT 1

1User::whereNull('confirmed_at')->get()

1SELECT * FROM `users` WHERE `confirmed_at` IS NULL

1User::whereNot('status','active')->all()

1SELECT * FROM `users` WHERE NOT `status` = 'active'

1User::whereNotNull('confirmed_at')->all()

1SELECT * FROM `users` WHERE `confiremd_at` IS NOT NULL

Laravel News Links

http://img.youtube.com/vi/yH8mVTDylXI/0.jpg

I wasn’t aware that, according to Wikipedia:

To promote the film ["You Only Live Twice"], United Artists Television produced a one-hour colour television programme titled Welcome to Japan, Mr. Bond, which first aired on 2 June 1967 in the United States on NBC. Bond regulars Lois Maxwell and Desmond Llewelyn appeared, playing respectively Miss Moneypenny and Q. Kate O’Mara appears as Miss Moneypenny’s assistant. The programme shows clips from You Only Live Twice and the then four existing Bond films, and contained a storyline of Moneypenny trying to establish the identity of Bond’s bride.

The James Bond Wiki notes:

The feature stars Lois Maxwell and Desmond Llewelyn in character as Miss Moneypenny and Q respectively as well as Kate O’Mara as Miss Moneypenny’s unnamed assistant. They all reflect on James Bond’s previous adventures in Dr. No, From Russia with Love, Goldfinger and Thunderball through the use of archival footage. In addition they speculate on Bond’s current and future assignments, showing preview clips of You Only Live Twice.

A subplot is included about a woman (who is never directly shown, akin to Ernst Stavro Blofeld) who is obsessed with becoming Bond’s next lover. She is seen holding a Pan paperback copy of On Her Majesty’s Secret Service, possibly as a teaser to the audience that it would be the next novel to be adapted into a film (which it was).

Fortunately for us, the entire TV special is available on YouTube.  Enjoy!

It’s a bit dated, of course, compared to modern high-tech gee-whiz Bond movies, but I still enjoyed it – and it stars Sean Connery, who as far as I’m concerned is still the best of the actors who played Bond.

Peter

Bayou Renaissance Man

https://static1.makeuseofimages.com/wordpress/wp-content/uploads/2023/09/black-flat-screen-tv-turned-on-displaying-design.jpg

MakeUseOf

https://theawesomer.com/photos/2023/09/mapping_sci_fi_locations_in_space_t.jpg

Link

Science fiction books, TV shows, and movies often set their stories in real locations in space. The Overview Effect put together a visualization that charts the relative locations of stories in fictional works like Star Trek and Alien, and Dune, using actual places in the universe to illustrate their distances and relationships.

The Awesomer

https://theawesomer.com/photos/2023/09/mapping_sci_fi_locations_in_space_t.jpg

Link

Science fiction books, TV shows, and movies often set their stories in real locations in space. The Overview Effect put together a visualization that charts the relative locations of stories in fictional works like Star Trek and Alien, and Dune, using actual places in the universe to illustrate their distances and relationships.

The Awesomer

https://i.kinja-img.com/gawker-media/image/upload/c_fill,f_auto,fl_progressive,g_center,h_675,pg_1,q_80,w_1200/9ddb7f2adc0fe8bb0feb3673ef11a842.jpg

Gizmodo

https://i0.wp.com/lefred.be/wp-content/uploads/2023/09/Selection_533-1.png?w=914&ssl=1

Sometimes it’s convenient to retrieve the user creation statement and to copy it to another server.

However, with the new authentication method used as default since MySQL 8.0, caching_sha2_password, this can become a nightmare as the output is binary and some bytes can be hidden or decoded differently depending of the terminal and font used.

Let’s have a look:

If we cut the create user statement and paste it into another server what will happen ?

We can see that we get the following error:

ERROR: 1827 (HY000): The password hash doesn't have the expected format.

How could we deal with that ?

The solution to be able to cut & paste the authentication string without having any issue, if to change it as a binary representation (hexadecimal) like this:

And then replace the value in the user create statement:

The user creation succeeded, and now let’s test to connect to this second server using the same credentials:

Using MySQL Shell Plugins

I’ve updated the MySQL Shell Plugins available on GitHub to use the same technique to be able to cut & paste the user creation and the grants:

And for MySQL HeatWave on OCI ?

Can we use the generated user creation statement and grants with MySQL HeatWave ?

For the user creation, there is no problem and it will work. However for the grants there is a limitation as some of the grants are not compatible or allowed within MySQL HeatWave.

The list of grants allowed in HeatWave is available on this page.

Let’s try:

As you can see, some of the privileges are not allowed and the GRANT statements fail.

You have the possibility to ask to the MySQL Shell Plugin to strip the incompatible privileges, using the option ocimds=True:

Now you can use the generated SQL statements with a MySQL HeatWave instance:

Conclusion

As you can see, the default authentication plugin for MySQL 8.0 and 8.1 is more secure but can be complicated to cut and paste. But as we say, “if there is no solution, there is no problem !”, and in this case we have also a solution to copy and paste the authentication string.

Enjoy MySQL !

Planet MySQL

public function index()
{
    $posts = Post::paginate(100);
    return view("posts.index", compact('posts'));
}

@extends('layouts.app')
@section('content')
<table>
        <thead>
            <tr>
                <th>No.</th>
                <th>Title</th>
            </tr>
        </thead>
        <tbody>
            @foreach ($posts as $post)
            <tr>
                <td></td>
                <td></td>
            </tr>
            @endforeach
        </tbody>
    </table>
    
    
    
@endsection

@foreach ($posts as $post)
<tr>
    <td></td>
    <td></td>
</tr>
@endforeach

Laravel News Links