Posts

Posted on

Bolt SQL – Fast and beautiful database client

https://picperf.dev/https://laravelnews.s3.amazonaws.com/featured-images/bolt-sql-featured.jpg

Bolt SQL - Fast and beautiful database client

Bolt SQL is a brand-new database client from the makers of TablePlus.

This new client focuses on speed and more cloud integration so you can more easily share your setup across machines or with your team.

As a die-hard TablePlus user, I was excited to see this beta, and I noticed the speed as soon as I exported and imported data into it. Both felt instant. It’s not 100% feature-complete with TablePlus, but I imagine it’ll be pretty close once they finish the beta. Let’s look at some of the features of this new app.

Differences between TablePlus and Bolt SQL

TablePlus mainly focuses on native experience and fast interaction with GUI. TablePlus runs completely on your machine, and all data is stored within it.

Bolt SQL moves to the cloud, allowing you new features like configuration synching on all users’ devices, real-time collaboration, sharing charts and reports, and even more AI integration coming in the future.

Friendly GUI tool, blazing fast, and tracking-free

Bolt SQL cherry-picked the features you use the mostand made them easy to access. The top priorities are performance, security, and privacy.

Real-time collaboration

Bolt SQL supports real-time collaboration so you and your team can work together!

Shared workspaces

Sync and your data on all devices so you can work everywhere. Bolt SQL uses end-to-end encryption; no one can access it without the private key.

Charting

With Bolt SQL you can generate bar or line charts from your queries to easily visualize your data.

More features coming soon!

The Bolt SQL team is planning on implementing the following in the coming weeks:

  1. Integrating chatGPT and other LLMs to improve the autosuggestions.
  2. Support more charts and a dashboard builder.
  3. Support advanced filters for the table view.
  4. Add table structure/index/foreign key editor.
  5. Support more database drivers like DuckDB, Redis, etc.

Bolt SQL Pricing

You can use Bolt SQL for free on the basic plan, but that doesn’t include any cloud features. If you want to try the beta, they include an early bird coupon in the top bar header of the site for 50% off.

Visit Bolt SQL for more details and try it. It works for MacOS and Windows, and a Linux version coming soon.

The post Bolt SQL – Fast and beautiful database client appeared first on Laravel News.

Join the Laravel Newsletter to get all the latest Laravel articles like this directly in your inbox.

Laravel News

Posted on

NSA Shares Top Ten Cybersecurity Misconfigurations

The National Security Agency (NSA), in partnership with the Cybersecurity and Infrastructure Security Agency (CISA), have highlighted the ten most common cybersecurity misconfigurations in large organizations. In their join cybersecurity advisory (CSA), they also detail the tactics, techniques, and procedures (TTPs) actors use to exploit these misconfigurations. From the report: Through NSA and CISA Red and Blue team assessments, as well as through the activities of NSA and CISA Hunt and Incident Response teams, the agencies identified the following 10 most common network misconfigurations: 1. Default configurations of software and applications 2. Improper separation of user/administrator privilege 3. Insufficient internal network monitoring 4. Lack of network segmentation 5. Poor patch management 6. Bypass of system access controls 7. Weak or misconfigured multifactor authentication (MFA) methods 8. Insufficient access control lists (ACLs) on network shares and services 9. Poor credential hygiene 10. Unrestricted code execution NSA and CISA encourage network defenders to implement the recommendations found within the Mitigations section of this advisory — including the following — to reduce the risk of malicious actors exploiting the identified misconfigurations: Remove default credentials and harden configurations; Disable unused services and implement access controls; Update regularly and automate patching, prioritizing patching of known exploited vulnerabilities; and Reduce, restrict, audit, and monitor administrative accounts and privileges. NSA and CISA urge software manufacturers to take ownership of improving security outcomes of their customers by embracing secure-by-design and-default tactics, including: Embedding security controls into product architecture from the start of development and throughout the entire software development lifecycle (SDLC); Eliminating default passwords; Providing high-quality audit logs to customers at no extra charge; and Mandating MFA, ideally phishing-resistant, for privileged users and making MFA a default rather than opt-in feature. A PDF version of the report can be downloaded here (PDF).

