Jump to content

my.cnf for tables with both MyISAM and MyISAM


Recommended Posts

Hello ! :)

I'm going to have the ibf_core_item_markers_storage table changed to InnoDB. I keep MyISAM for the other tables.

I don't know what to add in the my.cnf file.

Here is what I use so far :

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-slow-queries = /var/log/mysql-slow.log

long_query_time = 5

log-long-format

#log-queries-not-using-indexes

log-warnings=2

skip-locking

skip-innodb

query_cache_limit=6M

query_cache_size=512M

query_cache_type=1

query_cache_min_res_unit=2K

tmp_table_size=256M

max_heap_table_size=256M

max_user_connections=250

max_connections=500

interactive_timeout=100

wait_timeout=100

connect_timeout=60

thread_cache_size=512

key_buffer_size=3072M

max_allowed_packet=32M

table_cache=3072

#net_buffer_length=1M

join_buffer_size=4M

sort_buffer_size=6M

read_buffer_size=2M

read_rnd_buffer_size=4M

max_connect_errors=10

thread_concurrency=8

myisam_sort_buffer_size=1024M

low_priority_updates=1

concurrent_insert=2

server-id=1


#[mysql.server]

#user=mysql

#basedir=/var/lib


[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/lib/mysql/mysql.pid

#open_files_limit=8192

open_files_limit=9000


#[mysqldump]

#quick


#[mysqldump]

#quick

#max_allowed_packet=16M


[mysql]

no-auto-rehash


[isamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M


[myisamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M


[mysqlhotcopy]

interactive-timeout



Can you give me pieces of advice ?

I think I will certainly have to remove this : skip-innodb, but what else should I do ?

Thank you ! :)

Link to comment
Share on other sites

I also add what tuning-primer returns, perhaps this may help :

MySQL Version 5.0.92-community-log x86_64



Uptime = 42 days 7 hrs 39 min 20 sec


Avg. qps = 74


Total Questions = 272204832


Threads Connected = 1



Server has been running for over 48hrs.


It should be safe to follow these recommendations



SLOW QUERIES


The slow query log is enabled.


Current long_query_time = 5 sec.


You have 15277 out of 272204853 that take longer than 5 sec. to complete


Your long_query_time seems to be fine



BINARY UPDATE LOG


The binary update log is NOT enabled.


You will not be able to do point in time recovery


See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html



WORKER THREADS


Current thread_cache_size = 512


Current threads_cached = 235


Current threads_per_sec = 0


Historic threads_per_sec = 0


Your thread_cache_size is fine



MAX CONNECTIONS


Current max_connections = 500


Current threads_connected = 1


Historic max_used_connections = 236


The number of used connections is 47% of the configured maximum.


Your max_connections variable seems to be fine.



No InnoDB Support Enabled!



MEMORY USAGE


Max Memory Ever Allocated : 7.25 G


Configured Max Per-thread Buffers : 7.93 G


Configured Max Global Buffers : 3.50 G


Configured Max Memory Limit : 11.44 G


Physical Memory : 15.70 G


Max memory limit seem to be within acceptable norms



KEY BUFFER


Current MyISAM index space = 2.10 G


Current key_buffer_size = 3.00 G


Key cache miss rate is 1 : 2077


Key buffer free ratio = 13 %


Your key_buffer_size seems to be too high.


Perhaps you can use these resources elsewhere



QUERY CACHE


Query cache is enabled


Current query_cache_size = 512 M


Current query_cache_used = 297 M


Current query_cache_limit = 16 M


Current Query cache Memory fill ratio = 58.01 %


Current query_cache_min_res_unit = 2 K


MySQL won't cache query results that are larger than query_cache_limit in size



SORT OPERATIONS


Current sort_buffer_size = 6 M


Current read_rnd_buffer_size = 4 M


Sort buffer seems to be fine



JOINS


Current join_buffer_size = 4.00 M


You have had 694 queries where a join could not use an index properly


join_buffer_size >= 4 M


This is not advised


You should enable "log-queries-not-using-indexes"


Then look for non indexed joins in the slow query log.



OPEN FILES LIMIT


Current open_files_limit = 6654 files


The open_files_limit should typically be set to at least 2x-3x


that of table_cache if you have heavy MyISAM usage.


Your open_files_limit value seems to be fine



TABLE CACHE


Current table_cache value = 3072 tables


You have a total of 415 tables


You have 2024 open tables.


The table_cache value seems to be fine



TEMP TABLES


Current max_heap_table_size = 256 M


Current tmp_table_size = 256 M


Of 157752 temp tables, 22% were created on disk


Created disk tmp tables ratio seems fine



TABLE SCANS


Current read_buffer_size = 2 M


Current table scan ratio = 344 : 1


read_buffer_size seems to be fine



TABLE LOCKING


Current Lock Wait ratio = 1 : 17


You may benefit from selective use of InnoDB.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...