Why does performance of this batch insert degrade in MySQL? -


i'm looking understanding why insert query gets slower table size grows.

the query batch insert of 1k rows. batch mean multiple 'values' rows described in this answer.

there longblob field in table. typical size of blob field data rather small @ around 2kb (but can larger).

the image shows results of show profile query when inserting empty table vs inserting table had 1 million rows. fk tables empty in both cases.

enter image description here

table structure (column names changed anonymity):

create table `mytable` (  `modifiedtime` datetime default null,  `column1` varchar(255) default null,  `column2` varchar(255) default null,  `column3` varchar(128) default null,  `column4` decimal(20,5) not null,  `column5` varchar(128) default null,  `column6` varchar(255) default null,  `id` varchar(128) not null,  `creationtime` datetime default null,  `version` decimal(20,5) default null,  `status` varchar(255) default null,  `column7` varchar(128) default null,  `column8` varchar(128) default null,  `schemaless_attrs` longblob,  primary key (`id`),  unique key `ak_mytable` (`column4`,`column8`),  key `fk_mytable_column3` (`column3`),  key `fk_mytable_column5` (`column5`),  key `idx_managedbasedataitem_column1` (`column1`),  key `idx_mytable_column3` (`column3`),  key `idx_mytable_column4` (`column4`),  key `idx_mytable_status` (`status`) ) engine=innodb default charset=latin1;

the query below typical example. note: in real query there 1000 rows of 'values'.

insert `mytable` (`creationtime`, `modifiedtime`, `column8`,  `column5`, `column1`, `schemaless_attrs`, `version`, `column3`,  `column6`, `column2`, `column4`, `status`, `column7`, `id`)  values ('2015-07-01 14:47:41.259', '2015-07-01 14:47:41.259',  'single', 'single|myapp|0', 'myproduct',  _binary'a serialized java ojbect around 2k in size',  0, 'single|column3|cfd3229a-b424-41cc-b140-08cd92241064',  null, 'en', 1000001, 'pending', 'managedappentity',  'single|managedappentity|b095c316-293b-4a28-9a18-5422dd66eadd')

important notes:

  • we have app makes nearly same insert query same table structure. insert time never degrades other app tables grows.
  • since other app uses same exact mysql db, same table (with exception of 'version' field new), , and same insert query i'd prefer not focus on db config such 'innodb_buffer_pool_size', indexes, moving blog table, etc.
  • while there surely room improvement in db config, other app not showing slowdowns. i'm trying achieve similar performance same db config.

related questions:

  • i have asked 2 related questions around problem. clear, i'm not trying spam questions around topic i've had hard time pinning down whether slowdown coming app or db. seems specific question so. anyhow, other questions are:
    • question #1: (perhaps naive) question around whether column order makes difference when inserting blobs. comments on q indicate "no".
    • question #2: question focused more on dev stack app in question.


-

Comments

Post a Comment

Popular posts from this blog

OpenCV OpenCL: Convert Mat to Bitmap in JNI Layer for Android -

there several posts converting mat bitmap using utils.mattobitmap() function. i'm assuming function can called in java layer after importing utils class. i want transfer data memory address pointed uint32_t* bmpcontent; in code below. jniexport void jnicall java_com_nod_nodcv_nodcvactivity_runfilter( jnienv *env, jclass clazz, jobject outbmp, jbytearray indata, jint width, jint height, jint choice, jint filter) { int outsz = width*height; int insz = outsz + outsz/2; androidbitmapinfo bmpinfo; if (androidbitmap_getinfo(env, outbmp, &bmpinfo) < 0) { throwjavaexception(env,"gaussianblur","error retrieving bitmap meta data"); return; } if (bmpinfo.format != android_bitmap_format_rgba_8888) { throwjavaexception(env,"gaussianblur","expecting rgba_8888 format"); return; } uint32_t* bmpcontent; if (androidbitmap_lockpixels(env, outbmp,(void**)&bmpcontent)...
Read more

android - org.xmlpull.v1.XmlPullParserException: expected: START_TAG {http://schemas.xmlsoap.org/soap/envelope/}Envelope -

i trying hit soap api in application login purpose , getting above mentioned error , can 1 tell me going wrong , first time soap apis. here code hit soap :- vector<object> args = new vector<object>(); hashtable<string, object> hashtable = new hashtable<string, object>(); hashtable.put("email", "mss.siddhart28@gmail.com"); hashtable.put("password", "siddharth"); args.add(hashtable); soapserializationenvelope envelope = new soapserializationenvelope( soapenvelope.ver11); envelope.dotnet = true; envelope.xsd = soapserializationenvelope.xsd; envelope.enc = soapserializationenvelope.enc; (new marshalhashtable()).register(envelope); httptransportse androidhttptransport = new httptransportse( "http://tempuri.org/", 15000); androidhttptransport.debug = true; string result = ""; try { soapobject request = new soapobject("http:...
Read more

python - How to remove the Xframe Options header in django? -

i have made page has iframe . inside iframe want show multiple different links article facebook, or news, or youtube video or other possible url. but, due xframe header, unable so. referred following link: https://docs.djangoproject.com/en/1.8/ref/clickjacking/ , django xframeoptionsmiddleware (x-frame-options) - allow iframe client ip but didn't help. my settings.py file's middleware_classes is: middleware_classes = ( 'django.contrib.sessions.middleware.sessionmiddleware', 'django.middleware.common.commonmiddleware', 'django.middleware.csrf.csrfviewmiddleware', 'django.contrib.auth.middleware.authenticationmiddleware', 'django.contrib.auth.middleware.sessionauthenticationmiddleware', 'django.contrib.messages.middleware.messagemiddleware', 'django.middleware.clickjacking.xframeoptionsmiddleware', ) from http://django-secure.readthedocs.org/en/latest/middleware.html , found using ...
Read more