Author: admin yep

https://www.iheavy.com/wp-content/uploads/2023/08/MySQL-DBA-Interview.webp

MySQL DBAs are in greater demand now than they’ve ever been. While some firms are losing the fight for talent, promising startups with a progressive bent are getting first dibs with the best applicants.

Whatever the case, interviewing for a MySQL DBA is a skill in itself so I thought I’d share a guide of top MySQL DBA interview questions to help with your screening process.
It’s long and detailed with some background to give context so I will be publishing this in two parts.

The History of The DBA As A Career

In the Oracle world of enterprise applications, the DBA has long been a strong career path. Companies building their sales staff required Peoplesoft or SAP, and those deploying the financial applications or e-business suite needed operations teams to manage those systems.

At the heart of that operations team were database administrators or DBAs, a catchall title that included the responsibility of guarding your business’s crown jewels. Security of those data assets, backups, management, and performance were all entrusted to the DBA.

In the world of web applications, things have evolved a bit differently. Many a startup are driven only by developers. In those smaller shops, Operations tasks are designated to one developer who takes on the additional responsibility of managing systems.

In that scenario, Operations or DBA duties become a sort of secondary role to the primary one of building the application. Even in cases where the startup creates a specific operations role with one person managing systems administration, chances are they don’t also have DBA experience. Instead, these startups are more likely to manage the database as a typical Linux application.

When I Grow Up I (Don’t) Want To Be A MySQL DBA

Where do they come from, and why don’t a lot of computer science folks gravitate towards operations, and DBA? This may be in part due to the romance of certain job roles which we discussed in a past article, The Mythical MySQL DBA.

This pattern appeared a lot in the Oracle world as well. Many folks who were career DBAs actually moved to that role from the business side. In fact, you’d find that many didn’t have a computer science or engineering background in the first place.

In my experience, I saw many Linux and Unix administrators with a stronger foundation who would fit into the DBA role but were simply not interested in it. The same can be said of the MySQL side of the house.

Computer science grads don’t get out of school aiming for a career in ops or as a DBA because it has never been regarded as the pinnacle. It’s typically the PROGRAMMERS who become the rockstars in a cool startup.

But as the Internet grows into a richer and more complex medium, things are changing. People talk about scalability, high availability, zero downtime, and performance tuning. When brief outages cost millions in losses expectations are very high and that requires skilled, experienced DBAs.

We’ve made a list comprised of skill questions, general questions, and ‘good-to-know’ questions. Have fun grilling your candidate with them, although bear in mind that with interviews it’s not about knowing it all, but rather how the person demonstrates critical thinking skills.

Skills Questions

Here are the top 5 MySQL DBA interview questions:

1. Why Are SQL Queries So Fundamental to Database Performance?

This is the one question that a DBA should have an answer to. If they can’t answer this question, they’re unlikely to be a good candidate.

After a MySQL server is set up and running, with many of the switches and dials set to use memory and play well with other services on the Linux server, queries remain an everyday challenge. Why is this?

SQL queries are like little programs in and of themselves. They ask the database server to collect selections of records, cross-tabulate them with other collections of records, then sort them, and slice and dice them. All of this requires MySQL to build temporary tables, perform resource-intensive sorts, and then organize the output in nice bite-size chunks.

Unfortunately, there are many ways to get the syntax and the results right, yet not do so efficiently. This might sound like a moot point, but with modern websites you may have 5000 concurrent users on your site, each hitting pages that have multiple queries inside them.

What makes this an ongoing challenge is that websites are typically a moving target, with business requirements pushing new code changes all the time. New code means new queries, which pose ongoing risks to application stability.

2. Indexes – Too Many, Too Few; What’s The Difference?

Indexes are very important to the smooth functioning of a relational database. Imagine your telephone book of yore. I can look up all the people with the last name of “Hull” in Manhattan because I have the proper index. But most yellow pages don’t include an index for *first* names even though they might occasionally come in handy, for example with the names “Star” or “Persephone”.

You can imagine that, if you had a phone book that you maintain and update, every time you add or remove a name you also have to update the index. That’s right, and the same goes for your relational database.

So therein lies the trade-off, and it’s an important one. When you are *modifying* your data, adding, updating, or removing records, you must do work to keep the index up to date.

More indexes mean more work. However, when you’re looking up data or *querying* in SQL speak, more indexes mean more ways of looking up data fast. One more trade-off is that indexes take up more pages in your phonebook, and so too they take up more space on disk.

3. Backup & Recovery – Explain Various Types & Scenarios For Restore

Backups come in a few different flavors that the DBA should be familiar with.

Cold backups involve the shutdown of the database server (MySQL) and then back up all the data files by making a copy of them to another directory. To be really thorough, the entire datadir including binlogs, log files, /etc/my.cnf config file should also be backed up. The cold backup is a database in itself, and can be copied to an alternate server and mounted as-is.

Logical backups involve using the mysqldump tool. This locks tables while it runs to maintain consistency of changing data, and can cause downtime. The resulting dump file contains CREATE DATABASE, CREATE TABLE & CREATE INDEX statements to rebuild the database.

