XJapan Posted September 4, 2010 Posted September 4, 2010 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.
AndyF Posted September 5, 2010 Posted September 5, 2010 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 Posted September 5, 2010 Posted September 5, 2010 Burn3r - please post the details of your my.cnf so people can assess it and make suggestions. Cheers, Shaun :D
XJapan Posted September 5, 2010 Author Posted September 5, 2010 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 Posted September 5, 2010 Posted September 5, 2010 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 Posted September 5, 2010 Author Posted September 5, 2010 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 Posted September 6, 2010 Posted September 6, 2010 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. Posted September 12, 2010 Posted September 12, 2010 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 Posted September 12, 2010 Posted September 12, 2010 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 Posted September 12, 2010 Posted September 12, 2010 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.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.