| September 20, 2013 |  Posted In: MySQL

InnoDB performance optimization basicsNote: There is an updated post on this topic here.

I recently stumbled upon a post that Peter Zaitsev published back in 2007 titled “Innodb Performance Optimization Basics.” It’s a great post and reading it inspired me to examine what’s changed in the nearly six years that have followed in terms of MySQL, Percona Server – as well as in all of the other now-available infrastructures.

And a lot has in fact changed! In this post I am going to highlight most of the InnoDB parameters critical for InnoDB – specifically from a performance perspective. I’m a support engineer and I can tell you that Percona Support gets many questions related to the right sizing of basic InnoDB parameters.
So hopefully this post will help others with similar questions and issues.

For larger datasets, nowadays memory counted in hundreds of giga- and even in terabytes is not surprising. MySQL requires significant memory amounts in order to provide optimal performance. By caching hot datasets, indexes, and ongoing changes, InnoDB is able to provide faster response times and utilize disk IO in a much more optimal way. From a CPU standpoint, faster processors with many cores provide better throughput. CPUs with 32/64 cores or more are becoming common now, and the latest MySQL versions are able to utilize them much better then before. In terms of storage, SSD disks are replacing traditional spindles with great success, offering the best performance for the money. RAID 10 is still the most recommended level for most workloads, but first make sure your RAID controller is able to utilize the SSD drive’s performance and will not become the actual bottleneck. There are also many PCI-e Flash drives out there if you need even more IOPS.

Operating System:
Linux is the most common operating system for high performance MySQL servers. Make sure to use modern filesystems, like EXT4 or XFS on Linux, combined with the most recent kernel. Each of them has it’s own limits and advantages: for example XFS is fast in deleting large files, while EXT4 can provide better performance on fast SSD drives. Benchmark before you decide. Check this blog post to see how EXT4 can outperform XFS. You can use noatime and nodiratime options if you’re using innodb_file_per_table and a lot of tables though benefit of these is minor. The default I/O scheduler in Linux is Completely Fair Queuing (CFQ), while Noop/Deadline will be much better in most cases.. Setting swappiness to zero is generally recommended for the MySQL dedicated host, which will lower the tendency of swapping. Make sure the MySQL host does not run out of memory. Swapping is bad for MySQL and defeats the purpose of caching in memory. To learn more about swapping, check this blog post

