how to change innodb_log_file_size'

Moderator: crythias

bharat
OTRS newbie
Posts: 16
Joined: 08 Jul 2015, 08:57
OTRS Version?: 4.0.9
Real Name: dharmendra
Company: neso

how to change innodb_log_file_size'

Postby bharat » 09 Jul 2015, 13:39

Hi,
I am using centos and OTRS 4.0.9 and running first time installer.pl and it is stucked on innodb log file size.
please let me know how to change as I have already made changes in my.cnf and restarted httpd and myswld service but still it is prompting same message.

I have made all possible changes suggested in links.
like
SET GLOBAL innodb_fast_shutdown = 1;
and var/mysql file moving to bkp and then restarting myswl services still problem is same.

regards

EXG133
OTRS superhero
Posts: 217
Joined: 06 Aug 2012, 18:12
OTRS Version?: 3.1.7 & 4.04

Re: how to change innodb_log_file_size'

Postby EXG133 » 09 Jul 2015, 14:41

Versions older than 5.7 of MySQL won't change the logfiles automatically. You need to shut down the service, change the option and then move the current logfiles away. Then start the service and MySQL will throw an error it can't find the logfiles and create new ones.

To make sure I didn't forget anything heck here (for whatever version of MySQL you are using): http://dev.mysql.com/doc/refman/5.6/en/ ... ation.html

In the future include what version of MySQL you are using when asking database questions, much easier to answer then.

bharat
OTRS newbie
Posts: 16
Joined: 08 Jul 2015, 08:57
OTRS Version?: 4.0.9
Real Name: dharmendra
Company: neso

Re: how to change innodb_log_file_size'

Postby bharat » 16 Jul 2015, 11:47

Hi,
Sorry for delay in response.
please find below details.
I am using mysql 5.1.7 version and all the steps required already taken still I am getting same error message of size change is required.

please suggest.

Regards

root
Moderator
Posts: 986
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: how to change innodb_log_file_size'

Postby root » 16 Jul 2015, 12:42

What does the following query returns?

Code: Select all

SHOW VARIABLES LIKE 'innodb_log_file_size';
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

You need professional services? Check out http://znuny.com/

bharat
OTRS newbie
Posts: 16
Joined: 08 Jul 2015, 08:57
OTRS Version?: 4.0.9
Real Name: dharmendra
Company: neso

Re: how to change innodb_log_file_size'

Postby bharat » 17 Jul 2015, 07:02

Hi,
I got below result from command.

mysql> SHOW VARIABLES LIKE 'innodb_log_file_size';
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| innodb_log_file_size | 5242880 |
+----------------------+---------+
1 row in set (0.00 sec)

mysql>


Regards

root
Moderator
Posts: 986
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: how to change innodb_log_file_size'

Postby root » 17 Jul 2015, 08:23

Look's like you haven't set the innodb_log_filesize in the mtsql server configuration file.

  1. Shutdown the mysql server
  2. adjust the log file setting in the mysql sever config file
  3. move the logfiles away
  4. restart the server and check the setting with the query
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

You need professional services? Check out http://znuny.com/

bharat
OTRS newbie
Posts: 16
Joined: 08 Jul 2015, 08:57
OTRS Version?: 4.0.9
Real Name: dharmendra
Company: neso

Re: how to change innodb_log_file_size'

Postby bharat » 17 Jul 2015, 11:54

Hi,
I have already done this activity twice still same message.
whereas in mysql size is showing
query.mysql> SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'innodb_buffer_pool_size';
+----------------+
| variable_value |
+----------------+
| 8388608 |
+----------------+
1 row in set (0.00 sec)

mysql>

EXG133
OTRS superhero
Posts: 217
Joined: 06 Aug 2012, 18:12
OTRS Version?: 3.1.7 & 4.04

Re: how to change innodb_log_file_size'

Postby EXG133 » 17 Jul 2015, 13:15

innodb_buffer_pool_size is not the same as innodb_log_file_size.

innodb_buffer_pool_size = RAM used as cache for innodb table and index
innodb_log_file_size = size of the log files for innodb tables

Both values are very important for your DB but do completely different things.

User avatar
crythias
Moderator
Posts: 9921
Joined: 04 May 2010, 18:38
OTRS Version?: 4.0.x
Location: SouthWest Florida, USA
Contact:

Re: how to change innodb_log_file_size'

Postby crythias » 17 Jul 2015, 14:04

bharat wrote:I am using mysql 5.1.7 version


http://www.mysql.com/support/supportedplatforms/database.html
MySQL 5.1 is not supported on CentOS 6+

OTRS is not supported on CentOS 5-
https://otrs.github.io/doc/manual/admin/3.3/en/html/installation.html#installation-on-centos
OTRS 4.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

bharat
OTRS newbie
Posts: 16
Joined: 08 Jul 2015, 08:57
OTRS Version?: 4.0.9
Real Name: dharmendra
Company: neso

Re: how to change innodb_log_file_size'

Postby bharat » 21 Jul 2015, 08:48

Thanks for information.
would you please help to upgrade mysql from 5.1 to 5.5 on this box as it is not yet in production .
as whenever i am running yum it is only upgrading upto final version of mysql 5.1 only.
regards

bharat
OTRS newbie
Posts: 16
Joined: 08 Jul 2015, 08:57
OTRS Version?: 4.0.9
Real Name: dharmendra
Company: neso

Re: how to change innodb_log_file_size'

