Monthly Archives: March 2015

MySQL(2)- performance monitor

Currently, I use MySQL as Database to store data. The whole architecture is build under Play framework. But I meet the problem, like MySQL connection timeout, etc. So MySQL performance becomes more serious important. Here I list some commands to monitor MySQL performance. I don’t like to install additional tools to do it. In fact, MysqlAdmin already can do a lot of things.

1. MysqlAdmin

  • check MySQL server status
    mysqladmin -u root -p ping
  • check MySQL version
    mysqladmin -u root -p version
  • find out the current status of MySQL server?
    mysqladmin -u root -p status
    # Output:
    Uptime: 269161  Threads: 11  Questions: 36607  Slow queries: 0  Opens: 96  Flush tables: 1  Open tables: 70  Queries per second avg: 0.136
  • check status of all MySQL server variable’s and value’s?
    mysqladmin -u root -p extended-status
  • see all MySQL server variables and values
    mysqladmin -u root -p variables
  • check all the running process of MySQL server
    mysqladmin -u root -p processlist
  • some useful MySQL flush commands
    mysqladmin -u root -p flush-hosts 
    mysqladmin -u root -p flush-tables 
    mysqladmin -u root -p flush-threads 
    mysqladmin -u root -p flush-logs 
    mysqladmin -u root -p flush-privileges 
    mysqladmin -u root -p flush-status
  • kill sleeping MySQL client process
    mysqladmin -u root -p processlist
    mysqladmin -u root -p kill #id,#id

2. Within MySQL

  • find slow query

    • show configuration/variables first
      show variables like "%slow%";

      “slow_query_log” and “slow_query_log_file” are important.
      screenshot-2016-11-02-14-10-57

       show variables like 'long_query_time';

      Screenshot 2016-11-02 14.34.46.png

    • set long_query_time and turn on slow_query_log
      set long_query_time=1;
      set global slow_query_log='ON';
      
    • view log file
      cat /var/lib/mysql/<file_name>.log | more
    • show slow queries
      show global status like "%slow%";

      Screenshot 2016-11-02 14.11.34.png

  • check read/write collision or table lock check

    • table status
      show status like 'Table%';

      Table_locks_immediate shows the table-level lock number and these locks are immediately released. Table_locks_waited shows the table-level lock number which needs wait.
      If Table_locks_immediate/Table_locks_waited > 5000, this means you’d better to adopt InnoDB engine. Because InnoDB uses row-level lock, but MyISAM uses table-level lock. For high concurrent write application, InnoDB would be better.
      If Table_looks_waited is too high, this means it exists worse table-level lock contention situation.
      Screenshot 2016-11-02 14.12.27.png

  • check key lock which is related to index buffer usage status

    • show key buffer size
      show variables like "key_buffer_size";
      

      Screenshot 2016-11-02 14.13.24.png

    • show hit ratio
      show global status like "key_blocks_u%";
      

      Note: If “Key_blocks_unused” is too high, buffer size should be decreased.
      Screenshot 2016-11-02 14.13.51.png

    • modify key buffer size
      set global key_buffer_size=4194304;
  • find full table scan statement

    select * from sys.statements_with_full_table_scans;

    Screenshot 2016-11-02 14.15.01.png

  • others
    show [session|global] status like ...

    The difference betwee seesion and global is that session is only for current conversation, global is for the whole running time.

    # show all mysql operating info
    show status;
    # show insert times
    show status like "com_insert%";
    # show delete times
    show status like "com_delete%";
    # show query times
    show status like "com_select%";
    # show mysql service running time
    show status like "uptime";
    # show myslq connect times
    show status like "connections";

3. MySQL Health Check

http://www.fromdual.com/mysql-database-health-check is an online website which helps you to check MySQL status.

Screenshot 2016-11-02 14.15.36.png

4. Related Links which deserve to read

Advertisements

JSON Operation by Scala

Recently, I’m doing a subscription system for my current company. The content of subscription is to crawl data from another system. The most important thing is that the data is async and from multiple channels. My solution is to use Akka scheduler to deal with async issue. For multiple channels, I use MySQL to store temp data together and finally fetch them again and email them.

So when I decide which data structure to use, I choose Map, but considering I need to use string/text to store data to DB. things became not good, because converting string to Map is not a good method. Now JSON helps me a lot.

1. import some libs

import play.api.libs.json._

2. build a JSON by Scala

var bufJson = new JsArray()
// here you need to return back, since append will not return back
bufJson = bufJson.append(Json.obj{"status" -> "add"})

3. transfer JSON to String

val dataString = bufJson.toString()

4. transfer string back to JSON

val dataJson = Json.parse(dataString)
dataJson match {
  case JsArray(dataJsArray) => {
    //do your own things
  }
  case _ => 
}

5. How to fetch data in JSON

// "time" is your key, dataJson is JsValue/JsObject which gets from Json.obj or Json.parse
val time = dataJson.\("time")
// you can convert the result to any type
val time = dataJson.\("time").as[String]
// if there is sub-JSON in result you can convert to JsObject to benefit next step
val time1 = dataJson.\("time").as[JsObject]

6. How to loop the JSON

dataJson.keys.foreach { 
  eachKey =>
    val eachValue = dataJson.\(eachKey)
    ..
}

7. How to update value in JSON

Here I list five cases:

(1) append new value to existing structure: see temp

(2) overwrite value directly to exact path: see temp1

(3) remove one path from json: see temp2

(4) read old value and overwrite this value, see temp3

(5) pick a branch and remove a sub-branch, see temp4

// figure out path and the field which you want to update
val temp = ( __ \ 'key1' \ 'key2').json.update(
  __.read[JsObject].map{ o => o ++ Json.obj("key3" -> "value3")})

// Or you want to overwrite its value, Note: JsString or JsNumber or others which is depended on your value. 
val temp1 = (__ \ 'key1' \ 'key2' \ 'key3' ).json.put(JsString(<your_value>))

// Or you want to remove one branch from your json
val temp2 = (__ \ 'key1' \ 'key2').json.prune

// or pick a value and then overwrite it
val temp3 = (__ \ 'key1' ).json.pickBranch(
  (__ \ 'key2').json.update(
    of[JsNumber].map{case JsNumber(nb) => JsNumber(nb + 10)
  ) and Then
  (__ \ 'key21').json.update(
    of[JsArray].map{case JsArray(arr) => JsArray(arr :+ JsString("abc"))}
  )
)

// or pick a branch and remove sub-branch
val temp4 = (__ \ 'key2').json.pickBranch(
  (__ \ 'key23').json.prune
)

// update to your json
val newJson = oldJson.transform(temp)
newJson match {
  case JsSuccess(value, path) =>
    // here value is your final json 
    ....
  case _ =>
}

Detailed API usage, you need to check its document.

Useful link:

https://www.playframework.com/documentation/2.2.x/ScalaJson

https://www.playframework.com/documentation/2.0/api/scala/play/api/libs/json/JsArray.html

https://www.playframework.com/documentation/2.3.x/ScalaJsonTransformers