MySQL Innodb Settings 
From 5.5 InnoDB is the default engine, so these parameters are even more important for performance than before. The most important ones are:

  • innodb_buffer_pool_size: InnoDB relies heavily on the buffer pool and should be set correctly, so be sure to allocate enough memory to it. Typically a good value is 70%-80% of available memory. More precisely, if you have RAM bigger than your dataset setting it bit larger should be appropriate with that keep in account of your database growth and re-adjust innodb buffer pool size accordingly. Further, there is improvement in code for InnoDB buffer scalability if you are using Percona Server 5.1 or Percona Server 5.5 You can read more about it here.
  • innodb_buffer_pool_instances: Multiple innodb buffer pools introduced in InnoDB 1.1 and MySQL 5.5. In MySQL 5.5 the default value for it was 1 which is changed to 8 as new default value in MySQL 5.6. Minimum innodb_buffer_pool_instances should be lie between 1 (minimum) & 64 (maximum). Enabling innodb_buffer_pool_instances is useful in highly concurrent workload as it may reduce contention of the global mutexes.
  • Dump/Restore Buffer Pool: This feature speed up restarts by saving and restoring the contents of the buffer pool. This feature is first introduced in Percona Server 5.5 you can read about it here. Also Vadim benchmark this feature You can read more about it in this post. Oracle MySQL also introduced it in version 5.6, To automatically dump the database at startup and shutdown set innodb_buffer_pool_dump_at_shutdown & innodb_buffer_pool_load_at_startup parameters to ON.
  • innodb_log_file_size: Large enough InnoDB transaction logs are crucial for good, stable write performance. But also larger log files means that recovery process will slower in case of crash. However this is not such big issue since great improvements in 5.5. Default value has been changed in MySQL 5.6 to 50 MB from 5 MB (old default), but it’s still too small size for many workloads. Also, in MySQL 5.6, if innodb_log_file_size is changed between restarts then MySQL will automatically resize the logs to match the new desired size during the startup process. Combined log file size is increased to almost 512 GB in MySQL 5.6 from 4 GB. To get the optimal logfile size please check this blog post.
  • innodb_log_buffer_size: Innodb writes changed data record into lt’s log buffer, which kept in memory and it saves disk I/O for large transactions as it not need to write the log of changes to disk before transaction commit. 4 MB – 8 MB is good start unless you write a lot of huge blobs.
  • innodb_flush_log_at_trx_commit: When innodb_flush_log_at_trx_commit is set to 1 the log buffer is flushed on every transaction commit to the log file on disk and provides maximum data integrity but it also has performance impact. Setting it to 2 means log buffer is flushed to OS file cache on every transaction commit. The implication of 2 is optimal and improve performance if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes.
  • innodb_thread_concurrency: With improvements to the InnoDB engine, it is recommended to allow the engine to control the concurrency by keeping it to default value (which is zero). If you see concurrency issues, you can tune this variable. A recommended value is 2 times the number of CPUs plus the number of disks. It’s dynamic variable means it can set without restarting MySQL server.
  • innodb_flush_method: DIRECT_IO relieves I/O pressure. Direct I/O is not cached, If it set to O_DIRECT avoids double buffering with buffer pool and filesystem cache. Given that you have hardware RAID controller and battery-backed write cache.
  • innodb_file_per_table: innodb_file_per_table is ON by default from MySQL 5.6. This is usually recommended as it avoids having a huge shared tablespace and as it allows you to reclaim space when you drop or truncate a table. Separate tablespace also benefits for Xtrabackup partial backup scheme.

Along with that, there are lot of enhancements for InnoDB, specifically in Percona Server 5.5 and in Oracle MySQL 5.6. Persistent optimizer statistics is one of the features first introduced in Percona Server 5.5 that requires the enabling of the innodb_use_sys_stats_table in XtraDB. You can read more about it here. This feature is now included in Oracle MySQL 5.6, too. In MySQL 5.6 persistent stats are stored in two new tables: mysql.innodb_index_stats and mysql.innodb_table_stats. Through this query plans are much more accurate and consistent. You can read more about it in documentation. Also Percona Server 5.5 introduced a Thread Pool feature which is ported from MariaDB. You can read more about it in this documentation. On a related note, I recommend reading this blog post from Vadim on the Thread Pool feature.

Percona Server free and open source. An enhanced drop in Oracle MySQL replacement and some of the mentioned features are only applicable to Percona Server.

There are bunch of other options which you may want to tune but in this post we focus only InnoDB specifically.

Application tuning for Innodb:
Especially when coming from a MyISAM background, there will be some changes you would like to make with your application. First make sure you’re using transactions when doing updates, both for sake of consistency and to get better performance. Next if your application has any writes be prepared to handle deadlocks which may happen. Third you should review your table structure and see how you can get advantage of Innodb properties – clustering by primary key, having primary key in all indexes (so keep primary key short), fast lookups by primary keys (try to use it in joins), large unpacked indexes (try to be easy on indexes).

We covered almost all basic and important InnoDB parameters, OS related tweaking and hardware for optimal MySQL server performance. By setting all mentioned variables appropriately certainly help to boost overall MySQL server performance. 

More Resources


eBooks (free to download)

Database Tools

블로그 이미지



댓글을 달아 주세요

웹호스팅을 운영하면서 일반 사용자도 함수 및 프로시저 권한을 달라는 요청을 받았습니다

당연히 기본으로 권한이 포함되어 있는지 알았더니 이부분은 따로 설정을 해줘야 생성이 가능합니다

일반적인 웹호스팅 사용자  대부분이 프로시저나 함수등을 등록해서 사용하는 분들이 없기 때문에  모르고 있던 내용이기도 하다


