Mysql writes data in batches. How do you optimize for this type of performance problem

The test environment

Configuration directly affects the execution speed, first go to the test machine configuration:

• CPU I7 5500U (low voltage damage, will not buy low voltage U)
• Memory 8 GB DDR3 1600
• PHP 7.1
• Mysql 5.5.40
• Development framework CodeIgniter 3.1.2

What are the factors that affect write efficiency?

• Database engine

MyISAM and InnoDB are commonly used in the development of database engines. Other database engines I have not used in the development, so I will not test here.

Let’s take a look at the library table structure:

There are two tables in the test library:

Each table has the following structure (one increment ID, one vARCHAR type field to insert) :

Insert 20W into two tables by default

The PHP code looks like this:

/** ** Test insert efficiency ** @return void
	 * 
	 */
	public function insertTest(){ set_time_limit(0); // Prevent errors that exceed 300s 500$t1 = microtime(true); // Insert num randomlyfor ($i= 1;$i< = 200000;$i{+ +)$result = $this->db->insert('myisam'['value' => uniqid().$i]); } // Program run time$t2 = microtime(true);
		echo 'Time:'.round($t2-$t1And 3).'s < br >';
		echo 'Memory consumption:'Round (memory_get_usage () / 1048576, 2)." M<br/>";
	
	}Copy the code

Execution Result:

20W data Myisam is approaching 3 minutes.

Take a look at InnoDb’s default execution time:

It took 6 minutes and 49 seconds to insert 1W pieces of data. There was no way to wait. Based on this amount of data, 6 minutes and 49 * 20 =?

Later, as the amount of data increases, insertion performance will also be affected, so InnoDb takes more than 2 hours to insert 20W single-field data by default, which is unbearable.

• The business logic

It is obvious that there is a problem with the above business logic. Each data is inserted once, which increases the overhead of mysql. Every time data is inserted, it must be re-connected to mysql, which is definitely a waste of resources. So CI provides insert_batch(), which writes data in batches. Thinkphp3.2 also has addAll() support. All other frameworks should be available!

It’s a very simple idea to concatenate a two-dimensional array into SQL

Put a single Sql as follows:

$sql = “INSERT INTO TEST (value) VALUES (‘helloworld1’)”;

$sql = “INSERT INTO TEST (value) VALUES (‘helloworld2’)”;

Joining together as follows:

$sql = “INSERT INTO TEST (value) VALUES (‘helloworld1’), (‘helloworld2’)”;

Obviously, batch inserts are much faster.

Or 20W data, MyISAM batch query speed? (Existing data will affect the insertion efficiency, myISAM table has been cleared)

Let’s go back to the code

/** ** Test batch insert efficiency ** @return void
	 * @author [email protected]
	 * 
	 */
	public function insertTest(){ set_time_limit(0); // Prevent errors that exceed 300s 500$t1 = microtime(true); // Insert num randomlyfor ($i= 1;$i< = 200000;$i{+ +)$data[$i] = ['value' => uniqid().$i]; } // Program run time$t2 = microtime(true);
		echo 'Loop time:'.round($t2-$t1And 3).'s < br >';
		
		$this->db->insert_batch('myisam'.$data); // Batch insert$t3 = microtime(true);
		echo 'Insert time:'.round($t3-$t2And 3).'s < br >';
		
		echo 'Memory consumption:'Round (memory_get_usage () / 1048576, 2)." M<br/>";
	
	}Copy the code

Execution Result:

This is five times faster than the previous single insertion speed of 167 seconds. Increase the memory consumption by about 1.5 times. The memory replacement time can be ~~~

What speed is InnoDB going to have?

Execution Result:

This time it was done, and fast. It’s also N times more efficient than the previous two hours.

• Modify configuration parameters to improve performance:

Is the InnoDB engine really that slow? So low?

The obvious answer is: NO

One reason why InnoDB writes are so slow is that:

innodb_flush_log_at_trx_commitCopy the code

Parameter configuration problem

Default values as shown below:

Consult the mysql documentation for values:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

When innodb_flush_log_at_trx_COMMIT =0, the log buffer is written to the log file once per second, and the log file is flush(flushed to disk) at the same time. At this point, the transaction commit does not actively trigger a write to disk.

When innodb_flush_log_at_trx_COMMIT =1 (default), every time a transaction commits, MySQL writes data from the log buffer to log file and flush the log file to disk.

When innodb_flush_log_at_trx_COMMIT =2, MySQL writes data from the log buffer to the log file at each transaction commit, but does not flush to disk at the same time. However, MySQL performs a flush(flush to disk) every second.

Set the value to 2 and try again:

The speed increased by three times and was almost the same as MyISAM.

So later said InnoDB write speed is slow, may be a configuration problem

• You can optimize, right?

What else can you optimize?

Since we used insert_Batch for the framework, take a look at the CI source code:

/**
	 * The "set_insert_batch" function.  Allows key/value pairs to be set for batch inserts
	 *
	 * @param	mixed
	 * @param	string
	 * @param	bool
	 * @return	CI_DB_query_builder
	 */
	public function set_insert_batch($key.$value = ' '.$escape = NULL)
	{
		$key = $this->_object_to_array_batch($key);

		if(! is_array($key))
		{
			$key = array($key= >$value);
		}

		is_bool($escape) OR $escape = $this->_protect_identifiers;

		$keys = array_keys($this->_object_to_array(current($key)));
		sort($keys);

		foreach ($key as $row)
		{
			$row = $this->_object_to_array($row);
			if (count(array_diff($keys, array_keys($row))) > 0 OR count(array_diff(array_keys($row), $keys)) > 0)
			{
				// batch function above returns an error on an empty array
				$this->qb_set[] = array();
				return;
			}

			ksort($row); // puts $row in the same order as our keys

			if ($escape! == FALSE) {$clean = array();
				foreach ($row as $value)
				{
					$clean[] = $this->escape($value);
				}

				$row = $clean;
			}

			$this->qb_set[] = '('.implode(', '.$row).') ';
		}

		foreach ($keys as $k)
		{
			$this->qb_keys[] = $this->protect_identifiers($k, FALSE, $escape);
		}

		return $this;
	}Copy the code

The data that we pass in, the method will recirculate, judge. Therefore, it is recommended that the statement concatenate itself

The code is modified as follows:

/** ** Test insert efficiency ** @return void
	 * @author [email protected]
	 * 
	 */
	public function insertTest(){ set_time_limit(0); // Prevent errors that exceed 300s 500$t1 = microtime(true);
	
	
		$sql = "insert into innodb (value) VALUES"; // Insert num randomlyfor ($i= 1;$i< = 200000;$i{+ +)$val = uniqid().$i;
				
			$sql. ="(' {$val} '),";
			
		}
		
		$sql = substr($sql, 0, 1); // Program run time$t2 = microtime(true);
		echo 'Loop time:'.round($t2-$t1And 3).'s < br >';
		
		$this->db->query($sql); // Batch insert$t3 = microtime(true);
		echo 'Insert time:'.round($t3-$t2And 3).'s < br >';
		
		echo 'Memory consumption:'Round (memory_get_usage () / 1048576, 2)." M<br/>";
	
	}Copy the code

Execution Result:

20W data InnoDB cycles in 1.6 seconds and inserts in 1.2 seconds. The speed is good.

The concatenation statement may report an error setting

max_allowed_packet = 500M

Allows mysql to accept packet sizes.