33 Query large amount of data , Will the memory burst
If the host memory is only 100g, Now it's time for one 200g Do a full scan of your big watch , Will the database host run out of memory ?
Full table scan pair server The influence of layer
hypothesis , Now it's time for one 200g Of innodb surface db1.t33 Perform full table scan , of course , All scan results should be saved in the client , I'll use commands like this ：
mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t33" > $target_file
Innodb The data of is saved on the primary key index , So a full table scan is actually a direct table scan t33 Primary key index of , Because this statement has no other conditional judgment , Therefore, each row of the query can be directly placed in the result set , And then back to the client .
actually , The server does not need to save a complete result set , The process of getting and sending data is like this ：
--1 Get a line , write to net_buffer in , The size of this memory is determined by the parameter net_buffer_length, default 16k
--2 Get rows repeatedly , until net_buffer Write full , Call network interface to send out
--3 If successful , Just empty it net_buffer, Then proceed to the next line , And write it net_buffer
--4 If the send function returns eagain perhaps wsaewouldblcok, It means local network stack （socker send
buffer） It's full , Enter the waiting list , Until the network stack is writable again , Send again
Corresponding process
From this process , You can see that ：
--1 A query is in the process of sending , Occupied mysql The internal memory is the largest net_buffer_length So big , It's not going to be 200g
--2 socket send buffer It can't be achieved 200g（ Default definition /proc/sys/net/core/wmem_default）, If socket
send buffer It's full , The process of reading data will be suspended
in other words ,mysql yes ” Read and send ”, That means , If the client receives slowly , Will lead to mysql The server couldn't send the result , This transaction takes longer to execute .
For example, the following state , Deliberately let the client not read socket receive buffer Content in , And then on the server side show processlist See the results
If you see state The value of has always been ”sending to client”, It means that the network stack on the server side is full .
It was mentioned in the last article , If the client uses parameters -quick, Can use mysql_use_result method , This method is to read one line and take one line ,
For normal online business , If a query does not return many results , All recommended mysql_store_result This interface , Directly save the query results to the local memory .
Of course, the premise is that the query does not return many results , If there is a big query, the memory consumption of the client is very high , This situation needs to be changed mysql_use_result Interface
on the other hand , If you are in charge of maintenance mysql You can see that many threads are in the ”sending to
client” This state , It means to let the business development students optimize the query results , Is it reasonable to evaluate so many returned results .
Another state ”sending data”, actually , The state of a query statement changes like this ：
--mysql After the query statement enters the execution phase , First, set the state to ”sending data”
-- then , Send information about the column of the execution result (meta data) To client
-- Then continue the flow of the statement
-- After execution , Set the status to an empty string
in other words ,”sending data” Not necessarily ” Sending data ”, It may be at any stage of the actuator process , such as ,
SESSION A
SESSION B
begin;
select * from t where id=1 for update;
select * from t lock in share mode;
(blocked)
You can see that ,session B Obviously waiting for the lock , The status is displayed as sending data
in other words , Only if a thread is in ” Waiting for the client to receive the result ” The state of , Will be displayed ”sending to client”, And if it is displayed as ”sending
data”, What it means is that ” In progress ”.
Now I know , The result of the query is sent to the client in sections , So scan the whole table , Query returns a large amount of data , It doesn't blow up memory .
Full table scan pair innodb The impact of
In the introduction WAL When it comes to mechanisms , Analyzed innodb A function of memory , Is to save the result of the update , In coordination redo log, The random disk writing is avoided .
The data page of memory is in the buffer pool Managed in , stay WAL in buffer pool It plays the role of accelerating the update , And in fact ,buffer
pool There is a more important role , Is to speed up the query .
WAL The existence of mechanism , When a transaction is committed , The data page on disk is old , If you want to read this data page right now , You don't need to read the disk , Get the results directly from memory .
and buffer pool Speed up the query , An important indicator ： Memory hit rate
stay ([email protected]:3306) [(none)]> show engine innodb status\G; View results in
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
If all the data pages need to be queried can be directly obtained from the memory , The memory hit rate is 100%, But in the actual generation is very difficult to do .
Innodb buffer pool The size of is determined by the parameter innodb_buffer_pool_size control , It's usually in real memory 60%~80%
therefore innodb_buffer_pool_size Less than the number of disks is common , If buffer_pool It's full , And read a data page from disk , That would eliminate an old data page .
Innodb For memory management lru rule
Innodb Administration buffer pool Of LRU algorithm , Using linked list to achieve . But in innodb in , You can't use this directly lru algorithm , Some improvements have been made
stay innodb Implementation , according to 5:3 The proportion of the whole lru The linked list is divided into two parts young and old Two districts ,
--1 To access a data page p3, because p3 stay young region , So it's the same as the optimizer lru The algorithm is the same , One to the head of the linked list
--2 After that, you need to access a new data page that does not exist in the current linked list , At this time, the data page is still eliminated pm, But the newly inserted data page px, It's on the lru_old place
--3 be in old The data page of the region , Make this judgment every time you are interviewed ,
--- If this data page is in lru The linked list has existed for more than 30 years 1 second , Move it to the head of the list
--- If this data source is in lru The actual value in the linked list is shorter than 1 second , The position remains unchanged ,1 Second, this time has parameters innodb_old_blocks_time Controlled ,
This strategy is tailored to handle operations similar to full table scanning .
Technology