1

I'm migrating part of a mysql database into a postgresql database with a different Schema.

I've written a python script, using mysql-connector and psycopg2 modules. It takes care(should) to convert/transform and migrate the database stored files (binary), and its related Metadata into the new postgresql db structure. This is why the operation is a little bit heavy (binary data).

The script proceed as follow:

Open Databases connections

# Mysql connection
try:
    cnx_msql = mysql.connector.connect( host=host_mysql, user=user_mysql, passwd=pswd_mysql, db=dbna_mysql, buffered=True )
except mysql.connector.Error as e:
    print "MYSQL: Unable to connect!", e.msg
    sys.exit(1)

# Postgresql connection
try:
    cnx_psql = psycopg2.connect(**psql_param)
except psycopg2.Error as e:
    print('PSQL: Unable to connect!\n{0}').format(e)
    sys.exit(1)

Perform all the operations without commit (same transaction)

I have to fill 5 postgresql tables, so the following process is (lets say) iterated 5 times (PS: is not necessary to understand in detail the procedure):

  • Query the source database
  • Casting fields types (both metadata fields, and binary field : from bytearray to string)
  • Create a new postgresql empty largeobject
  • Write the converted byte into the largeobject
  • Add into the metadata row the OID generated (this is the relation from metadata to largeobject)

Commit everything

When everything is done, and only then, I'm going to commit:

## Committing
cnx_psql.commit()

## Closing database connections
cnx_msql.close()
cnx_psql.close()

OK then what is the problem?

The script get stuck all the times around the same point, and I don't mean it get stuck because of errors, it seems the script is waiting for system memory in order to continue. I do log all the query while script running, and log stuck as follow in the middle of a query logging: # cat db-migration/logs/files.py_out

[...]
EXECUTED QUERY : INSERT INTO file_secview (resource_id, version, page, file_oid, iv) VALUES (1442425313047166, 1, 1, NULL, NULL)
lo_migration | new oid : 990844
EXECUTED QUERY : INSERT INTO file_secview (resource_id, version, page, file_oid, iv) VALUES (1288620808802549, 1, 13, NULL, NULL)
lo_migration | new oid : 990845
EXECUTED QUERY : INSERT INTO file_secview (resource_id, version, page, file_oid

I'm on a linux CentOS and the RAM memory seems to be full when it happen:

# free -h
             total       used       free     shared    buffers     cached
Mem:           31G        31G       238M       139M        10M        18G
-/+ buffers/cache:        12G        19G 
Swap:         991M         0B       991M

looking at the processes with ps -ef | grep postgres I find out that postgres is in "idle in transaction state"

postgres 10762 15029  0 Jun01 ?        00:03:20 postgres: postgres asw3 ::1(46457) idle in transaction      

and the python script process is still there waiting to continue:

root     10760     1  0 Jun01 ?        00:01:38 db-migration/venv/bin/python ./files.py

Currently I'm reading documentation here in order to understand if I can edit some configuration parameter in order to fix the issue. Am I reading the correct section? Am I on the right road? I'm not sure what config I have to edit in my case

Does somebody know some better way to debug the problem?

Edits:

following the requested content of pg_stat_activity table:

postgres=# SELECT * FROM pg_stat_activity;
-[ RECORD 1 ]----+----------------------------------------------------------------------------------------------------------------
datid            | 13241
datname          | postgres
pid              | 19421
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2016-06-02 09:51:12.080112+00
xact_start       | 2016-06-02 09:51:35.269298+00
query_start      | 2016-06-02 09:51:35.269298+00
state_change     | 2016-06-02 09:51:35.269305+00
waiting          | f
state            | active
backend_xid      | 
backend_xmin     | 246599
query            | SELECT * FROM pg_stat_activity;
-[ RECORD 2 ]----+----------------------------------------------------------------------------------------------------------------
datid            | 91981
datname          | agora3
pid              | 10762
usesysid         | 10
usename          | postgres
application_name | 
client_addr      | ::1
client_hostname  | 
client_port      | 46457
backend_start    | 2016-06-01 15:51:43.105585+00
xact_start       | 2016-06-01 16:01:18.415429+00
query_start      | 2016-06-02 09:45:57.731472+00
state_change     | 2016-06-02 09:45:57.757931+00
waiting          | f
state            | idle in transaction
backend_xid      | 246599
backend_xmin     | 
query            | INSERT INTO file_secview (resource_id, version, page, file_oid, iv) VALUES (1442425299374162, 1, 9, NULL, NULL)

I executed again the query, and from there it seems the script is continuing sending the queries, it is not really stuck, mmmh then where the problem lay?:

-[ RECORD 2 ]----+----------------------------------------------------------------------------------------------------------------
datid            | 91981
datname          | agora3
pid              | 10762
usesysid         | 10
usename          | postgres
application_name | 
client_addr      | ::1
client_hostname  | 
client_port      | 46457
backend_start    | 2016-06-01 15:51:43.105585+00
xact_start       | 2016-06-01 16:01:18.415429+00
query_start      | 2016-06-02 10:12:21.966997+00
state_change     | 2016-06-02 10:12:21.993562+00
waiting          | f
state            | idle in transaction
backend_xid      | 246599
backend_xmin     | 
query            | INSERT INTO file_secview (resource_id, version, page, file_oid, iv) VALUES (1442425299374162, 1, 5, NULL, NULL)

Reading psycpg2 documentation I found the following warning:

By default, any query execution, including a simple SELECT will start a transaction: for long-running programs, if no further action is taken, the session will remain “idle in transaction”, an undesirable condition for several reasons (locks are held by the session, tables bloat...). For long lived scripts, either ensure to terminate a transaction as soon as possible or use an autocommit connection.

I'm thinking I'm going to try to enable autocommit, but how to do it?

  • Do you have any connections with the state "waiting" in pg_stat_activity? If yes, they are probably waiting for some DDL lock. Check out wiki.postgresql.org/wiki/Lock_Monitoring for some queries to investigate that – a_horse_with_no_name Jun 2 '16 at 9:46
  • Additionally: there is already a tool that can automate the upgrade from MySQL to Postgres: ora2pg.darold.net Despite the name, it also works for MySQL, not only Oracle – a_horse_with_no_name Jun 2 '16 at 9:46
  • Oh and: unless you really know that you want "large objects", using bytea is the better choice as a match to a MySQL BLOB column – a_horse_with_no_name Jun 2 '16 at 9:47
  • Many thanks for your comments, I updated the question, there are no connections in "waiting" state. For the ora2pg tool, I'm not sure it can handle a migration with 2 schemas that much different. For the discouragement largeobject usage, I completely agree with you, but developers took that choice, and I currently don't have the power to change that choice since we are already in prod – lese Jun 2 '16 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.