slow database retrieval

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

Re: slow database retrieval

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

Re: slow database retrieval

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

Re: slow database retrieval

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

Re: slow database retrieval

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

Re: slow database retrieval

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

Re: slow database retrieval

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

Re: slow database retrieval

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

Re: slow database retrieval

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 "
echo " --repair "
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

Re: slow database retrieval

lark isinulat:
> 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 "
> echo " --repair "
> 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...

i just want to ask a few questions about the script

printUsage() {
> echo "Usage: $0"
> echo " --optimize "
> echo " --repair "
> return
> }
..
..
..
case $1 in
> --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)..;->

Re: slow database retrieval

grace isinulat:
> lark isinulat:
> > 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 "
> > echo " --repair "
> > 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...
>
> i just want to ask a few questions about the script
>
> printUsage() {
> > echo "Usage: $0"
> > echo " --optimize "
> > echo " --repair "
> > return
> > }
> .
> .
> .
> case $1 in
> > --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)..;->

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

Re: slow database retrieval

grace wrote:
> grace isinulat:
>> lark isinulat:
>>> 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 "
>>> echo " --repair "
>>> 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...
>>
>> i just want to ask a few questions about the script
>>
>> printUsage() {
>>> echo "Usage: $0"
>>> echo " --optimize "
>>> echo " --repair "
>>> return
>>> }
>> .
>> .
>> .
>> case $1 in
>>> --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)..;->
>
> 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.

Re: slow database retrieval

On Jul 3, 8:19 am, lark wrote:
> grace wrote:
> > grace isinulat:
> >> lark isinulat:
> >>> grace wrote:
> >>>> lark isinulat:
> >>>>> == Quote from grace (zymp...@gmail.com)'s article
> >>>>>> Ayon kay lark:
> >>>>>>> == Quote from grace (zymp...@gmail.com)'s article
> >>>>>>>> Ayon kay lark:
> >>>>>>>>> == Quote from grace (zymp...@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 "
> >>> echo " --repair "
> >>> 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...
>
> >> i just want to ask a few questions about the script
>
> >> printUsage() {
> >>> echo "Usage: $0"
> >>> echo " --optimize "
> >>> echo " --repair "
> >>> return
> >>> }
> >> .
> >> .
> >> .
> >> case $1 in
> >>> --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)..;->
>
> > 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