optimizing mysql performances

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:
Code:
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:
Code:
[Warning] Changed limits: max_open_files: 32768  max_connections: 250  table_cache: 16254

Here is my /usr/local/etc/my.cnf:
Code:
[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
 
Make sure MySQL actually reads your my.cnf.
If I'm not mistaken it expects it to be saved in ${mysql_dbdir}/my.cnf.
 
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 :p
 
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 :D

PostgreSQL is not an option for me as I've have to run WordPress on that server which only support MySQL actually.
 
Hello can me please help With my.cnf the website is slow

Code:
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
 
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!
 
Back
Top