MySQL Forums
Forum List  »  MyISAM

Large BLOBs
Posted by: Frank Natoli
Date: June 25, 2006 09:53AM

Testing efficiency of MySQL MyISAM tables to absorb many row inserts each with large (350Kb) BLOB, see method below. Runs quickly (two inserts per second) for first 500 rows, then begins to slow down dramatically. By the time 700 rows have been inserted, each insert takes ten seconds. A total of 1000 rows takes five hours. This is on 2.8GHz Dell Dimension 4600 with 512Mb RAM and with 30Gb H/D available. Database file with 1000 rows only requires 38Mb. Tried changing table type to InnoDB and that runs much slower even with the early rows. I read about users with database files with tens of gigabytes of data. Is my method below particularly inefficient? Or is there a trick to speeding up MySQL processing of large BLOBs? Thanks.

public void createRow(String rowName, String fileName)
{
try
{
// read input data into byte array
File inputFile = new File(fileName);
byte[] inputFileData = new byte[(int)inputFile.length()];
FileInputStream inputStream = new FileInputStream(inputFile);
inputStream.read(inputFileData);
inputStream.close();

// create row
String sql = "insert into BlobTest (`BlobName`,`BlobData`) values ('" + rowName + "','')";
logger.debug(sql);
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.execute();

// read row
sql = "select `BlobData` from BlobTest where `BlobName`='" + rowName + "'";
logger.debug(sql);
stmt = connection.prepareStatement(sql);
ResultSet result = stmt.executeQuery();
if (!result.first())
{
System.err.println("select failed to find row");
System.exit(-1);
}
Blob inputBlobData = result.getBlob("BlobData");
inputBlobData.setBytes(1, inputFileData);

// update row
sql = "update BlobTest set `BlobData`=? where `BlobName`='" + rowName + "'";
logger.debug(sql);
stmt = connection.prepareStatement(sql);
stmt.setBlob(1, inputBlobData);
stmt.executeUpdate();
stmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
System.exit(-1);
}
catch (FileNotFoundException e)
{
e.printStackTrace();
System.exit(-1);
}
catch (IOException e)
{
e.printStackTrace();
System.exit(-1);
}
}

Options: ReplyQuote


Subject
Views
Written By
Posted
Large BLOBs
3213
June 25, 2006 09:53AM
1517
June 26, 2006 03:01AM
1923
June 26, 2006 05:42AM
1674
June 26, 2006 06:39AM
1615
June 26, 2006 09:17AM
1682
June 27, 2006 08:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.