In MySQL I always use “DESCRIBE TABLE” or “SHOW CREATE TABLE” to show the schema of a table but I always forget how to do this in PostgreSQL. The PostgreSQL equivalent is:
|
1 |
\d+ tablename |
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
|
1 |
ft_min_word_len = 3 |
Restart MySQL to apply the change. Get a list of full text indexes using this query:
|
1 2 3 |
use information_schema; SELECT TABLE_SCHEMA, TABLE_NAME FROM statistics WHERE index_type LIKE 'FULLTEXT%'; |
Do a repair on each table with a full text index:
|
1 |
repair table [dbname].[tablename] quick; |
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:
|
1 2 |
[mysqld] innodb_file_per_table=1 |
2. Change table to use single file:
|
1 |
ALTER TABLE table_name ENGINE=InnoDB; |
If you want to covert all databases then dump the entire database and import back into the server.
|
1 2 3 |
mysqldump --all-databases > db.sql mysql < db.sql |
Here’s the command to dump the triggers and stored procedures for a database:
|
1 |
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt [dbname] |
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:
|
1 |
cat /var/run/mariadb/mariadb.pid |
And then looking at the limits in the proc filesystem:
|
1 |
cat /proc/XXXXX/limits |
You’ll see something like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[root@web1 ~]# cat /proc/7688/limits Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 31209 31209 processes Max open files 1024 4096 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 31209 31209 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us |
Notice the numbers for “Max open files”. If you run into problems with MariaDB failing and you see errors like this in the log:
|
1 |
[ERROR] Error in accept: Too many open files |
Then you need to increase the open files limits by editing:
|
1 |
/usr/lib/systemd/system/mariadb.service |
and adding this line:
|
1 |
LimitNOFILE=infinity |
to the “[Service]” section. Then reload the systemctl daemon:
|
1 |
systemctl daemon-reload |
and restart the MariaDB service:
|
1 |
/sbin/service mariadb restart |
Now the limit will be increased. For example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[root@web1 ~]# cat /proc/9910/limits Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 31209 31209 processes Max open files 65536 65536 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 31209 31209 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us |
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
Reliable Penguin provides managed web hosting, systems administration, website and server migrations, and expert consulting.
Submit the form—or for immediate service call 866-649-7984.