MySQL Forums
Forum List  »  Perl

Re: MySQL performance over DB_File
Posted by: Bill Karwin
Date: September 10, 2006 09:26PM

Okay, suggestions:

Perl's interface to MySQL simulates server-side prepared statements by default, it does not use real server-side prepared statements unless you use "mysql_server_prepare" in your connect string. See the section "Prepared statement support" in DBD::mysql documentation: http://search.cpan.org/~capttofu/DBD-mysql-3.0007/lib/DBD/mysql.pm

Your usage of the [mysql] and [mysqld] sections in the config file may be wrong. The [mysql] section applies to the client tool. The options you're specifying in that section will be ignored by the server. Move them into the [mysqld] section. Verify that the settings have taken effect by examining variables with a statement, for example:
show variables like 'key_buffer_size';

Read about the server option "bulk_insert_buffer_size" here: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html It may help to buffer rows as you insert them.

Inserting large amounts of data into MySQL is many times faster if you can use LOAD DATA INFILE to read the data from a text file. You could try to use Perl to process your input and calculate the final values for the frequencies. Store the intermediary data in a Perl data structure as you process the input. Then once that's done, dump the data to a flat text file (basically a comma-separated or tab-separated file will do). Then load the text file into MySQL in one step, using LOAD DATA INFILE. See http://dev.mysql.com/doc/refman/5.0/en/load-data.html for more information on LOAD DATA INFILE.

Also read the optimization documentation page for INSERT, to see more tips on performance-improvement. http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Regards,
Bill K.

Options: ReplyQuote


Subject
Written By
Posted
September 07, 2006 03:40PM
September 07, 2006 11:03PM
September 10, 2006 02:42PM
Re: MySQL performance over DB_File
September 10, 2006 09:26PM
September 12, 2006 03:46PM


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.