Category: Uncategorized

Posted on

MySQL Key Performance Indicators (KPI) With PMM

https://percona.com/blog/wp-content/uploads/2023/06/mysql-replication-1024×520.pngMySQL Key Performance Indicators

As a MySQL database administrator, keeping a close eye on the performance of your MySQL server is crucial to ensure optimal database operations. A monitoring tool like Percona Monitoring and Management (PMM) is a popular choice among open source options for effectively monitoring MySQL performance.

However, simply deploying a monitoring tool is not enough; you need to know which Key Performance Indicators (KPIs) to monitor to gain insights into your MySQL server’s health and performance.

In this blog, we will explore various MySQL KPIs that are basic and essential to track using monitoring tools like PMM. We will also discuss related configuration variables to consider that can impact these KPIs, helping you gain a comprehensive understanding of your MySQL server’s performance and efficiency.

Let’s dive in and learn how (and what) to effectively monitor MySQL performance, along with examples from PMM, by understanding the critical KPIs to watch for.

Database uptime and availability

Monitoring database uptime and availability is crucial as it directly impacts the availability of critical data and the performance of applications or websites that rely on the MySQL database.

PMM monitors the MySQL uptime:

Percona Monitoring and Management KPI

show global status like 'uptime';

Indicates the amount of time (seconds) the MySQL server has been running since the last restart.

Query performance

Query performance is a key performance indicator (KPI) in MySQL, as it measures the efficiency and speed of query execution. This includes metrics such as query execution time, the number of queries executed per second, and the utilization of query cache and adaptive hash index.

Too many slow queries, inefficient queries, or long-running queries can indicate potential performance issues that may negatively impact the database’s performance and why monitoring query performance is crucial.

On PMM, we have the following panels showing the gist of query execution and summarizing the pattern. This is not an exhaustive list but an example of what we can watch for.

Number of slow queries recorded

MySQL slow queries

Select types, sorts, locks, and total questions against a database

Command counters and handlers used by queries give an overall traffic summary

MySQL traffic summary

Along with this, PMM also comes with Query Analytics giving much detailed information about queries getting executed.

Query Analytics

MySQL Query Analytics

You should watch out for status variables for these, for example:

show global status like '%sort%';
show global status like '%slow%';

Some of the configuration variables to note for:

  • slow_query_log: Enables / Disables slow query log.
  • long_query_time: Defines the threshold for query execution time, and queries taking longer than this threshold are considered slow and logged for further analysis
  • innodb_buffer_pool_size: Sets the size of the InnoDB buffer pool.
  • query cache: Disable (query_cache_size: 0, query_cache_type:OFF)
  • innodb_adaptive_hash_index: Check adaptive hash index usage to determine its efficiency.

Indexing efficiency

Monitoring indexing efficiency in MySQL involves analyzing query performance, using EXPLAIN statements, utilizing performance monitoring tools, reviewing error logs, performing regular index maintenance, and benchmarking/testing. This KPI is also directly related to Query Performance and helps improve it.

There are multiple tables MySQL internal system manages that come in handy, identifying the inefficient indexes, to name a few: sys.schema_unused_indexes, and information_schema.index_statistics.

The pt-duplicate-key-checker is another Percona Toolkit utility to eliminate duplicate indexes and improve indexing efficiency.

Connection usage

Connection usage is a critical key performance indicator (KPI) in MySQL, as it involves tracking the number of concurrent connections to the MySQL server and ensuring that it does not exceed the allowed limit. Improper configuration of connection settings can have catastrophic effects on the production database, resulting in it becoming inaccessible during connection spikes or even experiencing out-of-memory (OoM) kills of the MySQL daemon.

By monitoring and managing connection usage, you can proactively identify and address potential issues such as connection spikes, resource exhaustion, or improper configuration that may impact the availability and performance of the MySQL database.

PMM captures the MySQL connection matrix

MySQL connection matrix

It is important to provide appropriate max_connections and also monitor max_used_connections, max_used_connections_time to review the history of max usage to estimate the traffic.

Implementing appropriate connection pooling or choosing appropriate connection settings can help optimize resource utilization and reduce downtime or performance degradation. Hint Hint, ProxySQL helps.

CPU and memory usage

Monitor CPU and memory utilization of the MySQL server to ensure efficient resource utilization. It is advisable to have a dedicated production MySQL Server that can independently claim the system resources as needed. That said, it should also be monitored for usage, which will exhibit the traffic pressuring them.

PMM dashboard – CPU utilization and memory details

CPU utilization and memory details

The most effective memory configuration variable, innodb_buffer_pool_size, sets the size of the InnoDB buffer pool. 

