Re: How DBA solved overload problem ?
one of the first things to look for in database performance tuning is the existence (or nonexistence) of indexes (for SELECTs, that is). they are perhaps the #1 most used optimization for relational databases. if you can, i would suggest first tracking down the query text itself. sybase and oracle have mechanisms for logging full query text (oracle to a file, sybase to the sybsecurity database in sysaudits_0x tables), mysql and postgresql most likely have the same features. if not, it would be a trivial task to have query text logged to a file. next, i would determine if there are indeed indexes on the tables in the query. if there are none, try adding some. if there are some, then try adding more. :) (without getting into too much detail, try adding indexes on columns that are commonly joined on or restricted on...primary keys for tables is also nice). -Anthony. On Thu, Sep 18, 2003 at 12:08:51PM +0100, Supote Leelasupphakorn wrote: To all, As a newly DBA, I really don't know how I deal with this problem. My problem is not so long ago, my database server seem to overloaded. It take me a time to find the cause of problem. I realize that some program don't queried wiht inappropriated SQL statement. I mean they're not efficient one. AS DBA how do you solved this problem? Thanks in advance, Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk ___ [EMAIL PROTECTED] mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-chat To unsubscribe, send any mail to [EMAIL PROTECTED] pgp0.pgp Description: PGP signature
How DBA solved overload problem ?
To all, As a newly DBA, I really don't know how I deal with this problem. My problem is not so long ago, my database server seem to overloaded. It take me a time to find the cause of problem. I realize that some program don't queried wiht inappropriated SQL statement. I mean they're not efficient one. AS DBA how do you solved this problem? Thanks in advance, Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk ___ [EMAIL PROTECTED] mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: How DBA solved overload problem ?
Supote Leelasupphakorn wrote: To all, As a newly DBA, I really don't know how I deal with this problem. My problem is not so long ago, my database server seem to overloaded. It take me a time to find the cause of problem. I realize that some program don't queried wiht inappropriated SQL statement. I mean they're not efficient one. AS DBA how do you solved this problem? You're hitting up against, what I feel is one of the most common and most difficult to solve problems out there. The best solution is to fix the SQL statements. Unfortunately, it's quite possible that you won't be able to fix the application that's causing the problem, becuase it's closed-source and written by someone else. At that point, you're up against the frustrating chore of convincing the application designer to fix their lousy code, which is often impossible. In the end, it seems that most people end up buying faster, more expensive hardware to make up for lousy application design. It's a damn shame, but that's what seems to happen a lot. -- Bill Moran Potential Technologies http://www.potentialtech.com ___ [EMAIL PROTECTED] mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: How DBA solved overload problem ?
[cc list cut] On Thu, 18 Sep 2003, Supote Leelasupphakorn wrote: To all, As a newly DBA, I really don't know how I deal with this problem. My problem is not so long ago, my database server seem to overloaded. It take me a time to find the cause of problem. I realize that some program don't queried wiht inappropriated SQL statement. I mean they're not efficient one. AS DBA how do you solved this problem? Thanks in advance, It depends on your database to some extent; but basically, you need to gather information. Assuming privacy and monitoring concerns have been dealt with (hey, it happens), you ideally need to find a way to crank up your DB's logging levels so that it records query statistics. You're looking for particularly expensive queries. The usual crop of tools (explain) can help you to tune your datyabase (eg, via index creation, materialised views, etc.) or track down users to impart clue. From that you should be able to identify the applications that are the source of your problems. You're probably better off directing specific questions at support mailing lists for the DB in question. jan PS. If the DB in question is postgresql, you may find that complex query performance is highly erratic due to the genetic planner kicking in. Either turn up the number of generations it uses by an order of magnitude or so, or turn it off. -- jan grant, ILRT, University of Bristol. http://www.ilrt.bris.ac.uk/ Tel +44(0)117 9287088 Fax +44 (0)117 9287112 http://ioctl.org/jan/ Just because I have nothing to hide doesn't mean I have nothing to fear. ___ [EMAIL PROTECTED] mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: How DBA solved overload problem ?
As a newly DBA, I really don't know how I deal with this problem. My problem is not so long ago, my database server seem to overloaded. It take me a time to find the cause of problem. I realize that some program don't queried wiht inappropriated SQL statement. I mean they're not efficient one. AS DBA how do you solved this problem? In PostgreSQL, I flip on the following settings and then periodically scan PostgreSQL's the log file (/var/log/pgsql): log_duration = true log_pid = true log_statement = true log_timestamp = true It's tedious, but with grep on your side, it's not an impossible task or even a super hard one... just a bit tedious if you don't keep up with your developers. I've found doing this on the devel machines produces better bang for the buck than on the production DB's (though I still do it there occasionally). Once a query is found, I typically launch a big 'ole fashion egrep -r with a reasonably unique part of the query and am normally pretty successful in finding the culprit. Sometimes you'll have to use sockstat to find what machine and what PID you're dealing with, but that's something I have to do rarely. -sc PS Don't spam so many lists in the future, just -databases or just -questions would've been sufficient (the more topical the list the better). -- Sean Chittenden ___ [EMAIL PROTECTED] mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
