Tag Info

Hot answers tagged

19

I'm going to disagree with everyone here. Chuck some hardware at it. It's cheap, fast, easy, and will buy you the time needed to implement a proper CRM solution. The reason I'm advocating something that is anathema to just about everyone on not only this board, but stackoverflow as well, is that I've been a project manager/manager and have been on the ...


14

You might not need to do either. My suggestion is to simply add some indexes/keys to the table. tables with tens of thousands of rows with no primary key or index, which are also used heavily in multi-table joins Before spending a lot of money or time, take a couple hours and add indexes (or primary keys if you can) to any tables involved in those joins ...


13

A few production issues and workarounds that I've had with the Express edition: Scheduled Backups No SQL Agent Workaround: use any scheduler you want to run SQLCMD backup script or Expressmaint See also: How can I schedule a daily backup with SQl Server Express? SSIS SQL Express 2008 does have Import/Export Data Wizard, but Express edition's dtsexec ...


13

The other editions of SQL server get you things like the SQL Agent so you can schedule database maintenance and other jobs. As long as your database can fit withing the limits of the Express edition you'll be just fine. SQL server likes lots of RAM. The more the better. As the SQL Server can't load data into cache that'll put additional load on the ...


11

I am sorry, but my honest answer is this: If you don't even know how to check if your database is indexed you are so far out of your league that your only option is to hire a professional, given the scope of your project. Not a group of cheapo code slaves, but someone with a lot of experience with sites like yours. We can give you a plethora of tips, but ...


10

Here's the corner-of-the-napkin calculation: 10K drive - 150IOPS, 15K drive - 180IOPS (these numbers are used for example by EMC for array sizing). RAID10 read performance - 600IOPS. RAID5 read performance - 540IOPS (no penalties, straightforward multiple). RAID10 write performance - 300IOPS (each block goes to two spindles). RAID5 write performance - 135 ...


10

Stephen, you need to dig deeper first. Would the entire 'hot' (frequently used) subset of the database fit in RAM if you just upgraded RAM to something larger, like 32 or 64GB? Have you checked that your database has the right indexes in place, have you done a basic MySQL performance audit? About consumer gear: Using consumer-grade gear like your WD ...


9

First question: Why are you on a relational database to begin with, if you don't need ACID properties? It sounds like you're doing some kind of non-transactional work, so getting a RDMBS with transactions is probably too heavy for your environment. Second question: What kind of data are you storing? You sound like you need a column-store database, and ...


8

The lack of disk I/O implies that queries are fed mostly out of RAM. If you 'suddenly' have your hot tables not fit in RAM anymore and the server starts working the disks, you may be in for a bad ride. 2GB or RAM isn't very much these days, but back in the SQL2000 era it would have been sizeable. I'm guessing that the amount of data that the application ...


8

You should do a VACUUM ANALYZE as the VACUUM will allow space used by the deleted data to be reused and prevent transaction wraparound, and the ANALYZE will update planner statistics which should lead to better query plans for your reporting queries. A REINDEX is not theoretically required, but you may find that it results in better performance as the index ...


8

There is no one-size-fits-all answer to scaling MySQL. A few general tips: Scale "diagonally" as long as you can, ie. keep things on a single MySQL server as long as you're still able to run on commodity hardware. That probably means 2 x quad-core CPUs, 64+ GB RAM, 8 disk RAID 10 -- or higher. The upper end of what is "commodity hardware" is getting faster ...


6

this is a business question not a technical question. As a business owner: How strategic is the system to the business? the less strategic, the less i care & fixing it & any money spent, is money I could be using elsewhere to grow my business. Computer folk scare me as they all get in a big room & argue about design & cost me a fortune. ...


6

Ok, lets get real. Database + app server in use should not really swap. Now, I understand "swap out stuff not used like parts of the kernel etc.", but 64gb swap space is ridiculous. There is NO Way the computer can make use of that in a sensible fashion with decent speed. Takes too long. Cut that down. Significant. VERY significant. Like to 8gb or so. Maybe ...


5

