MySQL – Configuration

Background

Once a database is installed, one needs to configure it, let us take for instance adjust the memory limits.

 

Database – Data and Indexes

Database store data and indexes.  As I/O is usually the greatest drag the more we can keep in memory the better for us.

It is advisable that on a system dedicated to the Database, we should reserve 75 to 80% for INNODB Data and Index.

To adjust the buffer size MySQL exposes the innodb_buffer_pool_size parameter.

Configuration

GUI

MySQL Workbench

Steps

  1. Launch MySQL Workbench
  2. Make Changes
    • From the Navigator panel, select Instance \ Options File
      • On the Right Tab
        • Access the INNODB tab
          • innodb_buffer_pool_size
            • Set to the value you want
            • We wanted 2 GB and so we entered 2G
        • Click the Apply… tab to save your changes
  3. Restart Database
    • From the Navigator panel, select Instance \ Startup & Shutdown
      • On the Right Tab
        • Click the “Stop Server” button
          • Wait for the Instance to stop
        • Click the “Start Server” button
          • Wait for the Instance to re-start
  4. Review Changes
    • From the Navigator panel, select Status and Server Variables
    • On the Right Tab
      • Choose the “Server Variables” Tab
        • From the Categories list, choose InnoDB/Buffer Pool
        • On the Values list
          • Read innodb_buffer_pool_size
            • Name :- innodb_buffer_pool_size
            • Value :- 2147483648
            • Description :- Size of the memory buffer InnoDB uses to cache data and indexes of its tables

Options File \ INNODB

OptionsFile-InnoDB

 

Apply Changes to MySQL Configuration File

ApplyChanges

 

Server Variables

ServerVariables

 

Error

As of v5.7.5, MySQL allows the innodb_buffer_pool_size parameter to be set dynamically.

Unfortunately, we experienced intermittent problems.

Error Code: 1232. Incorrect argument type to variable ‘innodb_buffer_pool_size’

SQL

SET GLOBAL innodb_buffer_pool_size=1800M

Output

Image

ErrorCode-1232 -- Incorrect argument type

Textual


SET GLOBAL innodb_buffer_pool_size=1800M
Error Code: 1232. Incorrect argument type to variable 'innodb_buffer_pool_size'

Remedy

I will suggest that you edit the configuration file directly or set the parameter through the GUI pathway described earlier.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s