Shulou(Shulou.com)05/31 Report--

This article introduces you how to parse mysql and oracle architecture, the content is very detailed, interested partners can refer to, I hope to help you.

As an oracle learning engineer, now start learning mysql.

MySQL database and oracle compared to an open source database, from a learning point of view. It's not like oracle's education system. The documentation is comprehensive.

The best way to learn oracle is to read oracle's official documentation. My English level is limited. So I looked at high performance mysql and profound mysql

I read two books, both good. All the information is about how to do your job. There is still a little bit of architecture.

I personally think that learning database, we must first understand the database architecture and sql execution process. After a lot of inquiries. I feel like I understand.

First of all, oracle architecture, oracle architecture diagram officially has. There is a lot of talk about this on the internet too. If you don't understand, look it up. oracle has a lot of information on the market.

Tip: only innodb comparison, the rest of the storage engine is in

1, database (database):| - Physical storage: (OS files):| - datafile data file (where real data is stored) business data, system data, temporary data (order by.) fallback data

| |-countralfile 控制文件（存储的是数据库的结构信息）

| |-logfile 日志文件（存储的是改变的数据）做恢复用

|- Logical storage:| - table space: where the data dictionary is stored

|- Segments (disks/io, where various types of data are stored) cannot span tablespaces

|- Regions (which allocate space for segments) cannot span segments

|- Block (the smallest unit is block) cannot span regions

2, example (instance):| -sga: memory cpu: shard pool shared pool: information about recently used sql statements (programs) and recently used data dictionaries

| buffer cache Database buffer: recently used data, size determined by db_cache_size

| redo log buffer: log buffer of the most recently changed data. write sequentially, cyclically

| java pool

| large pool

|- Background processes:| - Database write process dbwr: write data from database buffer to data file

|- Log writing process lgwr: write data from log buffer to log file

|Checkpoint progress ckpt: 1, flag for database synchronization. The three core files must be guaranteed to be at the same checkpoint before the database can rise, first written in the log file and written in the head of the data file.

| 2. Trigger the database write process to write the data from the database buffer to the data file.

|- System monitoring process smon: 1, course recovery

| 2. Free up space for temporary segments

| 3. Merging adjacent space debris

|- Program Monitor Process pmon: frees resources (both normal and abnormal)

| MMON monitors the process to access statistics at specified times, so that it knows if the table space usage is up to a warning or no boundary

Let's look at the structure diagram of mysql

For mysql it is thread mode. So the corresponding oracle process above

master thread is mainly responsible for flushing dirty cache pages to data files, performing purge operations, triggering checkpoints, merging insert buffers

insert buffer thread is responsible for the merge operation of the insert buffer

read thread is responsible for database reading operations, multiple read threads can be configured

write theead is responsible for database read and write operations, multiple write threads can be configured

log theead Redo logs for the database are flushed to logfile

purge theead mysql5.5 after a separate purge theead performs the purge operation

After a transaction is committed, the undolog it uses may no longer be needed, so PurgeThread is needed to reclaim the undo pages that have been used and allocated. Prior to InnoDB version 1.1, the purge operation was done only in the Master Thread of the InnoDB storage engine. Starting with InnoDB version 1.1, purge operations can be performed independently in a separate thread to relieve Master Thread of work, thereby increasing CPU usage and improving storage engine performance. Users can enable a standalone Purge Thread by adding the following command to the MySQL database configuration file:

Look thread is responsible for lock control and deadlock detection

Error detection thread: mainly responsible for error monitoring and error handling

write theead master thread is equivalent to dbwr

dbwr: Write data from the database buffer to a data file

master thread is mainly responsible for flushing dirty cache pages to data files, performing purge operations, triggering checkpoints, merging insert buffers

write theead is responsible for database read and write operations, multiple write threads can be configured

Insert buffer thread is similar to SMON

System monitoring process smon:

1. Course recovery

2. Free up space for temporary segments

3. Merging adjacent space debris

insert buffer thread is responsible for merge operations of insert buffers

log theead

Log writing process lgwr: write data from log buffer to log file

log theead Redo logs for the database are flushed to logfile

The above is basically the structure of the process thread in mysql and oracle instace I summarized

Now analyze the memory structure of mysql

shared pool

shard pool: information about the most recently used sql statements (programs) and the most recently used data dictionary

QueryCache(hereinafter referred to as QC) is based on SQL statements to cache. If a SQL query starts with select, MySQL Server will try to use QC on it. Each Cache is stored with SQL text as a key. SQL text is not processed until QC is applied

mysql QC is very simple ah as long as the data will be changed to clear the QC data. It turns out I don't understand much. I understand now. However, in-depth analysis did not see

buffer cache and innodb buffer cache

Where data is stored. MySQL handles this problem at the innodb layer.

buffer cache database buffer: recently used data, size determined by db_cache_size redo log buffer and innodb redo log buffer

When writing to the database, first write redo log buffer to memory, refresh to hard disk, and write sequentially. Finally, refresh to datafile by writing process

Guaranteed IO written.

To sum up: the difference between mysql and mysql innodb is basically the same as the above points. MySQL is a lot simpler.

Learning mysql is a way to better understand databases.

About how to parse mysql and oracle architecture to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.