Mysql Trouble Shooting

Mysql Trouble Shooting

Check CPU Info
>nproc
4

4 cores CPU on the server
More information for the CPU
>lscpu
Architecture:          x86_64 CPU op-mode(s):        32-bit, 64-bit Byte Order:            Little Endian CPU(s):                4 On-line CPU(s) list:   0-3 Thread(s) per core:    1 Core(s) per socket:    1 Socket(s):             4 NUMA node(s):          1 Vendor ID:             AuthenticAMD CPU family:            21 Model:                 2 Stepping:              0 CPU MHz:               3000.104 BogoMIPS:              6000.20 Hypervisor vendor:     Xen Virtualization type:   para L1d cache:             16K L1i cache:             64K L2 cache:              2048K L3 cache:              6144K NUMA node0 CPU(s):     0-3


Check Indexes 
>show index from table_name;

Check the Running SQL
>show full processlist;

Open the Slow Query Log 
Option 1
>sudo vi /etc/mysql/my.cnf
This is the right configuration for mysql 5.6
slow-query-log-file = /var/log/mysql/mysql-slow.loglong_query_time = 5#log-queries-not-using-indexes

But this way requires mysql restart.

Option 2
>show variables like "slow%";
+------------------------------------+------------------------------+ | Variable_name                      | Value                        | +------------------------------------+------------------------------+ | slow_launch_time                   | 2                            | | slow_query_log                     | OFF                          | | slow_query_log_always_write_time   | 10.000000                    | | slow_query_log_file                | /var/lib/mysql/ldb2-slow.log | | slow_query_log_timestamp_always    | OFF                          | | slow_query_log_timestamp_precision | second                       | | slow_query_log_use_global_control  |    

check the long query time
>show variables like "long%";
+-----------------+-----------+ | Variable_name   | Value     | +-----------------+-----------+ | long_query_time | 10.000000 |

Change the long query time
>set long_query_time = 5;

Opent the slow query log
>set global slow_query_log = ON:

After that I got the slow query file, use mysqldumpslow to check that
>mysqldumpslow -s c -t 5 ./ldb2-slow.log
-s c, t, l, r means count, time, query time, results
-t top

After I found the SQL I will use this command to check more info
>explain SQL

Finally, I found the slow SQL.

Export the data into a file
>select device_id from device where tenant_id = 9 and device_id like 'serveraws1\_%'
    -> INTO OUTFILE '/tmp/serveraws1_device.csv'    -> ; Query OK, 90712 rows affected (0.07 sec)



References:
http://www.cyberciti.biz/faq/linux-get-number-of-cpus-core-command/
http://stackoverflow.com/questions/3202424/show-indexes-in-mysql-table

http://www.oicto.com/mysql-explain-show/