# optimizing mysql performances



## digrouz (Dec 27, 2011)

Hello,

I'm trying to improve the performances of my mysql server. I used a script mysqltuner.pl that advised me to increase the table_cache of my database. I did but it seems to be not functional. 

Here are my logs (/var/log/mysql/error.log) when I start mysql:

```
111227 16:22:56 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
111227 16:22:56 [Warning] Changed limits: max_open_files: 32768  max_connections: 250  table_cache: 16254
111227 16:22:56 InnoDB: The InnoDB memory heap is disabled
111227 16:22:56 InnoDB: Mutexes and rw_locks use GCC atomic builtins
111227 16:22:56 InnoDB: Compressed tables use zlib 1.2.5
111227 16:22:56 InnoDB: Initializing buffer pool, size = 32.0M
111227 16:22:56 InnoDB: Completed initialization of buffer pool
111227 16:22:56 InnoDB: highest supported file format is Barracuda.
111227 16:22:56  InnoDB: Waiting for the background threads to start
111227 16:22:57 InnoDB: 1.1.8 started; log sequence number 27667402
111227 16:22:57 [Note] Event Scheduler: Loaded 0 events
111227 16:22:57 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.5.19-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
```

I would like to fix that line but I don't understand how as I already tried to set _table_cache_ to _524288_:

```
[Warning] Changed limits: max_open_files: 32768  max_connections: 250  table_cache: 16254
```

Here is my /usr/local/etc/my.cnf:

```
[mysqld]
query_cache_size=256M
query_cache_type=1
query_cache_limit=4M
innodb_file_per_table
max_connections=250
key_buffer_size=256M
tmp_table_size=128M
max_heap_table_size=128M
thread_cache_size=4
[color="Red"]table_cache=524288[/color]
sort_buffer_size=4M
read_buffer_size=1M
innodb_buffer_pool_size=32M
slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow_queries.log
log-error = /var/log/mysql/error.log
```


----------



## SirDice (Dec 27, 2011)

Make sure MySQL actually reads your my.cnf. 
If I'm not mistaken it expects it to be saved in *${mysql_dbdir}*/my.cnf.


----------



## gkontos (Dec 27, 2011)

SirDice said:
			
		

> Make sure MySQL actually reads your my.cnf.
> If I'm not mistaken it expects it to be saved in *${mysql_dbdir}*/my.cnf.



/etc/my.cnf to my disappointment. Smells like Linux


----------



## vermaden (Dec 27, 2011)

> optimizing mysql performances


Best advice? Install PostgreSQL


----------



## digrouz (Dec 28, 2011)

Yes, it reads my my.cnf because it is a symbolic link to /var/db/mysql/my.cnf. 

Smells like Linux because I'm migrating from a Linux server 

PostgreSQL is not an option for me as I've have to run WordPress on that server which only support MySQL actually.


----------



## amilojko (Jan 2, 2012)

Check the options for starting MySQL, you can specify which config file to read in /etc/rc.conf.


----------



## Accessdenied (Jun 22, 2018)

Hello can me please help With my.cnf the website is slow 


```
local-infile = 0

back_log = 50

#skip-networking

skip-external-locking

#external-locking

character-set-server = utf8
collation-server = utf8_general_ci

max_connections = 2000

max_connect_errors = 50

table_open_cache = 2048

max_allowed_packet = 32M

binlog_cache_size = 1M

max_heap_table_size = 64M

read_buffer_size = 16M

read_rnd_buffer_size = 16M

sort_buffer_size = 16M

join_buffer_size = 16M

table_cache = 512

thread_cache_size = 256

thread_concurrency = 8

query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4

# If your system supports the memlock() function call, you might want to
# enable this option while running MySQL to keep it locked in memory and
# to avoid potential swapping out in case of high memory pressure. Good
# for performance.
#memlock

default-storage-engine = MYISAM


thread_stack = 192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 64M

log-bin=mysql-bin

binlog_format=mixed

# If you're using replication with chained slaves (A->B->C), you need to
# enable this option on server B. It enables logging of updates done by
# the slave thread into the slave's binary log.
#log_slave_updates

# Enable the full query log. Every query (even ones with incorrect
# syntax) that the server receives will be logged. This is useful for
# debugging, it is usually disabled in production use.
#log

# Print warnings to the error log file.  If you have any problem with
# MySQL you should enable logging of warnings and examine the error log
# for possible explanations.
#log_warnings

# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in "long_query_time" or which do not use
# indexes well, if log_short_format is not enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
slow_query_log

# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use "1" as a value here, as this will result in
# even very fast queries being logged from time to time (as MySQL
# currently measures time with second accuracy only).
long_query_time = 2


server-id = 1

key_buffer_size = 512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 512M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

# *** INNODB Specific options ***

# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 2G

innodb_data_file_path = ibdata1:10M:autoextend

innodb_write_io_threads = 8
innodb_read_io_threads = 8

#innodb_force_recovery=1

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commit = 1

#innodb_fast_shutdown

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

#innodb_log_group_home_dir

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120


[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick

max_allowed_packet = 32M

[mysql]
no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 8192
```

can you me please write for correct settings in MB or GB or K 


thx alot for help


----------



## SirDice (Jun 22, 2018)

We can't tell you what to change because we have no idea of your statistics.

NB. You are adding to a thread that's more than 6 years old!


----------



## CyberCr33p (Jun 22, 2018)

https://github.com/major/MySQLTuner-perl will give you some general tips.

Keep in mind that big buffers sizes may have worse results.


----------