1. 프로시저 및 함수 생성 권한 확인
SHOW VARIABLES LIKE '%log_bin_trust_function_creators%'; 
스크린샷, 2015-01-27 15:08:05

mysql 디폴트 값은 OFF 상태 입니다

2. 프로시저 및 함수 생성 권한 주기
SET GLOBAL log_bin_trust_function_creators = 1;
스크린샷, 2015-01-27 15:08:53
위와 같이 쿼리를 실행하면 Value 값이 ON 으로 변경이 됩니다

3. 생성된 함수나 프로시저를 확인하는 쿼리 입니다
SELECT * FROM information_schema.ROUTINES;

이제 일반 사용자들도 함수 및 프로시저를 생성 할 수가 있습니다

service mysqld restart 하면 당연히 global set 도 돌아오게 되는데 이제 permanent 하게 추가를 하려면 아래와 같이한다.

#vi /etc/my.cnf



블로그 이미지



댓글을 달아 주세요

MySQL 5.5.34에서 테스트하였습니다.
MySQL 기본 스토리지 엔진 변경

1 방법 1: 임시 적용[편집]

  • mysqld 재시작 없이 바로 적용 가능
  • 단, mysqld를 재시작하면 변경 전의 설정으로 원복된다...
SET default_storage_engine=엔진이름;
→ 엔진이름은 MyISAM, InnoDB 중 선택
mysql> SELECT engine, support FROM information_schema.engines WHERE support='DEFAULT';
| engine | support |
| InnoDB | DEFAULT |
1 row in set (0.00 sec)
mysql> SET default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT engine, support FROM information_schema.engines WHERE support='DEFAULT';
| engine | support |
1 row in set (0.00 sec)

2 방법 2: 영구 적용[편집]

vi /etc/my.cnf
→ [mysqld] 아래 부분 적당한 곳에 설정 추가
[root@zetawiki ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

3 같이 보기[편집]

4 참고[편집]

블로그 이미지



댓글을 달아 주세요

MySQL 현재 접속자 보기 및 MYSQL 모니터링 방법

MySQL Database의 경우 Oracle 이나 MS SQL Server에 비해서 대용량의 자료를 처리하는 경우가 적기에 튜닝에 필요성이 적은 것 같습니다. 그러나 웹이라는 환경은 많은 사용자가 동시에 접속을 할 수 있기에 항상 모니터링과 최적화는 기본이라고 생각합니다. 
본 강좌에서는 기본적인 모니터링 방법과 Connection과 Memory 부분에 대한 튜닝 방법을 소개하도록 하겠습니다.

출처 : http://www.albumbang.com/board/board_view.jsp?board_name=free&no=139

가. 모니터링 및 초기화 명령어
  • show status - MySQL 데이타베이스의 현재 상황
  • show Processlist - MySQL 프로세스 목록
  • show variables - 설정 가능한 모든 변수 목록
  • flush logs - MySQL의 로그파일 초기화
  • flush status - MySQL 상태정보 초기화
  • flush thread - 쓰레드 캐시에 저장된 쓰레드 초기화
  • flush tables - MySQL에 캐싱된 테이블 초기화
  • flush privileges - 권한정보 재 설정
나. Connection 튜닝
1. status 
  • Aborted_clients - 클라이언트 프로그램이 비 정상적으로 종료된 수
  • Aborted_connects - MySQL 서버에 접속이 실패된 수
  • Max_used_connections - 최대로 동시에 접속한 수
  • Threads_cached - Thread Cache의 Thread 수
  • Threads_connected - 현재 연결된 Thread 수
  • Threads_created - 접속을 위해 생성된 Thread 수
  • Threads_running - Sleeping 되어 있지 않은 Thread 수
2. system variables
  • wait_timeout - 종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )
  • thread_cache_size - thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.
  • max_connections - 최대 동시 접속 가능 수
