Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #40139 > unrolled thread

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

Back to article view | Back to comp.lang.python

Contents

  Largest possible size for executemany() in PEP-249 (Database API) roy@panix.com (Roy Smith) - 2013-02-28 12:05 -0500
    Re: Largest possible size for executemany() in PEP-249 (Database API) Neil Hodgson <nhodgson@iinet.net.au> - 2013-03-01 09:26 +1100
    Re: Largest possible size for executemany() in PEP-249 (Database API) Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-02-28 20:52 -0500

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

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.

[toc] | [next] | [standalone]

#40197

Roy Smith:

> _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?

    Its unlikely to be a limit in the number of records but a limit on 
the number of bytes in the serialized command stream. With a deep 
understanding of the format you could count bytes until about to go over 
and then flush. I'd *guess* that the data is being sent as textual SQL 
INSERT statements so you could work out what your insertions look like 
as INSERT statements and see how many fit into max_allowed_packet. 
max_allowed_packet is probably 1 million so looking like 100 bytes per 
INSERT but will depend on data as inserting "Ko" should use less bytes 
than inserting "Naragarajan".

    Neil

[toc] | [prev] | [next] | [standalone]

#40210

On 28 Feb 2013 12:05:28 -0500, roy@panix.com (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
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web