mysql config

[XJapan]

Hello,

I finished configuring and hardening the new VPS, but i'm worried about mysql overloading, and I'm not sure what to do in the configuration file.

What my.cnf configuration do you suggest to use on mysql for an IPB running on 1.25 GB RAM - CPU type: Intel® Xeon® CPU X3460 @ 2.80GHz , 2 cores

256 ram already used for LAMP, so 1 GB free.

And how to reduce the number of queries shown in the debug bar.

Regards.

[XJapan]

yo?

[AndyF]

I'll move your topic to "Server Management, Resources, Optimization" , where its more likely to be seen by those who can help with this kind of question :)

[Clickfinity]

Burn3r - please post the details of your my.cnf so people can assess it and make suggestions.

Cheers,
Shaun :D

[XJapan]

Thanks Andy!

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
max_connections = 2000
max_user_connections = 250
key_buffer = 128M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 8
concurrent_insert = 2
table_lock_wait_timeout = 35
wait_timeout = 35
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
table_cache = 2M
join_buffer_size = 1M
sort_buffer_size = 2M
read_buffer_size = 1M
thread_cache_size = 384
wait_timeout = 900
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 8M
net_buffer_length = 4M
thread_stack = 256K
skip-bdb
skip-innodb
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 131072
query_alloc_block_size = 65536
default-storage-engine = MyISAM

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash
#safe-updates

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

[agent462]

The my.cnf/.ini files mysql includes are terrible. Here is an example off of a windows box that you could port to your server.

This was for a server with a couple gig of memory so you'll want to scale it back a bit. This is also an innodb based system. myisam really shouldn't be used.

#

[client]


port=3306


[mysql]

default-character-set=utf8


# SERVER SECTION

# ----------------------------------------------------------------------

[mysqld]

server-id=40


port=3306

basedir="C:/Program Files/MySQL/MySQL Server 5.0/"

datadir="F:/db/data/"

tmpdir="T:/db/temp/"


default-character-set=utf8

default_collation= utf8_general_ci

default-storage-engine=INNODB

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"


#*** Other Settings ***

max_connections=600

query_cache_size=64M

table_cache=340

tmp_table_size=32M

thread_cache_size=100

transaction-isolation=repeatable-read


max_allowed_packet=16M

max_heap_table_size=32M

query_cache_limit=4M

query_cache_type=1

thread_stack=256k

skip_bdb


#*** Timeout Settings ***

wait_timeout=20

interactive_timeout=20

connect_timeout=60

wait_timeout=20

net_read_timeout=60

net_write_timeout=60


#*** MyISAM Specific options ***

# http://dev.mysql.com/doc/refman/5.0/en/myisam-start.html

myisam_max_sort_file_size=10G

myisam_sort_buffer_size=32M

key_buffer_size=32M

read_buffer_size=1M

read_rnd_buffer_size=1M

sort_buffer_size=4M

myisam_recover=BACKUP


#*** INNODB Specific options ***

# http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html

innodb_additional_mem_pool_size=32M

innodb_flush_log_at_trx_commit=2

innodb_support_xa=1

innodb_log_buffer_size=5M

innodb_buffer_pool_size=1024M

innodb_log_file_size=512M

innodb_thread_concurrency=8


#*** Log File Section ***

log-bin="L:/db/logs/mysql-bin"

log_bin_index="L:/db/logs/mysql-bin.index"

max_binlog_size=512M

sync_binlog=1

log-error="L:/db/logs/error_log"

log-slow-queries= "L:/db/logs/slow_queries"

long_query_time=1

log-warnings

expire_logs_days=14


#*** Security ***

secure_auth=1

local-infile=0

skip-symbolic-links

skip_show_database


[safe_mysqld]

open-files-limit=8192

[XJapan]

SQL dies and doesnt want to restart after adding those options.

#*** MyISAM Specific options ***

# http://dev.mysql.com/doc/refman/5.0/en/myisam-start.html

myisam_max_sort_file_size=10G

myisam_sort_buffer_size=32M

key_buffer_size=32M

read_buffer_size=1M

read_rnd_buffer_size=1M

sort_buffer_size=4M

myisam_recover=BACKUP

I googled a bit, and Seems like InnoDB is much better for Boards such as IPB.

I tried to convert my database, but I got an error with this table.

DBD::mysql::db do failed: The used table type doesn't support FULLTEXT indexes [for Statement "ALTER TABLE `forum`.`IPBmessage_posts` ENGINE=INNODB"] at ./mk-find line 2616.

I'm using this method:

http://linuxadminzone.com/how-to-quickly-convert-mysql-databases-from-myisam-to-innodb/

[agent462]

This is a typo myisam_max_sort_file_size=10G it should be way smaller. I must have grabbed an old revision of this file.

InnoDB is always better :)

When you change your innodb log file size you need to delete or rename the old ones on the server before restarting it. There are scripts that can convert your tables over or you can do each one manually via something like phpmyadmin. You will lose the myisam full text search functionality when you go to innodb. This won't really hurt you unless you're running a real large board which I would recommend you go with sphinx search anyway.

[Gary.]

NEVER use anyones my.cnf ! FACT

Using someones guide can easily mess things up..

Build your own and get the right guides by using mysql.tuner

Install mysql tuner via ssh.

# wget mysqltuner.pl

Once installed run the following:

# perl mysqltuner.pl

Do as it says the first time and add anything it requires.

Run it every 4 days for a month to get the best possible output.


thread_concurrency = 8 Should be 2 as you say its a 2 core machine
max_allowed_packet = 500M Crikey ! LOL - Set this to around 96M

Nobody can tell anyone what to put in ! Only you and your benchmark test can tell you what to put.

[phinsup]

Also another suggestion I would make. Make a couple small changes and let the server sit for a while, use top and check out your load. I've done things like run mysqltuner, made all the changes and all of a sudden the server is going nuts. If you make all the changes at once it can be a real nightmare to track down the troublesome setting, if you just make a couple it's a lot easier.

[phinsup]

I tried to convert my database, but I got an error with this table.

DBD::mysql::db do failed: The used table type doesn't support FULLTEXT indexes [for Statement "ALTER TABLE `forum`.`IPBmessage_posts` ENGINE=INNODB"] at ./mk-find line 2616.

I'm using this method:

[url="http://linuxadminzone.com/how-to-quickly-convert-mysql-databases-from-myisam-to-innodb/"]http://linuxadminzon...isam-to-innodb/[/url]

You need to drop the FULLTEXT index, then change the storage engine.