Another related variable, innodb_buffer_pool_instances, determines the number of buffer pool instances for the InnoDB storage engine, which can improve the performance of multi-core systems by reducing contention on the buffer pool latch.

That said, CPU or memory usage is not only limited to these two variable configurations, and further analysis is required to track what’s causing the usage spikes. The point we’re making here is they are critical for MySQL Performance.

Disk space usage

Monitor the disk space usage of MySQL data files, log files, and temporary files.

You may review the fragmented tables, binary-logs, and duplicate or redundant indexes to reclaim the space. As a best practice, It is advisable to have different mounts for MySQL data and log files with specific system configurations.

PMM – Disk Details, which includes disk usage as well as disk performance charts

MySQL disk performance charts

Replication lag

Monitoring replication lag is important as it can affect the consistency and reliability of data across multiple database instances in a replication setup. 

Replication lag can occur due to various factors such as network latency, system resource limitations, complex transactions, or heavy write loads on the primary/master database. If replication lag is too high, it can result in stale or outdated data on the replica/slave databases, leading to data inconsistencies and potential application issues.

PMM – MySQL Replication Summary dashboard

Related configuration or status variables to consider:

  • Seconds_Behind_Master: In SHOW REPLICA STATUS command, this value indicates the replication lag in seconds.

One of the possible improvements in lag would be utilizing Parallel Replication.

Backup and recovery metrics

Backup and recovery metrics are key performance indicators (KPIs) for MySQL that provide insights into the reliability, efficiency, and effectiveness of backup and recovery processes. They include backup success rate, backup duration, recovery time objective (RTO), recovery point objective (RPO), and backup storage utilization. Monitoring these metrics helps ensure data protection, minimize downtime, and ensure business continuity.

You should not only monitor the backup mount for disk space and backup log but also regularly test the restores and log to match RPO and RTO objectives.

Error rates

The MySQL error log contains information about errors, warnings, and other issues that occur within the MySQL database. By monitoring the error log, you can quickly identify and resolve any problems that may arise, such as incorrect queries, missing or corrupt data, or database server configuration issues.

  • error_log: Specifies the location of the MySQL error log.

Conclusion

In this blog, we discussed the basics of MySQL’s key performance indicators (KPIs) using PMM. By monitoring these KPIs, such as database uptime and availability, query performance, indexing efficiency, connection usage, CPU and memory usage, disk space usage, replication lag, backup and recovery metrics, and error rates, you can gain valuable insights into your MySQL server’s health and performance.

Note that the specific configuration variables and their optimal values may vary depending on the MySQL version, system hardware, workload, and other factors. It’s important to thoroughly understand the MySQL configuration variables and their impacts before making any changes and to carefully monitor the effects of configuration changes to ensure they improve the desired KPIs.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

 

Download Percona Monitoring and Management Today

Percona Database Performance Blog

Posted on

No Place to Place: Inside China’s Bicycle Graveyards

https://theawesomer.com/photos/2023/06/no_place_to_place_china_bike_graveyards_t.jpg

No Place to Place: Inside China’s Bicycle Graveyards

Link

With over 1.4 billion people, China generates a lot of waste. In 2017, it created a new problem. After shared bicycle programs cluttered streets with more than 25 million bikes, the government enforced fleet size limits, and countless bikes ended up in massive graveyards. Guoyong Wu’s short film uses aerial photography to show just how big the problem got.

The Awesomer

Posted on

Laravel File Uploads: Save Filename in DB with Folder and URL?

https://laraveldaily.com/storage/423/Copy-of-Copy-of-ModelpreventLazyLoading();-(6).png

When uploading files with Laravel, how to store the filename in the DB? Should you store filename.png? Or, include the folder of avatars/filename.png? Or, the full path https://website.com/avatars/filename.png? Let me tell you my opinion.

This tutorial will have an avatar field on the register page and an avatar column in the users table. Let’s see how we can save it.

The easiest way is to store just the filename and create a separate storage disk.

config/filesystems.php:

return [
 
    // ...
 
    'disks' => [
 
        // ...
 
        'avatars' => [
            'driver' => 'local',
            'root' => storage_path('app/public/avatars'),
            'url' => env('APP_URL').'/storage/avatars',
            'visibility' => 'public',
            'throw' => false,
        ],
 
    ],
 
    // ...
 
];

Then, when storing the file we need to specify the new avatars disk.

app/Http/Controllers/Auth/RegisteredUserController.php:

class RegisteredUserController extends Controller
{
    // ...
 
    public function store(Request $request): RedirectResponse
    {
        $request->validate([
            'name' => ['required', 'string', 'max:255'],
            'email' => ['required', 'string', 'email', 'max:255', 'unique:'.User::class],
            'password' => ['required', 'confirmed', Rules\Password::defaults()],
            'avatar' => ['nullable', 'image'],
        ]);
 
        if ($request->hasFile('avatar')) { 
            $avatar = $request->file('avatar')->store(options: 'avatars');
        } 
 
        $user = User::create([
            'name' => $request->name,
            'email' => $request->email,
            'password' => Hash::make($request->password),
            'avatar' => $avatar ?? null, 
        ]);
 
        // ...
    }
}

This way, your full URL filename https://website.com/storage/avatars/filename.png consists of three things:

  • Domain: https://website.com is stored in your APP_URL in .env file: so it is flexibly different for your local and production servers
  • Folder: /storage/avatars is in the config('disks.avatars.url') which corresponds to the internal structure of /storage/app/public/avatars described in the same config file. Both also can be flexibly changed if needed.
  • Filename: filename.png is the only thing actually landing in the DB column

To get the URL for the image in the Blade file, we would use URL method on the Storage facade providing the disk.

<img src="" alt="" />

But what if, after some time, you would need to go from local disks to, let’s say, Amazon S3?

The only change you would need to make is to change the disk, and maybe instead of the url, use the temporaryUrl method to provide the expiration time for the link.

<img src="" alt="" />

Laravel News Links

Posted on

How A 120mm Tank Round Works

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

Here’s something informational for Sunday, Nicholas Moran explaining exactly how a modern 120mm (AKA 120×570mm NATO, the type used by the M1A2 Abrams and the German Leopard 2) APFSDS round works.

  • He has a dummy blue round to demonstrate the features. “All the projectiles are color coded. Explosive, for example, would be green with yellow lettering.” APFSDS rounds are black.
  • “The aft cap is the one piece which is left behind after a modern round is fired, and this takes up a lot less room than a traditional shell casing rattling around inside the tank once you fire it.”
  • A long primer rod runs up the middle for more even propellent burning.
  • “A modern tank does have a firing pin. It’s electrically fired, but it has a firing pin. It looks just like a firing pin you’d expect from a rifle, except it’s about yay long…Electricity goes through the firing pin, sets off the primer, which sets off the propellant, which gives you
    the big boom.”

  • There are even emergency hand crank firing systems with dynamos to use if the electrical system goes down.
  • “The rest of the shell casing is made of a form of cellulose, and it is burned up in the explosion. So the aft cap is sufficient to seal the breach instead of requiring the entire casing to expand as you you’d find on a traditional round.”
  • “The catch is that this is simply not as robust as a metal shell.” Which is why the loader has to inspect rounds for scratches or bulges to the water-resistant coating. That could cause the round to break apart or misfire. “This is a bad thing.”
  • Which is why tank crews practice misfire drills to ensure safe handling of rounds so they don’t spread loose propellant all over the tank’s interior.
  • “The kinetic energy penetrator is itself a dart… it’s got fins at the back to keep the pointy end forwards, and it is kept centered as it goes down the tube by these sabot petals.”
  • “Modern sabots seem to have settled on three of these petals per projectile. Once the projectile has left the muzzle, the air is caught by the petals and they are peeled away.”
  • The discarded petals are a danger. “This is why sabot rounds such as APFSDS or M-PT should not be fired over the heads of friendly infantry.”
  • “The dart goes that way, hits metal, and basically punches through, taking little bits of metal inside with them. This is called a spall. These little fragments metal are extremely unhealthy to anyone or anything inside the vehicle which it hits.”
  • “However, if the armor is too thin to produce spalling, you get what is known as over-penetration. So you make a dart-sized hole on one side of the vehicle, a dart-sized hole on the far side of the vehicle, and dart sized holes on anything in-between, and outside of brown pants for the crewmen, quite possibly nothing else.”
  • “If so you’re firing such a target, you’re probably better off using a shaped charge round such as HEAT.”
  • He then show off a dummy HEAT projector, which has a funky blunt circular head that “in effect clears the air as a wind shield for the decidedly non-aerodynamic flat bit. The main body of the round also performs something of a stabilizing function and thirdly provides adequate standoff or room for the penetrating jet to form.”
  • “Here is a metal cone surrounded by explosives. The explosives detonate, the cone collapses the liner.”
  • Text popped up on screen at 9 minutes in notes that the penetrating jet is not high temperature plasma.

    • Here’s another video that provides a visualized simulation of how APFSDS rounds work.

    Lawrence Person’s BattleSwarm Blog