MySQL Database Configuration For Image Hosting Site

[Binay Kumar]

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.

[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.

 

[24x7server]

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

 

[cPanelMichael]

Hello,

I suggest using an optimization tool like the one referenced at:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[Binay Kumar]

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.

 

[cPanelMichael]

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.