Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I can't get an import to go past 1.1gig. I have a large 4gig InnoDB table/database I would like to get on my "tried and true" box.

Current config on this box I want to work with =

  • Dell PowerEdge 1300 with dual 600MHz CPUs and with 1 GB of RAM.
  • 9GB SCSI drive
  • Windows 2003 R2
  • Just installed MySQL 5.5 on USB E: volume and I will reset to /data for E still.

I have read many posts over the past two weeks and I have tired a few things with no luck. This is one of the better ones.

I know "this old box" should be able to do it. Can someone help?

UPDATED RESTART - 6:11pm-CST

Version: '5.5.35'  socket: ''  port: 3306  MySQL Community Server (GPL)
140107 18:11:29 [Note] E:\MySQL\MySQL Server 5.5\bin\mysqld: Normal shutdown

140107 18:11:30 [Note] Event Scheduler: Purging the queue. 0 events
140107 18:11:31  InnoDB: Starting shutdown...
140107 18:11:34  InnoDB: Shutdown completed; log sequence number 4374827314
140107 18:11:35 [Note] E:\MySQL\MySQL Server 5.5\bin\mysqld: Shutdown complete

140107 18:11:48 [Note] Plugin 'FEDERATED' is disabled.
140107 18:11:48 InnoDB: The InnoDB memory heap is disabled
140107 18:11:48 InnoDB: Mutexes and rw_locks use Windows interlocked functions
140107 18:11:48 InnoDB: Compressed tables use zlib 1.2.3
140107 18:11:48 InnoDB: Initializing buffer pool, size = 18.0M
140107 18:11:48 InnoDB: Completed initialization of buffer pool
140107 18:11:48 InnoDB: highest supported file format is Barracuda.
140107 18:11:57  InnoDB: Waiting for the background threads to start
140107 18:11:58 InnoDB: 5.5.35 started; log sequence number 4374827314
140107 18:11:58 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140107 18:11:58 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140107 18:11:58 [Note] Server socket created on IP: '0.0.0.0'.
140107 18:12:02 [Note] Event Scheduler: Loaded 0 events
140107 18:12:02 [Note] E:\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections.
Version: '5.5.35'  socket: ''  port: 3306  MySQL Community Server (GPL)

I am also using these to speedup tips for for InnoDB tables:

If you use InnoDB tables instead of MyISAM tables, add these extra commands
at the top of the file to avoid a great deal of disk access:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

And add these at the end:

SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

1/07/2014 8:51am CST I am posting OK, Not sure how to get all this proper format.

    140106 16:34:28 [Note] E:\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections.
Version: '5.5.35'  socket: ''  port: 3306  MySQL Community Server (GPL)
140106 21:26:08 [Note] Plugin 'FEDERATED' is disabled.
140106 21:26:09 InnoDB: The InnoDB memory heap is disabled
140106 21:26:09 InnoDB: Mutexes and rw_locks use Windows interlocked functions
140106 21:26:09 InnoDB: Compressed tables use zlib 1.2.3
140106 21:26:11 InnoDB: Initializing buffer pool, size = 18.0M
140106 21:26:11 InnoDB: Completed initialization of buffer pool
140106 21:26:11 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 1400651686
140106 21:26:11  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...
InnoDB: Doing recovery: scanned up to log sequence number 1405894144
140106 21:26:32  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: Doing recovery: scanned up to log sequence number 1411137024
InnoDB: Doing recovery: scanned up to log sequence number 1412614848
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 3146 row operations to undo
InnoDB: Trx id counter is 900
140106 21:27:14  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: Starting in background the rollback of uncommitted transactions
140106 21:27:19  InnoDB: Rolling back trx with id 718, 3146 rows to undo

InnoDB: Progress in percents: 1140106 21:27:19  InnoDB: Waiting for the background threads to start
 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 100140106 21:27:20 InnoDB: 5.5.35 started; log sequence number 1412614848

InnoDB: Rolling back of trx id 718 completed
140106 21:27:21  InnoDB: Rollback of non-prepared transactions completed
140106 21:27:21 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140106 21:27:21 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140106 21:27:21 [Note] Server socket created on IP: '0.0.0.0'.
140106 21:27:24 [Note] Event Scheduler: Loaded 0 events
140106 21:27:24 [Note] E:\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections.
Version: '5.5.35'  socket: ''  port: 3306  MySQL Community Server (GPL)

I see no errors? OK, I open another cmd prompt and =

net stop mysql The MYSQL service is stopping.. The MYSQL service had stooped successfully

But? Task manager still has mysql.exe burning cpu. mysqld stops, masterid.ibd file size stops growing and then mysql eats CPU

Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW CREATE TABLE masterid\G
ERROR 1046 (3D000): No database selected
mysql> use business_usafull;
Database changed
mysql> SHOW CREATE TABLE masterid\G
*************************** 1. row ***************************
       Table: masterid
