slow database retrieval

Discussion:

(too old to reply)

grace

2007-06-15 08:06:57 UTC

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

grace

2007-06-15 10:11:20 UTC

Permalink

Post by 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
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

lark

2007-06-15 16:59:07 UTC

Permalink

Post by grace

when i tried to do some comparisons between my old and my new server
Db_town 8902 rows direct from
machine: 50ms from network: 2687ms
410ms from network: 27609ms
Db_town 8902 rows direct from
machine: 60ms from network: 421ms
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

grace

2007-06-18 05:20:13 UTC

Permalink

Post by lark

Post by grace

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

lark

2007-06-18 13:50:34 UTC

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

grace

2007-06-22 02:25:17 UTC

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

lark

2007-06-22 14:33:58 UTC

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

grace

2007-06-30 03:37:43 UTC

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
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?
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
locate myisamchk or
whereis myisamchk
/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

lark

2007-07-02 16:10:17 UTC

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
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?
mysql-standard-4.1.22-pc-linux-gnu-i686-glibc23
Thanks,
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

grace

2007-07-03 05:26:29 UTC

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
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?
mysql-standard-4.1.22-pc-linux-gnu-i686-glibc23
Thanks,
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
##########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
$[_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.
mysql_optimize.sh --optimize databaseName databaseUser databasePassword

TNX the reply...

i just want to ask a few questions about the script

printUsage() {

Post by lark
echo "Usage: $0"
echo " --optimize <dbname>"
echo " --repair <dbname>"
return
}

.
.
.
case $1 in

Post by lark
--optimize) DBCMD=OPTIMIZE; doAllTables;;
--repair) DBCMD=REPAIR; doAllTables;;
--help) printUsage; exit 1;;
*) printUsage; exit 1;;
esac

i could see the REPAIR command..do this mean the code at the same time
repairs the table too? (or its just i don't understand it, im not
knowledgeable enough in writing scripts on mysql...)
if ever is it ok to run repair table frequently? isn't this command
for corrupted tables only?...
...(sorry i don't have mch time to read more and research about
syntax but i am sure learning from you)..;->

grace

2007-07-03 05:38:19 UTC

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
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?
mysql-standard-4.1.22-pc-linux-gnu-i686-glibc23
Thanks,
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
##########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
$[_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.
mysql_optimize.sh --optimize databaseName databaseUser databasePassword

TNX the reply...
i just want to ask a few questions about the script
printUsage() {

Post by lark
echo "Usage: $0"
echo " --optimize <dbname>"
echo " --repair <dbname>"
return
}

.
.
.
case $1 in

Post by lark
--optimize) DBCMD=OPTIMIZE; doAllTables;;
--repair) DBCMD=REPAIR; doAllTables;;
--help) printUsage; exit 1;;
*) printUsage; exit 1;;
esac

another, some of my tables already contains more than 30000 rows of
data...i thnk that's one thing that causing
the slow retrieval on my database? ;->

lark

2007-07-03 15:19:56 UTC

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
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?
mysql-standard-4.1.22-pc-linux-gnu-i686-glibc23
Thanks,
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
##########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
$[_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.
mysql_optimize.sh --optimize databaseName databaseUser databasePassword

TNX the reply...
i just want to ask a few questions about the script
printUsage() {

Post by lark
echo "Usage: $0"
echo " --optimize <dbname>"
echo " --repair <dbname>"
return
}

.
.
.
case $1 in

Post by lark
--optimize) DBCMD=OPTIMIZE; doAllTables;;
--repair) DBCMD=REPAIR; doAllTables;;
--help) printUsage; exit 1;;
*) printUsage; exit 1;;
esac

another, some of my tables already contains more than 30000 rows of
data...i thnk that's one thing that causing
the slow retrieval on my database? ;->

30000 rows is nothing. mysql should return result set in a jiffy (very
quickly).

the script does repair as well; however, you don't have to run it with
that option if you don't want to repair a table. the repair and optimize
do not run at the same time but one at a time. if you want to run the
repair just run the script with --repair instead of --optimize. make
sure your database is indexed properly. if you're on version 5.x you can
turn on the slow query log and find out what queries are taking more
than a set time (say 3 seconds) and then work on improving those.

grace

2007-07-12 05:07:34 UTC

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
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?
mysql-standard-4.1.22-pc-linux-gnu-i686-glibc23
Thanks,
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
##########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
$[_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.
mysql_optimize.sh --optimize databaseName databaseUser databasePassword

TNX the reply...
i just want to ask a few questions about the script
printUsage() {

Post by lark
echo "Usage: $0"
echo " --optimize <dbname>"
echo " --repair <dbname>"
return
}

.
.
.
case $1 in

Post by lark
--optimize) DBCMD=OPTIMIZE; doAllTables;;
--repair) DBCMD=REPAIR; doAllTables;;
--help) printUsage; exit 1;;
*) printUsage; exit 1;;
esac

another, some of my tables already contains more than 30000 rows of
data...i thnk that's one thing that causing
the slow retrieval on my database? ;->

30000 rows is nothing. mysql should return result set in a jiffy (very
quickly).
the script does repair as well; however, you don't have to run it with
that option if you don't want to repair a table. the repair and optimize
do not run at the same time but one at a time. if you want to run the
repair just run the script with --repair instead of --optimize. make
sure your database is indexed properly. if you're on version 5.x you can
turn on the slow query log and find out what queries are taking more
than a set time (say 3 seconds) and then work on improving those.- Hide quoted text -
- Show quoted text -

tnx...i am going to use your script..and as what you've said i must
check my tables' indexes...Currently, i am monitoring my database...
tnx for many help ;-.> am sure had learned so many from you..a
million tnx
grace