MySQL 5.7 – Index Condition Pushdown Optimization Bug

MySQL 5.7 – Index Condition Pushdown Optimisation Bug

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. It uses the indexed columns to determine if they should retrieve the row instead of retrieving the entire table then filtering off the unnecessary rows.

Disable ICP if you are running a query with ORDER BY DESC against partitioned tables. It slows down the query considerably. You will definitely hit this bug if you are running zabbix server on MySQL database.

To disable ICP, use the following:

SET optimizer_switch='index_condition_pushdown=off';

Hope this helps!

Regards,
Wei Shan

References:

,

Leave a comment

Using strace in Linux to troubleshoot database performance issues

Using strace in Linux to troubleshoot database performance issues

Use Case:

Using top and iostat, we know that the database is slow due to IO constraints. What if we want to narrow down to the exact file causing the performance issues? What if we want to know what is the DB process waiting on?

Find the PID of the process

[root@hostname ~]# ps -aef | grep mysqld
mysql 16013 15513 23 Dec08 ? 1-00:17:41 /usr/sbin/mysqld --basedir=/usr --datadir=/data/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/libgalera_smm.so --log-error=/var/log/mysql/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --wsrep_start_position=a09ed4ce-79bc-11e6-9b2e-63d190df3dd7:42170895

Find out which system calls took the longest time

[root@hostname ~]# strace -cp 16013
Process 16013 attached
^CProcess 16013 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
 98.63 28.601334 147430 194 poll
 1.33 0.387070 387070 1 restart_syscall
 0.03 0.008057 22 359 futex
 0.00 0.000891 5 195 accept
 0.00 0.000369 2 195 fcntl
------ ----------- ----------- --------- --------- ----------------
100.00 28.997721 944 total

Attach strace to the PID to find out the file descriptor it is waiting for.

[root@hostname ~]# strace -p 16013
Process 16013 attached
restart_syscall(<... resuming interrupted call ...>) = 1
accept(34, {sa_family=AF_LOCAL, NULL}, [2]) = 128
fcntl(128, F_SETFD, FD_CLOEXEC) = 0
futex(0x1e937c4, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x1e937c0, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x1e93800, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=50, events=POLLIN}, {fd=34, events=POLLIN}], 2, 4294967295) = 1 ([{fd=34, revents=POLLIN}])
accept(34, {sa_family=AF_LOCAL, NULL}, [2]) = 128
fcntl(128, F_SETFD, FD_CLOEXEC) = 0
futex(0x1e937c4, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0x1e937c0, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0x1e93800, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=50, events=POLLIN}, {fd=34, events=POLLIN}], 2, 4294967295) = 1 ([{fd=34, revents=POLLIN}])
accept(34, {sa_family=AF_LOCAL, NULL}, [2]) = 128
fcntl(128, F_SETFD, FD_CLOEXEC) = 0

Find the file that the file descriptor is pointing to!

ls -altr /proc/16013
lrwx------. 1 root root 64 Dec 12 15:46 50 -> /data/mysql/zabbix/history_uint#P#p201612160000.ibd
lrwx------. 1 root root 64 Dec 12 15:46 51 -> /data/mysql/zabbix/history_uint#P#p201612170000.ibd

Now we know mysqld process has been waiting for partitioned data file history_uint#P#p201612160000.ibd!

Hope this helps.

Regards,
Wei Shan

Leave a comment

2016 Year End Thoughts

2016 Year End Thoughts

I began this blog more than 4 years ago, on 8th September 2012. My life has undergone a massive change since then. 4 years ago, I started my first job working with Oracle databases on AIX/Solaris O/S. Today, I work with mainly MongoDB/PostgreSQL/MySQL on FreeBSD/Linux O/S. Within this short timespan, proprietary software market share has reduced massively. For every proprietary software, there is a open-source version out there. Albeit, it might not be as enterprise ready as the paid counterparts, but hey, it’s free! Also, database is a lot more cooler to work on now!

