List:General Discussion« Previous MessageNext Message »
From:spiros Date:August 18 2002 5:42pm
Subject:mysql just hangs!
View as plain text  
 
I was testing an apparent performance issue while I got a strange perl
 message , claiming it (with perl/DBI)

DBD driver has not implemented the AutoCommit attribute at perlscript.pl line 
1064, <IN> line 12911.
Issuing rollback() for database handle being DESTROY'd without explicit 
disconnect(), <IN> line 12911

this was strange, since I tested it on a test program and AutoCommit worked 
fine.

Anyway, the code died, I did some changes and it worked ok, until  it hit the 
same message. The problem now is that (I rebooted the server) I can do
/usr/bin/mysqld_safe fine, but when I do
mysql -u user -ppasswd database

it just hangs there(and this is also the case when I try to connect  via DBI)
I am using 4.0.1 with InnoDB. Could it be that I ran out of table space?
/var/lib/mysql/linux.err complains that:

/usr/sbin/mysqld: ready for connections
InnoDB: Warning: operating system error number 4 in a file operation.
InnoDB: Cannot continue operation.

Number of processes running now: 0
020818 21:56:29  mysqld restarted
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 43 1452255266
InnoDB: Doing recovery: scanned up to log sequence number 43 1452320768
InnoDB: Doing recovery: scanned up to log sequence number 43 1452386304
.....


 What should I do now?

Here is /etc/my.cnf

# Example mysql config file for very large systems.
#
# This is for large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.

