Description:
Server Details:
---------------------------------------------------------------------------------------------------
mysql-4.0.17-standard (Official MySQL RPM)
C compiler: 2.95.3
C++ compiler: 2.95.3
Dual GenuineIntel Pentium III (Katmai) 550 MHZ
Arch: i686 Cache: 512 KB
Vendor: Intel Memory: 1048 MB Stepping: 3
Family: 6 Swap: 2047 MB
Chipset: i440BX-8671 Bios: Award Software International, Inc. 4.51 PG - 10/12/00
Disk: 80GB Western Digital IDE 2MB Cache
Red Hat 9 (with latest updates from Red Hat Network)
System: Linux rh1 2.4.20-24.9smp #1 SMP Mon Dec 1 11:25:17 EST 2003 i686 i686 i386 GNU/Linux
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux
Thread model: posix
gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='g++' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Nov 15 08:31 /lib/libc.so.6 -> libc-2.3.2.so
-rwxr-xr-x 1 root root 1561228 Nov 13 13:56 /lib/libc-2.3.2.so
-rw-r--r-- 1 root root 2332200 Nov 13 13:18 /usr/lib/libc.a
-rw-r--r-- 1 root root 204 Nov 13 12:49 /usr/lib/libc.so
lrwxrwxrwx 1 root root 10 Sep 26 06:43 /usr/lib/libc-client.a -> c-client.a
Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '--without-embedded-server' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=' 'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' 'CXX='
Description:
---------------------------------------------------------------------------------------------------
MySQL 4.0.15 standard and MySQL 4.0.17 standard tested, both with the same results.
All work is done using the server (logging in with PuTTY as the ssh client on Win2k).
Using the benchmarking toolkit benchw from http://benchw.sourceforge.net. This is a data warehousing style benchmarking kit that creates 4 tables: 1 fact and 3 dimensions. The toolkit allows you to specify the size of the files you want to generate and load.
Using size 1 (1GB), all tests ran fine. However, when I generated data files for size 10 (10GB) and ran the load.sql script I came across a problem:
--
MySQL client drops connection to the MySQL database after loading exactly 36,126,181 records every time loading of the fact0 table.
--
The total file size for this set of data is only a few bytes short of 4GB, so I have checked this avenue (max file size), but obviously, the data file I'm loading is far greater in size than 4GB. I couldn't see anything in the MySQL configure line below that pointed to limiting table size to 4GB and what I've read about MySQL it is only limited by the underlying file system.
I've taken a look through bug histories and other lists but cannot find anything that sounds like this, although some have had the same error, but not loading data. I have changed the wait_timeout and interactive-timeout vars in my.cnf to 86400 and tried but no luck there either. my.cnf is at the end of this mail, as are the results of show variables.
MySQL continues to work in all facets, except that the data loading fails. Also, I can add one more record to the table and then I get the "Table full" error (details below):
mysql> select count(*) from fact0;
+----------+
| count(*) |
+----------+
| 36126181 |
+----------+
1 row in set (0.02 sec)
mysql> insert into fact0(d0key) values(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into fact0(d0key) values(1);
ERROR 1114: The table 'fact0' is full
MySQL Dump of table fact0:
---------------------------------------------------------------------------------------------------
/usr/local/benchw/dump> mysqldump --no-data -p benchw fact0
Enter password:
-- MySQL dump 9.10
--
-- Host: localhost Database: benchw
-- ------------------------------------------------------
-- Server version 4.0.17-standard-log
--
-- Table structure for table `fact0`
--
CREATE TABLE fact0 (
d0key int(11) NOT NULL default '0',
d1key int(11) NOT NULL default '0',
d2key int(11) NOT NULL default '0',
fval int(11) NOT NULL default '0',
ffill varchar(100) NOT NULL default ''
) TYPE=MyISAM;
Table status post-loading of data:
---------------------------------------------------------------------------------------------------
mysql> show table status;
+-------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment |
+-------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
| dim0 | MyISAM | Fixed | 100000 | 20 | 2000000 | 85899345919 | 1024 | 0 | NULL | 2003-12-21 14:23:16 | 2003-12-21 14:23:16 | NULL | | |
| dim1 | MyISAM | Dynamic | 100000 | 151 | 15199116 | 4294967295 | 1024 | 0 | NULL | 2003-12-21 14:23:16 | 2003-12-21 14:23:18 | NULL | | |
| dim2 | MyISAM | Dynamic | 100000 | 151 | 15199076 | 4294967295 | 1024 | 0 | NULL | 2003-12-21 14:23:16 | 2003-12-21 14:23:20 | NULL | | |
| dual | MyISAM | Fixed | 1 | 5 | 5 | 21474836479 | 1024 | 0 | NULL | 2003-12-14 15:18:17 | 2003-12-14 15:18:51 | NULL | | |
| fact0 | MyISAM | Dynamic | 36126182 | 118 | 4294967276 | 4294967295 | 1024 | 0 | NULL | 2003-12-21 14:23:16 | 2003-12-21 14:39:44 | NULL | | |
+-------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
5 rows in set (0.00 sec)
MySQL Variables:
---------------------------------------------------------------------------------------------------
mysql> show variables;
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON |
| connect_timeout | 2 |
| convert_character_set | |
| datadir | /u02/mysql/data/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | DISABLED |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| interactive_timeout | 86400 |
| join_buffer_size | 131072 |
| key_buffer_size | 268435456 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| local_infile | ON |
| locked_in_memory | OFF |
| log | ON |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | OFF |
| max_allowed_packet | 134216704 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 8192 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 1024 |
| pid_file | /u02/mysql/data/rh1.pid |
| log_error | /u02/mysql/log/mysql-error.log |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 5242880 |
| query_cache_size | 134217728 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 16773120 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 16777208 |
| sql_mode | 0 |
| table_cache | 256 |
| table_type | MYISAM |
| thread_cache_size | 8 |
| thread_stack | 126976 |
| tx_isolation | REPEATABLE-READ |
| timezone | NZDT |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.17-standard-log |
| version_comment | Official MySQL RPM |
| wait_timeout | 86400 |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
my.cnf:
---------------------------------------------------------------------------------------------------
/etc> cat my.cnf
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld deamon
# doesn't use much resources.
#
# 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]
port = 3306
socket = /var/lib/mysql/mysql.sock
# The MySQL server
[mysqld]
skip-innodb
skip-bdb
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /u02/mysql/data
log = /u02/mysql/log/mysql.log
log-error = /u02/mysql/log/mysql-error.log
safe-user-create
skip-external-locking
query_cache_limit = 5M
query_cache_size = 128M
query_cache_type = 1
key_buffer = 256M
max_allowed_packet = 128M
table_cache = 256
sort_buffer_size = 16M
myisam_sort_buffer_size = 64M
read_buffer_size = 16M
thread_cache = 8
thread_concurrency = 4
net_buffer_length = 8k
server-id = 1
tmpdir = /tmp
connect_timeout = 2
wait_timeout = 86400
interactive_timeout = 86400
[mysqldump]
quick
max_allowed_packet = 128M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 128M
sort_buffer = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive_timeout = 86400
How to repeat:
Using the benchw toolkit, drop the tables and re-runn the schema.sql and load.sql scripts again.
Suggested fix:
Unknown