그외에 status 또는 system variables 값은 참고의 Mysql 메뉴얼을 참조해 주십시요.
mysql> show variables like '%max_connection%';
| Variable_name   | Value |
| max_connections | 100   |
1 row in set (0.00 sec)

mysql> show status like '%connect%';
| Variable_name        | Value   |
| Aborted_connects     | 3782    |
| Connections          | 2961108 |
| Max_used_connections | 90      |
| Threads_connected    | 1       |
4 rows in set (0.01 sec)

mysql> show status like '%clients%';
| Variable_name   | Value |
| Aborted_clients | 2160  |
1 row in set (0.00 sec)

mysql> show status like '%thread%';
| Variable_name          | Value |
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 7     |
| Threads_connected      | 1     |
| Threads_created        | 1364  |
| Threads_running        | 1     |
6 rows in set (0.00 sec)

Cache Miss Rate(%) =  Threads_created / Connections * 100
Connection Miss Rate(%) = Aborted_connects / Connections * 100
Connection Usage(%) = Threads_connected / max_connections * 100

위의 경우는 Cache Miss Rate(%) = 0.05%, Connection Miss Rate(%) = 0.12%, Connection Usage(%) = 1%
3. 튜닝
  • Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.
  • DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다. 그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.
  • Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.
  • MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다. skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.
다. Memory 튜닝
1. status
  • key_block_unused - Key Cache에서 사용되고 있지 않은 Block 수
  • key_reads - Key Block 읽기 요청시 Disk을 읽은 수
  • key_read_requests - Key Block 읽기 요청수
2. system variables
  • key_buffer_size - 인덱스를 메모리에 저장하는 버퍼의 크기
  • table_cache - 전체 쓰레드가 사용할 오픈 가능한 테이블 수
  • myisam_sort_buffer_size - 테이블 repair,Alter table,load data에 사용되는 버퍼 메모리 크기
  • join_buffer_size - 조인을 위한 메모리 버퍼 크기
  • record_buffer - 순차적인 검색을 위해 사용되는 메모리 버퍼 크기
  • record_rnd_buffer - order by 절을 사용할 경우 디스크 사용을 피하기 위하여 사용하는 메모리 버퍼 크기
  • sort_buffer - order by 와 group by에 사용되는 메모리 버퍼 크기
  • tmp_table_size - group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용되는 메모리 크기
  • key_cache_block_size - block 의 크기(bytes, 기본값 1024)
mysql> show status like '%key%';
| Variable_name          | Value     |
| Com_preload_keys       | 0         |
| Com_show_keys          | 2945      |
| Handler_read_key       | 365020739 |
| Key_blocks_not_flushed | 0         |
| Key_blocks_unused      | 222601    |
| Key_blocks_used        | 231960    |
| Key_read_requests      | 847204435 |
| Key_reads              | 4195954   |
| Key_write_requests     | 25034738  |
| Key_writes             | 16452136  |
10 rows in set (0.00 sec)

Key Buffer Usage = 1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)Key_reads/Key_read_requests Rate(%) =  Key_reads/Key_read_requests * 100
Key_reads/Key_read_requests Relative Rate(%) = (1- ^Key_reads/^Key_read_requests) * 100

* ^Key_Reads = Current Key_Rreads - Previous Key_Reads

3. 튜닝
  • key_buffer_size는 총 메모리 크기의 25% 정도의 크기로 설정하는 것이 좋습니다.
  • Key_reads/Key_read_requests Rate(%)은 일반적으로 1%보다 적습니다. 1% 보다 높다면 Key Cache가 아닌 디스크를 읽은 경우가 많다고 판단할 수 있습니다. 또한 Key_reads/Key_reads_requests Relative Rate(%) 값이 지속적으로 90% 이상일 경우는 key_buffer_size가 효율적으로 설정되어 있다고 생각하시면 됩니다. 하지만 데이터베이스가 엄청나게 크거나 여러 데이터를 골고루 많이 읽는 데이터베이스라면 아무리 많은 양의 키 캐시를 설정해도 90% 이상의 적중률을 얻을 수는 없습니다.
