Hi Folks --
I'm running into some issues with large prepared statements, and would love some
feedback about approaches or tweaks that I might have overlooked :)
I'm working on a large-scale database for scientific data -- lots of individual
detector values and whatnot, that unfortunately need to be independently
addressable in queries. Makes it more fun, I guess :) In any case, the table
structure I'm working with looks like:
create table raddata (
granID integer not null,
scanID tinyint unsigned not null,
fpID tinyint not null,
c1 smallint,
c2 smallint,
c3 smallint,
[and so on and so forth....]
c2376 smallint,
c2377 smallint,
c2378 smallint,
primary key (fpID, scanID, granID)
);
Overall, it works out to 2381 fields, with an aggregate length of 5058 bytes per
row.
The data I'm working with comes from instrument data files, which I've pulled
apart and stuffed into C structures that look fairly similar:
typedef struct {
int granID;
u_char scanID;
char fpID;
short channels[2378];
} radRow;
>From our instrument data, I get about 3 million of these rows a day, and its
constantly gathering data. Each of my data "granules" has 12150 rows for this
table, and inserts are pretty slow.
Using single prepared statements, the row inserts take about 70 seconds, from
the in-memory structs. I've tried globbing them together by doing multiple row
inserts (INSERT into raddata values (....), (.....)), and have found some
interesting limitations. I can do 3 rows worth of insert in one statement
(15174 bytes of data, query string with ?'s is 14336 bytes). 4+ rows (20232
bytes of data, query string with ?'s for params is 19100 bytes) causes a
SIGBUS, and the ingest app dies in a ball of fire.
The table is locked for writes before any inserts, and unlocked immediately
after, so I don't think its thread contention for the table. Using the groups
of 3 rows per insert is actually a little slower, running 75-80 seconds.
I'm currently running both the ingest app and the mysql server (4.1.3-beta,
built from source) on a Sun Ultra 60 running Solaris 9.
Any suggestions as to how to do this more efficiently/faster? Or how to
increase the buffer space for the prepared statements, so as to do the inserts
in larger groups?
Thanks in advance!
Ken
===========================================================================
"Diplomacy is the weapon of the Civilized Warrior"
- Hun, A.T.
Ken Gieselman [EMAIL PROTECTED]
Endlessknot Communications http://www.endlessknot.com
===========================================================================
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
