Optimized Configuration

MySQL & MariaDB Other MySQL® Questions
1

Hi guys,

I’m setting up a PHP/MySQL web application on a new VPS. The VPS is running PHP 5 and MySQL 5, and has 512MB RAM (burstable to 1GB). The server and the application are freshly installed, so I don’t have much benchmarks on the database performance, but I would like it to be robust and scalable under a high-usage multi-user scenario.

The database schema consists of 4 tables only, all InnoDB except for one which is a MyISAM table. The MyISAM table has a full-text index on one of the VarChar fields. There are indexes on all relevant primary key and foreign key fields (all MediumInt).

I would appreciate if someone can review my database configuration (my.cnf listed below) and please suggest suitable improvements:

[mysqld]port = 3306socket = /var/lib/mysql/mysql.sockmax_connections = 400key_buffer = 16Mmyisam_sort_buffer_size = 32Mjoin_buffer_size = 1Mmax_allowed_packet = 16Mmax_connect_errors = 10table_cache = 1024thread_cache_size = 286query_cache_limit = 1Mquery_cache_size = 16Mquery_cache_type = 1tmp_table_size = 16Minteractive_timeout = 25wait_timeout = 1000connect_timeout = 10sort_buffer_size = 2Mread_buffer_size = 1Mread_rnd_buffer_size = 256Knet_buffer_length = 2Kthread_stack = 64Kft_min_word_len = 3skip-bdbskip-innodbskip-locking[mysqld_safe]open_files_limit = 8192[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash#safe-updates[isamchk]key_buffer = 8Msort_buffer_size = 8M[myisamchk]key_buffer = 32Msort_buffer = 32Mread_buffer = 16Mwrite_buffer = 16Msort_buffer_size = 8M[mysqlhotcopy]interactive-timeout

Thanks in advance!

2

You have a bunch of different things that are a bit strange about that config.

Have you checked out the different settings yourself yet?

Because you say that 3 of your tables are InnoDB and yet you have:

[B]Quote:[/B]
skip-innodb
in your config.
3

Thanks for your reply.

The config I posted is a template. Its in no way for final production use yet, and that’s why I’m seeking expert advice )

You are right, “skip-innodb” should be removed and instead the “innodb_*” parameters should be used.

Any recommendations for a stable (yet scalable) configuration to start with?

4

Please share any suggestions.

5

Some questions that I need answered if I’m going to give you proper suggestions.

How big is your database in MB?
So that I can get a feel for what the settings would be.

Do you really need InnoDB and why?
Because as I understand you need MyISAM due to fulltext index and it is better to just focus on one table type in that case.

How many queries per second are you expecting?
You should always try to do some estimation so that you know what to begin with. Otherwise you are just shooting in the dark with the settings.

6

Please suggest. Thanks!

7

OK, here’s a suggestion.

I have made some assumptions based on what you told me and some experienced guesses of my own.

You said the server had 512MB RAM.
Which means that I have deduced about 128MB (OS) + 128 (Apache+PHP) = 256Mb.
If you look at this later and it turns out they are using much less then you can increase the Innodb_buffer_pool_size parameter.
But at the same time you said that your DB was current only 20Mb so it will take you a while before your are going to need so much cache space.

I also made an estimation about future storage requirement for your DB.
One user = 1500 rows according to you and if we assume an average of 5 columns and a guess of column width to 100 bytes (which is probably very high but since I don’t know about your structure).
It still turns out to: 1500 * 5 * 100 = 750kb so the growth rate doesn’t seem to be that high.
Which means that if your InnoDB table space is set to 256Mb as I have in the conf file.
It will take you 3000 users to reach 256MB. Is this a reasonable figure?

Above I’m trying to get you to start to think about things like this. Because without actually trying to calculate something you will never make an educated guess.

Unless you had some special reason for some of the settings in the my.cnf that you posted.
I suggest something like this instead.
It is much simpler since I removed a lot of usually unnecessary or default values:

[B]Quote:[/B]

[mysqld]
max_connections = 256
skip-locking
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 5M
read_buffer_size = 1M
read_rnd_buffer_size = 4M

query_cache_size= 16M

innodb_data_file_path = ibdata1:256M:autoextend

You can set …_buffer_pool_size up to 50 - 80 %

of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M

Set …_log_file_size to 25 % of buffer pool size

innodb_log_file_size = 64M
innodb_log_buffer_size = 8M

Can increase insert/update performance a lot,

drawback is if OS crashes you might loose some

transactions.

innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

This should be sufficient for you to start with and run for quite some time. And by then your DB will be large enough to actually be able to spot any real performance problems.
8

Thanks a lot sterin! Your suggestions make good sense, and I’ll try them out.