Author: Abhishek Yadav
-
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…
//
-
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…
//
-
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…
//
-
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…
//
-
PG Basic Commands
========================================= –DB size select pg_database_size(‘databaseName’); /* ?column? | pg_database_size ———-+—————— 48104 | 7840623 */ postgres=# \l+ /* List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description —————-+—————+———-+————-+————-+———————–+———+————+——————————————– MyDB_Name | MyDB_Name user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 47 MB | pg_default | MyDB_Name tmpdb | MyDB_Name tmp | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 17 MB | pg_default | */ =========================================…
//
-
PG Backup Dump
Types of DB backup · Logical BACKUP <pg_dump> · Physical Backup PG SQL Dump Three tasks we are going to do… Restoring a backup Performing a backup Scheduling a backup job to run daily at 1am Create acweb Database createdb acweb Download TestDB from gitHub wget https://github.com/linuxacademy/content-postgresql-deepdive/raw/master/acweb/acweb.tar Restore the downloaded…
//
-
pg_switch_wal()
pg_switch_wal() is a system function which forces PostgreSQL to switch to a new WAL file. pg_switch_wal() returns the end LSN + 1 of the old WAL file. However, if there has been no activity which generates WAL since the last WAL file switch, a switch will not be carried out and the start location of the current…
//
-
pg_restore
pg_restore restores a PostgreSQL database from an archive file created by pg_dump. pg_restore [connection-option…] [option…] [filename] Example: We have dumped a database called mydb into a custom-format dump file: pg_dump -Fc mydb > db.dump To drop the database and recreate it from the dump:…
//
-
Database Transaction Log Full or Unable to write or Recovery Units belonging to database failed to generate a checkpoint
Some time working as a DBA or support engineer you might have faced this issue that DB log file full or recovery unit failed to generate checkpoint. We might get error in SQL error log like below… Error: 9002, Severity: 17, State: 2. The transaction log for database ‘DB_Name’ is full due to ‘LOG_BACKUP’. Could…
//
-
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…
//
-
Table Space addition in Oracle Database
Default location for Oracle-managed datafiles: show parameter db_create_file_dest; Displays Space Usage for Each Datafile Set lines 999 pages 100 select a.tablespace_name, trunc(SUM(a.bytes)/1024/1024/1024,2) “CurGb”, trunc(SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*8192/1024/1024/1024)),2) “MaxGb”, trunc((SUM(a.bytes)/1024/1024/1024 – round(c.”Free”/1024/1024/1024)),2) “TotalUsedGb”, trunc((SUM(decode(b.maxextend, null, A.BYTES/1024/1024/1024, b.maxextend*8192/1024/1024/1024)) – (SUM(a.bytes)/1024/1024/1024 – round(c.”Free”/1024/1024/1024))),2) “TotalFreeGb”, round(100*(SUM(a.bytes)/1024/1024 – round(c.”Free”/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) “UPercent” from dba_data_files a, sys.filext$ b, (SELECT d.tablespace_name…
//
-
Restore Point Creation in Oracle
Connect to the DB > sqlplus / as sysdba > show pdbs Connect to the PDB > alter session set container=pdb_name; > create restore point RP_Name guarantee flashback database; Check RP creation status select NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;
//