سلام
من یه سرور مجازی لینوکس centos 6.5 دارم با کنترل پنل دایرکت ادمین
cpu: 3هسته ای
رم: 6 گیگ
چه کانفیگی مناسب هست که توی فایل my.cnf بذارم
الان این فایل خالی هستش و هیچی توش نیست
نمایش نسخه قابل چاپ
سلام
من یه سرور مجازی لینوکس centos 6.5 دارم با کنترل پنل دایرکت ادمین
cpu: 3هسته ای
رم: 6 گیگ
چه کانفیگی مناسب هست که توی فایل my.cnf بذارم
الان این فایل خالی هستش و هیچی توش نیست
سلام
کانفیگ و بهینه سازی باید با توجه به مصرف اسکریپت انجام بشه.
از چه اسکریپتی استفاده میکنید؟
سلام
این کانفیگ برای این منابع خوب هست ولی باز باید بیشتر بررسی بشه چون من نمیدونم روی سرور شما چه اسکریپتی نصب هست:
[mysqld]
skip-innodb
default-storage-engine = MyISAM
long_query_time = 3
back_log = 50
max_connections = 500
max_user_connections= 200
key_buffer_size = 512M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 2048M
join_buffer_size = 2M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 12000
thread_cache_size = 1024
wait_timeout = 1000
interactive_timeout = 500
connect_timeout = 600
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 128M
net_buffer_length = 16384
max_connect_errors = 100000
thread_concurrency = 16
concurrent_insert = 2
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
range_alloc_block_size = 4096
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 8
local-infile=0
open_files_limit=15638
local-infile=0
open_files_limit=15638
درودنقل قول:
نوشته اصلی توسط novin-web
بردار گرامی کانفیگ شما اول اینکه روی mysql 5.6 جوابگو نیست و باعث میشه mysql استارت نشود بدلیل وجود table cache که در mysql 5.6 به بعد ساپورت نمیشود
و دوم اینکه استفاده از مقادیری مانند skip-innodb باعث از کار افتادن اسکریپت های تحت innodb مانند برخی انجمن ها میشود و مشکلات دیگر..
ضمن تشکر از شما که هدفتون کمک هستش پیشنهاد میکنم در مورد مواردی که به کاربران کمک میکنید اطلاعات دقیق تری ذکر کنید
ارادت
:53:
نقل قول:
نوشته اصلی توسط python
از https://raw.github.com/rackerhacker/.../mysqltuner.pl برای بهینه کردن استفاده کنید برای تنظیمات معمولی برای شما مناسب است
از وردپرس و سیستم پاپ آپ xdev استفاده میکنمنقل قول:
نوشته اصلی توسط OPENFIRE
- - - Updated - - -
بله دقیقا حق با شماست چون mysql داون شد و استارت نشدنقل قول:
نوشته اصلی توسط nginxweb
- - - Updated - - -
طرز کارش چگونه هست؟نقل قول:
نوشته اصلی توسط storage
up
نقل قول:
نوشته اصلی توسط python
فایل پرل هست دریافت و اجرا کنید 5-10 دقیقه منتظر باشید تا نتیجه بهشما بگوید
اینارو دادنقل قول:
نوشته اصلی توسط storage
کدوم هارو بذارم توی my.cnf?
اون فایلم کلا خالیه
کد HTML:
>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.31
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 250M (Tables: 203)
[--] Data in InnoDB tables: 183M (Tables: 317)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 333
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 6h 36m 31s (3M q [153.599 qps], 212K conn, TX: 12B, RX: 299M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 583.2M (9% of installed RAM)
[OK] Slow queries: 0% (0/3M)
[OK] Highest usage of available connections: 13% (21/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/182.7M
[OK] Key buffer hit rate: 98.4% (1B cached / 16M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (179 temp sorts / 610K sorts)
[!!] Temporary tables created on disk: 42% (173K on disk / 405K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 3% (393 open / 11K opened)
[OK] Open file limit used: 40% (410/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] InnoDB buffer pool / data size: 128.0M/183.7M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_open_cache (> 400)
innodb_buffer_pool_size (>= 183M)
نقل قول:
نوشته اصلی توسط python
در زیر نوشته شده مقادیر مورد نظر تغییر بدهید
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_open_cache (> 400)
innodb_buffer_pool_size (>= 183M)
توی my.cnf هیچی نیست خالی هستشنقل قول:
نوشته اصلی توسط storage
این مقادیر رو هم که میذارم mysql داون میشه و دیگه استارت نمیخوره
میشه یه مقادیر کلی با توجه به این اطلاعات بذارید تا همونارو بذارم؟
نقل قول:
نوشته اصلی توسط python
بطور مثال به شکل زیر وارد کنید
query_cache_size=128M
من به این شکل وارد کردم بازم mysql خاموش شد و روشن نشدنقل قول:
نوشته اصلی توسط storage
query_cache_size=8M
tmp_table_size=16M
max_heap_table_size=16M
thread_cache_size=4
table_open_cache=400
innodb_buffer_pool_size=183M
How to Optimize MySQL
مفید هست براتون
خیلی ممنوننقل قول:
نوشته اصلی توسط MajorHost
ببخشید من الان این دستور رو اجرا کردم:
cp -f /usr/share/mysql/my-large.cnf /etc/my.cnf
این سایتی که دادین گفته اگه رم بالای 2 گیگ باشه این دستور رو اجرا کنید برای mysql 4+5 رم منم 6 گیگ هستش
الان دیگه بذارم همین بمونه یا مقادیر my.cnf رو تغییر بدم؟
از چه ورژنی استفاده می کنید؟
MySQL 5.5.31نقل قول:
نوشته اصلی توسط storage
محتوا کامل فایل کانفیگ و لاگ بدهید
فایل کانفینگ:نقل قول:
نوشته اصلی توسط storage
اینم لاگ:کد:# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 6
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
کد:[Thu Jun 12 11:36:51 2014] [error] [client 5.134.177.32] request failed: error reading the headers
[Thu Jun 12 12:01:21 2014] [error] [client 5.223.58.2] request failed: error reading the headers
[Thu Jun 12 12:10:20 2014] [error] [client 31.47.61.34] request failed: error reading the headers
[Thu Jun 12 12:22:16 2014] [error] [client 151.233.167.207] request failed: error reading the headers
[Thu Jun 12 12:27:21 2014] [error] [client 188.158.252.39] request failed: error reading the headers
[Thu Jun 12 12:27:45 2014] [error] [client 188.158.252.39] request failed: error reading the headers
[Thu Jun 12 12:34:05 2014] [error] [client 151.233.146.220] request failed: error reading the headers
[Thu Jun 12 12:42:37 2014] [notice] caught SIGTERM, shutting down
[Thu Jun 12 12:42:38 2014] [warn] RSA server certificate CommonName (CN) `localhost' does NOT match server name!?
[Thu Jun 12 12:42:38 2014] [warn] Init: Name-based SSL virtual hosts only work for clients with TLS server name indication support (RFC 4366)
[Thu Jun 12 12:42:38 2014] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec)
[Thu Jun 12 12:42:39 2014] [warn] RSA server certificate CommonName (CN) `localhost' does NOT match server name!?
[Thu Jun 12 12:42:39 2014] [warn] Init: Name-based SSL virtual hosts only work for clients with TLS server name indication support (RFC 4366)
[Thu Jun 12 12:42:39 2014] [notice] Apache/2.2.27 (Unix) mod_ssl/2.2.27 OpenSSL/1.0.1e-fips DAV/2 configured -- resuming normal operations
[Thu Jun 12 12:48:57 2014] [notice] caught SIGTERM, shutting down
[Thu Jun 12 12:48:58 2014] [warn] RSA server certificate CommonName (CN) `localhost' does NOT match server name!?
[Thu Jun 12 12:48:58 2014] [warn] Init: Name-based SSL virtual hosts only work for clients with TLS server name indication support (RFC 4366)
[Thu Jun 12 12:48:58 2014] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec)
[Thu Jun 12 12:48:59 2014] [warn] RSA server certificate CommonName (CN) `localhost' does NOT match server name!?
[Thu Jun 12 12:48:59 2014] [warn] Init: Name-based SSL virtual hosts only work for clients with TLS server name indication support (RFC 4366)
[Thu Jun 12 12:48:59 2014] [notice] Apache/2.2.27 (Unix) mod_ssl/2.2.27 OpenSSL/1.0.1e-fips DAV/2 configured -- resuming normal operations
[Thu Jun 12 13:17:50 2014] [error] [client 2.191.141.23] request failed: error reading the headers
[Thu Jun 12 13:40:39 2014] [error] [client 192.3.92.3] File does not exist: /var/www/html/adspace, referer: http://www.yourlust.com/im/ads_footer.html
[Thu Jun 12 13:40:39 2014] [error] [client 192.3.92.3] File does not exist: /var/www/html/404.shtml, referer: http://www.yourlust.com/im/ads_footer.html
محتوا فایل کانفیگ حذف و فقط کد زیر قرار بدهید
[mysqld]
innodb_file_per_table=1
default-storage-engine=MyISAM
local-infile=0
open_files_limit=900000
سپس یکبار ریستارت کنید بعد از 24 ساعت مجدد برنامه پرل داده شده اجرا کنید و مقادیر به فایل کانفیگ اضافه کنید.