MySQL Performance: MySQL => Optimized Configuration

Members

Help

Home

Home » Performance » MySQL » Optimized Configuration

Show: Today's Messages :: Show Polls :: Message Navigator

Optimized Configuration [message #1424]

Fri, 15 June 2007 09:08

VictorZ
Messages: 9
Registered: June 2007

Junior Member

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            = 3306
socket          = /var/lib/mysql/mysql.sock
max_connections = 400
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
max_allowed_packet = 16M
max_connect_errors = 10
table_cache = 1024
thread_cache_size = 286
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K
ft_min_word_len = 3
skip-bdb
skip-innodb
skip-locking

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

Thanks in advance!

Report message to a moderator

Re: Optimized Configuration [message #1425 is a reply to message #1424 ]

Fri, 15 June 2007 09:22

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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:

Quote:

skip-innodb

in your config.

Report message to a moderator

Re: Optimized Configuration [message #1426 is a reply to message #1424 ]

Fri, 15 June 2007 09:44

VictorZ
Messages: 9
Registered: June 2007

Junior Member

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 Smile

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?

Report message to a moderator

Re: Optimized Configuration [message #1441 is a reply to message #1424 ]

Mon, 18 June 2007 06:02

VictorZ
Messages: 9
Registered: June 2007

Junior Member

Please share any suggestions.

Report message to a moderator

Re: Optimized Configuration [message #1442 is a reply to message #1441 ]

Mon, 18 June 2007 07:43

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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

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

2.
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.

3.
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.

Report message to a moderator

Re: Optimized Configuration [message #1445 is a reply to message #1442 ]

Mon, 18 June 2007 14:52

VictorZ
Messages: 9
Registered: June 2007

Junior Member

Thanks Sterin,

Quote:

1. How big is your database in MB?

The database will initially be around 10-20mb only, but it will scale up from day one, as users start adding data. Each user will have on average about 1000-1500 records (mainly with varchar and mediumint fields) as aggregate in all tables. There are couple of table joins (max between 2-3 tables) as well, and some composite indexes defined. Average fields per table is 4-5. So its not a very complex schema, but the data storage will scale up eventually.

Quote:

2. Do you really need InnoDB and why?

I've now fianlized on InnoDB (for all tables), because I've given up on MySQL FTS (the reason I had MyISAM), as I'm using Sphinx FTS now.

Quote:

3. How many queries per second are you expecting?

Based on preliminary usage analysis, I'm expecting around 50-100 queries per second initially, and it will scale up to 500-1000 queries per second (rough estimate) in the first 6 months.

Hoping to hear your suggestions and clarify the scalability.

Good day!

Report message to a moderator

Re: Optimized Configuration [message #1472 is a reply to message #1424 ]

Mon, 25 June 2007 02:13

VictorZ
Messages: 9
Registered: June 2007

Junior Member

Please suggest. Thanks!

Report message to a moderator

Re: Optimized Configuration [message #1506 is a reply to message #1424 ]

Sun, 01 July 2007 18:28

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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:

Quote:

[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.

Report message to a moderator

Re: Optimized Configuration [message #1510 is a reply to message #1424 ]

Tue, 03 July 2007 05:05

VictorZ
Messages: 9
Registered: June 2007

Junior Member

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

Report message to a moderator

Previous Topic:	replication problem after master crash
Next Topic:	One Very Large Table vs Many Little Tables

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 10 05:44:07 EDT 2009

Total time taken to generate the page: 0.00664 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.