My C-program inserts into mySql slowly if run remotely

[HenryMonster]

Hi All,

I have a FreeBSD production server and
a Linux-box, which we use for development
and as our ftp server at the same time.

Both machines host the same version of mySql: 3.23

I developed a simple C-program, which parses
ascii-files, which we receive daily
through ftp on our Linux machine from our
business partner and inserts new
records into a table (47 fields per record).

My manager want to run this program from Linux only !

When I run the program against our development mySql database on this same Linux machine, it processes
200 records in 3-5 seconds.

However when I run the same program (on Linux)
against our production database, it takes 7-10 minutes
to process the same number of records.

The tables & indexes are identical in both development and
production databases. The workload of the FreeBSD machine
is less than moderate (it can be deemed as idle).

ANY IDEA WHAT COULD CONTRIBUTE TO SUCH
DRASTIC PERFORMANCE DROP ?

Thank you very much in advance !

... and this is now becoming urgent


Henry Monster.

 

[sleipnir214]

Without knowing more about your code, I can only comment in the general. Some things that come to mind:

Slow DNS. You're using a server name to connect to the server, and DNS is slow. This can be particularly slowing if you're looking up the name each iteration through the insertion loop.

A congested network between your program and the MySQL server.
______________________________________________________________________
TANSTAAFL!

 

[HenryMonster]

Sleipnir214,

Thank you for quick response !

I open a connection with the database once in the initialization section of the program. I hope it's only
at this point that mySql client libraries need a DNS
resolution.

The program has a debugging mode which can
stream all SQL statements the program runs into
a text file. When I execute this file in
mySql-utility session on the same Linux machine
it performs at a lightning speed ...


The network does not seem to be congested.


I could post my program (> 1000 lines of code)
or send it over e-mail to anyone who has enough
compassion&patience to bother looking in my code.





Thank you !

And waiting for more suggestions.

 

[OldProgrammer]

If exactly the same compile image works differently on two different databases - maybe you need to look elsewhere?

I'd suggest looking at show variables - this might highlight some setting differences or some value differences - switch slow query loggin on using mysql.cnf - this might highlight a specific query that's performing slowly.

2) Look at handler_read_rnd, handler_read_rnd_next - the next value will be high if you're table scanning

3) You don't mention selects - so a question is are you doing selects before inserting?

4) If you keep going is the time taken increasing exponentially?
5) Check key reads - if it's high you might need a large key cache on your production database

Hope this helps