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

Groups > comp.lang.python > #40210

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

Show all headers | View raw

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/

Back to comp.lang.python | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

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

csiph-web