0

I have a 6 years old computer (WinXP) working as MySQL database server.

Finally decided to invest on a new computer to replace this old one but do not know what do I need to focus to have the best performance.

What is better processor speed, dual processing, multithreat, high cache memory, 64bits procesor? Or maybe a motherboard with 2 processors.

I would appreciate your opinions.

Thanks in Advance

Jerry

  • 5
    There's no way to properly answer this as you've not stated your app load, users, db size, resilience requirements, budget, scaling, response requirements, skill levels, support requirements etc. – Chopper3 Feb 16 '10 at 23:30

4 Answers4

2

As Chopper said, we can't really give a concrete answer without much more information about the size and requirements of your applications. But in general:

  • If it's running more-or-less happily today on a 6-year-old WinXP desktop, any modern hardware you throw at it will probably be hugely overspecced.

  • I would generally go for larger RAM first, so you can have larger in-memory buffers. If you can keep most of your working set of commonly-accessed data in memory instead of hitting the disc, you're golden.

  • A 64-bit-capable CPU is a given in today's mainstream. To use 4GB+ RAM you'll need to install a 64-bit OS on it, but that's not anything out of the ordinary any more.

  • Dual-core or quad-core is also a given. You can go higher than that, or multi-CPU if you really want, but for a simple database server you are unlikely to need it.

  • If you really want to spend some money, put an SSD in it!

bobince
  • 811
  • 4
  • 8
  • With DB applications, the main bottleneck is the data set. So increase the memory hierarchy size + speed - cache, ram, harddisk. – sybreon Feb 17 '10 at 00:37
1

Depends on what you're doing to the database.

How hard are you pushing it? You were using a 6 year old system before deciding to trash it...I'm guessing anything you get would be an improvement.

Without knowing how you're using the computer and what your needs are, you should see something better with just sticking 4+ gig of RAM on it and a fast processor. If you have a gig switch, get a gig network card in the machine.

Are you using the computer JUST as a database server? If you're doing other things on the system that affects things too.

And if you're serious about using this thing and pressing it as a server, you already have a backup scheme in place? Or do you need RAID also?

In my experience if you're doing this at home and not depending on this for income, getting fancy with 16 gig of RAM or hardware RAID or all sorts of bells and whistles will be overkill in the long run. If you ARE using this for income or depend on it being 24/7 reliable, you need to supply your workload, what you're doing with the machine, your budget, etc. and prioritize how you use the machine and what bottlenecks you're seeing now in order to get helpful advice.

Bart Silverstrim
  • 31,172
  • 9
  • 67
  • 87
  • I'm using a small store system with mutiple users, worst scenario is 20 connections at the same time. It is not 24/7 system is just bussiness hours operative. The desision of change the computer is because a new computer was added to the same network and has the same specs than the server now. Hard disk is 7200Rpm, 3Gb ram and Pentium procesor 1.7Ghz. As one of the tables has the sales since the system started, queries to this table make slow process aprox 1 million of registers. New processors has more cache memory, and multi threathing, does it will increase the speed on those queries. –  Feb 18 '10 at 17:22
  • Maximum 20 connections at a time? Keep the old server until it releases it Magic Smoke. Schedule it to reboot every night. 20 concurrent connections is nothing, and theoretically speaking, your server should be plenty fast enough for the job until the hard drives fail in about 15 years. If anything, what you have now is tremendous overkill. – Ernie Feb 18 '10 at 17:59
  • The real question is, are you experiencing slowdowns with the current server? If so, then you need to find out what the bottleneck is. Processor multithreading, more than 4 gig of memory, useless unless the database software is made to take advantage of it, and from your description you're not pushing it at all, although I don't understand what you mean by "queries to this table make slow process aprox 1 million of registers"? You have 20 connections from 1 million registers? – Bart Silverstrim Feb 18 '10 at 18:10
  • 1
    Also...what are you doing for backups? If this hard disk dies tomorrow, are you able to rebuild that server from bare metal restore? If not, FORGET about the (currently working) server being replaced, and get to work on a backup solution FIRST. You need it, ESPECIALLY if you're trying to migrate a business's server to new hardware! – Bart Silverstrim Feb 18 '10 at 18:11
  • Rereading it several times I think I see what you mean by the one table being slow. I think you might want to first find a way to archive some of the table data from a certain period of time and trim the table down a bit, if it has 6 years of sales on it. This is more a problem with your database architecture. For what you're doing, I think you'll stand to gain more if you trim out data you don't use...otherwise, you can throw hardware at it...if you are runnign 64 bit database software, go for a lot of RAM and 10,000 RPM disks. But I think it's overkill. – Bart Silverstrim Feb 18 '10 at 18:14
  • Thank you for your response. I will talk with my partner to wait some time or until the pc die. And yes I do full backup at the end of the day every day :S and the file is send to another computer. What I need to work on is keeping logs at mysql and send them daily to another computer instead of the full backup. –  Feb 18 '10 at 21:19