Note the file itself is not a database, but rather a set of instructions that can tell a MySQL server *HOW* to reconstruct the database. The important distinction here.

Hot backups are a great addition to the mix as they allow the physical database data files to be backed up *WHILE* the server is up and running. In MySQL, this can be achieved with the xtrabackup tool, available from Percona. Despite the name, it works very well with MyISAM and InnoDB tables too, so don’t worry if you’re not using xtradb tables.

There are a few different restore scenarios, and the candidate should be able to describe how these various backups can be restored, and what the steps to do so would be.

In addition, they should understand what point-in-time recovery is, and how to perform that as well. After restoring one of the above three backup types, the DBA would use the mysqlbinlog utility to apply any subsequent transactions from the binary logs.

So, if the backup was made at 2am last night, and you restore that backup, the mysqlbinlog tool would be used to dig up transactions since 2am, and apply them to that restored database.

4. Troubleshooting Performance

Since this is an ongoing challenge with relational databases, a good grasp of it is crucial. One way to challenge the candidate would be to describe a recent performance problem you experienced with your infrastructure and ask them how they would go about resolving it.

If they struggle with the particulars of what you ran into, ask them to describe a big performance challenge they solved, what the cause was, and how they performed the analysis.

Typically, the first steps involve mitigating the immediate problem by finding out what changed in the environment either operationally or code changes. If there is a bug that was hit or other strange performance anomalies, the first stop is usually looking at log files.

MySQL server error logs and the slow query log are key files. From there, analyzing those files during the timeframe where problems occurred should yield some clues.

You might also hope to hear some comments about metrics collection in this discussion. Tools such as cacti, Munin, OpenNMS, or ganglia are invaluable tools for drilling down on a past event or outage and sifting through server stats to find trouble.

5. Joins – Describe A Few Kinds and How the Server Performs Them

A basic understanding of INNER JOIN and OUTER JOIN would be a great start. A simple example might be employees and departments.

If you have four employees and two departments, an INNER JOIN of these tables together will give you the departments employees belong to.

Add another employee without assigning her to a department, and the inner join won’t display her. Further adding a new department that doesn’t yet contain employees won’t display either.

However, performing an OUTER JOIN will give you those matches with null in the department field, and null in the employee field respectively.

Thought of with another example, take a credit card company. One table contains cardholders identity, their number, address, and other personal information. A second table contains their account activity.

When they first join, they don’t have any monthly statements, so an INNER JOIN of cardholders with statements will yield no rows. However, an OUTER JOIN on those two tables will yield a record, with a null for the statements columns.

Continue reading our article Top MySQL DBA interview questions (Part 2) to learn five more questions that test a MySQL DBA’s knowledge.

The post Top MySQL DBA Interview Questions (Part 1) appeared first on iheavy.

Planet MySQL

https://blog.finxter.com/wp-content/uploads/2023/08/pexels-photo-1095601.jpeg

Understanding Timeit in Python

The timeit module is a tool in the Python standard library, designed to measure the execution time of small code snippets. It makes it simple for developers to analyze the performance of their code, allowing them to find areas for optimization.

The timeit module averages out various factors that affect the execution time, such as the system load and fluctuations in CPU performance. By running the code snippet multiple times and calculating an average execution time, it provides a more reliable measure of your code’s performance.

To get started using timeit, simply import the module and use the timeit() method. This method accepts a code snippet as a string and measures its execution time. Optionally, you can also pass the number parameter to specify how many times the code snippet should be executed.

Here’s a quick example:

import timeit

code_snippet = '''
def example_function():
    return sum(range(10))

example_function()
'''

execution_time = timeit.timeit(code_snippet, number=1000)
print(f"Execution time: {execution_time:.6f} seconds")

Sometimes, you might want to evaluate a code snippet that requires additional imports or setup code. For this purpose, the timeit() method accepts a setup parameter where you can provide any necessary preparation code.

For instance, if we adjust the previous example to include a required import:

import timeit

code_snippet = '''
def example_function():
    return sum(range(10))

example_function()
'''

setup_code = "import math"

execution_time = timeit.timeit(code_snippet, setup=setup_code, number=1000)
print(f"Execution time: {execution_time:.6f} seconds")

Keep in mind that timeit is primarily intended for small code snippets and may not be suitable for benchmarking large-scale applications.

Measuring Execution Time

The primary method of measuring execution time with timeit is the timeit() function. This method runs the provided code repeatedly and returns the total time taken. By default, it repeats the code one million times! Be careful when measuring time-consuming code, as it may take a considerable duration.

import timeit

code_to_test = '''
example_function()
'''

elapsed_time = timeit.timeit(code_to_test, number=1000)
print(f'Elapsed time: {elapsed_time} seconds')

When using the timeit() method, the setup time is excluded from execution time. This way, the measurement is more accurate and focuses on the evaluated code’s performance, without including the time taken to configure the testing environment.

Another useful method in the timeit module is repeat(), which calls the timeit() function multiple times and returns a list of results.

