Mysql – fast method for exporting a large table from thesql to postgresql

MySQLpostgresql

I've got a table with around 2.7 million records, 63 fields, and several indexes. I need to be able to get this table from mysql to postgresql quickly (say within an hour). I know mysql can export the data quickly, but postgresql seems to be very slow when importing the data. Any ideas? I've been importing a CSV produced from mysqldump for about 3 hours now.

Best Answer

The fastest way I can think of would be to drop all the indexes and triggers (or at least ALTER TABLE foo DISABLE TRIGGER ALL;) on the table in postgresql, add the \. end-of-input line to the end of your CSV file, then put the file on the server (preferably on a drive other than the one being used for the DB, or maybe somewhere on the network the server can access via NFS) and COPY foo FROM '/some/file.csv' WITH CSV; (8.x syntax, which is supported in 9.0. Depending on the CSV file format you may need to make modifications.) Afterwards, recreate the indexes and re-enable the triggers.

Some of the configuration options for postgres can affect data load times. Disabling autovacuum and increasing the time between checkpoints can help with huge loads, but you'll need to restart postgresql in order to change the checkpoint_segments or autovacuum settings, and you'll want to set autovacuum back on when you're done.

Related Solutions

Mysql – Querying a Large Dataset Fast: MySQL MEMORY (HEAP) engine, MyISAM, or something else (e.g. MemCached)

It's not clear exactly what your requirements are, but with such a small database, queries are likely to be fast on any engine as all the data will fit in memory.

Things like MyISAM and InnoDB require some tuning to work. Memcached is only useful if you need to scale to very high-load read-based workloads, i.e. those which are too high for a single server to serve out of a conventional in-memory database (Memcached's main feature is ability to scale across a pool of servers). But memcached cannot do conventional queries such as range scans, only lookups on a specific key, so it's not a general-purpose db.

I'd recommend using innodb with a buffer pool bigger than your data set, and choose your primary keys correctly. A primary key range scan should perform very well and should be able to be used for prefix matching (e.g. completion use-cases).

You will be able to scale to, say, 1000 queries per second with that on normal hardware. If you need more, either add replication slaves or consider something like memcached.

Consider asking on StackOverflow?

Mysql – How does one list warnings from the ‘thesqlimport’ utility

It's not possible with mysqlimport, however as an alternative you can do the following:

mysql --execute="LOAD DATA LOCAL INFILE '$WORKDIR/$table.csv' INTO TABLE $table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES (listOfColumnNames); SHOW WARNINGS"

Replace listOfColumnNames with an appropriate seperated list of columns.

The magic is (as Eduard previously mentioned) is to execute the LOAD DATA INFILE and the SHOW WARNINGS commands together in the same session, as mysqlimport doesn't provide a way to get at the warnings directly.

Best Answer

Related Solutions

Mysql – Querying a Large Dataset Fast: MySQL MEMORY (HEAP) engine, MyISAM, or something else (e.g. MemCached)

Mysql – How does one list warnings from the ‘thesqlimport’ utility

Related Topic