1

Since in a comment you said that you have a table with 1 millon registry, its possible also you need to improve query and not only hardware.

If i am in your situation, I will buy new hardware if that is possible and also check the slow queries.

Also I think it would be good idea to avoid windows if you use mysql. Few years ago, I used win2003 with mysql, but when I migrate to linux (host only running mysql service) The performance gain was a lot.

At respect to hardware (low cost budget): This is like a "home" o "small" setup, for 20 connections. 4gig ram minimum quad AMD (Its a lot for 20 connections at same time,but that computer could run other services also) And of course some RAID setup (you should investigate this) Remember to buy good motherboard.

If you are going to buy server hardware, I really dont know anything about this...so sorry!

But, i really think that you have problems with querys!

At home, i am running a core 2 duo, with 3gig of ram. Its running 20 openvz container with 4 web pages (like 15 connections at same time) and a lot of bots crawling internet (this makes like 1000 querys a second to mysql databases) and I dont have any performance issue.

Of course, if I had any slow query...well the thing changes a lot about performance.

Harddrives are sata, not using RAID!

Of course I am running linux.

hipe it helps

llazzaro
  • 421
  • 4
  • 16
  • Thank you for your response, It is not slow now, just when the big table is used for some reports. Those reports used to be quick at the begining but not anymore –  Feb 18 '10 at 21:14
0

Well, this depends heavily on the size and workload of the database. If it was running fine on a 6y old desktop machine, everything you get today as server grade hardware will likely fit the bill.

What do you see as the main reason why you want to replace the current system, except it's age and the fact it's running XP?

Some fundamentals: Basically, I would focus on lots of main memory and a really fast disk subsystem, as these are the main bottlenecks with databases. CPU speed isn't so much of an issue for moderate or low loaded databases, but 64bit CPU/OS is important, if only to allow more than 4G of RAM.

Sven
  • 98,649
  • 14
  • 180
  • 226
  • Well, The max ram allowed on this computer is 3Gb so to increase a new MB is neaded, and would prefer to leave current server as a normal computer and have a robust server with mor grow capability. so then the recomendation would be a 64bit cpu and more than 4Gb of Ram? –  Feb 18 '10 at 17:26
  • From what you later described, you would need a 64 bit database server to take full advantage of more RAM. But you seem to have an issue because you have 6 years of sales on a server that is running an otherwise small business...I'd think that a better approach might be to archive some of that database out into another table that's not constaly queried, or find a way to optimize that table. I just think it's silly for a small business to run a ginormous server because of one table being full of hardly used data... – Bart Silverstrim Feb 18 '10 at 18:17
  • Thank you. Yes, some improvement need to be done at the huge table, maybe do what you said and has one historical table with all the info and the current one with only last 10,000 registers. –  Feb 18 '10 at 21:13