results = timeit.repeat(code_to_test, repeat=3, number=1000)
averaged_result = sum(results) / len(results)
print(f'Average elapsed time: {averaged_result} seconds')

Sometimes it’s necessary to compare the execution speeds of different code snippets to identify the most efficient implementation. With the time.time() function, measuring the execution time of multiple code sections is simplified.

import time

start_time = time.time()
first_example_function()
end_time = time.time()

elapsed_time_1 = end_time - start_time

start_time = time.time()
second_example_function()
end_time = time.time()

elapsed_time_2 = end_time - start_time

print(f'First function elapsed time: {elapsed_time_1} seconds')
print(f'Second function elapsed time: {elapsed_time_2} seconds')

In conclusion, using the timeit module and the time.time() function allows you to accurately measure and compare execution times in Python.

The Timeit Module

To start using the timeit module, simply import it:

import timeit

The core method in the timeit module is the timeit() method used to run a specific code snippet a given number of times, returning the total time taken.

For example, suppose we want to measure the time it takes to square a list of numbers using a list comprehension:

import timeit

code_to_test = """
squared_numbers = [x**2 for x in range(10)]
"""

elapsed_time = timeit.timeit(code_to_test, number=1000)
print("Time taken:", elapsed_time)

If you are using Jupyter Notebook, you can take advantage of the %timeit magic function to conveniently measure the execution time of a single line of code:

%timeit squared_numbers = [x**2 for x in range(10)]

In addition to the timeit() method, the timeit module provides repeat() and autorange() methods.

• The repeat() method allows you to run the timeit() method multiple times and returns a list of execution times, while
• the autorange() method automatically determines the number of loops needed for a stable measurement.

Here’s an example using the repeat() method:

import timeit

code_to_test = """
squared_numbers = [x**2 for x in range(10)]
"""

elapsed_times = timeit.repeat(code_to_test, number=1000, repeat=5)
print("Time taken for each run:", elapsed_times)

Using Timeit Function

To measure the execution time of a function, you can use the timeit.timeit() method. This method accepts two main arguments: the stmt and setup. The stmt is a string representing the code snippet that you want to time, while the setup is an optional string that can contain any necessary imports and setup steps. Both default to 'pass' if not provided.

Let’s say you have a function called square() that calculates the square of a given number:

def square(x):
    return x ** 2

To measure the execution time of square() using timeit, you can do the following:

results = timeit.timeit('square(10)', 'from __main__ import square', number=1000)

Here, we’re asking timeit to execute the square(10) function 1000 times and return the total execution time in seconds. You can adjust the number parameter to run the function for a different number of iterations.

Another way to use timeit, especially for testing a callable function, is to use the timeit.Timer class. You can pass the callable function directly as the stmt parameter without the need for a setup string:

timer = timeit.Timer(square, args=(10,))
results = timer.timeit(number=1000)

Now you have your execution time in the results variable, which you can analyze and compare with other functions’ performance.

Examples and Snippets

The simplest way to use timeit.timeit() is by providing a statement as a string, which is the code snippet we want to measure the execution time for.

Here’s an example:

import timeit

code_snippet = "sum(range(100))"
elapsed_time = timeit.timeit(code_snippet, number=1000)
print(f"Execution time: {elapsed_time:.6f} seconds")

In the example above, we measure the time it takes to execute sum(range(100)) 1000 times. The number parameter controls how many repetitions of the code snippet are performed. By default, number=1000000, but you can set it to any value you find suitable.

For more complex code snippets with multiple lines, we can use triple quotes to define a multiline string:

Python Timeit Functions

The timeit module in Python allows you to accurately measure the execution time of small code snippets. It provides two essential functions: timeit.timeit() and timeit.repeat().

The timeit.timeit() function measures the execution time of a given statement. You can pass the stmt argument as a string containing the code snippet you want to time. By default, timeit.timeit() will execute the statement 1,000,000 times and return the average time taken to run it.

However, you can adjust the number parameter to specify a different number of iterations.

For example:

import timeit

code_to_test = "sum(range(100))"

execution_time = timeit.timeit(code_to_test, number=10000)
print(execution_time)

The timeit.repeat() function is a convenient way to call timeit.timeit() multiple times. It returns a list of timings for each repetition, allowing you to analyze the results more thoroughly. You can use the repeat parameter to specify the number of repetitions.

Here’s an example:

import timeit

code_to_test = "sum(range(100))"

execution_times = timeit.repeat(code_to_test, number=10000, repeat=5)
print(execution_times)

In some cases, you might need to include additional setup code to prepare your test environment. You can do this using the setup parameter, which allows you to define the necessary setup code as a string. The execution time of the setup code will not be included in the overall timed execution.

import timeit

my_code = '''
def example_function():
    return sum(range(100))

example_function()
'''

setup_code = "from __main__ import example_function"

result = timeit.timeit(my_code, setup=setup_code, number=1000)
print(result)

Measuring Execution Time of Code Blocks

The timeit module provides a straightforward interface for measuring the execution time of small code snippets. You can use this module to measure the time taken by a particular code block in your program.

Here’s a brief example:

import timeit

