| Michael F. Robbins on Wed, 12 Jun 2002 00:35:59 -0400 |
|
On Tue, 2002-06-11 at 18:22, Walt Mankowski wrote: > On Tue, Jun 11, 2002 at 11:14:38AM -0400, Kyle R . Burton wrote: > > (varchar 15), and a blob column. The average size of the blob column > > is about 1k (1099 bytes) - it has to be a blob (it's binary data - a > > compressed data structure). Depending on the specifics of the application, it may make more sense to store your data structure right on the filesystem (/path/to/mydata/PRIMARYKEY or similar). I've found that reading a few kilobytes from a blob is quite slow: overhead in translating from BDB/InnoDB/whatever to raw data, then transferring it to your app. You'll probably waste some unused inode space because of your small file sizes, but it might be worth it. However, I could certainly see that a directory with a million files would cause many problems of its own. > > We're currently only getting between 90 and 280 records per second. This > > is on a dual athlon 1.2ghz w/1.5gb of ram. We've already got noatime and > > notail set on the file systems the data files are stored on. How are connections to the database handled? Does each lookup call for a new connection, or are persistent shared connections (like in PHP) used? This makes a big difference in performance. > I'm certainly not a performance guru, but the only that comes to mind > off the top of my head is that it might be a bit more efficient to > store the the primary key as a char 15 instead of a varchar. Though I doubt that the suggestion above would be very effective alone, if you were to use my suggestion above about moving the blob data into the filesystem and then use a fixed-length primary key, MySQL would be able to store each row with a fixed length. This should increase performance quite a bit (faster seeking to desired record). Then again, if you were to store the blob data as files, I guess there'd be little need for MySQL. Michael F. Robbins mike@gamerack.com Attachment:
signature.asc
|
|