-2

Hi are trying to optimize MySQL for a huge traffic web server, we are using Apache 2.4 , and MySQL 5.6 version.

We have put these parameters in MySQL configuration file

cat /etc/my.cnf

[mysqld]

skip-external-locking

key_buffer = 500M

max_allowed_packet=64M

max_connections = 2000

max_user_connections = 50000

wait_timeout=180

connect_timeout=120

table_open_cache=4096

table_definition_cache=4096

myisam_sort_buffer_size = 64M

thread_cache_size = 128

query_cache_size = 190M

query_cache_limit = 3M

open_files_limit=10000

log_error = /var/log/mysql/error.log

default-storage-engine=MyISAM

[mysqld]

log-bin=mysql-bin

binlog-do-db=******

server-id=1

[mysql]

no-auto-rehash

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

replicate-do-db=*****

auto_increment_increment      = 2

auto_increment_offset         = 1

replicate-same-server-id = 0

slave-skip-errors=all

[mysqld]
slave-skip-errors=1062

skip-slave-start

query_cache_type=1

[mysqld]

innodb_lock_wait_timeout=120

slave_compressed_protocol=1
SwiftArchitect
  • 47,376
  • 28
  • 140
  • 179
rishi dadwal
  • 41
  • 1
  • 2
  • You can't get answers for those flue question. You should be more specific about your needs. What is your data size, which engine are you using, how may request we are talking about by saying huge etc. – cool Nov 22 '15 at 11:53
  • hi i have ran script .please find the output below and suggest. – rishi dadwal Nov 23 '15 at 09:13
  • Apache != huge traffic web server ; **Nginx is lighter and usually scale better** (Async conception, allow you to use at full power a multitcore server) – Blag Nov 28 '15 at 02:02

1 Answers1

1

query_cache_size = 190M

The QC is inefficient when it is big; limit to about 50M.

slave-skip-errors=1062

This is a flavor of "sweeping bugs under the rug".

default-storage-engine=MyISAM

passé.

key_buffer = 500M

If you do stick with MyISAM, and this server is running just MySQL, then use 1000M.

server-id=1

Make sure each server has a different value.

But that is only configuration. If you have slow queries, let's see them and try to improve them.

Rick James
  • 135,179
  • 13
  • 127
  • 222