major performance issue

[cerr]

Hi There,

I have a major performance issue with an application that is inserting
strings into a mysql database.
I'm not quite sure but we see major backlogs and it's only about 6MB
that it would be inserting in 24 hours. That should not be a major
problem i would say.
The way this logserver is working is:
while (1)
{
send message off to syslog on port 1515 which is on the same host.
/*message parsing with strtok and other standard C function calls.*/
sprintf(query,"insert into logs(source,date,time,program,msg)values
('%s','%d-%d-%s','%s','%s','%s[%s:%s');",
_source,
t->tm_year
+1900,month,_date,
_time,
_program,

_program,_program_name,newmsg
);
cout << "INSERT Query: " << query <<
endl;
_update_db(_source,query);
}

This seems to insert only one message per about 500 to 800ms
(estimated). Each message would be around 200 Bytes long. Sometimes
there's multiple messages arriving per second asynchronously.
My problem is, I can not do too much debugging and 'trying things' on
this because this is a system that's been switched live already. My
local system does not seem to be experiencing the same performance
issues.
Any clues where bottle necks may be and how they can be resolved?
Thanks a lot!

roN

PS: mysql --version
mysql Ver 14.12 Distrib 5.0.54, for pc-linux-gnu (i486) using
readline 5.2

[www.1-script.com]

Post by cerr
Hi There,
I have a major performance issue with an application that is inserting
strings into a mysql database.
I'm not quite sure but we see major backlogs and it's only about 6MB
that it would be inserting in 24 hours. That should not be a major
problem i would say.
while (1)
{
send message off to syslog on port 1515 which is on the same host.
/*message parsing with strtok and other standard C function calls.*/
sprintf(query,"insert into
logs(source,date,time,program,msg)values
('%s','%d-%d-%s','%s','%s','%s[%s:%s');",
_source,
t->tm_year
+1900,month,_date,
_time,
_program,
_program,_program_name,newmsg
);
" << query <<
endl;
_update_db(_source,query);
}
This seems to insert only one message per about 500 to 800ms
(estimated). Each message would be around 200 Bytes long. Sometimes
there's multiple messages arriving per second asynchronously.
My problem is, I can not do too much debugging and 'trying things' on
this because this is a system that's been switched live already. My
local system does not seem to be experiencing the same performance
issues.
Any clues where bottle necks may be and how they can be resolved?
Thanks a lot!
roN

Are those MyISAM tables?

In all likelihood the inserts are backing up behind selects - very common
on a live system. On your test rig there were probably hardly any selects
and so the inserts had the access to tables all by themselves. Selects
ahead of inserts actually strikes me as pretty normal and even rather
desirable priority on a live system.

on the other hand, you can always try "INSERT HIGH_PRIORITY" though in
this case you'd have to make sure there are no concurrent inserts from
your software