Quote:
>>grace wrote:
>>>lark isinulat:
>>>>== Quote from grace (zympoul@gmail.com)'s article
>>>>>Ayon kay lark:
>>>>>>== Quote from grace (zympoul@gmail.com)'s article
>>>>>>>Ayon kay lark:
>>>>>>>>== Quote from grace (zympoul@gmail.com)'s article
>>>>>>>>>Ayon kay grace:
>>>>>>>>>>i am wondering why my database retrieval becomes too slow...we set up
>>>>>>>>>>a new server (ubuntu, breezy badger) machine where we transferred all
>>>>>>>>>>our files from the old server.. Our new server uses Asus p5pe-vm
>>>>>>>>>>motherboard and an Intel Pentium D 3.0Ghz processor, compared to the
>>>>>>>>>>old one where we uses asrock motherboard and AMD Duron. Both has the
>>>>>>>>>>same version of mysql installed...
>>>>>>>>>>>
>>>>>>>>>>To summarized, both machine has the same configuration except for some
>>>>>>>>>>hardware changes and the version of the Ubuntu installed (we used
>>>>>>>>>>ubuntu hoary hedgehog on the old machine).
>>>>>>>>>>>
>>>>>>>>>>what would be the cause of our problem? i don't thnk it has someting
>>>>>>>>>>to do with the way i run my queries from my applications since i do
>>>>>>>>>>not encounter such problems on our old machine..Also i tried to
>>>>>>>>>>execute queries from my sqlyog :
>>>>>>>>>>ex:
>>>>>>>>>> select * from db_town
>>>>>>>>>> result: 8902 rows() in 2687 ms
>>>>>>>>>> where on the old server it should take only about 421 ms
>>>>>>>>>>>
>>>>>>>>>> select * from diagnostics_detail
>>>>>>>>>> result: 42499 rows() in 27609 ms
>>>>>>>>>> where on the old server it should take only about 4375
>>>>>>>>>>ms
>>>>>>>>>>>
>>>>>>>>>>Please please need again ur urgent help...
>>>>>>>>>>grace
>>>>>>>>>when i tried to do some comparisons between my old and my new server
>>>>>>>>>here's the outcome:
>>>>>>>>>NEW SERVER:
>>>>>>>>>Db_town 8902 rows direct from
>>>>>>>>>machine: 50ms from network: 2687ms
>>>>>>>>>Diagnostics_detail 42511 rows direct from machine:
>>>>>>>>>410ms from network: 27609ms
>>>>>>>>>OLD SERVER:
>>>>>>>>>Db_town 8902 rows direct from
>>>>>>>>>machine: 60ms from network: 421ms
>>>>>>>>>Diagnostics_detail 42511 rows direct from machine:
>>>>>>>>>410ms from network: 4375ms
>>>>>>>>>hope someone could help me...
>>>>>>>>>grace
>>>>>>>>i don't think this has anything to do with hardware.
>>>>>>>>be sure to run optimize table on all of your tables in the new server and then
>>>>>>>>post the results back in here.
>>>>>>>>--
>>>>>>>>POST BY: lark with PHP News Reader
>>>>>>> tnx for the advice...
>>>>>>>is it alright to run 'optimize table' while the database is active? -
>>>>>>>meaning while users are using it? to be sure i am planning to do it
>>>>>>>later when they are sure done with it..any results, i would inform
>>>>>>>you..tnx much
>>>>>>yes! it is safe to run optimize table on your tables while it the database server
>>>>>>is active.
>>>>>>--
>>>>>>POST BY: lark with PHP News Reader
>>>>> I am done optimizing my tables and I am happy with the result...
>>>>>Everything's back to normal except for these 9 tables where I used to
>>>>>get data for generating a summary of every customer's transaction. (a
>>>>>receipt-like form)
>>>>>On my application I have this print command button which contains this
>>>>>simple script:
>>>>>dw_servicetrans.retrieve(transcode)
>>>>>.
>>>>>.
>>>>>dw_servicetrans.print(true)
>>>>>When I click this button, it would take almost 2-3 seconds of
>>>>>retrieval on every first attempt, and if tried again it would be less
>>>>>than 1 second... I wonder why is this so since I am just calling the
>>>>>same script??...Not to consider much as a problem but for me, 2 -3
>>>>>seconds of retrieval for a small amount of data do matters ( its just
>>>>>that data are extracted from several tables, is this so?)...and
>>>>>it keeps bugging my mind as to why initially it is unable to retrieve
>>>>>with same speed like those of the succeeding attempts?
>>>>>I tried to run myisamchck on these 10 tables only but with no success.
>>>>>It returns
>>>>>Bash: myisamchk: command not found
>>>>>I could not even check for its manual: man myisamchk
>>>>>Returns : No manual available for myisamchk
>>>>>Do this mean that I haven't installed completely my mysql?
>>>>>I installed this version:
>>>>>mysql-standard-4.1.22-pc-linux-gnu-i686-glibc23
>>>>>Thanks,
>>>>>grace
>>>>no! it does not mean you haven't installed mysql completely! it only means
>>>>myisamchk is not in your path! :) you can do a couple of things to find out where
>>>>myisamchk is located:
>>>>locate myisamchk or
>>>>whereis myisamchk
>>>>>
>>>>then, it will tell your where it is. for example:
>>>>/usr/local/mysql/bin/myisamchk
>>>>>
>>>>once you have this information, use the whole path to run myisamchk on the table.
>>>>>
>>>>be sure to look at myisamchk --help first to see all of its parameteres (there are
>>>>a lot).
>>>>>
>>>>for the slow query, you may want to look at the index fields on those 10 tables.
>>>>look at your where statements and if you have any joins at your join statement.
>>>>place the right index fields at the right table and your query will become fast again.
>>>>--
>>>>POST BY: lark with PHP News Reader
>>>im into trouble again, and need your help badly..remember i did
>>>followed your advice of optimizing all my tables to speed up my
>>>database retrieval? Really it had improved my database
>>>performance...however, only yesterday, and all of a sudden, my
>>>database is running slow again!!? what i did was run the optimize
>>>table and everythings fine again...But i am worried now since it seems
>>>that my server is not stable enough to do its job..Or did i miss
>>>something while we were just configuring the server like setting the
>>>database cache, (w/c i don't know how to and is this important?) and
>>>so on and so forth?...Please help me solve this problem...sorry i
>>>really don't know what's
>>>going on inside my database...
>>>when i run the optimize command it would only return 'ok' or 'table is
>>>already up to date'..so i think there's nothing wrong with it.(i guess
>>>so)...
>>>>
>>>tnx in advance...
>>>grace
>>>>
>>sounds like you have a very volatile database. you may have to run
>>optimize on your tables frequently. here's a little script that will
>>help you to have all of your tables within a database optimized. you can
>>run on the command line or put it in your cron daily or whatever you like:
>>>
>>>
>>##########STart copying from the next line###########
>>#!/bin/sh
>>>
>>DBNAME=$2
>>DBUSER=$3
>>DBPASS=$4
>>>
>>printUsage() {
>> echo "Usage: $0"
>> echo " --optimize <dbname>"
>> echo " --repair <dbname>"
>> return
>>}
>>>
>>>
>>doAllTables() {
>> # get the table names
>> TABLENAMES=`mysql5 --password=$DBPASS --user=$DBUSER -D $DBNAME -e
>>"SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*:
>>\([_0-9A-Za-z]*\).*/\1/p'`
>>>
>> # loop through the tables and optimize them
>> for TABLENAME in $TABLENAMES
>> do
>> mysql5 --password=$DBPASS --user=$DBUSER -D $DBNAME -e "$DBCMD
>>TABLE $TABLENAME;"
>> done
>>}
>>>
>>if [ $# -eq 0 ] ; then
>> printUsage
>> exit 1
>>fi
>>>
>>case $1 in
>> --optimize) DBCMD=OPTIMIZE; doAllTables;;
>> --repair) DBCMD=REPAIR; doAllTables;;
>> --help) printUsage; exit 1;;
>> *) printUsage; exit 1;;
>>esac
>>#########END COPY on the above line############
>>>
>>this came from Son Nguyen from mysql website. i added the authentication
>>part to it.
>>>
>>you run it like this:
>>mysql_optimize.sh --optimize databaseName databaseUser databasePassword
>TNX the reply...
>.
>.
>.
>for corrupted tables only?...
