Largest possible size for executemany() in PEP-249 (Database API)

Discussion in 'Python' started by Roy Smith, Feb 28, 2013.

  1. Roy Smith

    Roy Smith Guest

    I'm trying to batch up inserts to a database using MySQLdb. When I
    get to something over 10,000 records per call, I get an exception:

    _mysql_exceptions.OperationalError: (1153, "Got a packet bigger than
    'max_allowed_packet' bytes")

    Is there any way (other than trial and error) to know how many records
    I can pass in one call before I blow up? As a practical matter, if I
    do batches of 1000 per call, I've probably gotten as much performance
    enhancement as I need, but it would be nice to know if there's a
    useful way to determine exactly what the maximum is.
     
    Roy Smith, Feb 28, 2013
    #1
    1. Advertising

  2. On 28 Feb 2013 12:05:28 -0500, (Roy Smith) declaimed the
    following in gmane.comp.python.general:

    > I'm trying to batch up inserts to a database using MySQLdb. When I
    > get to something over 10,000 records per call, I get an exception:
    >
    > _mysql_exceptions.OperationalError: (1153, "Got a packet bigger than
    > 'max_allowed_packet' bytes")
    >
    > Is there any way (other than trial and error) to know how many records
    > I can pass in one call before I blow up? As a practical matter, if I
    > do batches of 1000 per call, I've probably gotten as much performance
    > enhancement as I need, but it would be nice to know if there's a
    > useful way to determine exactly what the maximum is.


    Sure... Check the MySQL server settings...

    According to the (former) MySQL AB/MySQL Press "MySQL
    Administrator's Guide" (page 480-481) the default for max_allowed_packet
    is 16MB -- though that doesn't match the apparent default in my
    installation which is showing it as 1MB...

    -=-=-=-=-
    >>> import MySQLdb as db
    >>> con = db.connect(user="test", db="test", passwd="test")
    >>> cur=con.cursor()
    >>> cur.execute("show variables")

    228L
    >>> for (varb, val) in cur:

    .... if varb.startswith("max"):
    .... print varb, val
    ....
    max_allowed_packet 1048576
    max_binlog_cache_size 4294967295
    max_binlog_size 1073741824
    max_connect_errors 10
    max_connections 50
    max_delayed_threads 20
    max_error_count 64
    max_heap_table_size 16777216
    max_insert_delayed_threads 20
    max_join_size 4294967295
    max_length_for_sort_data 1024
    max_prepared_stmt_count 16382
    max_relay_log_size 0
    max_seeks_for_key 4294967295
    max_sort_length 1024
    max_sp_recursion_depth 0
    max_tmp_tables 32
    max_user_connections 0
    max_write_lock_count 4294967295
    >>>

    -=-=-=-=-=-
    --
    Wulfraed Dennis Lee Bieber AF6VN
    HTTP://wlfraed.home.netcom.com/
     
    Dennis Lee Bieber, Mar 1, 2013
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. k3xji

    PEP 249 - DB API question

    k3xji, Nov 4, 2008, in forum: Python
    Replies:
    5
    Views:
    319
    Lawrence D'Oliveiro
    Nov 5, 2008
  2. Roy Smith
    Replies:
    24
    Views:
    941
    Martin Gregorie
    Oct 17, 2010
  3. Roy Smith
    Replies:
    2
    Views:
    161
    Roy Smith
    Mar 30, 2013
  4. Dave Angel
    Replies:
    0
    Views:
    132
    Dave Angel
    Mar 29, 2013
  5. Dennis Lee Bieber
    Replies:
    23
    Views:
    382
    Roy Smith
    Mar 30, 2013
Loading...

Share This Page