def some_function():
    # Your code block here

time_taken = timeit.timeit(some_function, number=1)
print(f"Time taken: {time_taken} seconds")

In this example, the timeit.timeit() function measures the time taken to execute the some_function function. The number parameter specifies the number of times the function will be executed, which is set to 1 in this case.

For more accurate results, you can use the timeit.repeat() function, which measures the time taken by the code block execution for multiple iterations.

Here’s an example:

import timeit

def some_function():
    # Your code block here

repeat_count = 5
time_taken = timeit.repeat(some_function, number=1, repeat=repeat_count)
average_time = sum(time_taken) / repeat_count
print(f"Average time taken: {average_time} seconds")

In this example, the some_function function is executed five times, and the average execution time is calculated.

Besides measuring time for standalone functions, you can also measure the time taken by individual code blocks inside a function. Here’s an example:

import timeit

def some_function():
    # Some code here

    start_time = timeit.default_timer()
    # Code block to be measured
    end_time = timeit.default_timer()

    print(f"Time taken for code block: {end_time - start_time} seconds")

In this example, the timeit.default_timer() function captures the start and end times of the specified code block.

Using Timeit with Jupyter Notebook

Jupyter Notebook provides an excellent environment for running and testing Python code. To measure the execution time of your code snippets in Jupyter Notebook, you can use the %timeit and %%timeit magic commands, which are built into the IPython kernel.

The %timeit command is used to measure the execution time of a single line of code. When using it, simply prefix your line of code with %timeit.

For example:

%timeit sum(range(100))

This command will run the code multiple times and provide you with detailed statistics like the average time and standard deviation.

To measure the execution time of a code block spanning multiple lines, you can use the %%timeit magic command. Place this command at the beginning of a cell in Jupyter Notebook, and it will measure the execution time for the entire cell.

For example:

%%timeit
total = 0
for i in range(100):
    total += i

Managing Garbage Collection and Overhead

When using timeit in Python to measure code execution time, it is essential to be aware of the impact of garbage collection and overhead.

Garbage collection is the process of automatically freeing up memory occupied by objects that are no longer in use. This can potentially impact the accuracy of timeit measurements if left unmanaged.

By default, timeit disables garbage collection to avoid interference with the elapsed time calculations. However, you may want to include garbage collection in your measurements if it is a significant part of your code’s execution, or if you want to minimize the overhead and get more realistic results.

To include garbage collection in timeit executions, you can use the gc.enable() function from the gc module and customize your timeit setup.

Here’s an example:

import timeit
import gc

mysetup = "import gc; gc.enable()"
mycode = """
def my_function():
    # Your code here
    pass
my_function()
"""

elapsed_time = timeit.timeit(setup=mysetup, stmt=mycode, number=1000)
print(elapsed_time)

Keep in mind that including garbage collection will likely increase the measured execution time. Manage this overhead by balancing the need for accurate measurements with the need to see the impact of garbage collection on your code.

Additionally, you can use the timeit.repeat() and timeit.autorange() methods to measure execution time of your code snippets multiple times, which can help you capture the variability introduced by garbage collection and other factors.

Choosing the Best Timer for Performance Measurements

Measuring the execution time of your Python code is essential for optimization, and the timeit module offers multiple ways to achieve this. This section will focus on selecting the best timer for measuring performance.

When using the timeit module, it is crucial to choose the right timer function. Different functions may provide various levels of accuracy and be suitable for different use cases. The two main timer functions are time.process_time() and time.perf_counter().

time.process_time() measures the total CPU time used by your code, excluding any time spent during the sleep or wait state. This is useful for focusing on the computational efficiency of your code. This function is platform-independent and has a higher resolution on some operating systems.

Here is an example code snippet:

import time
import timeit

start = time.process_time()

# Your code here

end = time.process_time()
elapsed = end - start
print(f"Execution time: {elapsed} seconds")

On the other hand, time.perf_counter() measures the total elapsed time, including sleep or wait states. This function provides a more accurate measurement of the total time required by your code to execute. This can help in understanding the real-world performance of your code.

Here’s an example using time.perf_counter():

import time
import timeit

start = time.perf_counter()

# Your code here

end = time.perf_counter()
elapsed = end - start
print(f"Execution time: {elapsed} seconds")

In addition to measuring execution time directly, you can also calculate the time difference using the datetime module. This module provides a more human-readable representation of time data.

Here’s an example code snippet that calculates the time difference using datetime:

from datetime import datetime

start = datetime.now()

# Your code here

end = datetime.now()
elapsed = end - start
print(f"Execution time: {elapsed}")

Frequently Asked Questions

How to measure function execution time using timeit?

To measure the execution time of a function using the timeit module, you can use the timeit.timeit() method. First, import the timeit module, and then create a function you want to measure. You can call the timeit.timeit() method with the function’s code and the number of executions as arguments.

For example:

import timeit

def my_function():
    # Your code here

execution_time = timeit.timeit(my_function, number=1000)
print("Execution time:", execution_time)

What is the proper way to use the timeit module in Python?

The proper way to use the timeit module is by following these steps:

