How to do cpimport for .sql files in infinDB [Migrating MySQL to InfiniDB]

6 posts / 0 new
Last post
PrabhuQueia
PrabhuQueia's picture
Offline
Last seen: 4 months 5 days ago
Joined: Mar 21 2014
Junior Boarder

Posts: 5

Prabhu Singh
How to do cpimport for .sql files in infinDB [Migrating MySQL to InfiniDB]

Hi,

 

I am trying to migrate mysql databases to infindb So i have installed infinidb server after that when I am trying to import table.sql file to infinidb using cpimport by the following,

 

./cpimport  -j 10005 agent customerOrder /mysqlbackup/agent_customerOrder.sql  
Locale is : C

Input file(s) will be read from : /usr/local/Calpont/bin
Job description file : /usr/local/Calpont/data/bulk/tmpjob/agent_customerOrder_D20140322_T211016_Job_10005.xml
Log file for this job: /usr/local/Calpont/data/bulk/log/Job_10005.log
2014-03-22 21:10:16 (30098) INFO : successfully loaded job file /usr/local/Calpont/data/bulk/tmpjob/agent_customerOrder_D20140322_T211016_Job_10005.xml
2014-03-22 21:10:16 (30098) INFO : Job file loaded, run time for this step : 0.672342 seconds
2014-03-22 21:10:16 (30098) INFO : PreProcessing check starts
2014-03-22 21:10:16 (30098) INFO : input data file /mysqlbackup/agent_customerOrder.sql
2014-03-22 21:10:28 (30098) INFO : PreProcessing check completed
2014-03-22 21:10:28 (30098) INFO : preProcess completed, run time for this step : 11.1688 seconds
2014-03-22 21:10:28 (30098) INFO : No of Read Threads Spawned = 1
2014-03-22 21:10:28 (30098) INFO : No of Parse Threads Spawned = 3
2014-03-22 21:10:28 (30098) INFO : Number of rows with errors = 2.  Row numbers with error reasons are listed in file /mysqlbackup/agent_customerOrder.sql.Job_10005_30098.err
2014-03-22 21:10:28 (30098) INFO : Number of rows with errors = 2.  Exact error rows are listed in file /mysqlbackup/agent_customerOrder.sql.Job_10005_30098.bad
2014-03-22 21:10:31 (30098) INFO : For table agent.customerOrder: 2 rows processed and 0 rows inserted.
2014-03-22 21:10:32 (30098) INFO : Bulk load completed, total run time : 16.1093 seconds

 

 

But am getting the above error.Can any one help me to import my .sql file in infinidb server.

 

 

Regards,

PrabhuQueia
 

radams
radams's picture
Offline
Last seen: 5 hours 37 min ago
Joined: Jan 3 2011
Administrator

Posts: 487

Robert Adams
How to do cpimport for .sql files in infinDB [Migrating MySQL to

 

 

Hi PrabhuQueia,

 

Check the err and bad files to determine the issue.

 

/mysqlbackup/agent_customerOrder.sql.Job_10005_30098.err
/mysqlbackup/agent_customerOrder.sql.Job_10005_30098.bad

 

cpimport is used to import data files.The file being imported should contain the

data for the table. Please read the Admin Guide for information on

running cpimport.

 

Best regards,

 

Robert

 

 

PrabhuQueia
PrabhuQueia's picture
Offline
Last seen: 4 months 5 days ago
Joined: Mar 21 2014
Junior Boarder

Posts: 5

Prabhu Singh
 

 

Ok Thanks Robert. I have to know one more thing why the insertion is very slow in infinidb.If am inserting a record it is taking approximate 4 secs.

 

Is there any way to speed it up.?

radams
radams's picture
Offline
Last seen: 5 hours 37 min ago
Joined: Jan 3 2011
Administrator

Posts: 487

Robert Adams
  InfiniDB, like all column

 

 

InfiniDB, like all column stores, takes a big hit for inserts: we have to open a file, position the pointer and write data to each column, and each column is on a different spot on the disk. A row database only has to deal with one file (and maybe some indexes). 

PrabhuQueia
PrabhuQueia's picture
Offline
Last seen: 4 months 5 days ago
Joined: Mar 21 2014
Junior Boarder

Posts: 5

Prabhu Singh
Ok thanks I got it and when

Ok thanks I got it and when am creating an index in my server it is throwing the below error,

 

mysql> CREATE UNIQUE INDEX uniq ON Versions (datatype);
ERROR 122 (HY000): Lost connection to DDLProc
 

 

I have restarted infinidb and checked it.after that also am getting the same error.

 

There is no error in mysql log also.

 

 

radams
radams's picture
Offline
Last seen: 5 hours 37 min ago
Joined: Jan 3 2011
Administrator

Posts: 487

Robert Adams
'CREATE UNIQUE INDEX'

 

 

 

Hi PrabhuQueia,

 

The syntax 'CREATE UNIQUE INDEX' is not supported. We have opened a bug to provide a better error message. Here is a section from the Concepts guide which discusses why indexing is not required on a column based storage engine like InfiniDB. Please consult the guide for more information.

 

 

 

To get around their selective query inefficiencies, row‐based RDBMS’s utilize indexing, horizontal

partitioning, materialized views, summary tables, and parallel processing, all of which can help complex
queries perform better, but each comes with their own set of drawbacks as well. For example, while
indexing can certainly help queries complete faster in some cases, they also require more storage, impede insert/update/delete and bulk load operations (because the indexes must be maintained as well as the underlying table), and can actually degrade performance when they become heavily fragmented.
 
Moreover, in business intelligence/analytic environments, the ad‐hoc nature of such scenarios makes it
nearly impossible to predict which columns will need indexing, so tables end up either being over‐indexed (which causes load and maintenance issues) or not properly indexed and so many queries end up running much slower than desired.
 
Column‐oriented databases designed especially for analytics overcome the limitations that exist in
traditional RDBMS systems by storing, managing, and querying data based on columns rather than rows. Because only the necessary columns in a query are accessed rather than entire rows, I/O activities as well as overall query response times can be reduced. The end result is the ability to interrogate and return query results against either moderate amounts of information (tens or hundreds of GB’s) or large amounts of data (1‐n terabytes) in less time that standard RDBMS systems can.
 
 
Thanks,
 
Robert