Mysql table column replication, mysql connection time and memory control. Two temporary tables, temp_t, and table1, are both MyISAM and have about records respectively. Cron regularly obtains some information from the network every minute. it is first stored to temp_t (frequently written), and then cron regularly replicates information from temp_t to the positive table table1 every 10 minutes (frequently read, mysql table column replication every 10 minutes, mysql connection time and memory control.
Two temporary tables, temp_t, and table1, are both MyISAM and have about records respectively.
Cron regularly obtains information from the network every minute. it is first stored in temp_t (frequently written ),
Then, cron copies information from temp_t to the positive table table1 every 10 minutes (read frequently and write once every 10 minutes ).
Temp_t Field id (auto_increment), title (varchar (200), content (varchar (500), date (DATETIME), mykey (tinyint (1) Default 1, this is used to determine whether the data has been copied to the positive table. after the data is copied, the value is updated to 2)
Table1 Field id (without auto_increment), title (varchar (200), content (varchar (500), date (DATETIME)
300 records. each data table has about MB. each update requires about 2-3 K Records.
If you directly copy the data, the MYSQL connection time is too long, resulting in MYSQL Performance decline table1 will be similar to the locking table phenomenon, the query time of table1 is obviously longer
require dirname(__FILE__) . '/../connection.php';
mysql_select_db("news",$connextion);
mysql_query("SET NAMES utf8");
$query = mysql_query("SELECT * FROM temp_t where mykey = '1'");
while($rows = mysql_fetch_array($query)){
mysql_query("UPDATE temp_t SET mykey='2' WHERE id='".mysql_real_escape_string($rows['id'])."'");
mysql_query("INSERT INTO table1 (id,title,content,date) values ('".mysql_real_escape_string($rows['id'])."','".mysql_real_escape_string($rows['title'])."','".mysql_real_escape_string($rows['content'])."','".mysql_real_escape_string($rows['date'])."'");
}
mysql_close($connextion);
So I want to read the record data from temp_t first, store it as a json Array, and then copy the data to table1. However, sometimes Cannot allocate memory, then the whole server crashes (is it because the json Array is too large and occupies a huge amount of memory ). PS: The memory limit has been added to 64 MB in php. ini (but I always think that the cure is not a permanent cure. in such a replication process, there is no need for so much memory. The key is that the code is unreasonable ).
require dirname(__FILE__) . '/../connection.php';
mysql_select_db("news",$connextion);
mysql_query("SET NAMES utf8");
$query = mysql_query("SELECT * FROM temp_t where mykey = '1'");
$jon = array();
while($rows = mysql_fetch_array($query)){
$jon['a'] = $rows['id'];
$jon['b'] = $rows['title'];
$jon['c'] = $rows['content'];
$jon['d'] = $rows['date'];
$pjon .= json_encode($jon).',';
}
mysql_close($connextion);
$njso = json_decode('['.substr($pjon,0,-1).']');
foreach($njso as $nx){
if($nx->a){
require dirname(__FILE__) . '/../connection.php';
mysql_select_db("news",$connextion);
mysql_query("SET NAMES utf8");
mysql_query("UPDATE temp_t SET mykey='2' WHERE id='".mysql_real_escape_string($nx->a)."'");
mysql_query("INSERT INTO table1 (id,title,content,date) values ('".mysql_real_escape_string($nx->a)."','".mysql_real_escape_string($nx->b)."','".mysql_real_escape_string($nx->c)."','".mysql_real_escape_string($nx->d)."'");
mysql_close($connextion);
}
}
So do you have any less-efficient code to copy table information? Does mysql require short connection time and less memory consumption? Thank you. Share:
------ Solution --------------------
Execute these two sentences in sequence.
Insert into table1 (id, title, content, date) values
SELECT id, title, content, date from temp_t where mykey = '1'
UPDATE temp_t SET mykey = '2' WHERE mykey = '1'
You do not need to take over the php service.
Since temp_t is temporarily stored, why not delete the transferred data?
------ Solution --------------------
