Hi,
I am sending this mail in order to get things straight
about table corruption which I am experiencing with
4.0(as well as previous versions).
1)System specs: PIII x1000MHz, 1GB RAM, HD 37GB SCSI,
AHA29160N SCSI controller
2) Database MyISam tables, BUT 21000 tables in the
database(this is for performance reasons. I need
for each such table to run a number of queries and in
this way I already have 'VIEWS', except they are
permament). I could split that in 4 different databases
if it is a problem.
3) I import the data from mysqldumps(that is, the
program that reads the data does not directly import
them in the database, partly because of the corruption
problems, but also partly because I want backups anyway
and instead produces files that are importable just
like mysqldumps. These files are of the form
'INSERT INTO TABLE1 (.....);'
....
conceivably for all 21000 tables.
4) Corruption is a tossup. The import always works. (I mean I never get complaints)
However, when I run the queries sometimes they all run
fine, while other times I get an error 127
on the table it is working on.
myisamchk NEVER manages a complete recovery of all
records(typically 25% of the records). This makes it
hard to tell for sure if this has anythingto do with
the size of data imported, since the more files I
import the larger the probability of having corruption.
I have had corruption on a single import too though.
5)Before writing a bug report(the problem may be that
this is erratic and even on my PC corruption is not
consistent, e.g. I may import a file and get
corruption, or I may import another file without
corruption) and hence reproducibility may be
problematic., I would appreciiate someone setting me
straight on the filedescriptor issues(thanks Sinisa
thus far):
-It is not clear to me if the problem may be that when
mysql is importing data, it may be having more files
open than it(or the fs-reiser in this case) can handle.
Could this be the reason for (table)corruption?
-If so, accordingto the manual, there are 2 ways around:
either decrease table_cache in /etc/my.cnf
(currently 512) which however may also affect
performance
as I have a lot of tables
or increase ulimit/open-files-limit
-The question on this first option(table_cache) is:
if now I have 2000 open files and reduce the cache to
256 does it mean I will be getting half the open files?
-Regarding the more desirable option of increasing the
limit, are the two ways equivalent(that is, does open-
file-limit call ulimit?) Is changing either of them
enough , or-as I assume does one really have to change
ulimit?
-I have a ulimit -a limit giving a 1024 open file limit
while cat /proc/sys/fs/file-nr gives a hard limit of
8192.
However, the man page says the system may not allow
you to change ulimit
I did a ulimit -n 8192 as root
then ulimit -a limit says 8192
but when I open a window as a regular user, and do
ulimit -a limit it is still 1024. Rebooting
comes back with 1024
Is it at all possible to change it?
-Also, if I can/have to change open-files-limit,
do I need to do it in
/etc/my.cnf
/usr/bin/mysqld_safe
/both?
Last,
I have a start_Mysql=yes in rc.config
but in contrast to other systems used mysql, mysql does
not start when the system boots. Any ideas why?
-----------------------------
P.S. At some point I had the following very strange behavior
I go to /var/lib/mysql/mydatabase and erase this table
(the .frm,
.MYI and .MYD files. ) then I recreate the table
(justthe definition)
from file 1330.sql
No complaints. Then I connect to the database and get
the COUNT
It says 23, which was the count I believe before the
corruption
EVEN THOUGH 1330.sql has NO data, just the table
definitions.
Then I try to SELECT a field and it says error 127.
Could this be related to my large # of tables? Are
there any settings say in my.cnf to change?
.............................
myuser@quality5:~/BACKUPN > mysql -u myuser -p CDR <
1330.sql
Enter password:
myuser@quality5:~/BACKUPN > mysql -u myuser -pxxxxxx CDR
Welcome to the MySQL monitor. Commands end with ; or
\\g.
Your MySQL connection id is 479 to server version:
4.0.0-alpha-log
Type 'help;' or '\\h' for help. Type '\\c' to clear the
buffer.
mysql> SELECT COUNT(*) FROM TABLE_1330_1;
+----------+
| COUNT(*) |
+----------+
| 23 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT file FROM TABLE_1330_1;
ERROR 1030: Got error 127 from table handler
mysql>
/var/log/messages has nothing relevant
# 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=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=8
set-variable = thread_concurrency=2 # Try number of
CPU's*2
set-variable = myisam_sort_buffer_size=64M
log-bin
server-id = 1
# 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
[mysqlhotcopy]
interactive-timeout
I am sending this mail in order to get things straight
about table corruption which I am experiencing with
4.0(as well as previous versions).
1)System specs: PIII x1000MHz, 1GB RAM, HD 37GB SCSI,
AHA29160N SCSI controller
2) Database MyISam tables, BUT 21000 tables in the
database(this is for performance reasons. I need
for each such table to run a number of queries and in
this way I already have 'VIEWS', except they are
permament). I could split that in 4 different databases
if it is a problem.
3) I import the data from mysqldumps(that is, the
program that reads the data does not directly import
them in the database, partly because of the corruption
problems, but also partly because I want backups anyway
and instead produces files that are importable just
like mysqldumps. These files are of the form
'INSERT INTO TABLE1 (.....);'
....
conceivably for all 21000 tables.
4) Corruption is a tossup. The import always works. (I mean I never get complaints)
However, when I run the queries sometimes they all run
fine, while other times I get an error 127
on the table it is working on.
myisamchk NEVER manages a complete recovery of all
records(typically 25% of the records). This makes it
hard to tell for sure if this has anythingto do with
the size of data imported, since the more files I
import the larger the probability of having corruption.
I have had corruption on a single import too though.
5)Before writing a bug report(the problem may be that
this is erratic and even on my PC corruption is not
consistent, e.g. I may import a file and get
corruption, or I may import another file without
corruption) and hence reproducibility may be
problematic., I would appreciiate someone setting me
straight on the filedescriptor issues(thanks Sinisa
thus far):
-It is not clear to me if the problem may be that when
mysql is importing data, it may be having more files
open than it(or the fs-reiser in this case) can handle.
Could this be the reason for (table)corruption?
-If so, accordingto the manual, there are 2 ways around:
either decrease table_cache in /etc/my.cnf
(currently 512) which however may also affect
performance
as I have a lot of tables
or increase ulimit/open-files-limit
-The question on this first option(table_cache) is:
if now I have 2000 open files and reduce the cache to
256 does it mean I will be getting half the open files?
-Regarding the more desirable option of increasing the
limit, are the two ways equivalent(that is, does open-
file-limit call ulimit?) Is changing either of them
enough , or-as I assume does one really have to change
ulimit?
-I have a ulimit -a limit giving a 1024 open file limit
while cat /proc/sys/fs/file-nr gives a hard limit of
8192.
However, the man page says the system may not allow
you to change ulimit
I did a ulimit -n 8192 as root
then ulimit -a limit says 8192
but when I open a window as a regular user, and do
ulimit -a limit it is still 1024. Rebooting
comes back with 1024
Is it at all possible to change it?
-Also, if I can/have to change open-files-limit,
do I need to do it in
/etc/my.cnf
/usr/bin/mysqld_safe
/both?
Last,
I have a start_Mysql=yes in rc.config
but in contrast to other systems used mysql, mysql does
not start when the system boots. Any ideas why?
-----------------------------
P.S. At some point I had the following very strange behavior
I go to /var/lib/mysql/mydatabase and erase this table
(the .frm,
.MYI and .MYD files. ) then I recreate the table
(justthe definition)
from file 1330.sql
No complaints. Then I connect to the database and get
the COUNT
It says 23, which was the count I believe before the
corruption
EVEN THOUGH 1330.sql has NO data, just the table
definitions.
Then I try to SELECT a field and it says error 127.
Could this be related to my large # of tables? Are
there any settings say in my.cnf to change?
.............................
myuser@quality5:~/BACKUPN > mysql -u myuser -p CDR <
1330.sql
Enter password:
myuser@quality5:~/BACKUPN > mysql -u myuser -pxxxxxx CDR
Welcome to the MySQL monitor. Commands end with ; or
\\g.
Your MySQL connection id is 479 to server version:
4.0.0-alpha-log
Type 'help;' or '\\h' for help. Type '\\c' to clear the
buffer.
mysql> SELECT COUNT(*) FROM TABLE_1330_1;
+----------+
| COUNT(*) |
+----------+
| 23 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT file FROM TABLE_1330_1;
ERROR 1030: Got error 127 from table handler
mysql>
/var/log/messages has nothing relevant
# 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=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=8
set-variable = thread_concurrency=2 # Try number of
CPU's*2
set-variable = myisam_sort_buffer_size=64M
log-bin
server-id = 1
# 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
[mysqlhotcopy]
interactive-timeout