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

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


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


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

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


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


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

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

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


7

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


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

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

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

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

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

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.


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


3

If your index is unique, then tell SQL it's unique. The optimiser can use the additional info, there's no downside to making it such. That said, do you really need a second index? Is the different order of the keys for some other queries to use? If not, why not just add the additional include columns to the first index, it'll likely be cheaper in the long ...


3

It is a dump of the table. Are you running backups too often or something like that?


3

Running it with the same configuration does not provide you with a performance gain. It is the same engine and the same optimizer and so on. But you can configure the Enterprise version to act in parallel mode. So you can partition your table and indexes and your multi-CPU server can (theoretically) improve the performance (by factors). This also depends on ...


3

In short: Yes, this can make a lot of sense. It can make a lot of sense to have specific servers for different services. This is more common in the case of having separate hardware for different servers, but can make sense in the case of VPS's too; eg. different file systems provide different benefits to different applications, different mount options can ...



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