As you noted, this is a very broad estimate. The big question is what you use that $1000 to buy. Provided that you spring for a bit more memory and less processor power with an average hard disk, I would say that a reasonably coded application (where reasonable = mainly using whatever abstracting libraries your language provides) with those parameters ...


5

dmesg | grep -i numa This will tell you if your server is numa enabled. numactl -s will tell you if numa is enabled on a per process basis. If production nodes shouldn't use numa, then you probably shouldn't. I would suggest rather than relying on turning it off via numactl, you can and probably should usually turn numa off completely via BIOS. I've ...


4

My system is too slow and I am trying to improve its performance and efficiency. Too little memory. And, most important - like most people not knowing really about databases - you talk a lot about cores and RAM (and Win 7 - get rid of it and install a Windows Server, please), but totally ignore the one thing most important for database performance: ...


4

You really need to find a good DBA who can more carefully evaluate your particular requirements and make an informed recommendation. That said, I'd worry about making it work before worrying about scaling excessively, and put it all in one DB to start. There are advanced techniques to divide data so it's not all on the same server, without compromising the ...


4

It seems your connections to the DB are slow due to some other reason. How fast is a simple command line connection? (ie, non-php) My prime suspect would be failing (= timing out) dns lookups on the remote or local server; both forward and reverse lookups. From this page: start mysqld with --skip-name-resolve If that doesn't help, I would start analyzing ...


4

The best way I know of to tweak count()'s performance on huge data sets is to count() something that's been indexed (if you're trying to count a partial result, you can create a matching partial index).


4

I've been running raid arrays of those exact drives, the 160GB intel G2's for almost a year now. Its a cluster of 12 servers doing about 3500 queries per second right now, but thats with a lot of spare capacity, I've had it down at 6 servers and everything was fine. If you do the research and the math it basically boils down to "don't use them for five ...


4

At the low end, it basically boils down to "can you absolutely say that you have no shared data?" Unlike mysql, the database is an absolute boundry in postgresql. You cannot SELECT zip_code FROM common.city_zip WHERE city=... if you go with separate databases (at least not without dblink). If you have any shared data at all, postgresql's "schema" is ...


4

There are a few things you can do and check to give you more information on the issue and, in turn, handle it better: You have to find out exactly what is making your server "slow". Is it maxing out the CPU, is it running out of RAM and using swap space, is it IO limited, etc...? Each of these have a different solution so you need to know what the problem ...


4

If you're using Microsoft SQL Server or MySQL (I'm confident others will support this too, but those are the only 2 I know for definite) they both contain a dedicated data type for storing time values. I'd always recommend storing things like dates and times in their dedicated formats (DATE, TIME, DATETIME etc) because then if you need to query the values ...


4

It is indeed the sum of multiple processors' work. I'd bet dollars to donuts that if you look at the cached plan that you'll see some parallelism in it.


3

No one else will know the answer to this question better then yourself! I would do some investigation into where the bottlenecks are on your current server. i.e. CPU, memory, disk i/o and use that to at least guesstimate the kind of specs you would need for a new database server + allowing for future growth & headroom. Once you get your hands on the ...


3

I'd suggest using MySQL Proxy. With MySQL Proxy you can intercept and log every query going into your server. A simpler approach would be running mysqlbinlog to analyze the binary log files generated by MySQL (see man mysqlbinlog). Anyway, if you haven't done this already, it would be a good idea to run mysqltuner to see if there are any obvious problems ...


3

The advantage of RAID 10 will be in write performance, so the correct answer really depends on whether or not your database is doing a lot of writing to disk, and whether or not performance matters with this writing. Bear in mind that RAID 10 can offer a layer of resilience that RAID 5 doesn't have; you can afford to lose 2 disks (provided they're not a ...


3

You might consider looking into HBase and HyperTable as well; but again, as Avery Payne mentioned, you don't give us any information about your current application, just your database platform. Some things to keep in mind: Joins are done manually on the non-SQL platforms. They won't do things like foreign keys, aggregates, etc. All of that is manual. ...



Only top voted, non community-wiki answers of a minimum length are eligible