This year has been a life changing experience for me. I did a lot of freelance database consulting work, relocated to a different continent, also working with in a team with very different culture compared to mine. The tech industry in London is a lot more vibrant than Singapore. The average quality of engineers are a lot higher than back home. The best thing that happened to me professionally is that I realised that I wasn’t as good as I thought. I’m happy that I had this self realisation myself instead of having it shoved down my throat. (Think of China during the Qing dynasty!)

My professional motto is always to be the dumbest guy in the room. So far, I was never the dumbest guy. Fortunately, there was always this few guys that’s just a tad dumber that me. However, in this new team, I do feel like the dumbest guy in the room. It’s probably more of a fact than feeling. It is brutal and humbling and definitely not a pleasant experience. It doesn’t help emotionally when the working culture are forthright, meaning they are more than willing to engage in a point-blank technical conversation without any hesitation of being as blunt as they can be.

However, it does kindle this burning sensation within me to strive to be a better engineer. I do want to read as much as I can and catch up to their level. I will let my inner asian mindset shine here 🙂  2016 has been an amazing ride so far, I can’t wait for 2017 to happen. It’s going to be so much more exciting!

I wish everyone to have an awesome year in 2017!

Regards,
Wei Shan

Leave a comment

MongoDB 3.2 – Duplicating a collection with or without transformation

MongoDB 3.2 – Duplicating a collection with or without transformation

If you have a requirement to duplicate a collection, there’s multiple ways to do it. You could do an mongoexport/mongoimport, db.collection.copyTo, write a javascript loop function or simply, use aggregation to do it. The 2 method lacks option to perform any transformation. The last 2 option allows you to choose whether you want to perform any transformation.

Consider a books collection with the following document:

{
 "_id" : ObjectId("5836d55d500dsa1230f488ab0"),
 "a" : 1,
 "b" : "12345678",
 "c" : "12345678"
}

We want to duplicate the above, create another field, e, with the value of _id to another collection called books2. We could write a javascript using for loop (find.ForEach) to create initializeUnorderedBulkOp job, transform the fields and insert into another collection.

We could also use MongoDB aggregation to do it.

db.bookes.aggregate( [ { $project : { _id: 0, a:1, b:1, c:1, d: "$_id"} }, { $match: {} }, { $out : "books2" } ] )

1 line of code to do exactly what you need! Using my laptop(MBP with 16GB memory) as a test, with a million record, I achieved around ~12 times faster on aggregation than on javascript. Unlike copyTo(), there’s no database-level locking, so it’s friendly on your production system(Although it will mess up your cache with the big collection).

Personally, I like aggregation a lot. It feels a lot like Hadoop/MapReduce functions where the throughput is amazing but the latency is crap.

Regards,
Wei Shan

References:

  1. http://stackoverflow.com/questions/8933307/clone-a-collection-in-mongodb
  2. https://docs.mongodb.com/v3.2/reference/operator/aggregation/project/

Leave a comment

MongoDB 3.2 – Hardening and User Access Control

MongoDB 3.2 – Hardening and User Access Control

MongoDB has been on the news for being one of the most insecure NoSQL database software. By default, it is insecure with absolutely no security measures in place. Oracle and other various database software usually has a default admin user created and doesn’t allow any non-user to login to the database. However, MongoDB allows all user to login to the system on port 27017. Unless, of course, you lock in down 🙂

The MongoDB documentation has all the information you need to lock it down. Once you start MongoDB with –auth parameter, the system is effectively locked down. Recently, I had the job of securing a MongoDB cluster. One of the more difficult task is applying RBAC policies to your environment as you will need to understand the application/users access your MongoDB cluster.

My tips on securing your MongoDB cluster.

  1. Enable TLS for all MongoDB components (mongos, config servers and shard servers)
  2. Encrypt your drives that are storing MongoDB data
  3. Use x509 authentication mechanism
  4. Disable TLS1.0 and TLS1.1 TLS protocol
  5. Use RequireSSL for “SSLMode” parameter
  6. Use mutual TLS authentication to authenticate your application too
  7. Don’t use a self signed certificate. Get one from a public CA.
  8. Create a superuser with root and backup role. (This will be your “oracle” user in Oracle DB context)
  9. Create a local superuser with root, __system and backup role on each sharded replica set. This user will be required for shard maintenance

