MySQL Database Configuration For Image Hosting Site
Hi
I have a dedicated server Intel E3-1241v3, Software(mdadm) RAID, CentOS 6 64bit, 8GB RAM.
I am running an image hosting script (Zamfi PHP Image Hosting Script) on this server and two Wordpress blog. On Image hosting site there are max 100 users at a time currently and on wordpress blog there are 20-30 user are online max.
Some time mysql load average goes up-to 6.
Here is my.cnf file details. I think that I am missing something in my.cnf File.
As you can see that there is an entry - #innodb_flush_log_at_trx_commit=0 When I remove # from beginning everything is under control. But I read most of the places it cause some errors in future or something else. As I am not a professional so I just put # at beginning.
[mysqld]
performance-schema=0
local-infile=0
innodb_ft_min_token_size=2
ft_min_word_len=2
#innodb_force_recovery = 2
#innodb_flush_log_at_trx_commit=0
max_connections=3000
max_user_connections = 300
datadir=/var/lib/mysql
socket = /var/lib/mysql/mysql.sock
key_buffer_size = 1024M
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_log_file_size=250M
innodb_log_buffer_size=16M
#slow_query_log=1
#long_query_time=0.5
open_files_limit=50000
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 6M
tmp_table_size=128M
read_rnd_buffer_size=4M
max_heap_table_size=64M
myisam_sort_buffer_size=64M
thread_cache_size=8
query_cache_limit=12M
query_cache_size=256M
thread_concurrency = 8
#log-slow-queries = /var/log/mysqlslowqueries.log
max_allowed_packet=268435456
default-storage-engine=MyISAM
innodb_file_per_table=1
innodb_buffer_pool_size = 2048M
[isamchk]
key_buffer =64M
sort_buffer_size = 64M
read_buffer = 16M
write_buffer = 16M
[myisamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 16M
write_buffer = 16M
[mysqld]
interactive_timeout=60
wait_timeout=60
connect_timeout=60
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
log-error=/var/log/mysqld.log
As you can see that there is an entry - #innodb_flush_log_at_trx_commit=0 When I remove # from beginning everything is under control. But I read most of the places it cause some errors in future or something else. As I am not a professional so I just put # at beginning.
-
Hello :), For the mysql optimization I will suggest you please try with MySQLTuner-perl by major scripts. Please try this scripts when your mysql server uptime is more than 24 hours 0 -
Hello, I suggest using an optimization tool like the one referenced at: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you. 0 -
I already mentioned that I am not an expert. Can any one tell me what happened if I enable this entry in my.cnf file - #innodb_flush_log_at_trx_commit=0 I tried to do this. I created a folder tool in root and I uploaded both files from centos_whm to tool. After that I run this command chmod +x mysqlmymonlite.sh and after this ./mysqlmymonlite.sh run 2>/dev/null > mysqlmymonlite_stats.txt and finally run this command ./mysqlmymonlite.sh mysqltuner It generates 0 byte file. When I run only this command - ./mysqlmymonlite.sh It gives below error message awk: cmd. line:1: fatal: cannot open file `/etc/init.d/nginx' for reading (No such file or directory) awk: cmd. line:1: fatal: cannot open file `/etc/init.d/nginx' for reading (No such file or directory) awk: cmd. line:1: fatal: cannot open file `/etc/init.d/nginx' for reading (No such file or directory) Is there is something goes wrong. If it how can I fix this. Thanks and waiting for your reply. 0 -
Can any one tell me what happened if I enable this entry in my.cnf file - #innodb_flush_log_at_trx_commit=0
This option is documented at: MySQL :: MySQL 5.5 Reference Manual :: 14.17 InnoDB Startup Options and System Variables You may also find our system administration services resource page helpful if you require assistance from a qualified system administration company: System Administration Services | cPanel Forums Thank you.0
Please sign in to leave a comment.
Comments
4 comments