Solved

MySql Configuration

Posted on 2011-09-06
1
460 Views
Last Modified: 2012-06-22
Our website is currently experiencing a database bottleneck and we are wondering if its our configuration, we have the configuration posted below, we are not experienced in this matter, we tried to copy the configuration as best as possible from our previous website but it was a newer version of mysql, also the mysql workbench is throwing an error saying that...

"Configuration file did not contain section [mysqld], so a new one was added.  If that is not correct, please fix the sesction name in the Server Instance Editor and reopen the administrator."

this is causing an issue since we cant edit our configuration in the MySql Workbench.
[mysqld]

port=3306
basedir=C:\\Program Files (x86)\\Parallels\\Plesk\\Databases\\MySQL
datadir=C:\\Program Files (x86)\\Parallels\\Plesk\\Databases\\MySQL\\Data
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=400


# properties that are different from previous version, 64M,2M,1520
query_cache_size=1MB
query_cache_limit=128MB


tmp_table_size=64MB
key_buffer_size=256M
read_buffer_size=1M
table_cache=1520
myisam_sort_buffer_size=32M
innodb_additional_mem_pool_size=384M
thread_cache_size=38
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
read_rnd_buffer_size=256K


innodb_flush_log_at_trx_commit=1
innodb_log_file_size=10M
innodb_thread_concurrency=8
sync_binlog=1
ft_min_word_len=2
innodb_log_buffer_size=3M
innodb_buffer_pool_size=218M



old_passwords=1
thread_stack=196608
binlog_cache_size=32768
net_read_timeout=30
net_retry_count=10
net_write_timeout=30
thread_concurrency=10
open_files_limit=0
character-set-server=latin1
sort_buffer_size = 256k
wait_timeout = 360

[client]
port=3306

[mysql]
default-character-set=utf8

Open in new window

0
Comment
Question by:recruitit
1 Comment
 
LVL 26

Accepted Solution

by:
skullnobrains earned 500 total points
ID: 36490238
you definitely sjould make the innodb_buffer_pool_size as big as possible if you are using innodb

normally, when changing it, you should also change the log_buffer_size but THIS IS DANGEROUS IF YOU DO NOT READ THE MANUAL PAGE CONCERNING INNODB RECUPERATION so you may as well leave it alone

lots of other thing can be tuned such as make the transaction isolation lower, ad the skip_double_write option, remove innodb_flush_log_at_trx_commit=1
if you don't need it, remove the binlog if you do not use replication ...

anyway, i would not recommend changing anything other than the pool size before you perform what needs to be done first :

- post complete information such as mysql version, hardware information about the server (mainly RAM, number of CPUs and expected IO performance), and the type of load the server is under (lots af small inserts with a few huge selects with outer joins for example)

- tell us what you actually see and why you think this is a database bottleneck
i'm not questionning your conclusions, but you may have various problems from IO shortage to a number of connections exceeding mysql's max_users

- first check your application and your indexes for much more usefull optimisations
you may use the slow_queries_log to identify long queries and replay them by adding EXPLAIN before the select clauses

- maybe also look at the innodb log since you may also have deadlock issues

good luck
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question