Things you should know about TLS

  1. Remove unnecessary parameter (CAFile,  clusterFile) from your configuration file else mongod will hit errors while parsing them. Don’t even put them in the configuration file with empty string.
  2. MongoDB automatically enable authorization when you enable TLS. So remember to create your users before that! (link)
  3. MongoDB does not have a parameter for choosing which ciphersuite to use.

Once you enable TLS, you will need to work around the authentication mechanism in MongoDB. Some tips below will be helpful.

When you are only using TLS but not client authentication

Create a superuser via mongos

use admin
db.createUser( { user: "superuser", pwd: "password", roles: ["root", "backup"] }, { w:"majority", wtimeout: 5000} )

Create a role via mongos

use admin
db.createRole( { role: "readWrite", privileges: [ { resource: { db: "database_name", collection: "collection_name" }, actions: ["find", "insert"] } ], roles:[ ] }, { w: "majority" } )

How to login to the MongoDB cluster via mongos

# mongo 10.1.64.11:27017/admin --authenticationDatabase admin
mongo> db.auth("username","password")

When you are using both TLS and client authentication

Create a superuser via mongos

db.getSiblingDB("$external").runCommand(
 {
 createUser: "CN=superuser,OU=Test ,O=Test,L=UK,ST=London,C=UK",
 roles: [
 { role: 'root', db: 'admin' },
 { role: 'backup', db: 'admin'},
 { role: __system, db:'admin}
 ],
 writeConcern: { w: "majority" , wtimeout: 5000 }
 }
)

Create a role via mongos

use admin
db.createRole( { role: "readWrite", privileges: [ { resource: { db: "database_name", collection: "collection_name" }, actions: ["find", "insert"] } ], roles:[ ] }, { w: "majority" } )

How to login to the MongoDB cluster via mongos

db.getSiblingDB("$external").auth(
 {
 mechanism: "MONGODB-X509",
 user: "CN=superuser,OU=Test ,O=Test,L=UK,ST=London,C=UK"
 }
)

How to list all your mutual TLS authentication users

db.getSiblingDB("$external").runCommand( { usersInfo: 1 } )

Hope this helps!

 

Regards,
Wei Shan

Leave a comment

MongoDB 3.2 – setShardVersion failed, stale config on lazy receive

MongoDB 3.2 – setShardVersion failed, stale config on lazy receive

While we were hacking the config servers today, we hit multiple errors.

  • MongoDB version 3.2.11
  • 2 shards
  • 3 replica set per shards

Any find() or aggregation query against mongoS

2016-11-24T15:03:31.699+0000 E QUERY [thread1] Error: command failed: {
 "code" : 10429,
 "ok" : 0,
 "errmsg" : "setShardVersion failed shard: s1:s1./hostnames7 { oldVersion: Timestamp 0|0, oldVersionEpoch: ObjectId('000000000000000000000000'), ns: \"collections\", version: Timestamp 0|0, versionEpoch: ObjectId('000000000000000000000000'), globalVersion: Timestamp 1000|0, globalVersionEpoch: ObjectId('5836fa3761234567600eebbf'), reloadConfig: true, ok: 0.0, errmsg:
\"could not refresh metadata for collections with requested shard version 0|0||000000000000000000000000, stored shard version is 1|0||1234fa37631...\" }"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:290:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1312:5
@(shell):1:1

db.printCollectionStats() against mongoS

error: {
 "$err" : "stale config on lazy receive :: caused by :: $err: \"[myzips.zips] shard version not ok in Client::Context: this shard contains versioned chunks for myzips.zips, but no version set in request ( ns : myzips.zip...\" ( ns : myzips.zips, received : 0|0||000000000000000000000000, wanted : 1|1||50fdd55b14faa2aa46422a7a, recv )",
 "code" : 9996
} at src/mongo/shell/query.js:128

Root cause:

  • config servers have new updated metadata about the sharded cluster that is not updated in both mongoSs and mongoDs (Yes, despite MongoDB official documentation, mongoDs does have a cache copy of your config servers metadata

Solution as below:

  1. Restart mongoSs
  2. Restart all your shards mongoDs

Regards,
Wei Shan

Leave a comment

MongoDB 3.2 – server returned error on SASL authentication step: Authentication failed.

MongoDB 3.2 – server returned error on SASL authentication step: Authentication failed.

This issue will happen to all MongoDB tools (mongo, mongodump, mongotop, mongostat). However, I encounter the issue while using mongotop.

#mongotop --host=192.168.56.104:27017 -u username -p "THIS1sR3tard3d!!" --authenticationDatabase=admin 5
mongotop --host=192.168.56.104:27017 -u superuser -p "THIS1sR3tard3dmongostat --host=192.168.56.104:27017 --username=user --password='THIS1sR3tard3d!!' --authenticationDatabase=admin " --authenticationDatabase=admin 5
2016-11-17T17:43:59.523+0000 Failed: error connecting to db server: server returned error on SASL authentication step: Authentication failed.

The issue is with the password with special characters. Apparently, even when you double quote a password, it still doesn’t ignore the special characters. t doesn’t help the situation when the error message is really vague.

#mongotop --host=192.168.56.104:27017 -u username -p 'THIS1sR3tard3d!!' --authenticationDatabase=admin 5

To solve the issue, simply single quote the password field 🙂

Update:

Apparently, escaping the special characters using single quote works too!

mongo -u username -p '1234'@''@'' --authenticationDatabase admin <ip>:port/db

Hope this helps

Regards,
Wei Shan

Leave a comment

Oracle 11gR2 – Default Audit Policies

Oracle 11gR2 – Default Audit Policies

By default, when we create a new database instance in Oracle, the default value for AUDIT_TRAIL is set to “none”. When you set it to “DB” or “DB,EXTENDED”, it will have a set of default logging policies.

SQL> select * from dba_priv_audit_opts;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
 CREATE EXTERNAL JOB BY ACCESS BY ACCESS
 CREATE ANY JOB BY ACCESS BY ACCESS
 GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
 EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
 CREATE ANY LIBRARY BY ACCESS BY ACCESS
 GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
 DROP PROFILE BY ACCESS BY ACCESS
 ALTER PROFILE BY ACCESS BY ACCESS
 DROP ANY PROCEDURE BY ACCESS BY ACCESS
 ALTER ANY PROCEDURE BY ACCESS BY ACCESS
 CREATE ANY PROCEDURE BY ACCESS BY ACCESS
 ALTER DATABASE BY ACCESS BY ACCESS
 GRANT ANY ROLE BY ACCESS BY ACCESS
 CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
 DROP ANY TABLE BY ACCESS BY ACCESS
 ALTER ANY TABLE BY ACCESS BY ACCESS
 CREATE ANY TABLE BY ACCESS BY ACCESS
 DROP USER BY ACCESS BY ACCESS
 ALTER USER BY ACCESS BY ACCESS
 CREATE USER BY ACCESS BY ACCESS
 CREATE SESSION BY ACCESS BY ACCESS
 AUDIT SYSTEM BY ACCESS BY ACCESS
 ALTER SYSTEM BY ACCESS BY ACCESS

By default, it will log ALL sessions. This will increase the audit log size by a tremendous amount. Thus, unless you have a unique business requirement, it will be wiser if we just log failure login attempts only.

Disable audit logging on all session creation. Enable audit logging on unsuccessful session creation.

SQL> noaudit create session;
SQL> audit create session whenever not successful;

Verify Settings.

SQL> select * from dba_priv_audit_opts;

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
 CREATE SESSION NOT SET BY ACCESS

Hope this helps!

Regards,
Wei Shan

Leave a comment