1. Import the timeit module.
2. Define the code or function to be timed.
3. Use the timeit.timeit() method to measure the execution time, and optionally specify the number of times the code should be executed.
4. Print or store the results for further analysis.

How to time Python functions with arguments using timeit?

To time a Python function that takes arguments using timeit, you can use a lambda function or functools.partial(). For example:

import timeit
from functools import partial

def my_function(arg1, arg2):
    # Your code here

# Using a lambda function
time_with_lambda = timeit.timeit(lambda: my_function("arg1", "arg2"), number=1000)

# Using functools.partial()
my_function_partial = partial(my_function, "arg1", "arg2")
time_with_partial = timeit.timeit(my_function_partial, number=1000)

What are the differences between timeit and time modules?

The timeit module is specifically designed for measuring small code snippets’ execution time, while the time module is more generic for working with time-related functions. The timeit module provides more accurate and consistent results for timing code execution, as it disables the garbage collector and uses an internal loop, reducing the impact of external factors.

How to use timeit in a Jupyter Notebook?

In a Jupyter Notebook, use the %%timeit cell magic command to measure the execution time of a code cell:

%%timeit
# Your code here

This will run the code multiple times and provide the average execution time and standard deviation.

What is the best practice for measuring execution time with timeit.repeat()?

The timeit.repeat() method is useful when you want to measure the execution time multiple times and then analyze the results. The best practice is to specify the number of repeats, the number of loops per repeat, and analyze the results to find the fastest, slowest, or average time. For example:

import timeit

def my_function():
    # Your code here

repeat_results = timeit.repeat(my_function, number=1000, repeat=5)
fastest_time = min(repeat_results)
slowest_time = max(repeat_results)
average_time = sum(repeat_results) / len(repeat_results)

Using timeit.repeat() allows you to better understand the function’s performance in different situations and analyze the variability in execution time.

Recommended: How to Determine Script Execution Time in Python?

The post Measure Execution Time with timeit() in Python appeared first on Be on the Right Side of Change.

Be on the Right Side of Change

https://media.notthebee.com/articles/64d7f09f963ac64d7f09f963ad.jpg

So this Twitter account called Trump History uses AI to insert Trump into important historical moments and the results are visually stunning, extremely realistic, and absurdly hilarious. Here are the best ones:

Not the Bee

https://static1.makeuseofimages.com/wordpress/wp-content/uploads/2022/06/Barcode.jpg

 pip install python-barcode
 

 import barcode
from the barcode.writer import ImageWriter
 

 def generate_barcode(data, barcode_format, options=None):
    
    barcode_class = barcode.get_barcode_class(barcode_format)
    
    barcode_image = barcode_class(data, writer=ImageWriter())
    
    barcode_image.save("barcode", options=options)
 

 generate_barcode("MakeUseOf", "code128")
 

 
generate_barcode("MakeUseOf", "code128", options={"foreground":"red", 
                                                  "center_text": False, 
                                                  "module_width":0.4, 
                                                  "module_height":20}) 

 brew install zbar 
sudo apt-get install libzbar0 
pip install pyzbar opencv-python
 

 import cv2
from pyzbar import pyzbar
 

 def scan_barcode_from_image(image_path):
    
    image = cv2.imread(image_path)
    
    barcodes = pyzbar.decode(image)
    
    for barcode in barcodes:
        
        barcode_data = barcode.data.decode("utf-8")
        barcode_type = barcode.type
        print("Barcode Data:", barcode_data)
        print("Barcode Type:", barcode_type)
 

 scan_barcode_from_image("barcode.png")
> Barcode Data: MakeUseOf
> Barcode Type: CODE128
 

 def scan_barcode_from_webcam():
    
    video_capture = cv2.VideoCapture(0)

    while True:
        
        _, frame = video_capture.read()

        
        barcodes = pyzbar.decode(frame)

        
        for barcode in barcodes:
            
            barcode_data = barcode.data.decode("utf-8")
            barcode_type = barcode.type
            print("Barcode Data:", barcode_data)
            print("Barcode Type:", barcode_type)

        
        if cv2.waitKey(1) & 0xFF == ord("q"):
            break

    
    video_capture.release()
    cv2.destroyAllWindows()


scan_barcode_from_webcam()
 

MakeUseOf

https://static1.makeuseofimages.com/wordpress/wp-content/uploads/2023/08/a-wizard-with-a-wand-casting-spell-next-to-harry-potter-books.jpg

MakeUseOf

https://media.notthebee.com/articles/64d3b710c8c6364d3b710c8c64.jpg

Well, here’s your wild police chase story for the day straight from the great state of Tennessee.

Not the Bee

