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   |

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

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....

Cache Memory

Hey guys, In this blog I am going to explain you about Cache Memory

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.

Cache memory is fast and expensive. There are three general cache levels:

L1 cache, or primary cache, is extremely fast but relatively small, and is usually embedded in the processor chip as CPU cache.

L2 cache, or secondary cache, is often more capacious than L1. L2 cache may be embedded on the CPU, or it can be on a separate chip and have a high-speed alternative system bus connecting the cache and CPU. 

L3 cache, is specialized memory developed to improve the performance of L1 and L2. L1 or L2 can be significantly faster than L3, though L3 is usually double the speed of DRAM. Each core can have dedicated L1 and L2 cache, but they can share an L3 cache. If an L3 cache references an instruction, it is usually elevated to a higher level of cache.

In the past, L1, L2 and L3 caches have been created using combined processor and motherboard components. Recently, the trend has been toward consolidating all three levels of memory caching on the CPU itself. 

Implementing flash or more dynamic RAM (DRAM) on a system won't increase cache memory. This can be confusing since the term’s memory caching (hard disk buffering) and cache memory are often used interchangeably. Memory caching, using DRAM or flash to buffer disk reads, is meant to improve storage I/O by caching data that is frequently referenced in a buffer ahead of slower magnetic disk or tape. Cache memory, on the other hand, provides read buffering for the CPU.  

 

Performance –

Cache memory is important because it improves the efficiency of data retrieval. It stores program instructions and data that are used repeatedly in the operation of programs or information that the CPU is likely to need next. The computer processor can access this information more quickly from the cache than from the main memory. Fast access to these instructions increases the overall speed of the program.

Aside from its main function of improving performance, cache memory is a valuable resource for evaluating a computer's overall performance. Users can do this by looking at cache's hit-to-miss ratio. Cache hits are instances in which the system successfully retrieves data from the cache. A cache miss is when the system looks for the data in the cache, can't find it, and looks somewhere else instead. In some cases, users can improve the hit-miss ratio by adjusting the cache memory block size -- the size of data units stored.  

Cache vs. virtual memory –

A computer's DRAM is limited, and its cache memory is much smaller. Memory can be utilized when a large software or multiple apps are executing. Operating system constructs Virtual Memory to compensate for a lack of actual memory.

The OS does this by transferring inactive data from DRAM to disc storage. This method expands virtual address space by forming contiguous addresses that hold both a program and its data utilizing active memory in DRAM and inactive memory in HDDs. 

Virtual memory allows a computer to run larger programs or many programs at the same time, with each program acting as though it had unlimited memory.

The OS splits memory into page files or swap files that contain a specific number of addresses in order to convert virtual memory into physical memory. Those pages are kept on a disc, and when they're needed, the OS copies them to main memory and converts the virtual memory address to a physical location. A memory management unit is in charge of these translations (MMU).

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

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.

 

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.

MySQL join_buffer_size, sort_buffer_size, read_buffer_size and read_rnd_buffer_size

join_buffer_size, 

sort_buffer_size, 

read_buffer_size 

read_rnd_buffer_size

These four buffers are allocated per connection. So think twice before alerting.

For example, if you set join buffer size=2 M and max connections=800, MySQL will allocate an extra 2 M per connection (2 M x 800). The same is true for the remaining three buffers. Again, everything is per-connection.

In almost all circumstances, keeping the defaults by eliminating or commenting out these four config lines is the best option. As the number of connections grows, queries that require more space than is available owing to increasing buffer settings may be pushed to disc. This causes a bottleneck in your database server and significantly slows it down.

join_buffer_size

For each entire join between two tables, the join buffer size is allocated. "The minimum size of the buffer that is utilised for simple index scans, range index scans, and joins that do not use indexes and hence execute complete table scans," according to MySQL's documentation.

"Memory allocation time might cause severe performance decreases if the global size is larger than needed by most queries that use it," MySQL's literature continues. When a join can't use an index, the join buffer is used to cache table rows.

If your database suffers from many joins performed without indexes, it cannot be solved by increasing join_buffer_size. The problem is “joins performed without indexes.” Thus, the solution for faster joins is to add indexes. 

If we give join_buffer_size = 64MB its will be crazy. Its mean is 64MB of memory allocated to each new thread. Oh!  

sort_buffer_size

Unless you have data indicating otherwise, you should avoid arbitrarily increasing the sort_buffer_size as well. Memory here is also assigned per connection! MySQL’s documentation warns: “On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values.” Avoid increasing sort_buffer_size above 2M since there is a performance penalty that will eliminate benefits.

read_buffer_size

read_buffer_size Applies generally to MyISAM only and does not affect InnoDB. Consider converting your MySQL tables to InnoDB storage engine before increasing this buffer. InnoDB is the default storage engine of MySQL 5.7 and MySQL 8.0. InnoDB features rollback and crash-recovery capabilities to protect data.

read_rnd_buffer_size

read_rnd_buffer_size variable is also used mainly for MyISAM reads from tables. Also, consider InnoDB or MariaDB’s Aria storage engines. For the past decade, the default values of these buffers have remained the same.

key_buffer_size

The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).

innodb_sort_buffer_size

innodb_sort_buffer_size specifies the size of sort buffers used for sorting data during creation of an InnoDB index.

While sort_buffer_size is just a per-connection variable and do not belongs to any specific storage engine.

MySQL Uptime

 MySQL Uptime 

SELECT

  VARIABLE_VALUE AS Uptime_seconds,

  NOW() AS "Now",

  NOW() - INTERVAL VARIABLE_VALUE SECOND AS "Up since",

  DATEDIFF(NOW(), NOW() - INTERVAL VARIABLE_VALUE SECOND) AS "Uptime_days"

FROM performance_schema.session_status

WHERE VARIABLE_NAME = 'Uptime';

Output Example:

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

| Uptime_seconds | Now                 | Up since                   | Uptime_days |

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

| 16430228       | 2021-04-09 14:11:58 | 2020-10-01 10:14:50.000000 |         190 |

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

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;