Tag Info

Hot answers tagged

12

You need to incorporate the following into your my.cnf file [client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] default-character-set = utf8 collation-server = utf8_general_ci init-connect='SET NAMES utf8' character-set-server = utf8 You can find more information on the following manual pages ...


7

Here's another alternative formula in sproc form: DELIMITER // CREATE PROCEDURE sproc_show_max_memory ( OUT max_memory DECIMAL(7,4)) BEGIN SELECT ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + @@max_connections * ( @@read_buffer_size + ...


7

Going to post this as an answer, with the relevant information. The basic formulas are: Available RAM = Global Buffers + (Thread Buffers x max_connections) max_connections = (Available RAM - Global Buffers) / Thread Buffers To get the list of buffers and their values: SHOW VARIABLES LIKE '%buffer%'; Here's a list of the buffers and whether ...


5

This depends on the amount of MySQL Data and what storage engines your are using When it comes to MyISAM and InnoDB, they cache differently. I wrote a post about this back on April 14, 2011. Since your machine only has 768M of RAM you have to do a nice balancing act depending on which storage engine you are using the most. All MyISAM Please run this ...


5

You can actually find out where your compiled binary is instructed to read options files. $ mysqld --help --verbose 110923 17:09:29 [Warning] Can't create test file /usr/local/mysql-5.6.2-m5-osx10.6-x86_64/data/laptop1113-2.lower-test 110923 17:09:29 [Warning] Can't create test file /usr/local/mysql-5.6.2-m5-osx10.6-x86_64/data/laptop1113-2.lower-test ...


5

Percona just built a tool to do just that called the Configuration Wizard. I tested it out once just to see what it would return and the results were pretty darn close to what we were using on our servers, whose cnf's were put together by highly trained mysql certified dba's.


5

If you want to change some of the global configuration setting of MySQL There are two ways. 1. By Changing variable using SET GLOBAL option on running MySQL instance,this does not require server restart we can change variables like Ex : I need to set my key buffer to 128 MB SET GLOBAL key_buffer_size = 128*1024*1024; But using this technique we can not ...


4

You have to crank up innodb_io_capacity as well. The default is 200. Raise it to 5000 for starters. I would go to 20000. You may also want to make sure ib_logfile0 and ib_logfile1 are sufficiently large. The default value for innodb_log_file_size is 5M. I would raise that to 1G for starters. A larger InnoDB Buffer Pool would also help, perhaps 4G. To ...


4

Most likely /etc/my.cnf or /etc/mysql/my.cnf. Also check /home/username/my.cnf and /home/username/.mysql/my.cnf If no luck there you can run find / -type f -name my.cnf That will scan you're entire disk where non priviledged access is allowed (which is fine; it should find it, i'm assuming your distro came with it and you're able to connect or you ...


4

You many need to take a look at the comprehensive list of all Server Variables. Some of them you can change for the duration of your session, some you may change with the SET GLOBAL command, and other options may require a restart of MySQL. Here is the list of all options. This will also show which options can be used in option files.


4

I have written some posts in the StackExchnage Tuning MySQL for InnoDB and MyISAM How to keep InnoDB Diskspace under control Another Viewpoint on MySQL Diskspace Managament Viewpoint on InnoDB Optimization InnoDB Fine Tuning Please read these for the guidance you need. Now, for more pressing issues: You mentioned that you have 400MB of data, 1GB with ...


4

If there's only one my.cnf file, and it's /etc/my.cnf, then that's the one it loads. From the manual: On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first). /etc/my.cnf Global options SYSCONFDIR/my.cnf Global options $MYSQL_HOME/my.cnf ...


4

Depending on how your access was implemented, you may be able to look at my.cnf with the \! command. mysql> pager less mysql> \! cat /etc/mysql/my.cnf .... mysql> nopager If you want to know what variables are actually running on the server, look at SHOW GLOBAL VARIABLES mysql> SHOW GLOBAL VARIABLES;


4

One thing, here, is that you should be using this form, instead: mysql> show global status like '%open%'; Some of these counters are global and some of them are session, so not using the GLOBAL keyword gives you a split set of numbers (especially the Opened_table* values). The problem with tuning scripts is they can't possibly take into account all of ...


4

If the MySQL Debian-7 minimal cannot use local_infile, look around all the make files used for compiling to see if it is disabled by default or if local_infile is enabled for the Debian-7. Before taking that kind of time, please run the following: SHOW GLOBAL VARIABLES LIKE 'local_infile'; SET GLOBAL local_infile = 'ON'; SHOW GLOBAL VARIABLES LIKE ...