https://ashallendesign.ams3.cdn.digitaloceanspaces.com/public/blog/128/a-guide-to-encryption-and-hashing-in-laravel.png

        
1This is a secret message.    

        
1eyJpdiI6IjF1cmF0YU5TMkRnR3NUMVRMMm1udFE9PSIsInZhbHVlIjoieGloYW5VVWtXV2hjcVRVY3hGTkZ1bDdoOVBSZEo1VkVWNE1LSlB5S0lhNkF3SloxeWhRejNwbjN5SEgxeUJXayIsIm1hYyI6IjljNzY0MTBmMGJlZmRjNzcwMjFiMmFjYmJhNTNkNWVhODkxMTgzYmYwMjA3N2YzMjM1YmVhZWU4NDRiOTYzZWQiLCJ0YWciOiIifQ==    

        
1This is a secret message.    

        
1$2y$10$XY2DMYKvLrMj7yqYwyvK5OSvKTA6HOoPTpe3gVpVP5.Y4kN1nbOLq    

        
1$2y$10$NUgYbLrzxn471GzcIN10wedXEcltcbAasHqU7hCeMFv4aCTl/6bVW    

        
1$2y$10$AvBxO6HCRwYPNPZmeERIEOzLAJP7ZkcjrekdzaRLwY8YX4m9VJiFy    

        
1$2y$10$YQ3lzNx8h0tDgw4K3dzJAOxycZhDhTAnueSugbmoo3NDTuq1OT8KW    

        
1use Illuminate\Support\Facades\Hash;
2 
3$hashedValue = Hash::make('hello');
4 
5// $hashedValue = $2y$10$XY2DMYKvLrMj7yqYwyvK5OSvKTA6HOoPTpe3gVpVP5.Y4kN1nbOLq    

        
 1namespace App\Http\Controllers;
 2 
 3use App\Http\Controllers\Controller;
 4use Illuminate\Http\Request;
 5use Illuminate\Support\Facades\Hash;
 6 
 7final class PasswordController extends Controller
 8{
 9    public function update(Request $request)
10    {
11        // Validate the new password here...
12 
13        $request->user()->fill([
14            'password' => Hash::make($request->newPassword)
15        ])->save();
16    }
17}    

        
1$plainTextPassword = 'hello';
2$hashedPassword = "$2y$10$XY2DMYKvLrMj7yqYwyvK5OSvKTA6HOoPTpe3gVpVP5.Y4kN1nbOLq";
3 
4if (Hash::check($plainTextPassword, $hashedPassword)) {
5    // The passwords match...
6} else {
7    // The passwords do not match...
8}    

        
1return [
2 
3    // ...
4 
5    'driver' => 'bcrypt',
6 
7    // ...
8 
9];    

        
1$hashedValue = Hash::driver('argon')->make('hello');    

        
1$encryptedValue = encrypt('hello');    

        
1eyJpdiI6IitBcjVRanJTN3hTdnV6REdScVZFMFE9PSIsInZhbHVlIjoiZGcycC9pTmNKRjU3RWpmeW1GdFErdz09IiwibWFjIjoiODg2N2U0ZTQ1NDM3YjhhNTFjMjFmNmE4OTA2NDI0NzRhZmI2YTg5NzEwYjdmY2VlMjFhMGZhYzE5MGI2NDA3NCIsInRhZyI6IiJ9    

        
1$encryptedValue = 'eyJpdiI6IitBcjVRanJTN3hTdnV6REdScVZFMFE9PSIsInZhbHVlIjoiZGcycC9pTmNKRjU3RWpmeW1GdFErdz09IiwibWFjIjoiODg2N2U0ZTQ1NDM3YjhhNTFjMjFmNmE4OTA2NDI0NzRhZmI2YTg5NzEwYjdmY2VlMjFhMGZhYzE5MGI2NDA3NCIsInRhZyI6IiJ9';
2 
3$decryptedValue = decrypt($encryptedValue);    

        
1class User extends Model
2{
3    protected $casts = [
4        'my_secret_field' => 'encrypted',
5    ];
6}    

        
1$user->update(['my_secret_field' => 'hello123']);    

        
1eyJpdiI6IjM3MUxuV0lKc2RjSGNYT2dXanhKeXc9PSIsInZhbHVlIjoiNmxPZjUray9ZV21Ba1RnRkFNdHRTZz09IiwibWFjIjoiNTNlNmU0YTY5OGFjZWU2OGJiYzY4OWYzYzExYjMzNTI0MDQ2YTJiM2M4YWZkMjkyMGQxNmQ2MmYwNzQyNGFjYSIsInRhZyI6IiJ9    

        
1$result = $user->my_secret_field;
2 
3// $result is equal to: "hello123"    

        
 1use Illuminate\Encryption\Encrypter;
 2 
 3// Our custom encryption key:
 4$key = 'U2x2QdvosFTtk5nL0ejrKqLFP1tUDtSt';
 5 
 6$encrypter = new Encrypter(
 7    key: $key,
 8    cipher: config('app.cipher'),
 9);