라. 적용
system variables은 my.cnf 또는 my.ini 파일을 수정 후 MySQL Server 를 재시작 해 주십시요.
[www@smson www]$ vi /etc/my.cnf  # The MySQL server
port            = 3306
socket          = /tmp/mysql.sock
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
wait_timeout = 120


[root@smson mysql]# /usr/local/mysql/share/mysql/mysql.server restart

마. 참고

블로그 이미지



댓글을 달아 주세요

[MySQLD] Tunner

DB TUNE 2018. 4. 19. 18:43

With MySQL, common configuration mistakes can cause serious performance problems. In fact, if you mis-configure just one of the many config parameters, it can cripple performance! (see examples) Of course, the performance of MySQL is often tied greatly to the efficiency of your MySQL queries. It’s important to ensure that your performance issues are not due to poorly written MySQL queries. You can use MySQL’s slow query log, log_queries_not_using_indexes or APM tools which offer MySQL performance monitoring such as AppdynamicsPanoptaSematextDatadogInstrumentalOracle (MySQL Enterprise) and other APM tools.

MySQL tuning is quite an expansive topic. I won’t try to place any recommended config lines, values or settings here. Be wary, very wary of those. This post assumes that you’ve already optimized your queries and seek guidance with selecting the best performance config options (my.cnf) for MySQL. This can vary greatly case by case as there’s no one-size-fits-all advice. Therefore, also included below, are additional links to popular free MySQL tuning scripts and tools.

Popular scripts and tools available for MySQL performance tuning.CLICK TO TWEET

Stay up to date with the latest MySQL server versions

With each new version of MySQL released, there’s substantial performance and feature enhancements over previous versions. So the most important advice would be to upgrade, upgrade, upgrade. Have a look at some version performance comparisons here.

If you are seeking additional features or flexibility you may already be using MariaDB, which is an enhanced drop-in replacement for MySQL Server. Or, you may prefer Percona’s flavor of MySQL which is another improved compatible version of MySQL to consider. If you have seen notable improvements in using MariaDB or Percona over stock MySQL please share your experience below. I view them all as great options. I’ve used mostly MySQL and MariaDB and don’t have much experience with Percona but much like MariaDB I’ve have heard good things.


MySQL Performance Tuning Advice

Before continuing please have a look at the following MySQL performance tuning articles: Tuning MySQL: my.cnf, avoid this common pitfall! and Avoid This When Tuning MySQL Query Cache for Performance.

Other than the tuning scripts listed below, try to avoid online advice unless its via mysql.com* OR references directly Oracle’s MySQL documentation. You will notice that both blog posts linked to just above references and/or quotes MySQL’s docs. The bottom line is, there’s a ton of conflicting advice and opinions online. I guess my advice here should be to always crosscheck your config changes with MySQL’s official documentation. That includes everything I say here. In fact, when venturing to change MySQL’s defaults, unless you can bet your house or car on your proposed changes, its best to leave it as is. When there’s doubt… stick with defaults. As for changes, always base your changes on benchmarks, comparisons and time-tested firsthand data.


Selecting MySQL Storage Engine

This is simple, use InnoDB and avoid MyISAM when possible. For these reasons:

  • InnoDB has better crash recovery.
  • InnoDB has row-level locking, MyISAM can only do full table-level locking.
  • Like MyISAM, InnoDB now has FULLTEXT search indexes as of MySQL 5.6
  • InnoDB supports transactions, foreign keys and relationship constraints, MyISAM does not.


MySQL Performance Tuning Scripts

You cannot replace Professional MySQL tuning with scripts. Scripts serve as basic guides, sometimes spot on, but most times loose guides which will only solve the most grievous mis-configured parameters. Use them as a starting point. Meaning, before you contact a professional to tune MySQL use these tuning scripts so that at the very least you don’t have any so-called embarrassing config in your my.cnf file. For example, join_buffer_size set to 4GB when the total DB size is less than 1GB.

