Hi All, A little background here... I have recently moved over to V 5.0.18-standard from 4.1.14. Pardon my ignorance, but after 2 very late nights, I am about getting to my wits end :) This move wasn't entirely planned. A coworker started a "alter table..." command via mysql control center to change a table from myISAM to INNODB. 7 hours later, I killed off the process and tried to avoid a 'roll back' by deleting the #sql....ibd file. Several hours later... I was able to finally bring up the server using innodb_force_recovery = 3, exported everything using mysql_dump, then brought 5.0.18 online and imported it in. I had one table with 7 million records (in a mysql_dump file) that took about 15 hours to import. I think the reason had to do with the record size and my buffer settings. Anyway ... I have my my.cnf tweaked based on the my-innodb-heavy-4G.cnf file. things seem to be working better :) This is a 2 processor machine (P4 3.0 GHZ), 4 GB Ram. My innodb specific settings are : innodb_data_home_dir = /usr/local/mysql/data innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_log_arch_dir = /usr/local/mysql/data innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 256M innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_lock_wait_timeout = 150 innodb_thread_concurrency = 8 innodb_file_per_table On a "show table status" for this table in question, I get Data_free=0, which concerns me - but I think may explain the 15 hour import (if mysql had to continously allocate more disk space for this table). Is there a way to preallocate space for a innodb table (using innodb_file_per_table)? ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 2178, signal count 2175 Mutex spin waits 3141, rounds 9508, OS waits 281 RW-shared spins 3407, OS waits 1700; RW-excl spins 308, OS waits 184 Are these numbers good or bad?
Record lock, heap no 170 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd482e; asc C H.;; 1: len 30; hex 383436396631633237643365313164616232636464396666326565316230; asc 8469f1c27d3e11dab2cdd9ff2ee1b0;...(truncated); 2: len 4; hex 02786cb7; asc xl ;; Record lock, heap no 171 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd4838; asc C H8;; 1: len 30; hex 393138666333633037643365313164613837663265653063623736623262; asc 918fc3c07d3e11da87f2ee0cb76b2b;...(truncated); 2: len 4; hex 0278aa6c; asc x l;; Record lock, heap no 172 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd4852; asc C HR;; 1: len 30; hex 333439383934393137656431313164616162326430303033626165363063; asc 349894917ed111daab2d0003bae60c;...(truncated); 2: len 4; hex 0278def2; asc x ;; Record lock, heap no 177 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd4826; asc C H&;; 1: len 30; hex 666130613938653737643139313164616161626166326232666362663665; asc fa0a98e77d1911daaabaf2b2fcbf6e;...(truncated); 2: len 4; hex 027860bf; asc x` ;; Record lock, heap no 178 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd481b; asc C H ;; 1: len 30; hex 383030653537353437643365313164616239643065316530366133383835; asc 800e57547d3e11dab9d0e1e06a3885;...(truncated); 2: len 4; hex 027858dc; asc xX ;; Record lock, heap no 179 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd4848; asc C HH;; 1: len 30; hex 306534366136616537643161313164616162353166326232666362663665; asc 0e46a6ae7d1a11daab51f2b2fcbf6e;...(truncated); 2: len 4; hex 02782af8; asc x* ;; Record lock, heap no 180 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd4851; asc C HQ;; 1: len 30; hex 366337623533343937643366313164616231303264666539626137616233; asc 6c7b53497d3f11dab102dfe9ba7ab3;...(truncated); 2: len 4; hex 027845de; asc xE ;; Also - is something difference with grants? I get a signal 11 and mysql restarts when I try to give a grant... statement 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=536870912 read_buffer_size=2093056 max_used_connections=2 max_connections=200 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2571486 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x9b08ac0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbf43ae84, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x81536e8 0xffffe420 (nil) 0x8169819 0x816e176 0x8165c3e 0x8165769 0x8164c71 0x40031aa7 0x40166c2e New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x9b17fb8 = grant all on wholesale.pending_rates_bak to 'rates'@'10.%' identified by '' thd->thread_id=2 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 060106 10:05:05 mysqld restarted 060106 10:05:05 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 060106 10:05:05 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 4 486610513. InnoDB: Doing recovery: scanned up to log sequence number 4 489673016 060106 10:05:06 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 0, file name 060106 10:05:08 InnoDB: Started; log sequence number 4 489673016 060106 10:05:08 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.0.18-standard' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) George Law VoIP Network Developer 864-678-3161 [EMAIL PROTECTED] MSN: [EMAIL PROTECTED]