10 
11$encryptedValue = $encrypter->encrypt('hello');    

        
 1declare(strict_types=1);
 2 
 3namespace App\Providers;
 4 
 5use Illuminate\Database\Eloquent\Model;
 6use Illuminate\Encryption\Encrypter;
 7 
 8final class AppServiceProvider extends ServiceProvider
 9{
10    public function boot(): void
11    {
12        $this->defineCustomModelEncrypter();
13    }
14 
15    private function defineCustomModelEncrypter(): void
16    {
17        // Our custom encryption key:
18        $key = 'U2x2QdvosFTtk5nL0ejrKqLFP1tUDtSt';
19 
20        $encrypter = new Encrypter(
21            key: $key,
22            cipher: config('app.cipher'),
23        );
24 
25        Model::encryptUsing($encrypter);
26    }
27 
28    // ...
29}    

        
1INFO  Environment successfully encrypted.
2
3Key ........................ base64:amNvB/EvaX1xU+5R9Z37MeKR8gyeIRxh1Ku0pqNlK1Y
4Cipher ............................................................ AES-256-CBC
5Encrypted file ................................................. .env.encrypted    

        
1php artisan env:decrypt base64:amNvB/EvaX1xU+5R9Z37MeKR8gyeIRxh1Ku0pqNlK1Y    

Laravel News Links

https://www.percona.com/blog/wp-content/uploads/2023/08/MySQL-capacity-planning-150×150.jpg

As businesses grow and develop, the requirements that they have for their data platform grow along with it. As such, one of the more common questions I get from my clients is whether or not their system will be able to endure an anticipated load increase. Or worse yet, sometimes I get questions about regaining normal operations after a traffic increase caused performance destabilization.

As the subject of this blog post suggests, this all comes down to proper capacity planning. Unfortunately, this topic is more of an art than a science, given that there is really no foolproof algorithm or approach that can tell you exactly where you might hit a bottleneck with server performance. But we can discuss common bottlenecks, how to assess them, and have a better understanding as to why proactive monitoring is so important when it comes to responding to traffic growth.

Hardware considerations

The first thing we have to consider here is the resources that the underlying host provides to the database. Let’s take a look at each common resource. In each case, I’ll explain why a 2x increase in traffic doesn’t necessarily mean you’ll have a 2x increase in resource consumption.

Memory

Memory is one of the easier resources to predict and forecast and one of the few places where an algorithm might help you, but for this, we need to know a bit about how MySQL uses memory.

MySQL has two main memory consumers. Global caches like the InnoDB buffer pool and MyISAM key cache and session-level caches like the sort buffer, join buffer, random read buffer, etc.

Global memory caches are static in size as they are defined solely by the configuration of the database itself. What this means is that if you have a buffer pool set to 64Gb, having an increase in traffic isn’t going to make this any bigger or smaller. What changes is how session-level caches are allocated, which may result in larger memory consumption.

A tool that was popular at one time for calculating memory consumption was mysqlcalculator.com. Using this tool, you could enter in your values for your global and session variables and the number of max connections, and it would return the amount of memory that MySQL would consume. In practice, this calculation doesn’t really work, and that’s due to the fact that caches like the sort buffer and join buffer aren’t allocated when a new connection is made; they are only allocated when a query is run and only if MySQL determines that one or more of the session caches will be needed for that query. So idle connections don’t use much memory at all, and active connections may not use much more if they don’t require any of the session-level caches to complete their query.

The way I get around this is to estimate the amount of memory consumed on average by sessions as such…

({Total memory consumed by MySQL} – {sum of all global caches}) / {average number of active sessions}

Keep in mind that even this isn’t going to be super accurate, but at least it gives you an idea of what common session-level memory usage looks like. If you can figure out what the average memory consumption is per active session, then you can forecast what 2x the number of active sessions will consume.

This sounds simple enough, but in reality, there could be more to consider. Does your traffic increase come with updated code changes that change the queries? Do these queries use more caches? Will your increase in traffic mean more data, and if so, will you need to grow your global cache to ensure more data fits into it?

With the points above under consideration, we know that we can generally predict what MySQL will do with memory under a traffic increase, but there may be changes that could be unforeseen that could change the amount of memory that sessions use.

The solution is proactive monitoring using time-lapse metrics monitoring like what you would get with Percona Monitoring and Management (PMM). Keep an eye on your active session graph and your memory consumption graph and see how they relate to one another. Checking this frequently can help you get a better understanding of how session memory allocation changes over time and will give you a better understanding of what you might need as traffic increases.

CPU

When it comes to CPU, there’s obviously a large number of factors that contribute to usage. The most common is the queries that you run against MySQL itself. However, having a 2x increase in traffic may not lead to a 2x increase in CPU as, like memory, it really depends on the queries that are run against the database. In fact, the most common cause of massive CPU increase that I’ve seen isn’t traffic increase; it’s code changes that introduced inefficient revisions to existing queries or new queries. As such, a 0% increase in traffic can result in full CPU saturation.

This is where proactive monitoring comes into play again. Keep an eye on CPU graphs as traffic increases. In addition, you can collect full query profiles on a regular basis and run them through tools like pt-query-digest or look at the Query Analyzer (QAN) in PMM to keep track of query performance, noting where queries may be less performant than they once were, or when new queries have unexpected high load.

Disk space

A 2x increase in traffic doesn’t mean a 2x increase in disk space consumption. It may increase the rate at which disk space is accumulated, but that also depends on how much of the traffic increase is write-focused. If you have a 4x increase in reads and a 1.05X increase in writes, then you don’t need to be overly concerned about disk space consumption rates.

