Mysql 5.5 Configuration Optimize

A

arazit

Verified User
Joined
Aug 20, 2008
Messages
184
Hello
I want to optimize my.cnf file for 5.5 version
I used from http://help.directadmin.com/item.php?id=44 but get errors
service mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL.. ERROR! The server quit without updating PID file (/var/lib/mysql/hostname.com.pid)
I used ELS script but this script is not update to mysql 5.5 version.
How to do optimize such as http://help.directadmin.com/item.php?id=44?
 
Hello,

Copy of one of most suitable for your server file from

/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

to

/etc/my.cnf

Carefully read top lines in every file, as there it is denoted how much RAM you should have in order to be able to use the config.
And then later having mysqld uptime a week or so you might want to tune your MySQL with www.mysqltuner.pl
 
zEitEr said:
Hello,

Copy of one of most suitable for your server file from

/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

to

/etc/my.cnf

Carefully read top lines in every file, as there it is denoted how much RAM you should have in order to be able to use the config.
And then later having mysqld uptime a week or so you might want to tune your MySQL with www.mysqltuner.pl
Click to expand...
When I used this files I get erros and mysql dont started.
 
Look at the errors you're getting. If you can't figure out the problem perhaps you'll need to hire someone to log into your server and make the required changes.

Jeff
 
It can be the problem of hostname.com.pid sometime when restart mysqld, can you try to rename this file:
$mv /var/lib/mysql/hostname.com.pid /var/lib/mysql/hostname.com.pid_BAK

If the /var/lib/mysql/hostname.com.pid not found, mysqld will try to recreate it which will resolve the error.
 
If indeed it's a pid file error you can just delete the pid file before restarting MySQL; I don't see where it's said it's a pid file error.

Jeff
 
120916 02:28:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120916 2:28:38 [Note] Plugin 'FEDERATED' is disabled.
120916 2:28:38 InnoDB: The InnoDB memory heap is disabled
120916 2:28:38 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
120916 2:28:38 InnoDB: Compressed tables use zlib 1.2.3
120916 2:28:38 InnoDB: Using Linux native AIO
120916 2:28:38 InnoDB: Error: Linux Native AIO is not supported on tmpdir.
InnoDB: You can either move tmpdir to a file system that supports native AIO
InnoDB: or you can set innodb_use_native_aio to FALSE to avoid this message.
120916 2:28:38 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22]
120916 2:28:38 InnoDB: Warning: Linux Native AIO disabled.
120916 2:28:38 InnoDB: Initializing buffer pool, size = 128.0M
120916 2:28:39 InnoDB: Completed initialization of buffer pool
120916 2:28:39 InnoDB: highest supported file format is Barracuda.
120916 2:28:39 InnoDB: Waiting for the background threads to start
120916 2:28:40 InnoDB: 1.1.8 started; log sequence number 136426571179
^G/usr/sbin/mysqld: File './mysql-bin.000012' not found (Errcode: 2)
120916 2:28:40 [ERROR] Failed to open log (file './mysql-bin.000012', errno 2)
120916 2:28:40 [ERROR] Could not open log file
120916 2:28:40 [ERROR] Can't init tc log
120916 2:28:40 [ERROR] Aborting

120916 2:28:40 InnoDB: Starting shutdown...
120916 2:28:40 InnoDB: Shutdown completed; log sequence number 136426571179
120916 2:28:40 [Note] /usr/sbin/mysqld: Shutdown complete

120916 02:28:40 mysqld_safe mysqld from pid file /var/lib/mysql/server.domain.com.pid ended
 
You should pay attention to these lines:

Code: 
120916 2:28:38 InnoDB: Error: Linux Native AIO is not supported on tmpdir.
 InnoDB: You can either move tmpdir to a file system that supports native AIO
 InnoDB: or you can set innodb_use_native_aio to FALSE to avoid this message.
 120916 2:28:38 InnoDB: Error: Linux Native AIO check on tmpdir returned error[22]

What is wrong with your /tmp? (of course if /tmp is set as tmpdir in mySQL config).

Code: 
mount
?

Code: 
grep tmp /etc/my.cnf
?

What do you see?
 
Mount
Code: 
/dev/simfs on / type simfs (rw,relatime,usrquota,grpquota)
proc on /proc type proc (rw,relatime)
sysfs on /sys type sysfs (rw,relatime)
none on /dev type tmpfs (rw,relatime)
none on /dev/pts type devpts (rw,relatime)
none on /tmp type tmpfs (rw,nosuid,nodev,noexec,relatime)
none on /dev/shm type tmpfs (rw,nosuid,noexec,relatime)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw,relatime)


grep tmp /etc/my.cnf

I used /usr/share/mysql/my-huge.cnf and this file dont have tmp
 
You should refer to MySQL documentation in order to find, why this happens then or fix it. Or just as it was already recommended you should set

Code: 
innodb_use_native_aio to FALSE

in /etc/my.cnf

read documentation either in order to learn proper syntax on this directive.

Note, in case you find it difficult to solve the issue yourself, or have no time to read documentation, then you might want to hire somebody from these community. We all will be happy to give the best of our assistance. Just send a PM for a quote.
 
Hello,

It depends... without knowing your details, it's hard to say anything reliable. So check with notes from the files:

Code: 
[root@server]# head -n4 /usr/share/mysql/my-huge.cnf | tail -n2
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.

Code: 
[root@server]# head -n4 /usr/share/mysql/my-large.cnf | tail -n2
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.

Code: 
[root@server]# head -n5 /usr/share/mysql/my-medium.cnf | tail -n3
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
 
In this case the best choice would be to hire somebody, so someone could check your installation and details. There are some guys on these forums as well as me, who are available for this kind of job. So choose one.
 
So Think, we can use this command:

Code: 
cp -f /usr/share/mysql/my-large.cnf /etc/my.cnf

And the default value for max_allowed_packet is 1M, you should try to edit this value to other. Ex: 16M.

Then you check again the value of "max_allowed_packet" which is the same with new value.
Right now you can edit it.
 
You must log in or register to reply here.
Back
Top