Hi Maximo, The problem is that you have a config that requires more memory than you have available. Please reduce key buffer, record buffer and sort buffer. The clues are: 030117 17:02:55 Out of memory; Check if mysqld or some other process uses key_buffer_size + (record_buffer + sort_buffer)*max_connections = 3859309 K
3859309K= almost 4G! try key_buffer_size=64M record_buffer=1M sort_buffer=1M max_connections=256 Do you really need 10000 connections? Best of luck, Ken ----- Original Message ----- From: "Maximo Migliari" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, January 17, 2003 6:28 PM Subject: FreeBSD: MySQL crash when importing 148 MB dumpfile > Problem: I am migrating servers, from a dual PIII-866 Mhz with 384 MB of > RAM running Linux to the following server configuration: > > Dual Athlon 1.5 Ghz > 1 GB RAM > 2 x 18 GIG SCSI drives. > FreeBSD 4.7-Stable > MySQL-Max-3.23.54a (ELF) i.e. without linuxthreads - runs on only 1 process. > > I am trying to import a database that was saved using mysqldump into the > new machine using a method that has NEVER failed with me until now - that > is, to do: mysql -uroot -pmypassword tablename < tablename.sql. > > Here is how I did it, and the error message: > > [root@server1 admin]# mysql -uroot -pxxxxxxx _final <_final.sql > ERROR 2013 at line 1814: Lost connection to MySQL server during query > [root@server1 admin]# 030117 17:02:55 mysqld restarted > > I have attempted this on both: > MySQL-Max-3.23.54a and the latest MySQL 4 gamma release for FreeBSD. > > When importing other databases, it works fine, but for this larger file, > the server is crashing. I have the same version of the software running on > another server, a dual Intel 866 Mhz Linux system with only 384 MB of RAM, > and this sort of thing ALWAYS works. > I am using the same my.cnf on both systems, I have pasted my.cnf at the end > of this e-mail. > > This is what I get in the .err file after the server crashes and restarts: > ------------------------------------------------- > [root@server1 data]# cat server1.err > 030117 17:01:22 mysqld started > 030117 17:01:22 Warning: setrlimit returned ok, but didn't change limits. > Max open files is 11095 > 030117 17:01:22 Warning: Changed limits: max_connections: > 10000 table_cache: 542 > 030117 17:01:23 InnoDB: Started > /usr/local/mysql-max-3.23.54a/bin/mysqld: ready for connections > 030117 17:02:55 Out of memory; Check if mysqld or some other process uses > all available memory. If not you may have to use 'ulimit' to allow mysqld > to use more memory or you can add more swap space > mysqld got signal 11; > This could be because you hit a bug. It is also possible that this binary > or one of the libraries it was linked against is corrupt, improperly built, > or misconfigured. This error can also be caused by malfunctioning hardware. > We will try our best to scrape up some info that will hopefully help diagnose > the problem, but since we have already crashed, something is definitely wrong > and this may fail > > key_buffer_size=201322496 > record_buffer=1044480 > sort_buffer=1048568 > max_used_connections=0 > max_connections=10000 > threads_connected=1 > It is possible that mysqld could use up to > key_buffer_size + (record_buffer + sort_buffer)*max_connections = 3859309 K > bytes of memory > Hope that's ok, if not, decrease some variables in the equation > > 030117 17:02:55 mysqld restarted > 030117 17:02:55 Warning: setrlimit returned ok, but didn't change limits. > Max open files is 11095 > 030117 17:02:55 Warning: Changed limits: max_connections: > 10000 table_cache: 542 > 030117 17:02:56 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 0 402622121 > InnoDB: Doing recovery: scanned up to log sequence number 0 407864832 > InnoDB: Doing recovery: scanned up to log sequence number 0 413107712 > InnoDB: Doing recovery: scanned up to log sequence number 0 418350592 > InnoDB: Doing recovery: scanned up to log sequence number 0 423593472 > and this may fail > > key_buffer_size=201322496 > record_buffer=1044480 > sort_buffer=1048568 > max_used_connections=0 > max_connections=10000 > threads_connected=1 > It is possible that mysqld could use up to > key_buffer_size + (record_buffer + sort_buffer)*max_connections = 3859309 K > bytes of memory > Hope that's ok, if not, decrease some variables in the equation > > 030117 17:02:55 mysqld restarted > 030117 17:02:55 Warning: setrlimit returned ok, but didn't change limits. > Max open files is 11095 > 030117 17:02:55 Warning: Changed limits: max_connections: > 10000 table_cache: 542 > 030117 17:02:56 InnoDB: Database was not shut down normally. > .... > ...... LOTS OF INNODB RECOVERY MESSAGES... > ...... > /usr/local/mysql-max-3.23.54a/bin/mysqld: ready for connections > --------------------------- > > > I thought this might have been a kernal maxfile limitation, so I did: > sysctl kern.maxfiles=30000 > to increase maxfiles from ~11000 to 30000 > It keeps giving me the error, even after changing maxfiles. > > 90% of my tables are of type InnoDB, the rest are MyISAM > > My questions: > 1) the .err file suggests ulimit, however, I don't see why I would need > this, since on a smaller, slower machine this works just fine! > 2) is this happening because of FreeBSD's crappy threading (i.e. running > mysqld on 1 process only) ??? > > Please help me out! > below is a copy of my my.cnf: > > Maximo. > > ----- my.cnf ---- > [client] > port = 3306 > socket = /tmp/mysql.sock > > [mysqld] > port = 3306 > socket = /tmp/mysql.sock > skip-locking > set-variable = key_buffer=192M > set-variable = max_allowed_packet=1M > set-variable = table_cache=20000 > set-variable = record_buffer=1M > set-variable = sort_buffer=1M > set-variable = net_buffer_length=16K > set-variable = myisam_sort_buffer_size=64M > set-variable = thread_cache=8 > set-variable = thread_concurrency=4 > set-variable = max_connections=10000 > log-bin > server-id = 1 > > innodb_data_file_path = innodb/ibdata1:1000M:autoextend > innodb_data_home_dir = /usr/local/mysql/data/ > innodb_log_group_home_dir = /usr/local/mysql/data/innodb > innodb_log_arch_dir = /usr/local/mysql/data/innodb > set-variable = innodb_mirrored_log_groups=1 > set-variable = innodb_log_files_in_group=3 > set-variable = innodb_log_file_size=32M > set-variable = innodb_log_buffer_size=8M > innodb_flush_log_at_trx_commit=1 > innodb_log_archive=0 > set-variable = innodb_buffer_pool_size=192M > set-variable = innodb_additional_mem_pool_size=64M > set-variable = innodb_file_io_threads=4 > set-variable = innodb_lock_wait_timeout=50 > > [mysqldump] > quick > set-variable = max_allowed_packet=256M > > [mysql] > no-auto-rehash > > [isamchk] > set-variable = key_buffer=128M > set-variable = sort_buffer=128M > set-variable = read_buffer=2M > set-variable = write_buffer=2M > > [myisamchk] > set-variable = key_buffer=128M > set-variable = sort_buffer=128M > set-variable = read_buffer=2M > set-variable = write_buffer=2M > > [mysqlhotcopy] > interactive-timeout > > > -------------------------------------------------------------------- - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
