Re: MySQL performance over DB_File

[hoscalek]

Thanks for the encouragement. If I may describe the structure of my data and the MySQL server configuration in more detail.

My Perl script is rougly like this:

$databaze->do("CREATE TABLE lexical_names_XML
(lexical_name INT AUTO_INCREMENT PRIMARY KEY, word VARCHAR(256), frequency TEXT)");
# INDEX lexical_names
$databaze->do("CREATE INDEX lnme ON lexical_names_XML (word(30))");
# data operations
$insert_lnames = $databaze->prepare("INSERT INTO lexical_names_XML VALUES (DEFAULT,?,?)");
$update_lnames = $databaze->prepare("UPDATE lexical_names_XML
SET frequency = CONCAT(frequency, ?) WHERE word = ?");

...

while ($radek !~ /<\/html>/) {
...
$hnizdo; # the value to be newly indexed, or a frequency reference to be updated on an existing instace
$update_lnames->execute($frequency_new, $hnizdo);
if (! $update_lnames->rows) {
$insert_lnames->execute($hnizdo, $frequency_new);
$insert_lnames->finish;
};
};

I have Windows XP, Pentium 4 2.8GHz, 1GB RAM. Version of MySQL is 5.0.24a community edition. Version of DBD::mysql is 3.0007. I run MySQL only occasionally but then with a full dedication. The server is only for my usage on localhost (no external connections). Thus I opted for use of the configuration file my-huge.ini in which I manually edited/added some memory references:

[mysql]

default-character-set=latin1
skip-innodb
query_cache_min_res_unit=4096k
key_buffer_size=512M
sort_buffer_size=16M
myisam_sort_buffer_size=32M
query_cache_limit=4M
query_cache_size=64M

[mysqld]

skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 4
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=MYISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=8
tmp_table_size=32M

It this sufficient for some more performance advice?

Thanks,

Marek