Showing posts with label MySQL Architecture. Show all posts
Showing posts with label MySQL Architecture. Show all posts

Feb 15, 2022

Enable Data Load Local InFile

We were getting below error at the time of loading data into MySQL. 

Error: Programming Error: 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides.


Solution: 

Check the status of local_infile variable in MySQL...

> show variables like '%local%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| local_infile  | OFF    |

+---------------+-------+

In our care it was off. We solved this problem with the MySQL terminal command...

> SET GLOBAL local_infile = true;


Check the status now..

> show variables like '%local%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| local_infile  | ON   |

+---------------+-------+

Feb 10, 2022

Slow query log purge in MySQL

There are manly 3 steps...

1. Rename existing log file with _old.

2. Connect mysqladmin using socket and use  flush-logs.

3. Delete _old file created in first step.

 

Get the show query log location:

mysql> show variables like slow_query_log %';

Get the socket file location:

mysql> show variables like 'socket%';

In my case I got the info like below…

/u01/app/logs/DB_instance/slow-query-DB_instance.log

/var/run/DB_Instance/DB_instance.sock

Now go to the Log location and renaming slow query file…

mv slow-query-DB_instance.log slow-query- DB_instance.log_old 

 

Flush-log using mysqladmin

/u01/app/mysql80/bin/mysqladmin  flush-logs --socket=/var/run/DB_Instance/ DB_Instance.sock

You can check show query log file. There will be a new log file.

Now delete old log file

rm slow-query-DB_Instance.log_old

 

All set....

Dec 27, 2021

CPU Components

CPU consists of 6 main components:

1. CU Control unit 

2. ALU Arithmetic logic unit 

3. Registers

4. Cache

5. Buses

6. Clock

CPU

All the components work together to allow processing and system control.

Control Unit (CU)

It fetches, decodes, and executes instructions. It issues control signals that control hardware. It moves data around the system.

Arithmetic Logic Unit (ALU)

The ALU has two main functions:

1. It performs arithmetic and logical operations (decisions). The ALU is where calculations are done and where decisions are made.

2. It acts as a gateway between primary memory and secondary storage. Data transferred between them passes through the ALU.


Registers

Registers are small amounts of high-speed memory contained within the CPU. They are used by the processor to store small amounts of data that are needed during processing, such as:

1. The address of the next instruction to be executed

2. The current instruction being decoded

3. The results of calculations

Different processors have different numbers of registers for different purposes, but most have some, or all, of the following:

Program counter Memory address register (MAR) Memory data register (MDR) Current instruction register (CIR) Accumulator (ACC)

Cache

Cache is a small amount of high-speed random-access memory (RAM) built directly within the processor. It is used to temporarily hold data and instructions that the processor is likely to reuse. This allows for faster processing as the processor does not have to wait for the data and instructions to be fetched from the RAM.

Buses

A bus is a high-speed internal connection. Buses are used to send control signals and data between the processor and other components.

Three types of bus are used:

Address bus - carries memory addresses from the processor to other components such as primary memory and input/output devices. It’s a one-way pathway that allows information to pass in one direction only, carries information about where data is stored in memory.

Data bus - carries the actual data between the processor and other components.

Control bus - carries control signals from the processor to other components. The control bus also carries the clock's pulses.

Clock

The CPU contains a clock which is used to coordinate all the computer's component. The clock sends out a regular electrical pulse which synchronizes (keeps in time) all the components.

The frequency of the pulses is known as the clock speed. Clock speed is measured in hertz. The higher the frequency, the more instructions can be performed in any given moment of time.

 

Dec 20, 2021

innodb_change_buffer_max_size

With MySQL 5.6.2, you can set the maximum size of the change buffer as a percentage of the total size of the buffer pool using the innodb change buffer max size configuration option. The default value for innodb change buffer max size is 25. The maximum number of characters is 50.

On a MySQL server with a lot of insert, update, and delete activity, you might want to increase innodb change buffer max size so that change buffer merging keeps up with new change buffer entries and the change buffer doesn't hit its maximum size limit.

If the change buffer occupies too much of the shared memory space with the buffer pool, leading pages to age out of the buffer pool sooner than desired, you might want to reduce innodb change buffer max size on a MySQL server with static data used for reporting.

To get the best configuration, try different settings with a representative workload. The innodb change buffer max size setting is dynamic, so you can change it without having to restart the server.

Set to 25 to use up to 25% of the buffer pool to buffer changes. The default is 25, which is a reasonable quantity; if you want to increase it, simply increase the amount of RAM and the buffer pool size.


Oct 27, 2021

SHOW PROCESSLIST Statement

 The MySQL process list indicates the operations currently being performed by the set of threads executing within the server. The SHOW PROCESSLIST statement is one source of process information. For a comparison of this statement with other sources, see Sources of Process Information.


If you have the PROCESS privilege, you can see all threads, even those belonging to other users. Otherwise (without the PROCESS privilege), nonanonymous users have access to information about their own threads but not threads for other users, and anonymous users have no access to thread information.