Now, lets looks at popular scripts and tools available for MySQL performance tuning: MySqlTuner, Tuning-Primer, MySQLreport, phpMyAdmin Advisor and Percona Configuration Wizard for MySQL.



A script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

Mysqltuner Screenshot from 2017-07-18 13-58-17

MySQLTuner is maintained and indicator collect is increasing week after week supporting a lot of configurations such as Galera Cluster, TokuDB, Performance schema, Linux OS metrics, InnoDB, MyISAM, Aria, etc. – MySQLTuner on Github.

Manage and Monitor infrastructure - CA technologies



This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…” to produce sane recomendations for tuning server variables. It is compatable with all versions of MySQL 3.23 and higher (including 5.1).

Tuning-Primer Screenshot from 2017-07-18 14-11-52

The original script is no longer updated. I’ve been using this Tuning-primer version on Github which fully supports MariaDB.


phpMyAdmin Advisor

The Advisor system provides recommendations on server variables by analyzing MySQL status variables.

phpMyAdmin Advisor

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. Visit: phpmyadmin.


Mysqlreport from Percona’s Toolkit

Mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysqlreport is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS.

mysqlreport percona Screenshot from 2017-07-18 14-53-15

The Guide To Understanding mysqlreport (PDF) | Percona acquired Mysqlreport and you can find it as part of Percona’s Toolkit.


Percona Configuration Wizard for MySQL

From their website: “Apply Percona best practices to achieve better MySQL database performance and avoid the time, complexity, and risk of customizing a my.cnf configuration on your own. Simply copy and paste the results of the Percona Configuration Wizard for MySQL into your my.cnf file. Tens of thousands of MySQL users have already used this tool to improve their MySQL performance. When you complete the wizard, your configuration files are saved for future use and you can easily share them with colleagues. Registration is required but your information will not be shared with third parties.”

Percona Tools for MySQL Percona Configuration Wizard for MySQL_

Percona Configuration Wizard for Mysql  – a tool to help you generate my.cnf files based on your server’s hardware and other characteristics.

There are also a few free real-time monitoring tools such as innotopmytop (old), mtop (older). Fork anyone? Have a look also at Percona Monitoring and Management (free), Monyog (not free) or the other paid tools mentioned at the top.

블로그 이미지



댓글을 달아 주세요


Command-Line Format--innodb-file-per-table
System Variableinnodb_file_per_table
Default ValueON

When innodb_file_per_table is enabled (the default), InnoDB stores the data and indexes for each newly created table in a separate .ibd file instead of the system tablespace. The storage for these tables is reclaimed when the tables are dropped or truncated. This setting enables InnoDBfeatures such as table compression. SeeSection 14.7.4, “InnoDB File-Per-Table Tablespaces” for more information.

Enabling innodb_file_per_table also means that an ALTER TABLE operation moves an InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE rebuilds the table (ALGORITHM=COPY). An exception to this rule is for tables placed in the system tablespace using the TABLESPACE=innodb_systemoption with CREATE TABLE or ALTER TABLE. These tables are unaffected by the innodb_file_per_table setting and can only be moved to file-per-table tablespaces using ALTER TABLE ... TABLESPACE=innodb_file_per_table.

When innodb_file_per_table is disabled, InnoDB stores the data for tables and indexes in the ibdata files that make up the system tablespace. This setting reduces the performance overhead of file system operations for operations such as DROP TABLE or TRUNCATE TABLE. It is most appropriate for a server environment where entire storage devices are devoted to MySQL data. Because the system tablespace never shrinks, and is shared across all databases in an instance, avoid loading huge amounts of temporary data on a space-constrained system when innodb_file_per_table is disabled. Set up a separate instance in such cases, so that you can drop the entire instance to reclaim the space.

innodb_file_per_table is enabled by default. Consider disabling it if backward compatibility with MySQL 5.5 or 5.1 is a concern. This will prevent ALTER TABLE from moving InnoDB tables from the system tablespace to individual .ibd files.