Read more of this story at Slashdot.

Slashdot

Posted on

Testing Your Multi-database Multi-tenant Laravel Application

https://media.licdn.com/dms/image/D4D12AQF1GggB9-6qzg/article-cover_image-shrink_720_1280/0/1695238380041?e=2147483647&v=beta&t=8W5yL5H_xSLyU2j2LlLeUK50wJ_feX1ITiPs7aPStQE

For some months now I have been working on a multi-tenant loan management system which uses the Tenancy For Laravel package. Find it here on Github. Following their documentation, everything works perfectly so far. The only issues I have had is setting up my feature tests. The documentation on the testing doesn’t seem to work very well and it made me want to pull out my hair. After hours of trying, I finally came up with a simple solution to set up my tests.

First of, let’s talk about what was not working. From the docs here, the only thing to do is to initialize tenancy in the test setUp method and everything should work fine. This failed and I had to find another way.

Here is what I did:



Quick explanation of what is going on here. The setUp method is initializing tenancy so that everything works as if the application is being accessed through a tenant subdomain. I had to call withoutMiddleware because, those middlewares were causing the requests to fail.


Note: they were being used in my routes/web.php file.


tearDown was for cleaning things up.


All my test classes extended this as the base test class like so:




And that’s it, my tests were finally running. Hope this helps someone. If you found this useful, let me know in the comments. If you have implemented this in a different way as well, let me know in the comments.






Laravel News Links

	


	





		

		
Posted on 
Deploy your Laravel app in under five minutes, for free!
	


	
	

		
https://opengraph.githubassets.com/bdf97517a898b7f003d8826f8d3f7a35101514411eee0e9f704facdb103b8ada/fly-apps/fly-laravel










Fly-Laravel was created by Fly.io and is a quick way to get a Laravel app running on Fly.io. It was built using Laravel Zero.


Fly-Laravel assumes that you have flyctl installed, and that you have it connected to your Fly.io account. If you need help with this, check out  https://fly.io/docs/speedrun/.


These commands will help you set up apps on Fly.io. Remember that running these apps can cost money!


You can find more about Fly.io’s free allowance and pricing here.


Run composer require fly-apps/fly-laravel to install the latest version.


By default, commands are invoked using the vendor/bin/fly-laravel script. To avoid have to type all that for ecery command, you may configure a shell alias:




alias fly-laravel='vendor/bin/fly-laravel'




To make sure this is always available, you may add this to your shell configuration file in your home directory, such as ~/.zshrc or ~/.bashrc, and then restart your shell.


With this package, you can spin up Laravel, MySQL and/or Redis apps on Fly.io . There are two commands for every type of app: launch and deploy.


    

  • Launch will create a new application on Fly.io in the organization you choose.
    • 

  • Deploy will (re)deploy the app. This will update the app you’ve already created with launch.
    • 



    

  • You have an account on Fly.io
    • 

  • You have created an organization on Fly.io
    • 

  • You have installed the flyctl agent.
    • 



Run fly-laravel launch to create a new Laravel application. You will be able to pick the name, what organization to deploy in and what extra services you want to set up.


    

  • App name: What the app on Fly.io will be called. This can only contain alphanumeric characters and hyphens, for DNS reasons.
    • 

  • Primary Region: The primary region to deploy your app in. You should pick the region closest to your users. You can always add more regions, as specified in the Scaling Documentation
    • 

  • Organization: On Fly.io, apps can be grouped into organizations. Organizations are a great way to divide up apps, share access with team members and manage billing. If there’s only one organization available we’ll auto-select that one.
    • 

  • Services: you can pick if you want to run cron or a queue worker in the app. This will create a process group for each extra service, to scale independently.
    • 