# The following options will be passed to all MySQL clients
[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
skip-locking
set-variable    = key_buffer_size=500M
set-variable    = key_buffer=100M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=512
set-variable    = max_connections=10
set-variable    = tmp_table_size=400M
set-variable    = sort_buffer=2M
set-variable    = record_buffer=2M
set-variable    = thread_cache=8
set-variable    = thread_concurrency=4    # Try number of CPU's*2
#set-variable    = myisam_sort_buffer_size=64M
set-variable    = myisam_sort_buffer_size=4M
innodb_data_home_dir=
#and then use absolute file paths
#innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:2000M;ibdata8:2000M
innodb_data_file_path=/var/lib/mysql/ibdata1:2000M;/var/lib/mysql/ibdata2:2000M;/var/lib/mysql/ibdata3:2000M;/var/lib/mysql/ibdata4:2000M;/var/lib/mysql/ibdata5:2000M;/var/lib/mysql/ibdata6:2000M;/var/lib/mysql/ibdata7:2000M;/var/lib/mysql/ibdata8:2000M;/usr/ibdata/ibdata9:2000M;/usr/ibdata/ibdata10:2000M;/usr/ibdata/ibdata11:2000M;/usr/ibdata/ibdata12:2000M;/ibdata/ibdata13:2000M;/ibdata/ibdata14:2000M;/ibdata/ibdata15:2000M;/var/lib/mysql/ibdata16:2000M;/var/lib/mysql/ibdata17:2000M;/var/lib/mysql/ibdata18:2000M;/var/lib/mysql/ibdata19:2000M;/var/lib/mysql/ibdata20:2000M;/var/lib/mysql/ibdata21:2000M
#;/usr/ibdata/ibdata9:2000M;/usr/ibdata/ibdata10:2000M
## Comment next line if you do not need recovery(the hostname-bin.xxx files)
#log-bin
##
server-id    = 1
#set-variable    = open-files-limit=8192  #ulimit is 1024,hard 8192
set-variable = innodb_buffer_pool_size=1100M
# so that innodb_buffer_pool 
size+key_buffer+max_connections*(sort_buffer+record_buffer+2M)=1100+100+10*(2+2+2)=1260<2000M=RAM
set-variable = innodb_additional_mem_pool_size=700M
set-variable = innodb_log_file_size=1000M
set-variable = innodb_log_buffer_size=20M
# helps for large transactions
# Uncomment the following if you are using BDB tables
#set-variable    = bdb_cache_size=384M
#set-variable    = bdb_max_lock=100000

# Point the following paths to different dedicated disks
#tmpdir        = /tmp/
#log-update     = /path-to-dedicated-directory/hostname

[mysqldump]
quick
set-variable    = max_allowed_packet=256M

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

[isamchk]
set-variable    = key_buffer=256M
set-variable    = sort_buffer=256M
set-variable    = read_buffer=2M
set-variable    = write_buffer=2M

[myisamchk]
#set-variable    = key_buffer=256M
#set-variable    = sort_buffer=256M
#set-variable    = read_buffer=2M
#set-variable    = write_buffer=2M
set-variable    = key_buffer=25M
set-variable    = sort_buffer=25M
set-variable    = read_buffer=2M
set-variable    = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

-----------------------
below is the relevantpart  of /var/lib/mysql/linux.err:


/usr/sbin/mysqld: ready for connections
020731 18:08:23  /usr/sbin/mysqld: Normal shutdown

020731 18:08:24  InnoDB: Starting shutdown...
020731 18:15:02  mysqld started
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 32 1574840608
020731 18:15:09  InnoDB: Started
/usr/sbin/mysqld: ready for connections
020731 19:47:45  /usr/sbin/mysqld: Normal shutdown

020731 19:47:45  InnoDB: Starting shutdown...
020731 22:43:18  mysqld started
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 32 1574840618
020731 22:43:26  InnoDB: Started
/usr/sbin/mysqld: ready for connections

Status information:

Current dir: /var/lib/mysql/
Current locks:
key_cache status:
blocks used:        10
not flushed:         0
w_requests:          0
writes:              0
r_requests:          0
reads:               0

handler status:
read_key:            0
read_next:           0
read_rnd             0
read_first:          0
write:               0
delete               0
update:              0

Table status:
Opened tables:          0
Open tables:            0
Open files:             0
Open streams:           0

Number of processes running now: 0
020816 23:01:00  mysqld restarted
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 43 1440784804
InnoDB: Doing recovery: scanned up to log sequence number 43 1440849920
InnoDB: Doing recovery: scanned up to log sequence number 43 1440915456
InnoDB: Doing recovery: scanned up to log sequence number 43 1440980992
InnoDB: Doing recovery: scanned up to log sequence number 43 1441046528
InnoDB: Doing recovery: scanned up to log sequence number 43 1441112064
InnoDB: Doing recovery: scanned up to log sequence number 43 1441177600
InnoDB: Doing recovery: scanned up to log sequence number 43 1441243136
InnoDB: Doing recovery: scanned up to log sequence number 43 1441308672
InnoDB: Doing recovery: scanned up to log sequence number 43 1441374208
InnoDB: Doing recovery: scanned up to log sequence number 43 1441439744
InnoDB: After this prints a line for every 10th scan sweep:
InnoDB: Doing recovery: scanned up to log sequence number 43 1442095104
InnoDB: Doing recovery: scanned up to log sequence number 43 1442750464
InnoDB: Doing recovery: scanned up to log sequence number 43 1443405824
InnoDB: Doing recovery: scanned up to log sequence number 43 1444061184
InnoDB: Doing recovery: scanned up to log sequence number 43 1444716544
InnoDB: Doing recovery: scanned up to log sequence number 43 1445371904
InnoDB: Doing recovery: scanned up to log sequence number 43 1446027264
InnoDB: Doing recovery: scanned up to log sequence number 43 1446682624
InnoDB: Doing recovery: scanned up to log sequence number 43 1447337984
InnoDB: Doing recovery: scanned up to log sequence number 43 1447993344
InnoDB: Doing recovery: scanned up to log sequence number 43 1448648704
InnoDB: Doing recovery: scanned up to log sequence number 43 1449304064
InnoDB: Doing recovery: scanned up to log sequence number 43 1449959424
InnoDB: Doing recovery: scanned up to log sequence number 43 1450614784
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
020816 23:08:21  InnoDB: Started
/usr/sbin/mysqld: ready for connections
InnoDB: Warning: operating system error number 4 in a file operation.
InnoDB: Cannot continue operation.

Number of processes running now: 0
020818 21:56:29  mysqld restarted
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 43 1452255266
InnoDB: Doing recovery: scanned up to log sequence number 43 1452320768
I........
InnoDB: Doing recovery: scanned up to log sequence number 43 1452845056
InnoDB: Doing recovery: scanned up to log sequence number 43 1452910592
InnoDB: After this prints a line for every 10th scan sweep:
........
overy: scanned up to log sequence number 43 1477158912

Thread
mysql just hangs!spiros18 Aug
Re: mysql just hangs!Heikki Tuuri18 Aug