Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > Largest possible size for executemany() in PEP-249 (Database API)

Reply
Thread Tools

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

 
 
Roy Smith
Guest
Posts: n/a
 
      02-28-2013
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.
 
Reply With Quote
 
 
 
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      03-01-2013
On 28 Feb 2013 12:05:28 -0500, http://www.velocityreviews.com/forums/(E-Mail Removed) (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
(E-Mail Removed) HTTP://wlfraed.home.netcom.com/

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
largest Regex match possible gsterndale Ruby 2 07-23-2008 06:51 AM
Worlds Largest Photo and Worlds Largest Camera... Somebody Digital Photography 1 08-16-2007 02:51 AM
dynamically determine the largest possible font size ara.t.howard@gmail.com Javascript 1 02-06-2007 09:36 AM
Browser: view/text size/smallest to largest Sanitarium HTML 2 12-04-2003 09:18 PM
Largest possible value of Integer Constants Nicholas C Programming 7 09-12-2003 04:33 PM



Advertisments