To set up the app, a number of steps will occur to set up a basic Laravel app:


    

  • The locally installed Node and PHP versions are detected
    • 

  • The fly.toml app configuration file is generated. If you want to make changes to your app later on, this is where to do it.
    • 

  • Some folders and files are copied over, most notably the Dockerfile.
    • 

  • A randomly generated APP_KEY will be set as a secret on your app.
    • 



A note on the configured SESSION_DRIVER in the fly.toml file:


    

  • By default, your Laravel app will be configured with Cookie-based session storage. This allows sessions to work across multiple instances of your web app without the need of an external session service like Redis to make session data available to all the instances. Of course Cookie-based session storage has limits on how much session data it can store, so you might want to consider replacing this to allow storage of larger data.
    • 



After set up, your app will be ready to deploy! In your project root, a .fly folder will be added alongside a Dockerfile and a fly.toml file.


When launching databases you will need to deploy again so launch those before deploying the laravel app.


Run fly-laravel deploy to deploy your Laravel app. This will update the running app (if any) to include your latest changes. Add the --open flag to open the app in your browser when it has been deployed.


Run fly-laravel launch:mysql to create a new MySQL application. You will be able to pick the app name, what organization to deploy in, the MySQL username and the volume name. If a Laravel app is detected, you can opt to use the same organization and primary region.


    

  • App Name: What the app on Fly.io will be called. This can only contain alphanumeric characters and hyphens, for DNS reasons. By default, [laravel app name]-db will be proposed as the app name.
    • 

  • Organization: On Fly.io, apps can be grouped into organizations. Organizations are a great way to divide up apps, share access with team members and manage billing. If there’s only one organization available we’ll auto-select that one.
    • 

  • Primary Region: The primary region to deploy your app in. You should pick the region closest to your users. You can always add more regions, as specified in the Scaling Documentation
    • 

  • Volume Name: For data persistence, a volume will be needed for database applications. If there’s a volume with this name available, we’ll use that. If no volume with this name can be found, a 1GB volume will be created on deploy. More about volumes here: Volume Documentation.
    • 



Some notes when launching a MySQL database:


    

  • During the launch, some environment variables will be updated in the fly.toml configuration of the Laravel app. Redeploying the Laravel app will be necessary to reflect these changes.
    • 

  • The DB_CONNECTION env var in fly.toml will be set to ‘mysql’
    • 

  • On deploy, a small scale machine will be provisioned with a 1x shared CPU and 256Mb of memory. Consider scaling up the database for better performance.
    • 

  • By default, the innodb buffer pool size will be set to 64MB. Consider optimizing this based on your performance requirements. You can find this in .fly/mysql/fly.toml, in the [processes] section.
    • 

  • For the networking to work properly, the Laravel app and MySQL app should be in the same organization.
    • 



Run fly-laravel deploy:mysql to deploy the MySQL application. After the deployment we’ll run a quick check of the machine resources, and show a warning if the memory is smaller than 1GB.


Run fly-laravel launch:redis to launch a Redis application. You will be able to pick the app name, what organization to deploy in and the volume name. If a Laravel app is detected, you can opt to use the same organization and primary region.


    

  • App Name: What the app on Fly.io will be called. This can only contain alphanumeric characters and hyphens, for DNS reasons. By default, [laravel app name]-db will be proposed as the app name.
    • 

  • Organization: On Fly.io, apps can be grouped into organizations. Organizations are a great way to divide up apps, share access with team members and manage billing. If there’s only one organization available we’ll auto-select that one.
    • 

  • Primary Region: The primary region to deploy your app in. You should pick the region closest to your users. You can always add more regions, as specified in the Scaling Documentation
    • 

  • Volume Name: For data persistence, a volume will be needed for database applications. If there’s a volume with this name available, we’ll use that. If no volume with this name can be found, a 1GB volume will be created on deploy. More about volumes here: Volume Documentation.
    • 