innodb_file_per_table is dynamic and can be set ON or OFF using SET GLOBAL. You can also set this option in the MySQL configuration file (my.cnf or my.ini) but this requires shutting down and restarting the server.

Dynamically changing the value requires the SUPER privilege and immediately affects the operation of all connections.

'DB TUNE' 카테고리의 다른 글

MySQL 현재 접속자 보기 및 MYSQL 모니터링 방법  (0) 2018.04.26
[MySQLD] Tunner  (0) 2018.04.19
[MySQLD] innodb_file_per_table  (0) 2018.04.19
MySQL 튜닝 값을 탐지해주는 유용한 툴  (0) 2018.04.19
[MySQLD] table_open_cache  (0) 2018.04.19
[MySQLD] join_buffer_size  (0) 2018.04.19
블로그 이미지



댓글을 달아 주세요

※ 결과값에 대한 상세한 분석은 소개하지 않는다.  그 몫은 각자에게 맡기며, 여기서는 프로그램에 대한
    소개와 실행방법, 그리고 결과값 출력에 대해서만 언급하고자 한다.

MySQL 튜닝값을 탐지해주는 유용한 툴이 있어 같이 공유하고자 소개합니다.
두 가지 툴 모두 간단하게 다운로드 받아 설치과정 없이 실행만 하면, 결과 값을 얻을 수가 있습니다.

1) tuning-primer.sh

쉘 스크립트 소스로 구성되어 있음.

# wget http://www.day32.com/MySQL/tuning-primer.sh
# chmod +x tuning-primer.sh  또는 sh tuning-primer.sh 로 바로 실행
# ./tuning-primer.sh 

Using login values from ~/.my.cnf


Testing for stored webmin passwords: None Found

Could not auto detect login info!

Found Sockets:

Using: /tmp/mysql.sock
Would you like to provide a different socket?: [y/N]   ----------> enter 로 skip 진행함.
Do you have your login handy ? [y/N] :  -----------> 로그인이 걸려 있으므로 y 로 진행함.
User: 계정정보 입력
Password: 계정정보 입력

Would you like me to create a ~/.my.cnf file for you? [y/N] :    ------------> 위 정보값을 .my.cnf 에 넣어두겠느냐?  enter 로 skip 진행 
y 로 진행하는 경우 .my.cnf 파일에 다음 내용이 기록되니 보안을 위해서는 skip 하도록  함.

password= 계정pw

이후 아래와 같이 결과값 출력
> 현재 값이 괜찮은 항목에 대해서는 연한녹색으로 표시 
> 현재 값보다 튜닝이 필요한 항목에 대해서는 붉은색 계열로 표시 


             - By: Matthew Montgomery -

MySQL Version 4.0.27-log  is EOL please upgrade to MySQL 4.1 or later

Uptime = 70 days 5 hrs 7 min 11 sec
Avg. qps = 5
Total Questions = 32071286
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

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

The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 2889 out of 32071307 that take longer than 10 sec. to complete
Your long_query_time seems to be fine

The binary update log is enabled
tuning-primer.sh: line 541: [: : integer expression expected

Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

Current max_connections = 100
Current threads_connected = 1
Historic max_used_connections = 17
The number of used connections is 17% of the configured maximum.
Your max_connections variable seems to be fine.

Max Memory Ever Allocated : 504 M
Configured Max Per-thread Buffers : 458 M
Configured Max Global Buffers : 426 M
Configured Max Memory Limit : 884 M
Physical Memory : 1.96 G
Max memory limit seem to be within acceptable norms

Current MyISAM index space = 98 M
Current key_buffer_size = 384 M
Key cache miss rate is 1 : 488
Key buffer free ratio = 75 %
Your key_buffer_size seems to be fine

Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 21 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 68.09 %
No query_cache_min_res_unit is defined.  Using MySQL < 4.1 cache fragmentation can be inpredictable
MySQL won't cache query results that are larger than query_cache_limit in size

Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

Current join_buffer_size = 132.00 K
You have had 669 queries where a join could not use an index properly
You should enable "log-long-format"
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.

Current open_files_limit = 1134 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf

Current table_cache value = 512 tables
You have a total of 6623 tables
You have 512 open tables.
Current table_cache hit rate is 0%, while 100% of your table cache is in use
You should probably increase your table_cache

Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 100119 temp tables, 4% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

Current read_buffer_size = 1 M
Current table scan ratio = 253 : 1
read_buffer_size seems to be fine

Current Lock Wait ratio = 1 : 2166
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'


2) mysqltuner.pl

펄 소스로 구성되어 있음.

  # wget http://mysqltuner.pl/mysqltuner.pl
  # perl mysqltuner.pl

 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at 
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root계정
Please enter your MySQL administrative password: root패스워드 

인증과정 없이는 실행할 수 없다.

이 프로그램 툴 역시 색상으로 구분해주므로, 어디가 그렇고 그런지 파악하기 쉽게 되어 있다.
특히 말머리에 !!, OK 만으로도 상태가 어떤지는 알 것이다.

Recommendations 부분에는 어떻게 튜닝해줘야 하는지 어드바이스까지 해준다.


 >>  MySQLTuner 1.0.1 - Major Hayden <
 >>  Bug reports, feature requests, and downloads at 
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 493M (Tables: 304)
[--] Data in InnoDB tables: 282M (Tables: 12)
[!!] Total fragmented tables: 5

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 19h 32m 14s (110M q [704.797 qps], 11M conn, TX: 238B, RX: 6B)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 442.0M global + 16.4M per thread (5000 max threads)
[!!] Maximum possible memory usage: 80.4G (1379% of installed RAM)
[OK] Slow queries: 0% (4K/110M)
[OK] Highest usage of available connections: 15% (781/5000)
[OK] Key buffer size / total MyISAM indexes: 384.0M/163.0M
[OK] Key buffer hit rate: 100.0% (305M cached / 95K reads)
[OK] Query cache efficiency: 74.0% (52M cached / 71M selects)
[!!] Query cache prunes per day: 159148
[OK] Sorts requiring temporary tables: 0% (21K temp sorts / 2M sorts)
[OK] Temporary tables created on disk: 0% (8K on disk / 937K total)
[OK] Thread cache hit rate: 97% (269K created / 11M connections)
[OK] Table cache hit rate: 33% (512 open / 1K opened)
[OK] Open file limit used: 2% (680/25K)
[OK] Table locks acquired immediately: 99% (24M immediate / 24M locks)
[!!] InnoDB data size / buffer pool: 282.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 32M)
    innodb_buffer_pool_size (>= 282M)


출처: http://newsirin.tistory.com/36 [Nothing Else Matters]

'DB TUNE' 카테고리의 다른 글

[MySQLD] Tunner  (0) 2018.04.19
[MySQLD] innodb_file_per_table  (0) 2018.04.19
MySQL 튜닝 값을 탐지해주는 유용한 툴  (0) 2018.04.19
[MySQLD] table_open_cache  (0) 2018.04.19
[MySQLD] join_buffer_size  (0) 2018.04.19
MySQLD DataBase tune  (0) 2018.04.19
블로그 이미지



댓글을 달아 주세요

[MySQLD] table_open_cache

DB TUNE 2018. 4. 19. 18:36


System Variabletable_open_cache
Default Value2000
Minimum Value1
Maximum Value524288

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. See Section 5.1.7, “Server Status Variables”. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable. For more information about the table cache, see Section, “How MySQL Opens and Closes Tables”.

'DB TUNE' 카테고리의 다른 글

[MySQLD] Tunner  (0) 2018.04.19
[MySQLD] innodb_file_per_table  (0) 2018.04.19
MySQL 튜닝 값을 탐지해주는 유용한 툴  (0) 2018.04.19
[MySQLD] table_open_cache  (0) 2018.04.19
[MySQLD] join_buffer_size  (0) 2018.04.19
MySQLD DataBase tune  (0) 2018.04.19
블로그 이미지



댓글을 달아 주세요