Create Table: CREATE TABLE `masterid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CompanyName` varchar(50) DEFAULT NULL,
  `Address` varchar(60) DEFAULT NULL,
  `City` varchar(25) DEFAULT NULL,
  `State` varchar(2) DEFAULT NULL,
  `Zip` varchar(10) DEFAULT NULL,
  `County` varchar(100) DEFAULT NULL,
  `Gender` varchar(6) DEFAULT NULL,
  `PhoneNumber` bigint(10) DEFAULT NULL,
  `FaxNumber` varchar(10) DEFAULT NULL,
  `CompanyContact` varchar(30) DEFAULT NULL,
  `CompanyContactTitle` varchar(35) DEFAULT NULL,
  `WebSite` varchar(50) DEFAULT NULL,
  `TotalEmployees` int(1) DEFAULT NULL,
  `AnnualSales` int(6) DEFAULT NULL,
  `Industry` varchar(120) DEFAULT NULL,
  `SIC_Code` int(4) DEFAULT NULL,
  `SIC_Code_Description` varchar(80) DEFAULT NULL,
  `GenericCompanyEmailAddress` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4971617 DEFAULT CHARSET=utf8
1 row in set (1.86 sec)

mysql>
  • What is the physical size of mastered.ibd?

The SQL file to import is 4,317,330 and masteridb on this dell1300 stopped @ 1,159,168 KB and this morning I tried this on HP 6550b laptop. Same MySQL 5.5 and this has 4gm ram. masterid.ibd stopped @ 1,186,988,032 "BYTES" / 1024 = Same thing

share|improve this question

migrated from superuser.com Jan 8 at 17:16

This question came from our site for computer enthusiasts and power users.

    
I see no reason why you could not import a 4GB database on an old box. It might be slow, and if MySQL tries to store it all in RAM you might need swap and it will be very slow. But no reason why it would not work. Do you get any errors when your import gets to "1.6gig or something" ? –  Hennes Jan 6 at 19:42
    
I also tried it with an XAMPP setup.. 30 minutes ago, I just un-installed 5.5 again, after installing this morning....as I could not access from command prompt...so re-installing again in a few minutes –  user283724 Jan 6 at 20:01
    
@Hennes OK, Here is where I am: stackoverflow.com/questions/3927690/… Completed that and restarted from Windos CMD line, but it said error...but ran anyway. –  user283724 Jan 6 at 22:35
    
You always will run into some limit else the proces would complete into zero time. Those limits are either caused by lack of CPU, limited IO (aka disk, or indirectly memory to buffer disk IO), or something else (e.g waiting on the network, ability to output the results to screen. THough neither seems to apply here). So when things are slow the key is trying to find out what limits things. –  Hennes Jan 7 at 4:45
    
And why I really like old hardware and a old dual socket board like in a PowerEdge 1300 takes me back in memory lane, I really hope that any pre-processing (e.g. cleaning the DB) was done on faster hardware before you tried to (re)import it. Either that or lots of patience. –  Hennes Jan 7 at 4:45
show 4 more comments

2 Answers 2

I would like to recommend turning off one more option

[mysqld]
innodb_doublewrite = 0

Setting this option to 0 disables the Double Write Buffer. Here is Pictorial Representation of ibdata1:

InnoDB Architecture

InnoDB likes to write everything it does to the Double Write Buffer located somewhere in the middle of the file ibdata1 as a precaution should there be a crash. Missing data and pages are recovered from the Double Write Buffer.

Since you are doing a big migration to a slow device, you should disable it for the duration of the migration. In this instance, just do the following:

  • net stop mysql
  • Add innodb_doublewrite = 0 to my.ini
  • net start mysql
  • Import the data
  • net stop mysql
  • Remove innodb_doublewrite = 0 from my.ini
  • net start mysql

That should speed up writes by double (HA HA).

From the comments, I see you already started it again and have been running for a while now. If the migration fails again,...

Give This a Try !!!

share|improve this answer
    
Rolando, My "Sueper Hero"...-smile- I sure was hoping you would see this sir. Also? I send a LinkedIn request and note Sunday hoping you would see it. I actually have your posts regarding InnoDB all bookmarked >MySQL>MyHero folder... But sir? The problem seems to be :bigger than speed" issue. As what is happening is @ masterid.ibd stop populating at 1,159,168KB all the time. It took from 10am until 12:55pm today again...and it stopped again. Jim –  user283724 Jan 8 at 0:06
    
[ADDED] Added and trying again, 5 minutes before was 30,000KBS with "SuperRolando" tip, 48,000 5 minutes !! Also, prior to import I use: ALTER TABLE masterid DISABLE KEYS;SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0; SET AUTOCOMMIT = 0; –  user283724 Jan 8 at 0:22
    
Still a no go.. Something is halting this. mysqld stops, and then mysql.exe process chows up 50% cpu or maybe a whole processor. Closing the cmd window...that I ran the import script..stops this after . Problem also exited using Workbench –  user283724 Jan 8 at 5:28
add comment

I would try and use WAMP Server. It might be a little overkill for what you need (WAMP- Windows, Apache, MySQL, PHP) but I have deployed and imported several DB's well over 4GB in systems with less hardware then the one you describe without issue.

share|improve this answer
    
I battled 3 weeks and 30 installs of XAMPP..lol.. Anyway, this is now just crossing 1 gig importing, so ..8.11pm.. started at 5:40.. I know it's slower as I am going USB to USB :-=) –  user283724 Jan 7 at 2:12
    
No worries, good luck. –  Ryan E Jan 7 at 5:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.