Bug #42729 MySQLD scalability issues when setting ndb_cache_check_time above 0
Submitted: 10 Feb 2009 13:15 Modified: 22 Apr 2009 14:55
Reporter: Jonathan Miller Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S5 (Performance)
Version:mysql-5.1-telco-> OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[10 Feb 2009 13:15] Jonathan Miller 
Description:
setting ndb_cache_check_time > 0 will cause scalability issue with a MySQLD instance.

Usually performance numbers look like the following:

Clients/results in TPS
4         710.01667785645
8         1149.4833374023
12         1294.0513763428
16         1303.7833251953
20         1295.8599472046
24         1265.5366668701 

but setting ndb_cache_check_time above 0 (i.e. just using 1 ms) cause the TPS to flatten out.

4         857.46000671387
8         114.9766664505
12         121.55999946594
16         125.46333456039
20         128.85666513443
24         129.93666601181 

In testing I have found that it does not matter if you set it to 1 or 1000, you get the very same results

How to repeat:
Run TPC-B with and without setting.
[22 Apr 2009 14:47] Jonathan Miller 
After some additional testing of just the auto inc table, it is apparent that this is the same issue as reported in http://bugs.mysql.com/bug.php?id=31314.

Therefore, marking as duplicate and have updated 31314 to include this bug so devlopers have as much information as possible.

Wed Apr 22 16:15:52 CEST 2009
Test -> ndb_cache_check_time=0
------------
doing 1000 loops took 10 seconds
doing 1000 loops took 10 seconds
doing 1000 loops took 10 seconds
doing 1000 loops took 10 seconds
doing 1000 loops took 10 seconds
doing 1000 loops took 10 seconds
doing 1000 loops took 10 seconds
doing 1000 loops took 10 seconds
------------
Wed Apr 22 16:40:35 CEST 2009
Test -> ndb_cache_check_time=1
------------
doing 1000 loops took 43 seconds
doing 1000 loops took 44 seconds
doing 1000 loops took 44 seconds
doing 1000 loops took 44 seconds
doing 1000 loops took 44 seconds
doing 1000 loops took 44 seconds
doing 1000 loops took 45 seconds
doing 1000 loops took 45 seconds
[22 Apr 2009 14:52] Jonathan Miller 
Correct loader script

Attachment: tpcb_load_db.pl (application/x-perl, text), 31.77 KiB.
[22 Apr 2009 14:52] Jonathan Miller 
Test Drive, edit for your system

Attachment: loop.sh (application/octet-stream, text), 311 bytes.
[22 Apr 2009 14:53] Jonathan Miller 
Test Script

Attachment: tpcb.pl (application/x-perl, text), 6.35 KiB.
[22 Apr 2009 14:55] Jonathan Miller 
1) simple cluster, no thrills 
2) simple mysqld with ndb_cache_check_time option = 0 (to start with)
3) copy all attached files to /tmp (or where ever seem best for you)

4) load db

Port Style
/usr/bin/perl -w ./tpcb_load_db.pl --port=15001 --host=ndb13 --log=/tmp/load.log  --numA=100000

Socket Style
/usr/bin/perl -w ./tpcb_load_db.pl --host=ndb13  -sock  --spath=/tmp/mysql.sock --log=/space/cluster_rep_auto/logs/tpcb.log --numA=100000

5) run tests

sh -x  ./loop.sh

Note: you should edit loop.sh with the settings for your host. If you want to use a socket, remove the port and add (--sock --sopath=(your path))

6)   Review /tmp/log.out (you might put some type of separating comment in the file.)

7) change ndb_cache_check_time to 1 and restart the mysqld

8) run test

./loop.sh

9) review results