Julie Kurpa
asked on
MySQL Init Waits
I have a MySQL database running on Linux. I set up this database to hold syslog data and it's working fine. I still know very little about MySQL and can only stumble along as I try to learn.
I recently began monitoring the MySQL database though a database monitoring tool. The tool is indicating long waits (INIT Waits) related to a user "loguser". The files referenced by the tool are: /mysqldata/ib_logfile1 and /mysqldata/ib_logfile0
The monitor tool describes an INIT wait as follows:
"This wait occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. It represents time spent flushing the binary log, the InnoDB log, and other query cache cleanup operations.
This wait is typically a concern when it is the predominant wait for all or the majority of SQLs."
In delving deeper, the waits appear to be when commits are done and not necessarily when a particular SQL is executed.
I'd like some assistance with determining if I can tweak anything to make this database more efficient.
Since I know so little about MySQL, I'm sure that I've missed some important tuning steps when I created the database.
Thank you.
I recently began monitoring the MySQL database though a database monitoring tool. The tool is indicating long waits (INIT Waits) related to a user "loguser". The files referenced by the tool are: /mysqldata/ib_logfile1 and /mysqldata/ib_logfile0
The monitor tool describes an INIT wait as follows:
"This wait occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. It represents time spent flushing the binary log, the InnoDB log, and other query cache cleanup operations.
This wait is typically a concern when it is the predominant wait for all or the majority of SQLs."
In delving deeper, the waits appear to be when commits are done and not necessarily when a particular SQL is executed.
I'd like some assistance with determining if I can tweak anything to make this database more efficient.
Since I know so little about MySQL, I'm sure that I've missed some important tuning steps when I created the database.
Thank you.
ASKER
Hi Tomas,
Thanks for the links! I'm going through them now.
Here are the variables you asked about. Are these in bytes?
innodb_log_buffer_size = 8388608
innodb_log_file_size = 50331648
Thanks for the links! I'm going through them now.
Here are the variables you asked about. Are these in bytes?
innodb_log_buffer_size = 8388608
innodb_log_file_size = 50331648
Hi!
innodb_log_file_size = 50331648 ( 50 MB)
You should at least set it 2x this size to begin with and monitor the db after the change.
After that increase the innodb_log_buffer_size at least 1.5x.
Regards,
Tomas Helgi
innodb_log_file_size = 50331648 ( 50 MB)
You should at least set it 2x this size to begin with and monitor the db after the change.
After that increase the innodb_log_buffer_size at least 1.5x.
Regards,
Tomas Helgi
ASKER
so I'm reading the steps to increase the innodb_log_file_size variable.
I just did a status on the database and see this message below. Is this a concern before I start messing with anything?
Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable.
I just did a status on the database and see this message below. Is this a concern before I start messing with anything?
Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable.
ASKER
I got brave and restarted the MySQL service. The message no longer shows.
In preparing for increasing the innodb_log_file_size, I reviewed the MySQL log for errors. Should I do anything about the following messages?
[Warning TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_ti mestamp server option (see documentation for details)
[Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
[Warning] Buffered warning: changed limits: table_cache: 432 (requested 2000)
I found some instructions on increasing max_open_files ( I think) but am confused about the table_cache and whether I should leave that smallish.
What do you think?
In preparing for increasing the innodb_log_file_size, I reviewed the MySQL log for errors. Should I do anything about the following messages?
[Warning TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_ti
[Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
[Warning] Buffered warning: changed limits: table_cache: 432 (requested 2000)
I found some instructions on increasing max_open_files ( I think) but am confused about the table_cache and whether I should leave that smallish.
What do you think?
Hi!
This looks ok.
If you tail your mysql log file. Usually in the /var/log/ directory. Do you see any errors or warnings.
One thing you could do and should is install mysqltuner and run it after mysql has been running for at least 24 hours.
It gives you good tuning advices on what parameters you could increase.
You could download it through your Linux repo or here
Regards,
Tomas Helgi
This looks ok.
If you tail your mysql log file. Usually in the /var/log/ directory. Do you see any errors or warnings.
One thing you could do and should is install mysqltuner and run it after mysql has been running for at least 24 hours.
It gives you good tuning advices on what parameters you could increase.
You could download it through your Linux repo or here
Regards,
Tomas Helgi
ASKER
Thanks Tomas,
Those warnings that I posted were from the /var/log/mysqld.log
[Warning TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_ti mestamp server option (see documentation for details)
[Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
[Warning] Buffered warning: changed limits: table_cache: 432 (requested 2000)
Those warnings that I posted were from the /var/log/mysqld.log
[Warning TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_ti
[Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
[Warning] Buffered warning: changed limits: table_cache: 432 (requested 2000)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. I will install the tuner on Monday and see what it tells me.
I may not update this question for several days. ...but I will update. :)
I may not update this question for several days. ...but I will update. :)
ASKER
Hello! I'm am just now getting to install the SQL Tuner and will keep you posted.
ASKER
Got SQL Tuner installed. Here's the initial Performance section of the report:
-------- Performance Metrics -------------------------- ---------- ---------- ---------- ---------- -----
[--] Up for: 10d 22h 15m 34s (3M q [4.022 qps], 2K conn, TX: 3G, RX: 2G)
[--] Reads / Writes: 29% / 71%
[--] Binary logging is disabled
[--] Physical Memory : 3.6G
[--] Max MySQL memory : 410.5M
[--] Other process memory: 611.4M
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 71M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 250.7M (6.78% of installed RAM)
[OK] Maximum possible memory usage: 410.5M (11.10% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (5/3M)
[OK] Highest usage of available connections: 5% (9/151)
[OK] Aborted connections: 0.17% (4/2412)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 242K sorts)
[!!] Joins performed without indexes: 4428741
[OK] Temporary tables created on disk: 22% (1M on disk / 8M total)
[OK] Thread cache hit rate: 99% (9 created / 2K connections)
[OK] Table cache hit rate: 95% (144 open / 151 opened)
[OK] Open file limit used: 9% (100/1K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
-------- Performance schema -------------------------- ---------- ---------- ---------- ---------- ------
[--] Performance schema is enabled.
[--] Memory used by P_S: 71.6M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics -------------------------- ---------- ---------- ---------- ---------- ------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics -------------------------- ---------- ---------- ---------- ---------- ----------
[!!] Key buffer used: 18.5% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/116.0K
[OK] Read Key buffer hit rate: 100.0% (9M cached / 21 reads)
[OK] Write Key buffer hit rate: 100.0% (2M cached / 0 writes)
-------- InnoDB Metrics -------------------------- ---------- ---------- ---------- ---------- ----------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/123.4M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5 %): 48.0M/128.0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instanc es(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.98% (105380244 hits/ 105406185 total)
[OK] InnoDB Write log efficiency: 91.30% (2767302 hits/ 3030915 total)
[OK] InnoDB log waits: 0.00% (0 waits / 263613 writes)
-------- Performance Metrics --------------------------
[--] Up for: 10d 22h 15m 34s (3M q [4.022 qps], 2K conn, TX: 3G, RX: 2G)
[--] Reads / Writes: 29% / 71%
[--] Binary logging is disabled
[--] Physical Memory : 3.6G
[--] Max MySQL memory : 410.5M
[--] Other process memory: 611.4M
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 71M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 250.7M (6.78% of installed RAM)
[OK] Maximum possible memory usage: 410.5M (11.10% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (5/3M)
[OK] Highest usage of available connections: 5% (9/151)
[OK] Aborted connections: 0.17% (4/2412)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 242K sorts)
[!!] Joins performed without indexes: 4428741
[OK] Temporary tables created on disk: 22% (1M on disk / 8M total)
[OK] Thread cache hit rate: 99% (9 created / 2K connections)
[OK] Table cache hit rate: 95% (144 open / 151 opened)
[OK] Open file limit used: 9% (100/1K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
-------- Performance schema --------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 71.6M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics --------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics --------------------------
[!!] Key buffer used: 18.5% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/116.0K
[OK] Read Key buffer hit rate: 100.0% (9M cached / 21 reads)
[OK] Write Key buffer hit rate: 100.0% (2M cached / 0 writes)
-------- InnoDB Metrics --------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/123.4M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5 %): 48.0M/128.0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instanc
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.98% (105380244 hits/ 105406185 total)
[OK] InnoDB Write log efficiency: 91.30% (2767302 hits/ 3030915 total)
[OK] InnoDB log waits: 0.00% (0 waits / 263613 writes)
ASKER
I changed the parameters below to the values shown. The init waits have gone from 2 seconds to 1 second.
Will run the Tuner tomorrow and compare results with todays.
Will run the Tuner tomorrow and compare results with todays.
ASKER
Here's the performance report after making those changes:
-------- Performance Metrics -------------------------- ---------- ---------- ---------- ---------- -----
[--] Up for: 6d 2h 55m 8s (2M q [4.090 qps], 1K conn, TX: 1G, RX: 1G)
[--] Reads / Writes: 31% / 69%
[--] Binary logging is disabled
[--] Physical Memory : 3.6G
[--] Max MySQL memory : 422.5M
[--] Other process memory: 271.6M
[--] Total buffers: 181.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 71M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 262.7M (7.11% of installed RAM)
[OK] Maximum possible memory usage: 422.5M (11.43% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[!!] Slow queries: 25% (550K/2M)
[OK] Highest usage of available connections: 5% (9/151)
[OK] Aborted connections: 0.18% (3/1671)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 144K sorts)
[!!] Joins performed without indexes: 2644484
[OK] Temporary tables created on disk: 22% (1M on disk / 4M total)
[OK] Thread cache hit rate: 99% (9 created / 1K connections)
[OK] Table cache hit rate: 95% (142 open / 149 opened)
[OK] Open file limit used: 9% (100/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Performance schema -------------------------- ---------- ---------- ---------- ---------- ------
[--] Performance schema is enabled.
[--] Memory used by P_S: 71.6M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics -------------------------- ---------- ---------- ---------- ---------- ------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics -------------------------- ---------- ---------- ---------- ---------- ----------
[!!] Key buffer used: 18.5% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/116.0K
[OK] Read Key buffer hit rate: 100.0% (5M cached / 21 reads)
[OK] Write Key buffer hit rate: 100.0% (1M cached / 0 writes)
-------- InnoDB Metrics -------------------------- ---------- ---------- ---------- ---------- ----------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/124.4M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (78.125 %): 100.0M/128.0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instanc es(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.97% (58088622 hits/ 58105932 total)
[OK] InnoDB Write log efficiency: 91.35% (1559504 hits/ 1707179 total)
[OK] InnoDB log waits: 0.00% (0 waits / 147675 writes)
-------- Recommendations -------------------------- ---------- ---------- ---------- ---------- ---------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE syslogdb.SystemEvents; -- can free 23 MB
Total freed space after theses OPTIMIZE TABLE : 23 Mb
71 CVE(s) found for your MySQL release. Consider upgrading your version !
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 256.0K, or always use indexes with joins)
innodb_log_file_size should be equals to 1/4 of buffer pool size (=32M) if possible.
innodb_buffer_pool_instanc es (=1)
-------- Performance Metrics --------------------------
[--] Up for: 6d 2h 55m 8s (2M q [4.090 qps], 1K conn, TX: 1G, RX: 1G)
[--] Reads / Writes: 31% / 69%
[--] Binary logging is disabled
[--] Physical Memory : 3.6G
[--] Max MySQL memory : 422.5M
[--] Other process memory: 271.6M
[--] Total buffers: 181.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 71M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 262.7M (7.11% of installed RAM)
[OK] Maximum possible memory usage: 422.5M (11.43% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[!!] Slow queries: 25% (550K/2M)
[OK] Highest usage of available connections: 5% (9/151)
[OK] Aborted connections: 0.18% (3/1671)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 144K sorts)
[!!] Joins performed without indexes: 2644484
[OK] Temporary tables created on disk: 22% (1M on disk / 4M total)
[OK] Thread cache hit rate: 99% (9 created / 1K connections)
[OK] Table cache hit rate: 95% (142 open / 149 opened)
[OK] Open file limit used: 9% (100/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
-------- Performance schema --------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 71.6M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics --------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics --------------------------
[!!] Key buffer used: 18.5% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/116.0K
[OK] Read Key buffer hit rate: 100.0% (5M cached / 21 reads)
[OK] Write Key buffer hit rate: 100.0% (1M cached / 0 writes)
-------- InnoDB Metrics --------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/124.4M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (78.125 %): 100.0M/128.0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instanc
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.97% (58088622 hits/ 58105932 total)
[OK] InnoDB Write log efficiency: 91.35% (1559504 hits/ 1707179 total)
[OK] InnoDB log waits: 0.00% (0 waits / 147675 writes)
-------- Recommendations --------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE syslogdb.SystemEvents; -- can free 23 MB
Total freed space after theses OPTIMIZE TABLE : 23 Mb
71 CVE(s) found for your MySQL release. Consider upgrading your version !
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 256.0K, or always use indexes with joins)
innodb_log_file_size should be equals to 1/4 of buffer pool size (=32M) if possible.
innodb_buffer_pool_instanc
Hi!
This I suggest you take a closer look at
[!!] Joins performed without indexes: 2644484
You can increase join_buffer_size or what is better list out the queries not using index and put indexes on the tables that matches the queries join and where clause columns.
Regards,
Tomas Helgi
This I suggest you take a closer look at
[!!] Joins performed without indexes: 2644484
You can increase join_buffer_size or what is better list out the queries not using index and put indexes on the tables that matches the queries join and where clause columns.
Regards,
Tomas Helgi
Hi!
How is the mysql server performing now ?
Are there any more issues ?
Regards,
Tomas Helgi
How is the mysql server performing now ?
Are there any more issues ?
Regards,
Tomas Helgi
ASKER
Thanks for the feedback Tomas. I was on vacation and just returned today. Jumping into this again.
ASKER
Can you tell me how to list out queries that are not using indexes?
Hi!
You need to enable the slow query log for that.
In my.cnf you enable both
http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
Regards,
Tomas Helgi
You need to enable the slow query log for that.
In my.cnf you enable both
slow-query-log=1
log_queries_not_using_indexes=1
and also setlong_query_time = max time in seconds that query may run on your server (default 10 seconds)
Just make sure you set this high enough to catch the "top of the iceberg" queries first. Low value can produce large output file in few seconds on high traffic system depending on how good/bad your queries are. :)http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
Regards,
Tomas Helgi
ASKER
Thanks!
I have done the following:
set global slow_query_log_file='/mysq ldata/db-s low.log';
set global long_query_time=10;
set global log_queries_not_using_inde xes='ON';
set global slow_query_log = 'ON';
In looking at the contents of /mysqldata/db-slow.log, it's mostly the queries done by the performance analyzer (Solarwinds DPA). I manually ran several of the queries shown and the performance was instant.
I increased the long_query_time to 20. Still seeing those same queries along with some others. In manually running those queries, they are extremely fast.
I'm not understanding the value for long_query_time. From what I've read, it's supposed to be queries running for xx number of seconds, right? Yet when I manually run those queries, they are less than 1 second.
What am I doing wrong?
I have done the following:
set global slow_query_log_file='/mysq
set global long_query_time=10;
set global log_queries_not_using_inde
set global slow_query_log = 'ON';
In looking at the contents of /mysqldata/db-slow.log, it's mostly the queries done by the performance analyzer (Solarwinds DPA). I manually ran several of the queries shown and the performance was instant.
I increased the long_query_time to 20. Still seeing those same queries along with some others. In manually running those queries, they are extremely fast.
I'm not understanding the value for long_query_time. From what I've read, it's supposed to be queries running for xx number of seconds, right? Yet when I manually run those queries, they are less than 1 second.
What am I doing wrong?
Hi!
Then those queries are not using indexes.
This setup I gave you logs both queries not using indexes as well as queries taking longer than N seconds.
Take one of the queries and do explain or explain extended on it to see it's execution plan.
http://dev.mysql.com/doc/refman/5.7/en/using-explain.html
You could, by adding some indexes to the tables that those queries are running on, shorten those exectution times even more. : )
Regards,
Tomas Helgi
Then those queries are not using indexes.
This setup I gave you logs both queries not using indexes as well as queries taking longer than N seconds.
Take one of the queries and do explain or explain extended on it to see it's execution plan.
http://dev.mysql.com/doc/refman/5.7/en/using-explain.html
You could, by adding some indexes to the tables that those queries are running on, shorten those exectution times even more. : )
Regards,
Tomas Helgi
ASKER
I can't help wondering if we are barking up the wrong tree. I've read that it's not always a bad thing for a query to not use indexes. If I can run the query manually and it runs in 2 miliseconds, I don't see how I can get it to run better.
Hi!
Well it all depends on the size of the data in the tables. If you have very small tables (below 1000 rows) then queries will run fast whether it's using tablescan or index scan. The difference is not significant. However when data grows you will want the queries to run on indexes as it is much faster than running tablescans on very large tables. In case of very large tables index usage becomes very significant.
Regards,
Tomas Helgi
Well it all depends on the size of the data in the tables. If you have very small tables (below 1000 rows) then queries will run fast whether it's using tablescan or index scan. The difference is not significant. However when data grows you will want the queries to run on indexes as it is much faster than running tablescans on very large tables. In case of very large tables index usage becomes very significant.
Regards,
Tomas Helgi
ASKER
That makes sense.
This database really only contains 3 tables that I created for the application. Two are tiny code tables and the third is a syslog table capturing network stuff. There's over 400,000 records in the syslog table so far.
I do have a couple indexes on the syslog table.
I disabled a database performance analyzer (DPA) that was running against this database and so far, I don't see anything being logged as not using indexes.
So it seems that the DPA was the one racking up the counts of SQL not using indexes.
I have done more tweaking to variables and re-running the mysqltuner.pl. Here's a snippet below. It seems better.
-------- Performance Metrics -------------------------- ---------- ---------- ---------- ---------- -----
[--] Up for: 1d 1h 57m 24s (287K q [3.075 qps], 2K conn, TX: 84M, RX: 76M)
[--] Reads / Writes: 2% / 98%
[--] Binary logging is disabled
[--] Physical Memory : 3.6G
[--] Max MySQL memory : 1.8G
[--] Other process memory: 279.4M
[--] Total buffers: 1.6G global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 71M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.6G (45.01% of installed RAM)
[OK] Maximum possible memory usage: 1.8G (49.51% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/287K)
[OK] Highest usage of available connections: 1% (3/151)
[OK] Aborted connections: 0.19% (5/2612)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10 sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 38% (120 on disk / 313 total)
[OK] Thread cache hit rate: 99% (3 created / 2K connections)
[OK] Table cache hit rate: 95% (142 open / 149 opened)
[OK] Open file limit used: 9% (101/1K)
[OK] Table locks acquired immediately: 99% (229K immediate / 229K locks)
-------- Performance schema -------------------------- ---------- ---------- ---------- ---------- ------
[--] Performance schema is enabled.
[--] Memory used by P_S: 71.6M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics -------------------------- ---------- ---------- ---------- ---------- ------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics -------------------------- ---------- ---------- ---------- ---------- ----------
[!!] Key buffer used: 18.5% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/116.0K
[!!] Read Key buffer hit rate: 76.9% (91 cached / 21 reads)
-------- InnoDB Metrics -------------------------- ---------- ---------- ---------- ---------- ----------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.5G/124.4M
[OK] InnoDB log file size / InnoDB Buffer pool size: 385.0M/1.5G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.97% (10258651 hits/ 10261595 total)
[OK] InnoDB Write log efficiency: 91.30% (275276 hits/ 301509 total)
[OK] InnoDB log waits: 0.00% (0 waits / 26233 writes)
-------- Recommendations -------------------------- ---------- ---------- ---------- ---------- ---------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE syslogdb.SystemEvents; -- can free 23 MB
Total freed space after theses OPTIMIZE TABLE : 23 Mb
Remove Anonymous User accounts - there are 3 anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password' ); )
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password' ); )
Restrict Host for user@% to user@SpecificDNSorIp
71 CVE(s) found for your MySQL release. Consider upgrading your version !
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_ta ble_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_buffer_pool_instanc es(=1)
This database really only contains 3 tables that I created for the application. Two are tiny code tables and the third is a syslog table capturing network stuff. There's over 400,000 records in the syslog table so far.
I do have a couple indexes on the syslog table.
I disabled a database performance analyzer (DPA) that was running against this database and so far, I don't see anything being logged as not using indexes.
So it seems that the DPA was the one racking up the counts of SQL not using indexes.
I have done more tweaking to variables and re-running the mysqltuner.pl. Here's a snippet below. It seems better.
-------- Performance Metrics --------------------------
[--] Up for: 1d 1h 57m 24s (287K q [3.075 qps], 2K conn, TX: 84M, RX: 76M)
[--] Reads / Writes: 2% / 98%
[--] Binary logging is disabled
[--] Physical Memory : 3.6G
[--] Max MySQL memory : 1.8G
[--] Other process memory: 279.4M
[--] Total buffers: 1.6G global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 71M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.6G (45.01% of installed RAM)
[OK] Maximum possible memory usage: 1.8G (49.51% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/287K)
[OK] Highest usage of available connections: 1% (3/151)
[OK] Aborted connections: 0.19% (5/2612)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10 sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 38% (120 on disk / 313 total)
[OK] Thread cache hit rate: 99% (3 created / 2K connections)
[OK] Table cache hit rate: 95% (142 open / 149 opened)
[OK] Open file limit used: 9% (101/1K)
[OK] Table locks acquired immediately: 99% (229K immediate / 229K locks)
-------- Performance schema --------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 71.6M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics --------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics --------------------------
[!!] Key buffer used: 18.5% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/116.0K
[!!] Read Key buffer hit rate: 76.9% (91 cached / 21 reads)
-------- InnoDB Metrics --------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.5G/124.4M
[OK] InnoDB log file size / InnoDB Buffer pool size: 385.0M/1.5G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.97% (10258651 hits/ 10261595 total)
[OK] InnoDB Write log efficiency: 91.30% (275276 hits/ 301509 total)
[OK] InnoDB log waits: 0.00% (0 waits / 26233 writes)
-------- Recommendations --------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE syslogdb.SystemEvents; -- can free 23 MB
Total freed space after theses OPTIMIZE TABLE : 23 Mb
Remove Anonymous User accounts - there are 3 anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'
Restrict Host for user@% to user@SpecificDNSorIp
71 CVE(s) found for your MySQL release. Consider upgrading your version !
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_ta
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_buffer_pool_instanc
Hi!
Tune these parameters
tmp_table_size (> 16M) = 30M
max_heap_table_size (> 16M) = 30M
innodb_buffer_pool_instanc es(=1) = 1
and decide wheter or not you do the other recommendations outlisted and you are good for now. :)
Regards,
Tomas Helgi
Tune these parameters
tmp_table_size (> 16M) = 30M
max_heap_table_size (> 16M) = 30M
innodb_buffer_pool_instanc
and decide wheter or not you do the other recommendations outlisted and you are good for now. :)
Regards,
Tomas Helgi
ASKER
Thanks so much for your help and explanations. :) I learned quite a bit.
Next I'm going to look into those CVE things.
Next I'm going to look into those CVE things.
How big are your ib_logfiles (innodb_log_file_size) and innodb_log_buffer_size ?
Here are good blogs on how to calculate and tune the logfilesize.
https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
http://www.mysqlab.net/knowledge/kb/detail/topic/innodb/id/6553
I usually have the innodb_log_file_size set to 10 or 15 MB
You can change the logfilesize safely by setting the size you want and follow this
https://www.percona.com/blog/2011/07/09/how-to-change-innodb_log_file_size-safely/
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_file_size
Regards,
Tomas Helgi