| Bug #31139 | MySQL 6.0.2-alpha and Falcon and bug when trying to create thousands of tables | ||
|---|---|---|---|
| Submitted: | 21 Sep 2007 20:34 | Modified: | 1 Feb 2008 8:29 |
| Reporter: | Mariella Di Giacomo | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Falcon storage engine | Severity: | S1 (Critical) |
| Version: | 6.0.2 | OS: | Linux (Linux 2.2.6 and Debian 4.0) |
| Assigned to: | Vladislav Vaintroub | CPU Architecture: | Any |
[23 Sep 2007 4:05]
MySQL Verification Team
Application to create Falcon Tables
Attachment: bug31139.cpp (text/plain), 1.14 KiB.
[23 Sep 2007 4:40]
MySQL Verification Team
Thank you for the bug report. Verified with a Windows 64-bit server: mysql> show variables like "%version%"; +-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | protocol_version | 10 | | version | 6.0.3-alpha-nt | | version_comment | Source distribution | | version_compile_machine | unknown | | version_compile_os | Win64 | +-------------------------+---------------------+ 5 rows in set (0.03 sec) All memory values are displayed in KB. Abbreviation key: Pri Priority Thd Number of Threads Hnd Number of Handles VM Virtual Memory WS Working Set Priv Private Virtual Memory Priv Pk Private Virtual Memory Peak Faults Page Faults NonP Non-Paged Pool Page Paged Pool Cswtch Context Switches C:\>pslist 3280 -m pslist v1.28 - Sysinternals PsList Copyright ® 2000-2004 Mark Russinovich Sysinternals Process memory detail for LUAR: Name Pid VM WS Priv Priv Pk Faults NonP Page mysqld-nt 3280 104772 19592 35064 43408 5686 10 759 Name Pid VM WS Priv Priv Pk Faults NonP Page mysqld-nt 3280 383560 256480 295184 295184 71783 24 759 First output before to begin to create Falcon tables and second output after 40000 tables were created. The memory usage grows according the number of tables created.
[20 Nov 2007 4:06]
Kevin Lewis
Chris, You can probably figure out where this is crashing. Mariella Di Giacomo got a crash with 4 different configurations. The testcase is currently a .c file, but I bet you could create a stored procedure to create empty tables in a loop. Then the testcase could be put into a mysql-test-run script.
[1 Feb 2008 8:28]
Vladislav Vaintroub
The crash seems to disappear in the latest Falcon. I was able to create 500000
tables on both 64 and 32 bit Windows. The test in mysqltest format is
below and (I beter not include it into the test suite, since as it is huge - runs for one hour and consumes 10GB disk).
--source include/have_falcon.inc
SET storage_engine = Falcon;
--disable_warnings
DROP PROCEDURE IF EXISTS p;
SET GLOBAL falcon_record_memory_max = 5368709120;
--enable_warnings
delimiter //;
create procedure p ()
begin
declare v int default 1;
while v < 500000 do
select 'alter',v;
set @v = concat('create table t',v,'(i int)');
prepare stmt1 from @v;
execute stmt1;
set v = v + 1;
end while;
end//
call p()//
DROP PROCEDURE p//
delimiter ;//
[1 Feb 2008 8:29]
Vladislav Vaintroub
Can't reproduce with latest 6.0.5BK
Description: The environment is a Linux Debian 4.0 with Linux 2.6 kernel, 2 Intel(R) Xeon(TM) CPU 3.00GHz with 4 cores each. The total amount of RAM is 8 GB. The filesystem type used is ext3. (With this system/environment and MySQL 5.1.21 have been able to create and populate oven a million of tables with MyISAM and Innodb) I have compiled the source code of MySQL 6.0.2 with the options shown below: ./configure CC=gcc CFLAGS="-DBIG_JOINS=1 -O2" CXX=g++ CXXFLAGS="-DBIG_JOINS=1 -felide-constructors -fno-rtti -O2" --prefix=/usr/local/mysql-6.0.2 --localstatedir=/usr/local/mysql-6.0.2/data --libexecdir=/user/local/mysql-6.0.2/bin --datadir=/user/local/mysql-6.0.2/data -with-comment="Debian x86_64" --with-server-suffix="Debian x86_64" --enable-shared --enable-static --enable-thread-safe-client --enable-assembler --enable-local-infile --with-big-tables --with-raid --with-mysqld-user=mysql --with-libwrap --with-mysqld-ldflags=-all-static --with-vio --with-bench --with-readline --with-extra-charsets=all --with-innodb --with-isam --with-archive-storage-engine --with-csv-storage-engine --with-federated-storage-engine --with-falcon --with-heap --with-partition --with-embedded-privilege-control --with-zlib-dir=bundled --with-ssl=bundled I have disabled Innodb engine. I have included a list of the server variables, at the bottom of this file, just in case. In this case scenario I have just used the Falcon engine type and the database does not contain any other data. 1) First run I have started creating tables (without partitioning) whit engine type Falcon. After having created approximately 8,500 empty tables (approximately 30 minutes) mysqld server was still alive, you could connect with the mysql client, but any command that you would type would freeze (never come back). Below are shown the Falcon settings used: +----------------------------------+-----------------+ | Variable_name | Value | +----------------------------------+-----------------+ | falcon_checkpoint_schedule | 7 * * * * * | | falcon_debug_mask | 0 | | falcon_debug_server | OFF | | falcon_disable_fsync | OFF | | falcon_index_chill_threshold | 4 | | falcon_initial_allocation | 1073741824 | | falcon_max_transaction_backlog | 150 | | falcon_page_cache_size | 4194304 | | falcon_page_size | 4096 | | falcon_record_chill_threshold | 5 | | falcon_record_memory_max | 262144000 | | falcon_record_scavenge_floor | 50 | | falcon_record_scavenge_threshold | 67 | | falcon_scavenge_schedule | 15,45 * * * * * | | falcon_serial_log_buffers | 10 | | falcon_serial_log_dir | | | have_falcon | YES | +----------------------------------+-----------------+ 2) Second run I have re-started the jobs with 16 jobs running from a client, but in this case I had all the default settings of the falcon variables (I had reset the falcon_initial_allocation value to its default value) This run was stopped because of "Exception: record memory is exhausted" (approximately 50,000 tables were created before dying) 3) Third run I have re-started the jobs with 16 jobs running from a client, but in this case I had all the default settings of the falcon variables except the falcon_record_memory_max, which has been set to 5GB and the falcon_debug_server. show variables like "%falcon%"; +----------------------------------+-----------------+ | Variable_name | Value | +----------------------------------+-----------------+ | falcon_checkpoint_schedule | 7 * * * * * | | falcon_debug_mask | 839 | | falcon_debug_server | OFF | | falcon_disable_fsync | OFF | | falcon_index_chill_threshold | 4 | | falcon_initial_allocation | 0 | | falcon_max_transaction_backlog | 150 | | falcon_page_cache_size | 4194304 | | falcon_page_size | 4096 | | falcon_record_chill_threshold | 5 | | falcon_record_memory_max | 5368709120 | | falcon_record_scavenge_floor | 50 | | falcon_record_scavenge_threshold | 67 | | falcon_scavenge_schedule | 15,45 * * * * * | | falcon_serial_log_buffers | 10 | | falcon_serial_log_dir | | | have_falcon | YES | +----------------------------------+-----------------+ This run was able to create a little over 165,000 tables and after that the mysqld server died. 4) I have recompiled the mysql 6.0.2 with the -fomit-frame-pointer gcc option I have re-started the jobs with 16 jobs running from a client, but in this case I had all the default settings of the falcon variables except the falcon_record_memory_max, which has been set to 5GB In this case after having created approximately 40,000 tables, mysqld died again. show variables like "%falcon%"; +----------------------------------+-----------------+ | Variable_name | Value | +----------------------------------+-----------------+ | falcon_checkpoint_schedule | 7 * * * * * | | falcon_debug_mask | 839 | | falcon_debug_server | OFF | | falcon_disable_fsync | OFF | | falcon_index_chill_threshold | 4 | | falcon_initial_allocation | 0 | | falcon_max_transaction_backlog | 150 | | falcon_page_cache_size | 4194304 | | falcon_page_size | 4096 | | falcon_record_chill_threshold | 5 | | falcon_record_memory_max | 5368709120 | | falcon_record_scavenge_floor | 50 | | falcon_record_scavenge_threshold | 67 | | falcon_scavenge_schedule | 15,45 * * * * * | | falcon_serial_log_buffers | 10 | | falcon_serial_log_dir | | | have_falcon | YES | +----------------------------------+-----------------+ Thanks for your help, Mariella How to repeat: Re-run the table creation with the same Falcon and MySQL settings.