Some notes when launching a Redis application:


    

  • During the launch, some Laravel environment variables will be updated in its fly.toml configuration. Redeploying the Laravel app will be necessary to reflect these changes.
    • 

  • The CACHE_DRIVER and SESSION_DRIVER env vars in fly.toml will be set to ‘redis’
    • 

  • On deploy, a small scale machine will be provisioned with a 1x shared CPU and 256Mb of memory. Consider scaling up the database for better performance.
    • 

  • For the networking to work properly, the Laravel app and Redis app should be in the same organization.
    • 



Run fly-laravel deploy:redis to deploy the Redis application. After the deployment we’ll run a quick check of the machine resources, and show a warning if the memory is smaller than 1GB.


For more information about fly.io, check out the Fly.io Docs.


For more Laravel-related content, check out the Laravel-Bytes blog.


Fly-Laravel is an open-source software licensed under the MIT license.








Laravel News Links

	


	





		

		
Posted on 
phpcrypter: A PHP Source Code Encrypter
	


	
	

		
https://opengraph.githubassets.com/b6210f37aab8b86ce927256862837411f1839184f9f4377681f1e007d859a3ca/chr1sc0des/phpcrypter










The goal of this open source package is security through obscurity.


It aims to offer an alternative to delivering your closed source projects in plaintext. Instead, you can opt to deliver them in ciphertext (encrypted), alongside a binary PHP extension which will decrypt them on the fly.


This package uses symmetric encryption, therefore the AES-256 key (which is only known to you as the developer), can be unique per project and/or release. To avoid being detected by hex editors (e.g. Hex Fiend) and the strings command, the key is stored within the binary as an XOR cipher, split into 32 parts. Additionally, the XOR key is also split into 32 parts. All 64 key parts are then shuffled together along with 64 random key parts (128 parts in total) to ensure that the AES-256 and XOR key parts never appear in the same place twice.


If you search for an obfuscation package, there is almost always a complimentary deobfuscation package available (written by someone else), which renders the original package obsolete (unfortunately). On the other hand, AES-256 encryption hasn’t been broken (yet)!


That being said, I would certainly consider obfuscation as a compliment to encryption. If your source code is obfuscated first (before encryption) and someone tries to reverse engineer your project by looking at the opcodes and stepping through it, it would be much more difficult to understand.


Typically, obfuscation focuses on altering the execution flow of your source code, combined with the scrambling of the names of your classes, methods, functions, variables and string literals. Because obfuscation essentially rewrites your code, it inevitably comes with a few “gotchas” along the way. Encryption, on the other hand, keeps your code intact (exactly as you wrote it).


    

  1. PHP ^8.2
    2. 

  2. phpize
    3. 



This package was built with support for Windows in mind, however, it has not been tested yet.


The below assumes that you’re currently in your application’s root directory.




$ composer require chr1sc0des/phpcrypter --dev






$ ./vendor/bin/phpcrypter generate [--clean] [--] <name> [<payload>]




The below command will generate a unique AES-256-CBC symmetric key named foo:




$ ./vendor/bin/phpcrypter generate foo




Additionally, a .phpcrypter/foo directory will be created in your application’s root, containing a PHP extension skeleton. The symmetric key is the ❤️ of the skeleton 🦴 — they will both be used to later build a binary PHP extension of the same name (foo.so).


A good rule of thumb is one key (and therefore one PHP extension) per project.


The output of the above command will be similar to the following:




Success!
Payload: pAYL0AD==





❗ Please remember to add /.phpcrypter to your .gitignore file.


‼️ Additionally, it is important to save the payload in a password manager, such as 1Password or pass.




$ cd .phpcrypter/foo
$ phpize
$ ./configure
$ make
$ make install




The above commands will build a PHP extension named foo.so and copy it into your PHP extension directory. The directory can be found via the following command:




$ php -i | grep ^extension_dir




You should then add the following line to your php.ini configuration file:




extension=foo.so
foo.decrypt=1





The location of the loaded php.ini configuration file can be found via the following command:




$ php -i | grep "Loaded Configuration File"




