Analyse slow-query-log using mysqldump

Analyzing the slow-query-log using tools like mysqldumpslow, pt-query-digest, and mysqlsla can provide valuable insights into the performance of your MySQL database. Here's a step-by-step guide on how to use these tools:

1. Enable slow-query-log:

• Uncomment the following lines in /etc/mysql/my.cnf:

slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

• Adjust long_query_time based on your server's performance and the expected execution time of queries.

2. Collect slow query logs:

• Let the server run with the slow query log enabled for some time to collect data.

3. Analyze slow query logs:

Using mysqldumpslow:

• Command to show top 5 queries returning maximum rows:
  

  mysqldumpslow -a -s r -t 5 /var/log/mysql/mysql-slow.log

• Command to sort output by count (frequency of occurrence) of queries:
  

  mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log

Using pt-query-digest:

• Basic command to analyze slow query logs:
  

  pt-query-digest /var/log/mysql/mysql-slow.log

• Filter queries for a specific database:
  

  pt-query-digest /var/log/mysql/mysql-slow.log --filter '$event->{db} eq "db_wordpress"'

Using mysqlsla:

• Basic command to analyze slow query logs

./mysqlsla /var/log/mysql/mysql-slow.log

• Filter queries for a specific database:

  
  ./mysqlsla /var/log/mysql/mysql-slow.log -mf "db=db_name"

4. Interpretation and Action:

• Review the output of the analysis tools to identify slow queries, their frequency, and potential performance bottlenecks.
• Investigate queries that are returning a large number of rows or have a high frequency of occurrence.
• Optimize slow queries by adding indexes, rewriting queries, or optimizing database schema.
• Monitor the performance after optimizations and fine-tune as necessary.

By following these steps and using the provided tools, you can effectively analyze and optimize the performance of your MySQL database, especially in scenarios like debugging WordPress plugins or identifying performance issues in large-scale applications.

Inode space issue , finding largest inode entry direcotry

To find the directory with the largest inode entry, you can use the following command:

For example:

sudo find / -xdev -printf '%h\n' | sort | uniq -c | sort -nr | head

 Let's break down this command:

• sudo: Run the command with root privileges to access all directories.
• find / -xdev -printf '%h\n': Find all files and directories starting from the root directory (/) while excluding other mounted filesystems (-xdev). Print only the directory portion of each file found (%h) followed by a newline (\n).
• sort: Sort the output alphabetically (directories will be grouped together).
• uniq -c: Count the occurrences of each unique directory.
• sort -nr: Sort the counts numerically in reverse order (largest counts first).
• head: Display the first few lines of output, which will show the directories with the largest number of files.

This command will help you identify the directory with the most files, which could be contributing to the inode space issue. Once you find the problematic directory, you can further investigate and take appropriate actions to manage the inode usage.

 

Understanding the Boot Process in Redhat Linux & CentOS 5&6

The boot process in Redhat Linux and CentOS involves several stages, each responsible for initializing different components of the system. Here's a breakdown of the boot process:

 

1. BIOS (Basic Input/Output System):

   • Performs system integrity checks.
   • Searches, loads, and executes the boot loader program.
   • Detects and loads the MBR (Master Boot Record) boot loader.

2. MBR (Master Boot Record):

   • Located in the 1st sector of the bootable disk (/dev/hda or /dev/sda).
   • Contains information about the GRUB (Grand Unified Bootloader) boot loader.

3. GRUB (Grand Unified Bootloader):

   • Displays a splash screen and waits for user input.
   • Loads the default kernel image specified in the grub configuration file (/boot/grub/grub.conf).
   • Has knowledge of the filesystem.
   • Loads and executes the kernel and initrd images.

4. Init:

   • Reads the /etc/inittab file to determine the default run level.
   • Defines the available run levels (0-6) and their corresponding modes (halt, single user, multiuser, etc.).
   • Sets the default run level based on the configuration.
   • Loads all appropriate programs based on the default run level.

5. Runlevel Programs:

   • Services and programs started during bootup.
   • Located in directories corresponding to each run level (/etc/rc.d/rc*.d/).
   • Symbolic links are available under /etc/rc*.d/ for convenience.
   • Programs starting with 'S' are executed during startup, while those starting with 'K' are executed during shutdown.
   • The sequence number in the program names determines the order of execution.

   For example:

   • S12syslog starts the syslog daemon with a sequence number of 12.
   • S80sendmail starts the sendmail daemon with a sequence number of 80.
   • Programs with lower sequence numbers are executed before those with higher numbers.

 

How to Move MySQL Datadir to an alternate location

In this guide, we'll walk through the process of changing the MySQL data directory to a new location to free up space on the /var partition. This is particularly useful when the /var partition is running low on space, causing MySQL to fail or lose data. Follow these steps carefully to ensure a smooth transition.

Step 1: Backup the MySQL Databases

First, it's crucial to create a backup of all MySQL databases to ensure data security. Execute the following command to dump all databases into a single file and compress it:

mysqldump --add-drop-table --all-databases | gzip > /home/alldatabases.sql.gz

Step 2: Stop the MySQL Server

Before moving the data directory, stop the MySQL server using the following command:

/etc/init.d/mysql stop

Step 3: Move the Data Directory

Navigate to the current MySQL data directory (/var/lib) and move it to the new location (/home/mysql) using the following commands:

cd /var/lib mv mysql /home/mysql

Step 4: Create Symbolic Link

Create a symbolic link from the original data directory location to the new location to ensure compatibility with MySQL configurations:

ln -s /home/mysql mysql

Step 5: Set Ownership

Ensure that the MySQL user owns the new data directory by executing the following command:

chown -R mysql:mysql /home/mysql

Step 6: Start MySQL Server

Start the MySQL server using the following command:

/etc/init.d/mysql start

Step 7: Verify MySQL Status

Check the status of the MySQL server to ensure it's running without any issues:

ps aux | grep mysql

Conclusion

By following these steps, you've successfully moved the MySQL data directory from the /var partition to a new location (/home/mysql), thereby freeing up space on the /var partition. This ensures smooth operation of MySQL and prevents data loss or service failures due to insufficient disk space. Always remember to back up your databases before making significant changes to ensure data integrity. If you encounter any issues, feel free to seek assistance from a qualified administrator.

exim dead but subsys locked

Exim, the widely-used mail transfer agent (MTA), can sometimes cause issues on servers, especially when it needs to be temporarily disabled or restarted. One common error encountered is "exim dead but subsys locked." Here's a simple guide on how to disable Exim and resolve this error quickly.

1. Stop the Exim Service

To stop the Exim service, use one of the following commands based on your system:

/etc/init.d/exim stop

or

service exim stop

2. Create an Empty File

Next, create an empty file named "eximdisable" under the "/etc" directory using the following command:

touch /etc/eximdisable

Now, when you attempt to restart or start the Exim service, you'll encounter the following error:

/etc/init.d/exim status exim dead but subsys locked

This indicates that the Exim service remains stopped, and "chkservd" cannot start it.

3. Fix the Error

To resolve the "exim dead but subsys locked" error, simply remove the "eximdisable" file using the following command:

rm -rf /etc/eximdisable

Once the file is removed, the Exim service can be started or restarted without encountering the error.

Conclusion

Disabling Exim temporarily and fixing the "exim dead but subsys locked" error is a straightforward process. By following these steps, you can manage Exim effectively on your server and troubleshoot common issues with ease. If you encounter any further difficulties, feel free to reach out for assistance, and we'll be here to help you promptly.