3

This will not cause any problems. I have always setup all MySQL 5.5. DB servers for clients with both plugins. In fact, I answered a question back on Aug 05, 2011 where I posted an update on Aug 08, 2011 on how to install both plugins at the same time Please see Is MySQL Replication Affected by a High-Latency Interconnect? Here is the excerpt from that ...


3

Ok, thanks this answer I figured out that I needed to put the config WITHIN [mysqld] block in my.cnf to take effect. My mistake was that I just had appended the commands at the end of my.cnf file. Then at mysql restart I encounterd InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes To resolve this error, I needed to deleted log ...


3

One of the most important variables for InnoDB is innodb_buffer_pool_size. This is the amount of memory allocated to load tablespace information for InnoDB only. Since you are mixing MyISAM and InnoDB, you will need to find a good balance between key_buffer_size (for MyISAM indexes) and innodb_buffer_pool_size (for InnoDB reads). In general, you want to ...


3

As a first course of action, I'd watch the output of 'iostat -xk 10 10' to see if the disk IO is saturating the line. I notice that you sent specs for your memory system, but not your disk system. What's the underlying array here that you're writing so much to? If you are insert-heavy, then that could play a big part.


3

Check the MySQL documentation about the precedence of reading configuration files and their location. On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first). File Name Purpose /etc/my.cnf Global options /etc/mysql/my.cnf Global options (as ...


3

I have an interesting surprise for you. The only Optimizing for FullText Indexing you can do is not something at the my.cnf level. It is all about two things: The Stopword List The Query STOPWORDS There are 543 stopwords that you may or may not want filtered out of FULLTEXT indexes. The list of stopwords was built at compile time. You can override that ...


3

You know what they say, about "when all else fails..." This release continues the process begun in MySQL 5.6.6 of making changes to the default values of server parameters. The motivation for these changes is to provide better out-of-box performance and to reduce the need for database administrators to change settings manually. These changes are ...


2

DISCLAIMER : Not a Debian User Based on the comments, I would suggest the following cp /etc/mysql/my.cnf /etc/my.cnf cp /etc/mysql/my.cnf /usr/etc/my.cnf cp /etc/mysql/my.cnf /var/lib/mysql/my.cnf (or wherever datadir is) If after making these my.cnf file and restarting mysql you still cannot change innodb_buffer_pool_size, then I would suspect the ...


2

A simple solution for that would be to keep the my.cnf file identical, and use symlinks on your machine sto point to whatever directory you want the data to be in. For example, assuming you my.cnf contains this: [mysqld] datadir=/mysql_data On you linux host, you would: cd / ln -s /var/lib/mysql mysql_data Add permissions to this folder for mysql user: ...


2

MySQL works fine without the set-variable in the my.cnf file


2

It is true that innodb_buffer_pool_size is the single most important MySQL with InnoDB tuning parameter. However if you wish to go further, then tools.percona.com has a my.cnf generator that will generate a very reasonable configuration for you. Disclaimer: Percona is my employer.


2

There are some options you will need to consider InnoDB Buffer Pool The reason 26G was picked is that you have 32GB of RAM and 80% of that is 25.6 G. Since you mentioned that you will have 100 databases and 100 applications making this a multitenant DB Server, you are going to have to get the InnoDB Buffer Pool just right. Please run this query: SELECT ...


2

The .ibd files will be under the innodb_data_home_dir under a subdirectory, the subdirectory name matching the database name. Given innodb_data_home_dir=/etc/local/mysql/data and innodb_file_per_table is set: SHOW VARIABLES LIKE 'innodb_data_home_dir' SHOW VARIABLES LIKE 'innodb_file_per_table' then when table test.mytable is created, the .ibd file ...


2

You need to change the my.cnf file to include the single line under [mysqld] in your file max_allowed_packet=16M now restart the MySQL service and you are done. You can see its current value in mysql like this: SHOW VARIABLES LIKE 'max_allowed_packet' You can try changing it like this, but it's unlikely this will work on shared hosting: SET GLOBAL ...


2

3 seconds is kind of a long time for mysql. I bet you have a lot of full-table scans going on that are using up CPU. I've gotten good mileage in decreasing CPU with just a little indexing. This is not going to be a short answer, but try this: Enable the slow query log (set global slow_query_log=on and set global ...



Only top voted, non community-wiki answers of a minimum length are eligible