Network_ninja Posted August 19, 2011 Share Posted August 19, 2011 hey guys... Sorry if i am posting this one in the wrong forum. We just have a problem in our company.. All our systems are loading so slow that takes about a minute or so and many are complaining about it: We have about 4 systems created in VB6 and about 4 systems created in PHP We use Mysql Database and the version is 4.1.10 We only use 1 machine, our database and codes resides in there: For the VB system : System 1: maximum of 200 user at a time, about 4GB database in size System 2: maximum of 20 user at a time, about 6GB database in size System 3: maximum of 10 user at a time, about 5GB database in size System 4: maximum of 15 user at a time, about 4GB database in size For the PHP System: System 1: maximum of 200 user at a time, about 8GB database in size System 2: maximum of 200 user at a time, about 2GB database in size System 3: maximum of 200 user at a time, about 1GB database in size System 4: maximum of 200 user at a time, about 3GB database in size Base from the info above guys what do you think is the reason why our systems are loading very slow.... Is it because our database is going bigger and bigger? or because of the number of user? We do have a proper index in our table and we also use LIMIT in our queries.... Tnx everyone... Quote Link to comment Share on other sites More sharing options...
fenway Posted August 21, 2011 Share Posted August 21, 2011 Impossible to tell -- too little information. Quote Link to comment Share on other sites More sharing options...
Network_ninja Posted August 22, 2011 Author Share Posted August 22, 2011 sorry.... but can u tell me what other information do you want to know? tnx Quote Link to comment Share on other sites More sharing options...
fenway Posted August 22, 2011 Share Posted August 22, 2011 I don't know what a system is, for starters. But you're not showing any errors, any output from the slow query log, any examples of common queries, traffic, etc. Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted August 22, 2011 Share Posted August 22, 2011 Do you have any heavy queries running? mysql> show full processlist; Do your servers get too much traffic to handle? tail -f /usr/weblogs/access.log Do an explain on all your queries, and make sure they are using an index, if not modify the query or create an index that the query can use. if it is using an index, can you make the index better? Quote Link to comment Share on other sites More sharing options...
Network_ninja Posted August 23, 2011 Author Share Posted August 23, 2011 In the slow query logs I can see something like this, what does it means? # Query_time: 16 Lock_time: 0 Rows_sent: 2 Rows_examined: 870923 # Query_time: 14 Lock_time: 0 Rows_sent: 0 Rows_examined: 870920 # Query_time: 15 Lock_time: 0 Rows_sent: 94 Rows_examined: 871013 - And we can also see that there are a lots of SELECT *, SELECT distinct in our queries will modify this one can help the performance? - And also we find out that the VB systems are not closing the connection after script execution, does also closing the connection will help increase the performance? - And does running OPTIMIZE table will also help increase the performance And also currently we are now modifying some of our queries... I will be back soon after we reconstruct our heavy queries.... Thanks for all your help guys..... Quote Link to comment Share on other sites More sharing options...
fenway Posted August 23, 2011 Share Posted August 23, 2011 Yeah, that's the problem -- examining 900K rows to return 2. What are those queries -- can you post them, and their accompanying EXPLAIN? Quote Link to comment Share on other sites More sharing options...
Network_ninja Posted August 24, 2011 Author Share Posted August 24, 2011 In connection with the previous post, here is my query: Query1: select depbank,left(unitspaid,3) as unitspaid, sum(checkamtfx) as checkamtfx from pdc where statusdate = '2009-09-04' and right(depbank,1) = 'D' and right(checkstatus,2) = '-R' group by left(unitspaid,3),depbank Query2: insert into `cashpos3` (mbtcho)SELECT distinct depbank from pdc where statusdate = '2009-09-04' and right(depbank,1) = 'D' and right(checkstatus,2) = '-R' and not isnull(depbank) and depbank <> '' Query3: SELECT * FROM pdc WHERE depbank='MBTCHO' AND checktype <> 'ADV' AND batch ='2009-09-07' AND (NOT ISNULL(checkno) AND checkno<>'') AND RIGHT(checkstatus,2)<>'-H' order by checktype,pullorder Pls. see attach file for the accompanying EXPLAIN.... note: Query2 is an insert select type query, I obtain the EXPLAIN by removing the INSERT INTO and replacing it by EXPLAIN. [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
fenway Posted August 24, 2011 Share Posted August 24, 2011 You don't have any indexes -- that's the problem. Why are you querying on parts of a column? Quote Link to comment Share on other sites More sharing options...
Network_ninja Posted August 24, 2011 Author Share Posted August 24, 2011 I will try working on the indexes because the tables already have 3 indexes that are used by other queries... How many indexes can I create in a table? the table has 42 fields.. Sorry but I don't understand this question.. Why are you querying on parts of a column? Do you mean this part: right(depbank,1) = 'D' and right(checkstatus,2) = '-R' The queries and tables are created by the previous developers, what I am doing right now is trying to optimize the performance of the system... Tnx bro..... Quote Link to comment Share on other sites More sharing options...
fenway Posted August 24, 2011 Share Posted August 24, 2011 Well, those previous developers left you with a giant mess. If you add an index on (statusdate), queries 1 and 2 should be noticeably improved. For query3, try ( batch, depbank ). Quote Link to comment Share on other sites More sharing options...
Network_ninja Posted August 25, 2011 Author Share Posted August 25, 2011 tnx for the reply bro... the difference is very noticeable when i add index to that fields... but it's quite a big system so I will try to optimize them all... tnx a lot... Quote Link to comment Share on other sites More sharing options...
fenway Posted August 25, 2011 Share Posted August 25, 2011 tnx for the reply bro... the difference is very noticeable when i add index to that fields... but it's quite a big system so I will try to optimize them all... tnx a lot... You don't want to optimize ALL of them. You wan't to optimize the slowest & most frequently executed ones. Quote Link to comment Share on other sites More sharing options...
Network_ninja Posted August 26, 2011 Author Share Posted August 26, 2011 Yes.... I mean I will optimize the heavy queries... tnx for your help..... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.