Without the FULL keyword, SHOW PROCESSLIST displays only the first 100 characters of each statement in the Info field.

The SHOW PROCESSLIST statement is very useful if you get the too many connections error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege), to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users).

Threads can be killed with the KILL statement. See Section 13.7.8.4, “KILL Statement”.

Example of SHOW PROCESSLIST output:

mysql> SHOW FULL PROCESSLIST\G *************************** 1. row *************************** Id: 1 User: system user Host: db: NULL Command: Connect Time: 1030455 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 1004 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL *************************** 3. row *************************** Id: 3112 User: replikator Host: artemis:2204 db: NULL Command: Binlog Dump Time: 2144 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 4. row *************************** Id: 3113 User: replikator Host: iconnect2:45781 db: NULL Command: Binlog Dump Time: 2086 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 5. row *************************** Id: 3123 User: stefan Host: localhost db: apollon Command: Query Time: 0 State: NULL Info: SHOW FULL PROCESSLIST



Show ProcessList fetches the information from INFORMATION_SCHEMA.PROCESSLIST table. 

select * from INFORMATION_SCHEMA.PROCESSLIST;

MySQL Full Versus Point-in-Time (Incremental) Recovery

 A full recovery restores all data from a full backup. This restores the server instance to the state that it had when the backup was made. If that state is not sufficiently current, a full recovery can be followed by recovery of incremental backups made since the full backup, to bring the server to a more up-to-date state.

Incremental recovery is recovery of changes made during a given time span. This is also called point-in-time recovery because it makes a server's state current up to a given time. Point-in-time recovery is based on the binary log and typically follows a full recovery from the backup files that restores the server to its state when the backup was made. Then the data changes written in the binary log files are applied as incremental recovery to redo data modifications and bring the server up to the desired point in time.

MySQL Full Vs Incremental Backups

 A full backup includes all data managed by a MySQL server at a given point in time. An incremental backup consists of the changes made to the data during a given time span (from one point in time to another). MySQL has different ways to perform full backups, such as those described earlier in this section. Incremental backups are made possible by enabling the server's binary log, which the server uses to record data changes.

MySQL Physical (Raw) Vs Logical Backups

 Physical backups consist of raw copies of the directories and files that store database contents. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur.

Logical backups save information represented as logical database structure (CREATE DATABASECREATE TABLE statements) and content (INSERT statements or delimited-text files). This type of backup is suitable for smaller amounts of data where you might edit the data values or table structure, or recreate the data on a different machine architecture.

Physical backup methods have these characteristics:

  • The backup consists of exact copies of database directories and files. Typically this is a copy of all or part of the MySQL data directory.

  • Physical backup methods are faster than logical because they involve only file copying without conversion.

  • Output is more compact than for logical backup.

  • Because backup speed and compactness are important for busy, important databases, the MySQL Enterprise Backup product performs physical backups. For an overview of the MySQL Enterprise Backup product, see MySQL Enterprise Backup Overview.

  • Backup and restore granularity ranges from the level of the entire data directory down to the level of individual files. This may or may not provide for table-level granularity, depending on storage engine. For example, InnoDB tables can each be in a separate file, or share file storage with other InnoDB tables; each MyISAM table corresponds uniquely to a set of files.

  • In addition to databases, the backup can include any related files such as log or configuration files.

  • Data from MEMORY tables is tricky to back up this way because their contents are not stored on disk. (The MySQL Enterprise Backup product has a feature where you can retrieve data from MEMORY tables during a backup.)

  • Backups are portable only to other machines that have identical or similar hardware characteristics.

  • Backups can be performed while the MySQL server is not running. If the server is running, it is necessary to perform appropriate locking so that the server does not change database contents during the backup. MySQL Enterprise Backup does this locking automatically for tables that require it.

  • Physical backup tools include the mysqlbackup of MySQL Enterprise Backup for InnoDB or any other tables, or file system-level commands (such as cpscptarrsync) for MyISAM tables.

  • For restore:

    • MySQL Enterprise Backup restores InnoDB and other tables that it backed up.

    • ndb_restore restores NDB tables.

    • Files copied at the file system level can be copied back to their original locations with file system commands.

Logical backup methods have these characteristics:

  • The backup is done by querying the MySQL server to obtain database structure and content information.

  • Backup is slower than physical methods because the server must access database information and convert it to logical format. If the output is written on the client side, the server must also send it to the backup program.

  • Output is larger than for physical backup, particularly when saved in text format.

  • Backup and restore granularity is available at the server level (all databases), database level (all tables in a particular database), or table level. This is true regardless of storage engine.

  • The backup does not include log or configuration files, or other database-related files that are not part of databases.

  • Backups stored in logical format are machine independent and highly portable.

  • Logical backups are performed with the MySQL server running. The server is not taken offline.

  • Logical backup tools include the mysqldump program and the SELECT ... INTO OUTFILE statement. These work for any storage engine, even MEMORY.

  • To restore logical backups, SQL-format dump files can be processed using the mysql client. To load delimited-text files, use the LOAD DATA statement or the mysqlimport client.