3

Im in the process of setting up a new database server. I have been running a few mysql database servers before and it has been working okay.

But i would like to hear the recommended setup for my server. For example, what should i set the max connection, query_cache_size, table_cache and so on.

I have arround 4-600 per second:

Open tables: 112 Queries per second avg: 430.386.

The server i am setting it up on have the following configuration:

Linux version 2.6.32-5-amd64 (Debian 2.6.32-41squeeze2)

2x Intel Xeon X3440 @ 2.53GHz

4GB Ram

/, /boot, /tmp etc on Software RAID1, 2x 7200RPM SATA

Data location on Software RAID0, 2x7200RPM SATA

Im am going to place the mysql databases on the RAID0.

Am im missing anything? Let me know!

Thanks in advance, im looking forward to hearing from you :-)

/Rasmus

Improve this question
1
  • Get a good backup solution, because RAID0 isn't really reliable.
    – Lucas Kauffman
    Apr 8, 2012 at 5:05

4 Answers 4

Reset to default
5

it's impossible to give any good answer without knowing the type of workload, amount of data and usage patterns you have. honestly even with some knowledge of those at the end you'll have to find out by yourself what works best. that means lot's of benchmarking, tracking what goes on inside, regular checking of the slow query logs etc.

mysql's default settings are not really optimal for the modern hardware, as a reasonable starting point you can use values proposed by percona's mysql configuration wizard or mysqltuner.pl.

most probably inodb as a storage engine will be more optimal in your case. only 4GB of memory is quite little [but again i dont know what's the total size of your data set]. 2x Xeon X3440 is a lot in terms of the cpu power, you'd better have a lot of concurrent traffic and be able to fit all the data in memory, otherwise you'll not make much use of available processors just waiting for the disks to read/write the data.

side note: data on raid0? i hope you do have hot spare server with real-time replication or that the data is not important at all.

Improve this answer
2
  • Hey, thanks. I will try to look at mysqltuner.pl and percona's wizard. I was planning of mirroring the db, and thats why i could but it on the RAID0... Thought it would be someway faster? As of right now, the issue with my db is that it waits for the hdd.. I will also be checking the other links
    – Rasmus
    Apr 8, 2012 at 10:28
  • @Rasums - your comment still does not tell us anything about size of your data and nature of the queries; mysql's replication has its limitations - it's single threaded on the slave so if you have a lot of parallel queries you'll need slave machine to be faster [in terms of io, cpu power] than master to keep up with it. or you'll have varying slave lag.
    – pQd
    Apr 8, 2012 at 11:52
2

RAID0 aka stripe is VERY dangerous for DB. And for everything else too. One disk lost - everything lost. Stripe is slightly faster than single disk and other RAIDs, but too risky.

Improve this answer
1
  • I know that raid 0 is not safe, but when doing replication on db it seems for me to be okay? I believe that there is some better performance in a raid0
    – Rasmus
    Apr 8, 2012 at 10:31
2

WTF? You've got the non-performance critical stuff / easily restored stuff running off a mirror, while your performance critical, volatile data is on JBOD????? Missing something? You've got it completely backwards.

That is the first, and most glaring issue here. If you really have 4 disks, and assuming this is some sort of OLTP application, then I'd go with something like...

disk1  [md0][md1  ][md2          ]
disk2  [md0][md1  ][md2          ]
disk3  [sw0][md3                 ]
disk4  [sw1][md3                 ]

Where boot is on md0, root on md1, data distributed across md2 and md3 (where md0...3 are RAID1). With non-mirrored RAID on disks 3 & 4 (you might consider putting logs in their own partition in place of sw1).

The setup for an OLAP type system would be very different.

You might also want to spend some time thinking about the filesystem you run on the data partitions (xfs is probably a safe bet).

As to tuning the mysql instance - that is dependant on what data you are storing and how it is being accessed. Read the tuning section in the manual and get hold of myqltuner.pl and/or the Percona toolkit. But first you need to decide if innodb or myisam is more appropriate for you - again this depends on the layout of data and how it is accessed.

Improve this answer
2
  • Well, i had the idea that the raid0 was slighty faster than the raid1 and therefore i'd place it on that kind of raid. Am im wrong? If i replicate the db to another server, the data should be recoverly very fast..?
    – Rasmus
    Apr 8, 2012 at 10:33
  • Only for specific workloads (OLAP rather than OLTP) - and it has no fault tolerance (indeed you're twice as likely to use your data as if you kept it on one disk).
    – symcbean
    Apr 11, 2012 at 8:19
0

For basic statistics and recommendations you can start with mysqltuner.pl script. Do not apply recommendations blindly, as it might decrease performance.

MySQLTuner Github page

For extended support and common recommendations/tweaks it worth to check Percona official site and their blog

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .