I have an ec2 t2.micro instance having memory 1 GB.

My database consist of a single table having records approximately 1.2 million.

How should i tune my database?

This is the result i got after running mysqltuner and yes i have adjusted the variables from recommendation given below but still no change

-------- Log file Recommendations ---------------------------------------------- --------------------

[--] Log file: /var/log/mysql/error.log(31K)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[!!] /var/log/mysql/error.log contains 21 warning(s).
[!!] /var/log/mysql/error.log contains 15 error(s).
[--] 4 start(s) detected in /var/log/mysql/error.log
[--] 1) 2017-03-17T07:41:04.336711Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2017-03-17T07:26:38.914453Z 0 [Note] /usr/sbin/mysqld: ready for connect                                                  ions.
[--] 3) 2017-03-17T06:44:00.306760Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2017-03-17T06:42:45.581096Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3 shutdown(s) detected in /var/log/mysql/error.log
[--] 1) 2017-03-17T07:40:20.754987Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2017-03-17T07:26:37.241863Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2017-03-17T06:43:56.024176Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Performance Metrics -----------------------------------------------------------------------

[--] Up for: 1h 8m 38s (31K q [7.602 qps], 11K conn, TX: 16M, RX: 6M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 990.7M
[--] Max MySQL memory    : 386.4M
[--] Other process memory: 448.8M
[--] Total buffers: 226.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 264.3M (26.67% of installed RAM)
[OK] Maximum possible memory usage: 386.4M (39.01% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/31K)
[OK] Highest usage of available connections: 23% (36/151)
[OK] Aborted connections: 0.02%  (2/11501)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 14.3% (2K cached / 19K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 18K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 49% (7K on disk / 14K total)
[OK] Thread cache hit rate: 99% (78 created / 11K connections)
[OK] Table cache hit rate: 59% (129 open / 217 opened)
[OK] Open file limit used: 0% (6/1K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)

-------- MyISAM Metrics ----------------------------------------------------------------------------

[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/52.0K
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------

[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/603.0M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 95.01% (92607770 hits/ 97474927 total)
[!!] InnoDB Write Log efficiency: 229.41% (39 hits/ 17 total)
[OK] InnoDB log waits: 0.00% (0 waits / 56 writes)

-------- Recommendations ---------------------------------------------------------------------------

General recommendations:

Control warning line(s) into /var/log/mysql/error.log file
Control error line(s) into /var/log/mysql/error.log file
MySQL started within last 24 hours - recommendations may be inaccurate
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_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause

Variables to adjust:

query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_buffer_pool_size (>= 603M) if possible.
innodb_log_file_size should be equals to 1/4 of buffer pool size (=32M) if possible.

Table Structure

Table Structure the most used query on my server is SELECT id,link,file,reg_date,filetype FROM table WHERE filename LIKE '%{$in}%' $sqltype ORDER BY id DESC LIMIT $startrow, 20 . The variable $sqltype is used to categorize using where clause eg.: $sqltype = 'where filesize>1000


Three things I can suggest here:

  1. WHERE filename LIKE '%{$in}%' - this will make a full table scan, so more rows = slower the query get. To fix this you'll need to either add another column in the WHERE clause e.g. WHERE reg_date > '2017-03-10' and filename LIKE '%{$in}%' (create an index on reg_date column) OR WHERE filename LIKE '{$in}%' (remove the leading % if possible, create an index on "filename").
  2. Create an index on filesize
  3. Micro instances on AWS have a limit on CPU cycles/second or so. This means that you can only get a certain level of performance on these instances. Use your local dev machine to compare performance against EC2
  • not much help, can you tell me what to change in my cnf file? – Azhar Mar 17 '17 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.