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

Path	csiph.com!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!news1.tnib.de!feed.news.tnib.de!news.tnib.de!newsfeed.freenet.ag!news2.euro.net!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
Return-Path	<python-python-list@m.gmane.org>
X-Original-To	python-list@python.org
Delivered-To	python-list@mail.python.org
X-Spam-Status	OK 0.022
X-Spam-Evidence	'H': 0.96; 'S': 0.00; 'inserts': 0.07; 'subject:PEP': 0.07; 'val': 0.07; 'exception:': 0.09; 'received:80.91': 0.09; 'received:80.91.229': 0.09; 'received:gmane.org': 0.09; 'received:list': 0.09; '"got': 0.16; '(other': 0.16; '1024': 0.16; '1048576': 0.16; 'bytes")': 0.16; 'error)': 0.16; 'received:80.91.229.3': 0.16; 'received:plane.gmane.org': 0.16; 'subject:API': 0.16; 'subject:possible': 0.16; '>>>': 0.18; 'feb': 0.19; 'trying': 0.21; 'import': 0.21; 'installation': 0.23; "i've": 0.23; 'pass': 0.25; "doesn't": 0.28; 'header:X-Complaints-To:1': 0.28; 'subject:Database': 0.29; 'subject:size': 0.29; 'probably': 0.29; "i'm": 0.29; "skip:' 20": 0.32; 'skip:_ 30': 0.32; 'print': 0.32; 'url:home': 0.33; 'to:addr:python-list': 0.33; 'skip:d 20': 0.34; 'server': 0.35; 'bigger': 0.35; 'something': 0.35; 'there': 0.35; 'received:org': 0.36; 'but': 0.36; 'useful': 0.36; 'charset:us- ascii': 0.36; 'subject: (': 0.36; 'skip:v 20': 0.37; 'subject:: ': 0.38; 'performance': 0.39; 'to:addr:python.org': 0.39; 'subject:-': 0.40; 'header:Received:5': 0.40; 'is.': 0.62; 'maximum': 0.63; '10,000': 0.65; 'records': 0.68; 'press': 0.71; 'gotten': 0.75; 'trial': 0.81; '(roy': 0.84; '2013': 0.84; 'blow': 0.84; 'email addr:panix.com': 0.84; 'smith)': 0.84; 'subject:Largest': 0.84; 'apparent': 0.91; 'dennis': 0.91; 'enhancement': 0.95
X-Injected-Via-Gmane	http://gmane.org/
To	python-list@python.org
From	Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject	Re: Largest possible size for executemany() in PEP-249 (Database API)
Date	Thu, 28 Feb 2013 20:52:00 -0500
Organization	> Bestiaria Support Staff <
References	<kgo2oo$5tk$1@panix2.panix.com>
Mime-Version	1.0
Content-Type	text/plain; charset=us-ascii
Content-Transfer-Encoding	7bit
X-Gmane-NNTP-Posting-Host	adsl-76-253-101-185.dsl.klmzmi.sbcglobal.net
X-Newsreader	Forte Agent 3.3/32.846
X-No-Archive	YES
X-BeenThere	python-list@python.org
X-Mailman-Version	2.1.15
Precedence	list
List-Id	General discussion list for the Python programming language <python-list.python.org>
List-Unsubscribe	<http://mail.python.org/mailman/options/python-list>, <mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive	<http://mail.python.org/pipermail/python-list/>
List-Post	<mailto:python-list@python.org>
List-Help	<mailto:python-list-request@python.org?subject=help>
List-Subscribe	<http://mail.python.org/mailman/listinfo/python-list>, <mailto:python-list-request@python.org?subject=subscribe>
Newsgroups	comp.lang.python
Message-ID	<mailman.2696.1362102732.2939.python-list@python.org> (permalink)
Lines	57
NNTP-Posting-Host	2001:888:2000:d::a6
X-Trace	1362102732 news.xs4all.nl 6921 [2001:888:2000:d::a6]:53364
X-Complaints-To	abuse@xs4all.nl
Xref	csiph.com comp.lang.python:40210

Show key headers only | 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