MySQL Forums
Forum List  »  Perl

DBD::mysql::st execute failed: Lost connection to MySQL server during query
Posted by: Philip Johnson
Date: June 29, 2011 02:26PM

I hope someone here can give me some ideas, I've been trying to figure this issue out for the last week.

I have a MySQL server running version MySQL-5.5.11 that I recently moved our interface engine onto (I work at a hospital). Even since then, Perl threads have been dying with the titular Lost connection error, indicating any number of statement executions that have been previously prepared.

What is so confusing to me is how rare these crashes are, and that they are happening on prepared statements that, with the same parameters, will work either before or after within the same second.

One such statement, for example, is
$HL7LIB_UpdateStatus_stmt = $DBH->prepare("REPLACE INTO InterfaceStatus (Process, pid, Status, Debug, LastTransaction, Transaction) VALUES ( ?, ?, ?, ?, NOW(), ?)");
The columns are in order VARCHAR(10), INT(10), VARCHAR(100), INT(2), DATETIME, and BIGINT(20). This statement gets executed on the magnitude of 1,000,000 times a day by one script alone (probably closer to 50,000,000 times across all scripts that are including and using the same .pm file this statement is defined within), but it will only crash with the above error, say, 4 times in a day. Also this statement gets prepared once, when the script starts up, so it's not being prepared again and again repeatedly.

I've looked through most of the causes I've found online for this kind of error. My max_allowed_packet = 256M, I know I'm not exceeding that size. I know no one is doing a KILL on the connection. I know the connection is not being closed, and the thread is not forking. This is being run on localhost, so there can't be network issues. I've run extended checks on all tables in the entire database, and haven't found any problems. This connection is also at most idle for 2-3 seconds at a time, usually less then a second, so I know I'm nowhere near the wait_timeout or interactive_timeout.

The only thing I can think of is in my zeal I've set some allocation value too high without increasing something else. The server itself is a beast, 6 dual core processors with hyper-threading for 24 logical processors, 48 gigs of ram.

If anyone has any ideas, I would be extremely thankful. I've including my my.cnf below.

[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
key_buffer_size = 384M
max_allowed_packet = 256M
table_open_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 = 128M
log-slow-queries = /var/log/mysql-slow-queries.log
long_query_time = 2
log-error = /var/log/mysql-error.log
max_connections = 350
old-password = 1
expire_logs_days = 3

skip-external-locking
federated

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 40

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 100

# Replication Master Server (default)
# binary logging is required for replication
# binary logging format - mixed recommended
log-bin = mysql-bin
binlog_format = mixed
max_binlog_size = 100M

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata01:50G;ibdata02:50G;ibdata03:50G;ibdata04:50G;ibdata05:50G
innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 % of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 20G
innodb_additional_mem_pool_size = 1G
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_autoinc_lock_mode = 2

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout



Edited 1 time(s). Last edit at 06/29/2011 02:35PM by Philip Johnson.

Options: ReplyQuote




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.