MYSQL SYSTEM PARAMETERS

From a previous blog post I talked about MySQL database. From this text I’m going to show much more advanced topic in MySQL which is known as system parameters. To optimize our database server to be more efficient we should know about the system parameters. You can see all the system parameters by just typing ‘SHOW VARIABLES;‘ at the mysql command line.

20

21

22

Next if we want to change any of the above variables for better performance, what we have to do is assigning a new value for a particular variable. For example I’ll change the wait_timeout variable. The existing value is 28800. I want to change it to 25000. Let’s see how to do it.

SET variable_name = new_value;

25

Again if I check the variables, then I can see the value has been changed successfully.

26

Here  have listed some other important variables as well,

  • key_buffer_size : Total memory for MySQL.
  • table_open_cache : Number of tables can be kept open simultaneously.
  • wait_timeout : time to wait before a timeout.
  • sort_buffer_size : Memory for sorting operation.
  • read_buffer_size : Memory for Select operation.
  • net_buffer_length : How much data is prepared into a buffer, to be going over the net, before actually going over the network in a whole.
  • read_rnd_buffer_size : Used after a sort, when reading rows in sorted order.
  • max_allowed_packet : Maximum size of packet that can be transfered.
  • thread_stack : Stack size for a thread.
  • thread_cache_size : Cache for connections.
  • max_connections : Maximum amount of concurrent connections.
  • memlock : Avoid swapping.
  • query_cache_size : Memory allocated for a query.

1

2

Using this method we can change any variable and make our database server much more efficient. Though this is a very short post, it is very important when it comes to industry level. The optimization of database server will save your time and resources. Hope to see you soon with another important topic. Thank You!

Advertisements

3 thoughts on “MYSQL SYSTEM PARAMETERS

Leave a Reply

Fill in your details below or click an icon to log in:

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