Higher MySQL RAM usage, need to optimize

[cbu]

Hello all!

I have VPS with running 2.5GB RAM, Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz (cache 15360 KB). It front end Nginx and backend Apache. This VPS run two wordpress sites. First site page preview 3,000-3,500 (daily) second site 500-600 (daily). First site mysql database size is 56.1MB and 2nd site MySQL database size is 10.3MB.

[email protected] [~]# mysql --version
mysql  Ver 14.14 Distrib 5.5.35, for Linux (i686) using readline 5.1

When I'm running ps -aux command, I can see higher RAM usage

[email protected] [~]# ps -aux
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ

USER   PID    %CPU %MEM    VSZ   RSS      TTY   STAT START   TIME COMMAND
mysql  11980  0.4       3.5   755956 93016   ?        Sl   07:50     0:05  /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql -

When I'm running free -m I receive following output.

[email protected] [~]# free -m
             total       used       free     shared    buffers     cached
Mem:          2560       1927        632          0          0       1516
-/+ buffers/cache:        410       2149
Swap:            0          0          0

When I'm running MySQL tuner, I get following output.

[email protected] [~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-cll
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 66M (Tables: 239)
[--] Data in InnoDB tables: 4M (Tables: 28)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 7

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21m 18s (22K q [17.603 qps], 380 conn, TX: 97M, RX: 3M)
[--] Reads / Writes: 78% / 22%
[--] Total buffers: 608.0M global + 3.6M per thread (50 max threads)
[OK] Maximum possible memory usage: 786.1M (30% of installed RAM)
[OK] Slow queries: 0% (0/22K)
[OK] Highest usage of available connections: 10% (5/50)
[OK] Key buffer size / total MyISAM indexes: 16.0M/12.9M
[!!] Key buffer hit rate: 93.0% (320K cached / 22K reads)
[OK] Query cache efficiency: 59.8% (11K cached / 18K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Joins performed without indexes: 74
[OK] Temporary tables created on disk: 19% (192 on disk / 1K total)
[OK] Thread cache hit rate: 98% (5 created / 380 connections)
[!!] Table cache hit rate: 2% (96 open / 4K opened)
[OK] Open file limit used: 9% (177/1K)
[OK] Table locks acquired immediately: 100% (10K immediate / 10K locks)
[OK] InnoDB data size / buffer pool: 4.9M/384.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_cache (> 96)

[email protected] [~]#

This is my MySQL configuration file (my.cnf):- /http://pastebin.com/x5FwuKiK

How do I reduce MySQL memory usage?

Thanks!

 

[thinkbot]

replace my.cnf with this

[mysqld]
default-storage-engine = MyISAM
local-infile = 0

max_connections = 50
wait_timeout = 30
connect_timeout = 10

thread_cache_size = 16
table_open_cache = 700

key_buffer_size = 30M

join_buffer_size = 512K
sort_buffer_size=256K

query_cache_type = 1
query_cache_size = 30M
query_cache_limit = 1M

tmp_table_size = 30M
max_heap_table_size = 30M

open_files_limit=5000

innodb_buffer_pool_size = 30M
innodb_file_per_table = 1
innodb_stats_on_metadata=0

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1


memory savings will be huge, without loosing anything from the performance

 

[cbu]

Thank you thinkbot your support.

Now it giving me following recommendation. Could you please tell me how to correct that issue? Also is it OK use "Maximum possible memory usage: 201.6M (7% of installed RAM)"

Could you please tell me how do I adjust Maximum possible memory usage?

This is my MySQLTuner log.

[email protected] [~]# mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-cll
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 54M (Tables: 239)
[--] Data in InnoDB tables: 4M (Tables: 28)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 12

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16m 35s (16K q [16.864 qps], 317 conn, TX: 78M, RX: 2M)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 136.0M global + 1.3M per thread (50 max threads)
[OK] Maximum possible memory usage: 201.6M (7% of installed RAM)
[OK] Slow queries: 0% (0/16K)
[OK] Highest usage of available connections: 10% (5/50)
[OK] Key buffer size / total MyISAM indexes: 30.0M/12.5M
[OK] Key buffer hit rate: 99.8% (255K cached / 555 reads)
[OK] Query cache efficiency: 60.4% (8K cached / 14K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 793 sorts)
[!!] Joins performed without indexes: 55
[OK] Temporary tables created on disk: 19% (135 on disk / 678 total)
[OK] Thread cache hit rate: 98% (5 created / 317 connections)
[OK] Table cache hit rate: 25% (314 open / 1K opened)
[OK] Open file limit used: 10% (533/5K)
[OK] Table locks acquired immediately: 100% (6K immediate / 6K locks)
[OK] InnoDB data size / buffer pool: 4.8M/30.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 512.0K, or always use indexes with joins)

replace my.cnf with this

[mysqld]
default-storage-engine = MyISAM
local-infile = 0

 

[thinkbot]

There is nothing to adjust now, it just tells you that your maximum memory usage for MySQL can be 201.6M, meaning 7% of installed RAM

previously it was 786.1MB
[OK] Maximum possible memory usage: 786.1M (30% of installed RAM)

of course it's not very accurate, its just a pointer, calculating mysql memory is bit more complicated

 

[cPanelMichael]

Hello

To note, you might find the tuner available in the following thread better suited towards newer versions of MySQL:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[cbu]

Now I'm using thinkbot updated configuration.

Looks like table_cache getting lower in every minutes. 20 minutes ago it's ratio is 98%, but now it's reduced to 10%. PHPMyAdmin advice to "Flushing the query cache". Here's it advice,

Issue:
Less than 80% of the query cache is being utilized.

Recommendation:
This might be caused by query_cache_limit being too low. Flushing the query cache might help as well.

Justification:
The current ratio of free query cache memory to total query cache size is 38.6%. It should be above 80%

Used variable / formula:
100 - Qcache_free_memory / query_cache_size * 100

Test:
value < 80

This is the MySQL Tuner result.

[email protected] [~]# mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-cll
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 55M (Tables: 239)
[--] Data in InnoDB tables: 4M (Tables: 28)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 11

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 16m 39s (79K q [17.198 qps], 1K conn, TX: 383M, RX: 12M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 136.0M global + 1.3M per thread (50 max threads)
[OK] Maximum possible memory usage: 201.6M (7% of installed RAM)
[OK] Slow queries: 0% (8/79K)
[OK] Highest usage of available connections: 10% (5/50)
[OK] Key buffer size / total MyISAM indexes: 30.0M/12.5M
[OK] Key buffer hit rate: 99.9% (1M cached / 753 reads)
[OK] Query cache efficiency: 64.2% (44K cached / 69K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 3K sorts)
[!!] Joins performed without indexes: 266
[OK] Temporary tables created on disk: 20% (710 on disk / 3K total)
[OK] Thread cache hit rate: 99% (5 created / 1K connections)
[!!] Table cache hit rate: 6% (328 open / 4K opened)
[OK] Open file limit used: 10% (546/5K)
[OK] Table locks acquired immediately: 99% (30K immediate / 30K locks)
[OK] InnoDB data size / buffer pool: 4.8M/30.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
join_buffer_size (> 512.0K, or always use indexes with joins)
table_cache (> 700)

How do I fix this issue?

- - - Updated - - -

Thank you Michael. I use MySQLTuner. So if I use both of these scripts same time, it will cause any issue?

Hello

To note, you might find the tuner available in the following thread better suited towards newer versions of MySQL:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

- - - Updated - - -

Thank you Michael. I use MySQLTuner. So if I use both of these scripts same time, it will cause any issue?

Hello

To note, you might find the tuner available in the following thread better suited towards newer versions of MySQL:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[thinkbot]

Hey, this is not an issue, you got 239 MyiSAM tables, they all fit in table_open_cache 700 buffer, there are also some temporary tables created along the way
It's very good

 

[cbu]

Hey, this is not an issue, you got 239 MyiSAM tables, they all fit in table_open_cache 700 buffer, there are also some temporary tables created along the way
It's very good

Thank thinkbot.

 

[cbu]

Randomly I can see several MySQL server has gone away for query MySQL error. How do I fix it?

eg:-

WordPress database error MySQL server has gone away for query SELECT option_value FROM xxxx_xxxx_options WHERE option_name = '_transient_doing_cron' LIMIT 1 made by _get_cron_lock

 

[thinkbot]

this particular query should be immediate, probably query before that one took longer than wait_timeout allowed
checkout slow query log

 

[cbu]

After the 24 hours, I get following result.

Last login: Sat Feb 15 18:26:37 2014 from 103.21.164.6
[email protected] [~]# ./mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-cll
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 27M (Tables: 239)
[--] Data in InnoDB tables: 4M (Tables: 28)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 21

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 1h 35m 25s (1M q [20.118 qps], 27K conn, TX: 8B, RX: 558M)
[--] Reads / Writes: 65% / 35%
[--] Total buffers: 146.0M global + 1.3M per thread (50 max threads)
[OK] Maximum possible memory usage: 211.6M (8% of installed RAM)
[OK] Slow queries: 0% (100/1M)
[!!] Highest connection usage: 100% (51/50)
[OK] Key buffer size / total MyISAM indexes: 30.0M/7.6M
[OK] Key buffer hit rate: 100.0% (39M cached / 1K reads)
[OK] Query cache efficiency: 64.8% (951K cached / 1M selects)
[!!] Query cache prunes per day: 11911
[OK] Sorts requiring temporary tables: 0% (66 temp sorts / 76K sorts)
[!!] Joins performed without indexes: 5632
[OK] Temporary tables created on disk: 16% (12K on disk / 74K total)
[OK] Thread cache hit rate: 96% (986 created / 27K connections)
[!!] Table cache hit rate: 0% (521 open / 101K opened)
[OK] Open file limit used: 14% (723/5K)
[OK] Table locks acquired immediately: 99% (792K immediate / 792K locks)
[OK] InnoDB data size / buffer pool: 4.8M/30.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
max_connections (> 50)
wait_timeout (< 30)
interactive_timeout (< 28800)
query_cache_size (> 30M)
join_buffer_size (> 512.0K, or always use indexes with joins)
table_cache (> 700)

I changed
max_connections = 80
query_cache_size = 40M

But no idea about following things, what could be the proper value for them?

wait_timeout (< 30)
interactive_timeout (< 28800)


When I run MySQL tuning primer it gives following output.

Last login: Sat Feb 15 17:35:56 2014 from 103.21.164.6
[email protected] [~]# tuning-primer.sh

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.5.35-cll i686

Uptime = 1 days 1 hrs 33 min 4 sec
Avg. qps = 20
Total Questions = 1851450
Threads Connected = 2

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
MySQL :: MySQL 5.5 Reference Manual :: 5.1.4 Server System Variables
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 0.100000 sec.
You have 100 out of 1851471 that take longer than 0.100000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See MySQL :: MySQL 5.5 Reference Manual :: 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log

WORKER THREADS
Current thread_cache_size = 16
Current threads_cached = 14
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 50
Current threads_connected = 2
Historic max_used_connections = 51
The number of used connections is 102% of the configured maximum.
You should raise max_connections

INNODB STATUS
Current InnoDB index space = 1 M
Current InnoDB data space = 4 M
Current InnoDB buffer pool free = 51 %
Current innodb_buffer_pool_size = 30 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 172 M
Configured Max Per-thread Buffers : 65 M
Configured Max Global Buffers : 106 M
Configured Max Memory Limit : 171 M
Physical Memory : 2.50 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 7 M
Current key_buffer_size = 30 M
Key cache miss rate is 1 : 24817
Key buffer free ratio = 79 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 30 M
Current query_cache_used = 24 M
Current query_cache_limit = 2 M
Current Query cache Memory fill ratio = 80.27 %
Current query_cache_min_res_unit = 4 K
However, 12701 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 516.00 K
You have had 5626 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 5000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 700 tables
Current table_definition_cache = 400 tables
You have a total of 308 tables
You have 521 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 40 M
Current tmp_table_size = 40 M
Of 61907 temp tables, 16% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 166 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 3219
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=ALWAYS'.

[email protected] [~]#

 

[thinkbot]

you dont have to increase all the values recommended by this script, those are only pointers, you need to understand the impact of the change before making the actuall change

Please review your slow queries log, since you probably have some queries that stuck your server, and this is why your connections limit gets to max

you dont have to increase or change those values
wait_timeout (< 30)
interactive_timeout (< 28800)

 

[cbu]

this particular query should be immediate, probably query before that one took longer than wait_timeout allowed
checkout slow query log

Thank you thinkbot your support. I checked the slow query log, it has lots of things.

# Query_time: 0.283086 Lock_time: 0.000267 Rows_sent: 0 Rows_examined: 1
use database_name_here;
SET timestamp=1392400502;
UPDATE `table_prefix_here_options` SET `option_value`

Most of the log query shows wp options table. Yes there are some wordpress plugin too. Specially security plugin (eg:-Wordfence Security)

- - - Updated - - -

you dont have to increase all the values recommended by this script, those are only pointers, you need to understand the impact of the change before making the actuall change

Please review your slow queries log, since you probably have some queries that stuck your server, and this is why your connections limit gets to max

you dont have to increase or change those values
wait_timeout (< 30)
interactive_timeout (< 28800)

you can increase table_open_cache to 6000
since you got a lot of temporary tables

Thanks again thinkbot. I just increased table_open_cache. Looks like it improved the page loading time.

Are there any way to periodically delete unwanted table cache? I think only way to reduce temporary tables is restart the SQL server. Am I correct? I can't restart SQL service regularly.

 

[thinkbot]

"Are there any way to periodically delete unwanted table cache? I think only way to reduce temporary tables is restart the SQL server. Am I correct? I can't restart SQL service regularly."

You don't understand, there is nothing to deltee there
to reduce temporary tables on disk, you have to optimize/rewrite slow queries

First check what queries takes the most time, to do that review slow query log
then you can check why they take so much time, and optimize them

and install munin plugin in WHM, so you can have better view on server resources usage

 

[cbu]

"Are there any way to periodically delete unwanted table cache? I think only way to reduce temporary tables is restart the SQL server. Am I correct? I can't restart SQL service regularly."

You don't understand, there is nothing to deltee there
to reduce temporary tables on disk, you have to optimize/rewrite slow queries

First check what queries takes the most time, to do that review slow query log
then you can check why they take so much time, and optimize them

and install munin plugin in WHM, so you can have better view on server resources usage

Thanks again. Yes, I have munin server monitor plugin. Again thank you so much for your help. :D

 

[thinkbot]

if you have, you can post here please images for CPU, LOAD, RAM ? it would show us more the difference and current load

 

[cbu]

if you have, you can post here please images for CPU, LOAD, RAM ? it would show us more the difference and current load

Thank you again think bot your support. These are the MUNIN screenshots. Looks like slow mysql queries started after tweak the my.cnf file. Is it its configuration issue or just a normal?

MySQL



RAM.


Load.


CPU

[mysqld]
default-storage-engine = MyISAM
local-infile = 0
event_scheduler = on

max_connections = 80
wait_timeout = 30
connect_timeout = 10

thread_cache_size = 16
table_open_cache = 6000

key_buffer_size = 30M

join_buffer_size = 512K
sort_buffer_size = 256K

query_cache_type = 1
query_cache_size = 70M
query_cache_limit = 4M
query_cache_wlock_invalidate=ON

tmp_table_size = 80M
max_heap_table_size = 80M

open_files_limit=5000

innodb_buffer_pool_size = 30M
innodb_buffer_pool_instances = 6
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 6
innodb_stats_on_metadata = 0
innodb_use_sys_malloc = 0

#sql tune prime recommendations
low_priority_updates = 1
concurrent_insert=ALWAYS

#general_log
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-error = /var/log/mysqld.log
log-queries-not-using-indexes

 

[cbu]

Is it ok if I adjust long_query_time=0.1 value. Because I can see lots of WordPress database error MySQL server has gone away for query errors in WordPress debug.

 

[thinkbot]

long_query_time=0.1
means only to mark queries as slow when they take more than 0,1s

so it doesn't make any difference, it's just for logging queries, so we know which ones are slower

the best thing you can do now, is make a reeview of slow queries
cd /root
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

and then copy contents of slow.txt here in [code brackets, or on some file upload site

 

[cbu]

long_query_time=0.1
means only to mark queries as slow when they take more than 0,1s

so it doesn't make any difference, it's just for logging queries, so we know which ones are slower

the best thing you can do now, is make a reeview of slow queries
cd /root
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

and then copy contents of slow.txt here in [code brackets, or on some file upload site

I just installed that package. How much time should I wait for take that report?