Hello mysql,
I've recently tried to do a test of mysqld with big tables (>4GB) on
linux 2.4 mysql 3.23.30
1) The strange thing is even without setting up max_rows got a
table to grow for more than 4Gb with insert test. Is this possible ?
(May be you use special pointers for static rows, there position is
counted by multiplying an offset by a row size). Anyway as I
remember I hit 4GB limit then doing the same test in 3.23.29
GLBC used is 2.1.3 from SUSE distribution.
2)Both times (with max_rows=1000000000 and with no maxrows) I got
mysql to hit some limit.
drwxrw---- 2 mysql daemon 4096 Jan 11 12:51 .
drwxr-xr-x 19 mysql daemon 4096 Jan 11 12:50 ..
-rw-rw---- 1 mysql daemon 6609670144 Jan 11 13:23 bench1.MYD
-rw-rw---- 1 mysql daemon 126694400 Jan 11 13:21 bench1.MYI
-rw-rw---- 1 mysql daemon 8622 Jan 11 12:51 bench1.frm
mysql was showing the following status:
ruby:/spylog/db/test # mysqladmin processlist | grep -v Sleep
+----+------+-----------+------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
| |
+----+------+-----------+------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| 2 | root | localhost | test | Query | 224 | update | insert into bench1 values
|(31030041,31030041,31030041,'ABCDEFGHIJ'),(31030042,31030042,31030042,'ABC |
| 6 | root | localhost | | Query | 0 | | show processlist
| |
+----+------+-----------+------+---------+------+--------+------------------------------------------------------------------------------------------------------+
and the query was unable to be killed.
I connected to this process by GDB and looked the followings:
/spylog/db/test/11303: No such file or directory.
Attaching to program: /usr/local/mysql/libexec/mysqld, Thread 11303
0x8143211 in __libc_nanosleep ()
(gdb) bt
#0 0x8143211 in __libc_nanosleep ()
#1 0x81431ad in __sleep (seconds=60) at ../sysdeps/unix/sysv/linux/sleep.c:82
#2 0x8101ca1 in my_write (Filedes=9, Buffer=0x820bc01 ' ' <repeats 164 times>,
Count=164, MyFlags=36) at my_write.c:55
#3 0x81090ae in my_raid_write (fd=9,
Buffer=0x820bbd0 "Ü\023\200ý\001\023\200ý\001\023\200ý\001ABCDEFGHIJ", ' '
<repeats 177 times>..., Count=213, MyFlags=36)
at raid.cc:206
#4 0x80e94e2 in _mi_write_static_record (info=0x820c310,
record=0x820bbd0 "Ü\023\200ý\001\023\200ý\001\023\200ý\001ABCDEFGHIJ", ' '
<repeats 177 times>...) at mi_statrec.c:69
#5 0x80ef03e in mi_write (info=0x820c310,
record=0x820bbd0 "Ü\023\200ý\001\023\200ý\001\023\200ý\001ABCDEFGHIJ", ' '
<repeats 177 times>...) at mi_write.c:133
#6 0x80b87f9 in ha_myisam::write_row (this=0x820b848,
buf=0x820bbd0 "Ü\023\200ý\001\023\200ý\001\023\200ý\001ABCDEFGHIJ", ' ' <repeats
177 times>...) at ha_myisam.cc:233
#7 0x809ed09 in write_record (table=0x82103a8, info=0xbf5ffa24) at sql_insert.cc:379
#8 0x809e725 in mysql_insert (thd=0x8208fb0, table_list=0x8209780, fields=@0x8209210,
values_list=@0x8209144, duplic=DUP_ERROR,
lock_type=TL_WRITE_CONCURRENT_INSERT) at sql_insert.cc:231
#9 0x807c661 in mysql_execute_command () at sql_parse.cc:1323
#10 0x807e168 in mysql_parse (thd=0x8208fb0,
inBuf=0x6021c018 "insert into bench1 values
(31030041,31030041,31030041,'ABCDEFGHIJ'),(31030042,31030042,31030042,'ABCDEFGHIJ'),(31030043,31030043,31030043,'ABCDEFGHIJ'),(31030044,31030044,31030044,'ABCDEFGHIJ'),(31030"...,
length=999961) at sql_parse.cc:2017
#11 0x807aa5f in do_command (thd=0x8208fb0) at sql_parse.cc:652
#12 0x807a0e7 in handle_one_connection (arg=0x8208fb0) at sql_parse.cc:403
#13 0x811fd76 in pthread_start_thread (arg=0xbf5ffe40) at manager.c:241
(gdb) quit
well this may look like not enough space on the disk - but really
there are a lot of space free.
myisamchk afterwards shows the following:
Checking MyISAM file: bench1.MYI
Data records: 31031315 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed
- check file-size
myisamchk: error: Size of indexfile is: 126697472 Should be: 637411328
myisamchk: warning: Size of datafile is: 6609670144 Should be: 6609670095
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Can't read indexpage from filepos: 209841152
--
Best regards,
Peter mailto:[EMAIL PROTECTED]
---------------------------------------------------------------------
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