Postby bharat » 21 Jul 2015, 10:38

Hi,
Thanks a lot for information.
I have updated mysql to 5.5 now.

regards

bharat
OTRS newbie
Posts: 16
Joined: 08 Jul 2015, 08:57
OTRS Version?: 4.0.9
Real Name: dharmendra
Company: neso

Re: how to change innodb_log_file_size'

Postby bharat » 21 Jul 2015, 11:49

Hi,
Now getting below error.
even though I made settings and restarted mysqld and https multiple time.

regards
You do not have the required permissions to view the files attached to this post.

User avatar
crythias
Moderator
Posts: 9921
Joined: 04 May 2010, 18:38
OTRS Version?: 4.0.x
Location: SouthWest Florida, USA
Contact:

Re: how to change innodb_log_file_size'

Postby crythias » 21 Jul 2015, 14:35

[mysqdld_safe] may not be the context under which you are running mysql. see if [mysql] or [mysqld] is a section under my.cnf and make your change there.
OTRS 4.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

bharat
OTRS newbie
Posts: 16
Joined: 08 Jul 2015, 08:57
OTRS Version?: 4.0.9
Real Name: dharmendra
Company: neso

Re: how to change innodb_log_file_size'

Postby bharat » 24 Jul 2015, 13:23

thanks crythias.

timmy80
OTRS newbie
Posts: 49
Joined: 18 Nov 2013, 17:37
OTRS Version?: 4.0.5
Real Name: Timmy Lutz
Company: ERNI AG

Re: how to change innodb_log_file_size'

Postby timmy80 » 20 Oct 2015, 15:52

Für alle die es sich einfach machen wollen und nicht erst einige Commands zusammen suchen wollen. Hier Step by Step

Stopping mysql
# service mysqld stop

Rename all logfiles to xyz_OLD
# find /var/lib/mysql -type f -name "ib_logfile?" -exec mv {} {}_OLD \;

Check the logfiles if this is renamed
# ls -la /var/lib/mysql/ib_logfile*
-rw-rw---- 1 mysql mysql 5242880 Apr 3 01:24 /var/lib/mysql/ib_logfile0_OLD
-rw-rw---- 1 mysql mysql 5242880 Jan 31 2010 /var/lib/mysql/ib_logfile1_OLD

Grep your my.cnf if there a parameter already set
# grep innodb_log_file_size /etc/my.cnf
You will see, nothing will displayed.

Open my.cnf in "etc" with notepad++ or similar
And add following parameter

innodb_log_file_size = 265M
Looks like:
[mysqld]
query_cache_size = 48M
max_allowed_packet = 48M
innodb_log_file_size = 256M

Run the command again
# grep innodb_log_file_size /etc/my.cnf

And you will see the grep will now show you a value.

# grep innodb_log_file_size /etc/my.cnf
innodb_log_file_size = 256M

Start mysql
#service mysqld start

Done
OTRS 4.0.7
OS: CentOS 6.6

MusicMaikel
OTRS newbie
Posts: 14
Joined: 08 Dec 2016, 13:21
OTRS Version?: 5.x.x

Re: how to change innodb_log_file_size'

Postby MusicMaikel » 15 Dec 2016, 17:34

Hmmm, I have the same problem, but it still gives the error when I add it to the config file... Where do I need to put the line, because I already tried it under [mysqld], but with no result...

root
Moderator
Posts: 986
Joined: 18 Dec 2007, 12:23
OTRS Version?: 4/5/6
Real Name: Roy Kaldung
Company: Znuny Inc.
Contact:

Re: how to change innodb_log_file_size'

Postby root » 16 Dec 2016, 14:00

Give us your complete my.cnf
OTRS 4/5/6 CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

You need professional services? Check out http://znuny.com/

MusicMaikel
OTRS newbie
Posts: 14
Joined: 08 Dec 2016, 13:21
OTRS Version?: 5.x.x

Re: how to change innodb_log_file_size'

Postby MusicMaikel » 16 Dec 2016, 16:06

Fixed it already, don't really know how tho xD

MusicMaikel
OTRS newbie
Posts: 14
Joined: 08 Dec 2016, 13:21
OTRS Version?: 5.x.x

Re: how to change innodb_log_file_size'

Postby MusicMaikel » 14 Feb 2017, 12:18

Started with a fresh new installation and this time I can't get it to work...
Added the lines that are given above, but when I add them and repeat the grep command, it gives me the following output:

Code: Select all

grep: /etc/my.cnf: No such file or directory


Although, I did change the my.cnf file:

Code: Select all

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port      = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice      = 0

[mysqld]
query_cache_size = 48M
max_allowed_packet = 48M
innodb_log_file_size = 256M
#
# * Basic Settings
#
user      = mysql
pid-file   = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port      = 3306
basedir      = /usr
datadir      = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir   = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address      = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet   = 48M
thread_stack      = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 48M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log_file = /var/log/mysql/mysql-slow.log
#slow_query_log      = 1
#long_query_time = 2
#log_queries_not_using_indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin         = /var/log/mysql/mysql-bin.log
expire_logs_days   = 10
max_binlog_size         = 100M
#binlog_do_db      = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet   = 21M

[mysql]
#no-auto-rehash   # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/


Please help?! :(

EDIT: Got it to work now, took some time to process I think...


Return to “Help”

Who is online

Users browsing this forum: Bing [Bot] and 12 guests