Next, verify that the extension is loaded:




$ ./vendor/bin/phpcrypter encrypt <payload> <path>...




The below encrypts multiple directories and files at once. You must specify the previously obtained payload as the first argument.




$ ./vendor/bin/phpcrypter encrypt "pAYL0AD==" \
  "dir-1" \
  "dir-2" \
  "file-1.php" \
  "file-2.php"




❗ The contents of any PHP files found in the above paths will be overwritten. It is highly recommended that you create a new Git branch for these files:




$ git checkout -b encrypted




If you’re just experimenting, it’s useful to be able to encrypt and decrypt at will. The below decrypts any directories and/or files previously encrypted with the payload argument:




$ ./vendor/bin/phpcrypter decrypt <payload> <path>...




❗ Again, the contents of any PHP files found in the above paths will be overwritten.




<?php // @foo
if (! extension_loaded('foo')) exit('The "foo" extension is not loaded');
#pAYL0AD==




The PHP code block should be self explanatory, however, the final line contains a base64 encoded string containing the phpcrypter version, the IV (initialization vector) and the encrypted source code.


By default, when the extension is loaded, it simply hooks into the internals of PHP, namely the zend_compile_file() function, but it doesn’t do anything, unless the foo.decrypt configuration option is set to 1.


In production, it is recommended that you set foo.decrypt to 0 in your php.ini configuration file. This means that there’s no additional overhead for unencrypted PHP files (which will typically be any open source packages in your Composer dependencies).


Then, it is recommended that you use ini_set('foo.decrypt', 1) in any unencrypted PHP files which include/require encrypted files. For example, if you would like to encrypt a controller, you should use ini_set() within an unencrypted base controller. You cannot use ini_set() within encrypted PHP files because zend_compile_file() works at a lower level.


Below are some autocannon benchmarks (10 connections for 10s):






Extension Loaded
Extension Enabled
File Encrypted
Avg. Latency






No
No
No
2860.78 ms




Yes
php.ini
No
2923.03 ms




Yes
php.ini
Yes
2970.96 ms




Yes
ini_set()
Yes
2890.86 ms






When you’re ready to deploy your encrypted files, you should build an extension for that platform if it differs from your workstation, for example, Linux vs. macOS.


In the event that you need to install multiple extensions on the same server (for different projects), you should consider installing phpcrypter globally:




$ composer global require chr1sc0des/phpcrypter




You must specify the previously obtained payload as the second argument, so that the same key becomes the ❤️ of this skeleton 🦴 too.




$ cd ~/.composer

$ export HISTCONTROL=ignorespace
$  ./vendor/bin/phpcrypter generate foo "pAYL0AD=="
$ unset HISTCONTROL




💡 Using HISTCONTROL=ignorespace prevents any commands that are prefixed with a space from appearing in your shell’s history.


You should refer to the previous section, following the appropriate steps for this particular platform.


You are now ready to deploy your encrypted PHP files! 🚀


The MIT License (MIT).








Laravel News Links

	


	





		

		
Posted on 
A complete history of Laravel’s versions from 2011 to 2023
	


	
	

		
https://life-long-bunny.fra1.digitaloceanspaces.com/media-library/production/178/conversions/sngqsGxoLry9qHLY5j7XaXm5dLJ5C5-metabGFyYXZlbC5qcGc%3D–optimized.jpg












A complete history of Laravel’s versions (2011-2023)



                    Updated on


        




                                    A complete history of Laravel's versions (2011-2023)


If you’re a Laravel enthusiast like me, take a journey through history and explore a detailed timeline of Laravel’s evolution from its inception in 2011 to 2023.


As we dive into each version release, we’ll highlight the game-changing features and improvements that Laravel brought along. Whether you’re planning an upgrade or just curious about how far Laravel has come—this comprehensive guide seals the deal.






Laravel News Links

	


	





		

		
Posted on 
Don’t Start Using Your MySQL Server Until You’ve Configured Your OS
	


	
	

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


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