Top Nav

Archive | Databases

Change Minimum Word Length In Fulltext Search

By default, MySQL sets the minimum word length in full text search indexes to 4 characters. Here are the steps to lower this limit to 3.

Edit /etc/my.cnf and add

Restart MySQL to apply the change.

Get a list of full text indexes using this query:

Do a repair on each table with a full text index:

 

 

 

0

MySQL InnoDB File Per Table

By default MySQL 5.5 and prior store all InnoDB tables in a single table space or file. Improved performance and managability can be accheived by using the innodb_file_per_table option to cause MySQL to use a separate file for each table. This option is enabled by default on MySQL 5.6 and later.

To implement:
1. Add option to my.cnf:

2. Change table to use single file:

If you want to covert all databases then dump the entire database and import back into the server.

 

0

Mysqldump TRIGGERS Only

Here’s the command to dump the triggers and stored procedures for a database:

 

0

MariaDB on CentOS 7 – “Error in accept: Too many open files”

By default is seems the soft and hard open files limits on MariaDB in CentOS 7 are 1024 and 4096 respectfully. You can see these limits by first getting the process ID:

And then looking at the limits in the proc filesystem:

You’ll see something like this:

Notice the numbers for “Max open files”.

If you run into problems with MariaDB failing and you see errors like this in the log:

Then you need to increase the open files limits by editing:

and adding this line:

to the “[Service]” section. Then reload the systemctl daemon:

and restart the MariaDB service:

Now the limit will be increased.  For example:

UPDATE: We’ve seen similar problems with nginx. The solution is similar … increase the limits for the nginx service.

UPDATE: As noted by Bastiaan Welmers in the comments, it better to copy the service control file then to edit:

 

 

 

 

 

1

Install Percona Server 5.5 On CentOS 6.5

Today we’re going to install Percona Server 5.5 on a CentOS 6.5 server. Our server is hosted on Amazon AWS but the procedure should be the same for any host.

Percona Server is a drop-in replacement for MySQL that features enhanced performance. You can learn more about Percona here:

http://www.percona.com/

We’re going to do our install using the Percona Yum repository. The documentation is here:

http://www.percona.com/doc/percona-server/5.5/installation/yum_repo.html

Lets get started by adding the repository. Run this command as root:

and you’ll get output similar to this:

Now if you run this command:

You’ll see a list of available packages like:

Since our goal is to install Percona 5.5, we’ll do the following:

The install proceeds like this:

As you can see a number of supporting packages were also installed.

Next let’s configure Percona to start on boot:

And run it for the first time:

which should give some output like:

Notice that the service name for Percona is “mysql” instead of “mysqld” which is used by MySQL.

At this point the Percona Server 5.5 install has been completed.

 

 

1