Bug #65426 conflict on Nosql performance and data integrity of mysql-5.6.6-labs
Submitted: 26 May 2012 4:42 Modified: 22 Mar 2013 18:43
Reporter: jis che Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Memcached Severity:S2 (Serious)
Version:mysql-5.6.6 OS:Linux (mysql-5.6.6-labs-april-2012 (Source distribution))
Assigned to: CPU Architecture:Any

[26 May 2012 4:42] jis che
Description:
[mysqld]
datadir=/usr/local/db
skip-name-resolve
port=3308
user=mysql

log-bin=/usr/local/db/binlog

binlog-format="mixed"

slow_query_log=1
slow_query_log_file=/usr/local/db/slow.log
long_query_time=1

#innodb_direct_access_enable_binlog=1
--mysqld doesn't recognize the above option.

#daemon_memcached_enable_binlog="ON"
--is this option collaborative with the below option??

#loose-daemon_memcached_w_batch_size=301
--here is the point of this report:
  When w_batch_size is set to 301 , To do memcache_add and memcache_delete 10000 key/value pairs with php, the performance is acceptable(700reqs/second), BUT , data integrity is broken from the "memcache get" command and "sql select" query.
no matter the tx_isolation default or set to READ-UNCOMMITED.
  on the other hand, if the w_batch_size is set to 1 as default, the data integrity is ok, BUT , the performance of memcache_add becomes   terrible(15~20reqs/second) . 

How to repeat:
>Release:	mysql-5.6.6-labs-april-2012 (Source distribution)
Modify CMakeLists.txt ,
  OPTION(WITH_INNODB_MEMCACHED "" ON)

>C compiler:    gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3)

>C++ compiler:  c++ (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3)

>Environment:
	<machine, os, target, libraries (multiple lines)>
System: Linux db33 2.6.32-220.el6.x86_64 #1 SMP Tue Dec 6 19:48:22 GMT 2011 x86_64 x86_64 x86_64 GNU/Linux
Architecture: x86_64

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using built-in specs.
Target: x86_64-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-boo
tstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu
-unique-object --enable-languages=c,c++,objc,obj-c++,java,fortran,ada --enable-java-awt=gtk --disable-dssi --with-java-home=/usr/lib/jvm/java-1.5.0-gcj-1.5.
0.0/jre --enable-libgcj-multifile --enable-java-maintainer-mode --with-ecj-jar=/usr/share/java/eclipse-ecj.jar --disable-libjava-multilib --with-ppl --with-
cloog --with-tune=generic --with-arch_32=i686 --build=x86_64-redhat-linux
Thread model: posix
gcc version 4.4.6 20110731 (Red Hat 4.4.6-3) (GCC) 
Compilation info (call): CC='/usr/bin/gcc'  CFLAGS=' -Wall -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF'  CXX='/usr/bin/c+
+'  CXXFLAGS=' -Wall -Wno-unused-parameter -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF'  LDFLAGS=''  ASFLAGS=''
Compilation info (used): CC='/usr/bin/gcc'  CFLAGS=' -Wall -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF'  CXX='/usr/bin/c+
+'  CXXFLAGS=' -Wall -Wno-unused-parameter -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF'  LDFLAGS=''  ASFLAGS=''

Start mysql as the above configuration [mysqld] , the rest steps are the same as 
plugin/innodb_memcached/README-innodb_memcached .

and do the test php script,
<?php

echo time() ;
echo "\n" ;

$mh = memcache_connect("192.168.1.33", 11211);
for( $k=0 ; $k<10000 ; $k++ ) {
	$v =  $k . "value" ;
  	//memcache_delete($mh , $k );
	memcache_add($mh , $k , $v , false, 0);
}
memcache_close($mh);
echo time();
echo "\n";
?>

Suggested fix:

maybe , let Innodb to do the transaction before starting the counting circle of daemon_memcached_w_batch_size ?
[22 Feb 2013 18:43] Sveta Smirnova
Thank you for the report.

> data integrity is broken from the "memcache get" command and "sql select" query.

What do you mean by this phrase? Do you insert key-value pairs using PHP script and try to read it from parallel SQL connection?

In this case this is not a bug according to http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_daemon_memcached_w_ba...:

----<q>----
This value is set to 1 by default, on the assumption that any data being stored is important to preserve in case of an outage and should immediately be committed. When storing non-critical data, you might increase this value to reduce the overhead from frequent commits; but then the last N-1 uncommitted write operations could be lost in case of a crash. 
----</q>----

So until you lose more than 300 uncommitted write operations this can be considered a bug, otherwise - not.
[23 Mar 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".