Re: mysql server crashes - high context switch rate - large 'merge' table
In message <[EMAIL PROTECTED]>, Johannes B. Ullrich <[EMAIL PROTECTED]> writes I am having problems with MySQL 4.0.12 on RedHat Advanced Server 2.1 using a dual Xeon with 8 GByte of RAM. I have a database collecting logs. Each day, a new table is created. In order to allow for queries across more than one day, I use 'MERGE' tables. Each individual table contains 10-30 Million rows. The merge table covers 30 days (one month). At the start of a new day, the merge table is rebuild using an 'alter table' statement. However, this statement is sometimes locked for quite a while, if queries against the table are pending. In this case, the context switch rate of the server 'explodes' to 100,000 and higher. The server becomes essentially unresponsive. Usually, I can 'fix' things by killing some queries that lock the 'alter table' querie. However, this causes the server to crash in some cases (not well reproducable). Error message from log: :Snipped. Can I suggest you try creating a second merge table with a different name and then doing a double rename to swap the two merge tables i.e. "rename table merge to merge_old, merge_new to merge". Anyway that's what I'd try next. Cheers -- Chad Hanna Chairman Berkshire Family History Society www.berksfhs.org.uk Quality Family History Data www.familyhistoryonline.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding index fails with error 1034: 126
In message <[EMAIL PROTECTED]>, Andrew Braithwaite <[EMAIL PROTECTED]> writes Hi, What are the sizes of your index files? (i.e. name_index2.MYI in the datadir). It's a complete guess but with a large table like that, it's possible that you're reaching some kind of limit with your os/mysql setup due to file size. (greater than 4 GB would be my guess) Cheers, Andrew Thanks, Files aren't huge - row size is 86 bytes. name_index2.MYD 1190595954 name_index2.MYI would go over 1 Gbyte as well. It feels like some limit within MySQL - perhaps thrashing in the key cache (not that I know what I'm talking about) - but that should just make it much slower, shouldn't it? For better, or more probably worse, I'm operating with some swap (1 Gbyte RAM with 256 Mbyte swap). Using the huge configuration (for 1 to 2 Gbytes RAM) makes no difference, errors after an hour. Dropping the first two indexes, and then adding the troublesome third index on its own works. .MYI Index size is 212,271,104 bytes takes 5min 2.83 secs. Cheers, Chad -Original Message- From: Chad Hanna [mailto:[EMAIL PROTECTED] Sent: Tuesday 01 April 2003 17:03 To: [EMAIL PROTECTED] Subject: Adding index fails with error 1034: 126 Hi, I've been bashing my brains out for a day or two on this, so I'd appreciate some pointers. When altering a table to add indexes I get a message like : error 1034: 126 at record pos 957847618 mysql -V tells me: mysql Ver 11.18 Distrib 3.23.51, for unknown-freebsdelf4.6.2 (i386) myisamchk -dv name_index2 tells me: myisamchk -dv name_index2 MyISAM file: name_index2 Record format: Fixed length Character set: latin1 (8) File-version:1 Creation time: 2003-04-01 13:32:18 Recover time:2003-04-01 14:26:25 Status: checked Data records: 13844139 Deleted blocks: 0 Datafile parts: 13844139 Deleted data: 0 Datafile pointer (bytes):4 Keyfile pointer (bytes):3 Datafile length:1190595954 Keyfile length: 522063872 Max datafile length: 369367187454 Max keyfile length: 17179868159 Recordlength: 86 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 4 multip. unsigned long 0412131328 1024 6 4 unsigned long 0 2 1120 multip. char packed stripped 0 97328128 1024 5520 char stripped 0 803 char 0 show create table name_index2 tells me the create statement is: CREATE TABLE `name_index2` ( `dataset_vers_id` int(10) unsigned NOT NULL default '0', `row_id` int(10) unsigned NOT NULL default '0', `quality_level` tinyint(4) NOT NULL default '100', `surname` char(20) NOT NULL default '', `emanrus` char(20) NOT NULL default '', `surname_id` int(10) unsigned NOT NULL default '0', `forename` char(20) NOT NULL default '', `forename_id` int(10) unsigned NOT NULL default '0', `forename_pos` tinyint(4) NOT NULL default '0', `place_code` char(3) NOT NULL default '', `start_year` smallint(6) NOT NULL default '0', `end_year` smallint(6) NOT NULL default '2100', KEY `name_index_idx1` (`dataset_vers_id`,`row_id`), KEY `name_index_idx2` (`surname`,`forename`,`place_code`) ) TYPE=MyISAM I've been adding the indexes one at time to try and isolate the problem, the failure occurs when I add: alter table name_index2 add index name_index_idx3 (surname_id, forename_id, place_code); Possibly important: 744845 data records (~ 5%) have a surname_id = 0 I'm using the 'large' my.cnf from the distribution with a couple of tweaks. A couple of weeks ago, a 10% smaller table took 45 minutes to add the indexes. This one an hour to add two indexes and several hours to fail to add all four. I'm getting to the stage when I'm considering MERGE tables. Any ideas welcome! -- Chad Hanna Chairman Berkshire Family History Society www.berksfhs.org.uk -- Chad Hanna Chairman Berkshire Family History Society www.berksfhs.org.uk Quality Family History Data www.familyhistoryonline.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding index fails with error 1034: 126
Hi, I've been bashing my brains out for a day or two on this, so I'd appreciate some pointers. When altering a table to add indexes I get a message like : error 1034: 126 at record pos 957847618 mysql -V tells me: mysql Ver 11.18 Distrib 3.23.51, for unknown-freebsdelf4.6.2 (i386) myisamchk -dv name_index2 tells me: myisamchk -dv name_index2 MyISAM file: name_index2 Record format: Fixed length Character set: latin1 (8) File-version:1 Creation time: 2003-04-01 13:32:18 Recover time:2003-04-01 14:26:25 Status: checked Data records: 13844139 Deleted blocks: 0 Datafile parts: 13844139 Deleted data: 0 Datafile pointer (bytes):4 Keyfile pointer (bytes):3 Datafile length:1190595954 Keyfile length: 522063872 Max datafile length: 369367187454 Max keyfile length: 17179868159 Recordlength: 86 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 4 multip. unsigned long 0412131328 1024 6 4 unsigned long 0 2 1120 multip. char packed stripped 0 97328128 1024 5520 char stripped 0 803 char 0 show create table name_index2 tells me the create statement is: CREATE TABLE `name_index2` ( `dataset_vers_id` int(10) unsigned NOT NULL default '0', `row_id` int(10) unsigned NOT NULL default '0', `quality_level` tinyint(4) NOT NULL default '100', `surname` char(20) NOT NULL default '', `emanrus` char(20) NOT NULL default '', `surname_id` int(10) unsigned NOT NULL default '0', `forename` char(20) NOT NULL default '', `forename_id` int(10) unsigned NOT NULL default '0', `forename_pos` tinyint(4) NOT NULL default '0', `place_code` char(3) NOT NULL default '', `start_year` smallint(6) NOT NULL default '0', `end_year` smallint(6) NOT NULL default '2100', KEY `name_index_idx1` (`dataset_vers_id`,`row_id`), KEY `name_index_idx2` (`surname`,`forename`,`place_code`) ) TYPE=MyISAM I've been adding the indexes one at time to try and isolate the problem, the failure occurs when I add: alter table name_index2 add index name_index_idx3 (surname_id, forename_id, place_code); Possibly important: 744845 data records (~ 5%) have a surname_id = 0 I'm using the 'large' my.cnf from the distribution with a couple of tweaks. A couple of weeks ago, a 10% smaller table took 45 minutes to add the indexes. This one an hour to add two indexes and several hours to fail to add all four. I'm getting to the stage when I'm considering MERGE tables. Any ideas welcome! -- Chad Hanna Chairman Berkshire Family History Society www.berksfhs.org.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