Once again, we look at proactive monitoring to help us. Using time-lapse metrics monitoring, we can monitor overall disk consumption and the rate at which consumption occurs and then predict how much time we have left before we run out of space.

Disk IOPS

The amount of disk IOPS your system uses will be somewhat related to how much of your data can fit into memory. Keep in mind that the disk will still need to be used for background operations as well, including writing to the InnoDB redo log, persisting/checkpointing data changes to table spaces from the redo log, etc. But, for example, if you have a large traffic increase that’s read-dependent and all of the data being read in the buffer pool, you may not see much of an IOPS increase at all.

Guess what we should do in this case? If you said “proactive monitoring,” you get a gold star. Keep an eye out for metrics related to IOPS and disk utilization as traffic increases.

Before we move on to the next section, consider the differences in abnormal between disk space and disk IOPS. When you saturate disk IOPS, your system is going to run slow. If you fill up your disk, your database will start throwing errors and may stop working completely. It’s important to understand the difference so you know how to act based on the situation at hand.

Database engine considerations

While resource utilization/saturation are very common bottlenecks for database performance, there are limitations within the engine itself. Row-locking contention is a good example, and you can keep an eye on row-lock wait time metrics in tools like PMM. But, much like any other software that allows for concurrent session usage, there are mutexes/semaphores in the code that are used to limit the number of sessions that can access shared resources. Information about this can be found in the semaphores section in the output of the “SHOW ENGINE INNODB STATUS” command.

Unfortunately, this is the single hardest bottleneck to predict and is based solely on the use case. I’ve seen systems running 25,000+ queries per second with no issue, and I’ve also seen systems running ~5,000 queries per second that ran into issues with mutex contention.

Keeping an eye on metrics for OS context switching will help with this a little bit, but unfortunately this is a situation where you normally don’t know where the wall is until you run right into it. Adjusting variables like innodb_thread_concurrency can help with this in a pinch, but when you get to this point, you really need to look at query efficiency and horizontal scaling strategies.

Another thing to consider is configurable hard limits like max_connections, where you can limit the upper bound of the number of connections that can connect to MySQL at any given time. Keep in mind that increasing this value can impact memory consumption as more connections will use more memory, so use caution when adjusting upward.

Conclusion

Capacity planning is not something you do once a year or more as part of a general exercise. It’s not something you do when management calls you to let you know a big sale is coming up that will increase the load on the hosts. It’s part of a regular day-to-day activity for anyone that’s operating in a database administrator role.

Proactive monitoring plays a big part in capacity planning. I’m not talking about alert-based monitoring that hits your pager when it’s already too late, but evaluating metrics usage on a regular basis to see what the data platform is doing, how it’s handling its current traffic, etc. In most cases, you don’t see massive increases in traffic all at once; typically, it’s gradual enough that you can monitor as it increases and adjust your system or processes to avoid saturation.

Tools like PMM and the Percona Toolkit play a big role in proactive monitoring and are open source for free usage. So if you don’t have tools like this in place, this comes in at a price point that makes tool integration easier for your consideration.

Also, if you still feel concerned about your current capacity planning, you can always reach out to Percona Managed Services for a performance review or query review that will give you a detailed analysis of the current state of your database along with recommendations to keep it as performant as possible.

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

https://res.cloudinary.com/benjamin-crozat/image/upload/dpr_auto,f_auto,q_auto,w_auto/v1690645860/dhjbi100_uiarfm.png

APP_ENV=production
APP_DEBUG=false

function something(
    #[\SensitiveParameter]
    $top_secret_parameter,
    $who_cares_parameter,
) {
    throw new \Exception('Whoops, something bad happended!');
}

// app/Policies/PostPolicy.php
public function update(User $user, Post $post)
{
    return $user->id === $post->user_id;
}
 
// app/Http/Controllers/PostController.php
public function update(Request $request, Post $post)
{
    $this->authorize('update', $post);
 
    // ...
}

<form method="POST" action="">
    @csrf
 
    <p>
        <label for="name">First Name</label>
        <input type="text" id="name" name="name" value="" required />
    </p>
 
    …
</form>

use Illuminate\Http\Request;
 
class PostController extends Controller
{
    function store(Request $request)
    {
        $validated = $request->validate([
            'user_id' => 'required|exists:users,id',
            'title' => 'required|string|min:3|max:255',
            'content' => 'required|string|min:3',
            'published' => 'sometimes|boolean'
        ]);
 
        Post::create($validated);
 
        //
    }
}

$request->validate([
    'file' => 'required|mimes:gif,jpeg,png,webp',
]);

use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Contracts\Queue\ShouldBeEncrypted;
 
class SomeJob implements ShouldQueue, ShouldBeEncrypted
{
    //
}

Laravel News Links

https://tailwindcraft.com/storage/photos/1/cover.pngMeticulously designed open-source UI components powered by Tailwind CSS. Streamlines web development with versatile prebuilt elements, seamless Tailwind CSS integration, and a vibrant open-source community.Laravel News Links