Category: My SQL
-
About Us
At CLOTA Technology, we provide innovative solutions in database technologies, web development, cloud maintenance, and software support to help businesses achieve their goals. Our services include expert SQL and NoSQL database design, implementation, and maintenance with certified DBAs boasting 15+ years of experience. We specialize in custom website and app creation, cloud services for migrations,…
//
-
Deadlock
Deadlock is a situation where two or more transactions wait for completion of each-other for resources but none of them completes. or Deadlock occur between the transactions when 2 or more translations permanently block each-other in such a ways that each transaction having a lock on the resource where the other tasks are trying to…
//
-
Physical Database Architecture
Database Files SQL Server maps a DB to a set of OS files visible to SQL Server. We can create 32767 files in a DB. First page (Page 0) of every DB file is FHP file header page. Second page (Page 1) of every DB file PFS (page free space) page. PFS uses 1 byte for each…
//
-
Delete vs Truncate
Its a common interview question which people like to ask to know your understanding on RDMS concepts. I too few times encountered the question in interviews. Pleases don’t ask me interview result. Haha ha… DELETE: 1. DELETE is a DML Command. 2. DELETE statement is executed using a row lock, each row in the table…
//
-
Backup and Recovery DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA My SQL PostgreSQL SQL Server
Pages in Database
Boot Page Every DB has only one boot page. Boot page stores DB configuration information. It can find in page # 9 of the first DB data file. Bulk Changed Map (BCM) Page BCM page keep track of the extends which has modified by bulked-logged operations. Log backup thread reads this page and get the extends…
//
-
Phases of Recovery in Database
At the time of SQL instance reboot or start, database goes in the three phases of recovery… 1. Analysis Phase of recovery 2. Redo Phase of recovery 3. Undo Phase of recovery 1. Analysis Phase of recovery This phase does two things… First, construct a Dirty Page Table (DPT) Second, construct an Active Transaction Table. Dirty Page Table consist…
//
-
DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA Monitoring My SQL Performance Tuning PostgreSQL SQL Server
SQL Server Memory Architecture
There are four major components of SQL memory architecture… 1. Memory Broker2. Memory Object3. Memory Clerk4. Memory Node In short, BOCN. (For remember it “Boy Of Cartoon-Network”) 1. Memory Broker Purpose of Memory Broker is to provide a centralized mechanism to distribute/control memory the allocation made by each component in SQL Server. Note: Memory brokers do…
//
-
DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL PostgreSQL SQL Server
Mirroring Witness getting disappear from Mirroring Setup…
Recently we have faced one issue where DB mirroring witness getting disappear from mirroring setup. This is happening once Principal get down or SQL services restarts. In the case, we found DB fail-over is also is not working. Once the Principal server come online the DB start serving role as Principal DB and get in…
//
-
Backup and Recovery DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA My SQL Performance Tuning PostgreSQL SQL Server
Ghost Records
When any record get deleted from DB, SQL Server does not delete the records physically from disk immediately. The deleted records which exists physically in storage called Ghost Records. SQL Server does not use synchronous method to delete the records physically for DML operation because if SQL Server delete the records synchronously, this will going to drastically…
//
-
Export one table from one MySQL instance to other MySQL instance
Syntax Export one table: $ mysqldump -u root -p database_name table_name > dump.txt password ***** Import one table: $ mysqldump -u root -p database_name table_name < dump.txt password ***** Below was the actual request… Please copy MySQL table charge_master from clt-mysqlP_11.b_Rore to clt-mysqlD_61.SMS Step by step implementation… –Connect MySQL on clt-mysqlP_11 Step 1. Take the row count. mysql> select count(*) from…
//
-
InnoDB Architecture
Buffer Pool In MySQL, buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool. For efficiency…
//