Description:
It seems that a program with a prepared statement cache of a lot of prepared statements with many parameters cause memory leak.
How to repeat:
Create the following table:
CREATE TABLE `users` (
`id` int(10) NOT NULL,
`name` varchar(30) COLLATE gbk_bin NOT NULL,
`birth` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin
Populate it with 100000 random items (This is my test setting, maybe fewer items is ok too). I use the following Java program.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class PopulateUsers {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test", null);
Statement ps = conn.createStatement();
for (int i = 0; i < 100000; i++) {
ps.executeUpdate("insert into users values(seq, 'aaaaaaaaaaaaaaaaaaaaa', now())");
if ((i % 1000) == 0)
System.out.print(".");
}
}
}
Run the following program:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Hashtable;
import java.util.Random;
import java.util.Map.Entry;
public class TestPrepare {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
for (int i = 0; i < 20; i++)
new WorkerThread().start();
}
}
class WorkerThread extends Thread {
private static int seed = 0;
private Random r;
@Override
public void run() {
r = new Random(seed++);
try {
testMySQLPrepareCache();
} catch (SQLException e) {
e.printStackTrace();
}
}
void testMySQLPrepareCache() throws SQLException {
Hashtable<String, PreparedStatement> psCache = new Hashtable<String, PreparedStatement>();
int maxCacheSize = 20;
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/ddb_demo1", "root", null);
while (true) {
int numIds = 10 + r.nextInt(400);
StringBuilder sql = new StringBuilder("select * from users where id = ?");
for (int i = 1; i < numIds; i++)
sql.append(" or id = ?");
sql.append(" order by name limit 20");
PreparedStatement ps;
ps = psCache.get(sql.toString());
// A simple PreparedStatement cache
if (ps == null) {
ps = conn.prepareStatement(sql.toString());
while (psCache.size() >= maxCacheSize) {
Entry<String, PreparedStatement> e = psCache.entrySet().iterator().next();
e.getValue().close();
psCache.remove(e.getKey());
}
}
psCache.put(sql.toString(), ps);
for (int i = 0; i < numIds; i++) {
ps.setInt(i + 1, r.nextInt(100000));
}
ResultSet rs = ps.executeQuery();
rs.close();
}
}
}
This program starts 20 threads. All of them just iusse "select * from users where id = ? or id = ? ..." queries, the number of parameters is generated randomly. And each thread will cache at most 20 PreparedStatements.
Run the program and you'll find that the memory usage of MySQL will grow and will not drop to the initial point after you terminate the program. Run the program again and again, for the most case you'll find the the momory usage will also grow steadily.
Description: It seems that a program with a prepared statement cache of a lot of prepared statements with many parameters cause memory leak. How to repeat: Create the following table: CREATE TABLE `users` ( `id` int(10) NOT NULL, `name` varchar(30) COLLATE gbk_bin NOT NULL, `birth` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin Populate it with 100000 random items (This is my test setting, maybe fewer items is ok too). I use the following Java program. import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class PopulateUsers { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test", null); Statement ps = conn.createStatement(); for (int i = 0; i < 100000; i++) { ps.executeUpdate("insert into users values(seq, 'aaaaaaaaaaaaaaaaaaaaa', now())"); if ((i % 1000) == 0) System.out.print("."); } } } Run the following program: import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Hashtable; import java.util.Random; import java.util.Map.Entry; public class TestPrepare { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); for (int i = 0; i < 20; i++) new WorkerThread().start(); } } class WorkerThread extends Thread { private static int seed = 0; private Random r; @Override public void run() { r = new Random(seed++); try { testMySQLPrepareCache(); } catch (SQLException e) { e.printStackTrace(); } } void testMySQLPrepareCache() throws SQLException { Hashtable<String, PreparedStatement> psCache = new Hashtable<String, PreparedStatement>(); int maxCacheSize = 20; Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/ddb_demo1", "root", null); while (true) { int numIds = 10 + r.nextInt(400); StringBuilder sql = new StringBuilder("select * from users where id = ?"); for (int i = 1; i < numIds; i++) sql.append(" or id = ?"); sql.append(" order by name limit 20"); PreparedStatement ps; ps = psCache.get(sql.toString()); // A simple PreparedStatement cache if (ps == null) { ps = conn.prepareStatement(sql.toString()); while (psCache.size() >= maxCacheSize) { Entry<String, PreparedStatement> e = psCache.entrySet().iterator().next(); e.getValue().close(); psCache.remove(e.getKey()); } } psCache.put(sql.toString(), ps); for (int i = 0; i < numIds; i++) { ps.setInt(i + 1, r.nextInt(100000)); } ResultSet rs = ps.executeQuery(); rs.close(); } } } This program starts 20 threads. All of them just iusse "select * from users where id = ? or id = ? ..." queries, the number of parameters is generated randomly. And each thread will cache at most 20 PreparedStatements. Run the program and you'll find that the memory usage of MySQL will grow and will not drop to the initial point after you terminate the program. Run the program again and again, for the most case you'll find the the momory usage will also grow steadily.