Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 31 to 40 of 78 10 Older Entries

Displaying posts with tag: configuration (reset)

Upgrading to Barracuda & getting rid of huge ibdata1 file
+2 Vote Up -0Vote Down

Some of this is old stuff, but more people are now converting to InnoDB plugin, so as to enjoy table compression, performance boosts. Same holds for people converting to Percona’s XtraDB. InnoDB plugin requires innodb_file_per_table. No more shared tablespace file.

So your ibdata1 file is some 150GB, and it won’t reduce. Really, it won’t reduce. You set innodb_file_per_table=1, do ALTER TABLE t ENGINE=InnoDB (optionally ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8), and you get all your tables in file-per-table …

  [Read more...]
Tuning InnoDB Configuration
+2 Vote Up -0Vote Down

I had earlier written a post on tuning the MySQL server configuration which was more geared towards the MyISAM storage engine. While that is not because I didn't intend on ignoring InnoDB but because I had planned a whole post on tuning InnoDB related configuration. So this post is the post that I had planned, I have discussed the major configuration parameters in here that should help you out most of the times.

Where’s my cnf file?
+1 Vote Up -0Vote Down

So you have a running MySQL server, it’s working well and everyone’s happy. You want to make a minor change to the configuration file, so you edit the file, restart MySQL – but the change doesn’t catch!

Or maybe you want to check that some global variable has not been dynamically changed without an update to the configuration file. But the configuration file doesn’t make any sense — it looks like nothing is common between the file and the server.

Wait, which my.cnf file does MySQL read? Rather, which my.cnf files?

Ever happened to you? If you’re well organized, and only keep a single …

  [Read more...]
MySQL terminology: processes, threads & connections
+3 Vote Up -0Vote Down

There’s some confusion in the MySQL terminology for processes, threads & connections, which I will try to resolve. I can’t explain the full nature of what processes and threads are; please see Wikipedia [1] [2] for that. But here’s some basics with regard to MySQL:

  • MySQL server is a single process application.
  • It is multithreaded.
  • It (usually) acts as a TCP/IP server, accepting connections.
  • Each connection gets a dedicated …
  [Read more...]
Tuning MySQL Server Settings
+0 Vote Up -0Vote Down

The default configuration file for MySQL is intended not to use many resources, because its a general purpose sort of a configuration file. The default configuration does enough to have MySQL running happily with limited resources and catering to simple queries and small data-sets. The configuration file would most definitely need to be customized and tuned if you intend on using complex queries and when you have good amount of data. Most of the tunings mentioned in this post are applicable to the MyISAM storage engine, I will soon be posting tunings applicable to the Innodb storage engine. Getting started...

MMM for MySQL single reader role
+0 Vote Up -2Vote Down

The standard documentation and tutorials on MMM for MySQL, for master-master replication setup, suggest one Virtual IP for the writer role, and two Virtual IPs for the reader role. It can be desired to only have a single virtual IP for the reader role, as explained below.

The two IPs for the reader role

A simplified excerpt from the mmm_common.conf sample configuration file, as can be found on the project’s site and which is most quoted:

...
<host db1>
  ip                      192.168.0.11
  mode                    master
  peer …
  [Read more...]
Reviewing your MySQL installation on Oracle Enterprise Linux
+5 Vote Up -1Vote Down

After successfully Installing MySQL, let us take a look at an operational MySQL instance on your Oracle Enterprise Linux server.

User Management

By default there will be a new mysql user and group created. This user is used to run the mysqld process is generally not used for any other purpose.

$ grep mysql /etc/{passwd,shadow,group}
/etc/passwd:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
/etc/shadow:mysql:!!:14796::::::
/etc/group:mysql:x:27: …
  [Read more...]
Verifying GROUP_CONCAT limit without using variables
+4 Vote Up -0Vote Down

I have a case where I must know if group_concat_max_len is at its default value (1024), which means there are some operation I cannot work out. I’ve ranted on this here.

Normally, I would simply:

SELECT @@group_concat_max_len

However, I am using views, where session variables are not allowed. Using a stored function can do the trick, but I wanted to avoid …

  [Read more...]
Those oversized, undersized variables defaults
+2 Vote Up -0Vote Down

Some mysqld parameters are far from having reasonable defaults. Most notable are the engine-specific values, and in particular the InnoDB parameters.

Some of these variables have different defaults as of MySQL 5.4. innodb_buffer_pool_size, for example, is 128M on 5.4. innodb_log_file_size, however, has changed back and forth, as far as I understand, and is down to 5M again. These settings are still the same on 5.5.

I wish to present some not-so-obvious parameters which, in my opinion, have poor defaults, for reasons I will explain.

  [Read more...]
Replication configuration checklist
+1 Vote Up -1Vote Down

This post lists the essential and optional settings for a replication environment.

It does not explain how to create replicating slaves. See How To Setup Replication for that. However, not all configuration options are well understood, and their roles in varying architectures can change.

Here are the settings for a basic Master/Slave(s) replication architecturee.

Essential

  • log-bin: enable binary logs on the master. Replication is based on the master logging all modifying queries …
  [Read more...]
10 Newer Entries Showing entries 31 to 40 of 78 10 Older Entries

Planet MySQL © 1995, 2015, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.