On what factors does speed of mysql depends
Dear Friends, I have a database with approximately 10 tables with about 1 lakh records each in 3 tables, I need to know that on what factors does the speed of mysql depends, 1)Does a table having records effects the speed of data fetch of another table in the same database. 2)Whats the approximate size of a table ideal for mysql, Any other factors you want mine attention to be foccused on. Thanks, Abhishek Jain
Re: speed of mysql insert through C api
On Thu, 15 Nov 2001, Jonothan Farr wrote: > There's no way to know that without knowing the schema of the > table you're inserting into. How you index it is going to be the >biggest factor there. just a plain file with 2 columns - one of them is a decimal field - taht's all. cu, fns -[ mailto:[EMAIL PROTECTED] http://www.megaram.hu/~fns icq:878397 ]- -[ Scuba Divers: Take only photographs and leave only bubbles! ]- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: speed of mysql insert through C api
On Thu, 15 Nov 2001, Gabor Penoff wrote:
> can you tell me an estimated speed about filling 100 records with
> mysql insert by using the C api?
Last insert job I ran did c. 400 inserts per second. This is real
("wall") time and some computations were involved.
(Dont go running fetching a calculator, I did it for you: 1 million
records === 42 minutes.)
--
,
M A R I O data miner, LIACC, room 221 tel 351+226078830, ext 121
A M A D O Rua Campo Alegre, 823 fax 351+226003654
A L V E S P-4150-180 PORTO, Portugalmob 351+939354002
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: speed of mysql insert through C api
There's no way to know that without knowing the schema of the table you're inserting into. How you index it is going to be the biggest factor there. --jfarr - Original Message - From: "Gabor Penoff" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, November 15, 2001 2:23 PM Subject: speed of mysql insert through C api > Hi folks, > > can you tell me an estimated speed about filling 100 records with > mysql insert by using the C api? > I know the question sounds silly - all I have now is a suspicion that > I make something wrong (I mean the insert seems to be quite slow here > at home). > > cu, > fns > -[ mailto:[EMAIL PROTECTED] http://www.megaram.hu/~fns icq:878397 ]- > -[ Scuba Divers: Take only photographs and leave only bubbles! ]- > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
speed of mysql insert through C api
Hi folks, can you tell me an estimated speed about filling 100 records with mysql insert by using the C api? I know the question sounds silly - all I have now is a suspicion that I make something wrong (I mean the insert seems to be quite slow here at home). cu, fns -[ mailto:[EMAIL PROTECTED] http://www.megaram.hu/~fns icq:878397 ]- -[ Scuba Divers: Take only photographs and leave only bubbles! ]- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Design,Performance and Speed of MySQL
On Sat, Nov 03, 2001 at 12:07:03AM +, Manjeet Singh wrote: > Hi Group, > > I am new to mysql, and would like you all to give some time in > resolving my trouble. I am stuck up with a problem of designing > stores database for a big company.The company is located in > different places and wants web-based insertion, retrival and > maintaining of data and records in the database. Okay... > I am worried of the performance and speed of quering the database > and getting displayed on the browser, as they have around 25 million > records in the database. 25 million records isn't a lot, given modern hardware. > I need to know: > 1) What is the maximum storage capacity of MySQL, It's really, really big. I don't remember the exact number anymore, but there has been at least once instance of a database with over 2 billion rows in it. > 2) How will be the performance for this type of huge > database, where web-based frontend is used and what can be > done so has to increase the performance. Make sure you give MySQL sufficient memory. Make sure you data is properly indexed. > 3) How to speed-up the querying time, as the queryed > results are manipluated through scripts and displayed on > web browser. Same as above. > 4) How can it be tuned for a better performance. Again, same as above. Have a read thru this: http://www.linux-mag.com/2001-06/mysql_01.html to start with. And watch for part #2 in the December issue. :-) > As, i am still in the planning stage with the details of my client > as above, i have to decide on with wich database i should proceed. > > I would also want to know will MySQL is capable of > withstanding the load: Yes. > 1) As so many users from different locations will be > querying and getting results on web. How many users at once? > 2) Even they are users who will be adding data to the > database. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 60 days, processed 1,316,159,175 queries (253/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Design,Performance and Speed of MySQL
Hi Group, I am new to mysql, and would like you all to give some time in resolving my trouble. I am stuck up with a problem of designing stores database for a big company.The company is located in different places and wants web-based insertion, retrival and maintaining of data and records in the database. I am worried of the performance and speed of quering the database and getting displayed on the browser, as they have around 25 million records in the database. I need to know: 1) What is the maximum storage capacity of MySQL, 2) How will be the performance for this type of huge database, where web-based frontend is used and what can be done so has to increase the performance. 3) How to speed-up the querying time, as the queryed results are manipluated through scripts and displayed on web browser. 4) How can it be tuned for a better performance. As, i am still in the planning stage with the details of my client as above, i have to decide on with wich database i should proceed. I would also want to know will MySQL is capable of withstanding the load: 1) As so many users from different locations will be querying and getting results on web. 2) Even they are users who will be adding data to the database. 3) Some scripts will be working in the background to generate reports automatically by querying again to the database. And, let me also know what will be the realistic approach to proceed forward. ___ *NEW* Yahoo! Messenger for SMS. Now on your Cellforce phone*NEW* Visit http://in.mobile.yahoo.com/smsmgr_signin.html - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Expected speed of MySQL on a 166
Hi. Do you have an index SIC? If so, I don't know any reason which would explain such a slow server for such a simple query (on a table with only 9.000 records), no matter what the configuration is. If SIC is one of those unidexed decimals, the speed may be expected, depending on the database size (in MB, not records), because the whole database file has to be scanned (and therefore to be loaded from disk). In this case - if indexing is not feasable - you may want to consider to split up the table into the small, heavily used and big, seldomly used columns into two tables and join them as required. This will reduce the amount of data read from disk. Bye, Benjamin. PS: Btw, 168 columns sound like a strange table design... On Fri, Jun 15, 2001 at 05:51:11PM -0600, [EMAIL PROTECTED] wrote: > I'm currently using a 166 UNIX machine for a dedicated MySQL database, > and I'm interested in knowing what kind of speed I should be expecting > from it. I ask because right now it's at a point of being unusable even > for development on some of the larger queries. > > I compiled MySQL 3.23.37 myself with -O6's -march-i586 and various > ./configure options I don't remeber. The database I'm using has 3 tables > of about 9 thousand entries, un-indexed decimal(5,8)'s, and a few > tinytext's. There are 168 columns. A query like 'SELECT 97TotalAssets, > 98TotalAssets FROM Business WHERE SIC='112'' will take a good 5 seconds. > Any more useful of a query will take up to 90 seconds. All the queries are > SELECT's. > > Is there something I can do, or am I going to need something faster than a > 166? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Expected speed of MySQL on a 166
On Fri, Jun 15, 2001 at 05:51:11PM -0600, Chris Cameron wrote: > > I compiled MySQL 3.23.37 myself with -O6's -march-i586 and various > ./configure options I don't remeber. The database I'm using has 3 > tables of about 9 thousand entries, un-indexed decimal(5,8)'s, and a > few tinytext's. There are 168 columns. A query like 'SELECT > 97TotalAssets, 98TotalAssets FROM Business WHERE SIC='112'' will > take a good 5 seconds. Any more useful of a query will take up to > 90 seconds. All the queries are SELECT's. Is the SIC column indexed? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 1 days, processed 9,816,515 queries (83/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Expected speed of MySQL on a 166
I'm currently using a 166 UNIX machine for a dedicated MySQL database, and I'm interested in knowing what kind of speed I should be expecting from it. I ask because right now it's at a point of being unusable even for development on some of the larger queries. I compiled MySQL 3.23.37 myself with -O6's -march-i586 and various ./configure options I don't remeber. The database I'm using has 3 tables of about 9 thousand entries, un-indexed decimal(5,8)'s, and a few tinytext's. There are 168 columns. A query like 'SELECT 97TotalAssets, 98TotalAssets FROM Business WHERE SIC='112'' will take a good 5 seconds. Any more useful of a query will take up to 90 seconds. All the queries are SELECT's. Is there something I can do, or am I going to need something faster than a 166? Thanks Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Speed of mysql
oh. no.i am not talking about of the speed of SELECT. my problem is the speed of INSERT and UPDATE. evenmore indexes only slow down the speend of insert,update. i ruduce the index to only i need. - What sort of queries are you doing on this large table? I notice you only have a couple of the fields indexed jason - Original Message - From: "chchen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, February 10, 2001 6:35 PM Subject: Speed of mysql hi,all i have a strange question. I use FreeBSD4.2-RELEASE + mysql-3.23.32 Dual PIII667+ 768MB RAM I have a table like this CREATE TABLE ABC ( A int(10) unsigned DEFAULT '0' NOT NULL, B int(10) unsigned DEFAULT '0' NOT NULL, C0 bigint(20) unsigned DEFAULT '0' NOT NULL, C1 bigint(20) unsigned DEFAULT '0' NOT NULL, C2 bigint(20) unsigned DEFAULT '0' NOT NULL, C3 bigint(20) unsigned DEFAULT '0' NOT NULL, C4 bigint(20) unsigned DEFAULT '0' NOT NULL, C5 bigint(20) unsigned DEFAULT '0' NOT NULL, C6 bigint(20) unsigned DEFAULT '0' NOT NULL, C7 bigint(20) unsigned DEFAULT '0' NOT NULL, Total bigint(20) unsigned DEFAULT '0' NOT NULL, A2 smallint(5) DEFAULT '0' NOT NULL, B2 smallint(5) DEFAULT '0' NOT NULL, PRIMARY KEY (A, B), KEY B (B), KEY B2 (B2) ); and i write a C program to insert data into this table. and my data have about 1M+ rows. i find when it insert data in mysql, it seems at a high speed. but after about 200K rows+, it slow down and down. actually i don;t know if it slow down. but use CPU usage of my C program and Mysqld from the begin is 9x% , and then.less and less after about 200K+. fiannly it usually use only 1x or even less only x%'s cpu usage. i tried to use memory disk to stored Table, it seems won't happen what i say before. so i think it is the I/O problem. soi think maybe is my table too big to search the data. soi separate my table into 256 tables. to reduce the size of table but... seems it doesn't useful to solve speed problem. and when it slow down(CPU useage reduce), i check all these 256 tables, no one is bigger than 100K rows. soanyone have good idea to solve this situation? please don't ask me to put it all into the memory. i tried before. but table is too big. i guess about 500MB for the MYD and MYI Regards chChen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Speed of mysql
What sort of queries are you doing on this large table? I notice you only have a couple of the fields indexed jason - Original Message - From: "chchen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, February 10, 2001 6:35 PM Subject: Speed of mysql hi,all i have a strange question. I use FreeBSD4.2-RELEASE + mysql-3.23.32 Dual PIII667+ 768MB RAM I have a table like this CREATE TABLE ABC ( A int(10) unsigned DEFAULT '0' NOT NULL, B int(10) unsigned DEFAULT '0' NOT NULL, C0 bigint(20) unsigned DEFAULT '0' NOT NULL, C1 bigint(20) unsigned DEFAULT '0' NOT NULL, C2 bigint(20) unsigned DEFAULT '0' NOT NULL, C3 bigint(20) unsigned DEFAULT '0' NOT NULL, C4 bigint(20) unsigned DEFAULT '0' NOT NULL, C5 bigint(20) unsigned DEFAULT '0' NOT NULL, C6 bigint(20) unsigned DEFAULT '0' NOT NULL, C7 bigint(20) unsigned DEFAULT '0' NOT NULL, Total bigint(20) unsigned DEFAULT '0' NOT NULL, A2 smallint(5) DEFAULT '0' NOT NULL, B2 smallint(5) DEFAULT '0' NOT NULL, PRIMARY KEY (A, B), KEY B (B), KEY B2 (B2) ); and i write a C program to insert data into this table. and my data have about 1M+ rows. i find when it insert data in mysql, it seems at a high speed. but after about 200K rows+, it slow down and down. actually i don;t know if it slow down. but use CPU usage of my C program and Mysqld from the begin is 9x% , and then.less and less after about 200K+. fiannly it usually use only 1x or even less only x%'s cpu usage. i tried to use memory disk to stored Table, it seems won't happen what i say before. so i think it is the I/O problem. soi think maybe is my table too big to search the data. soi separate my table into 256 tables. to reduce the size of table but... seems it doesn't useful to solve speed problem. and when it slow down(CPU useage reduce), i check all these 256 tables, no one is bigger than 100K rows. soanyone have good idea to solve this situation? please don't ask me to put it all into the memory. i tried before. but table is too big. i guess about 500MB for the MYD and MYI Regards chChen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Speed of mysql
hi,all i have a strange question. I use FreeBSD4.2-RELEASE + mysql-3.23.32 Dual PIII667+ 768MB RAM I have a table like this CREATE TABLE ABC ( A int(10) unsigned DEFAULT '0' NOT NULL, B int(10) unsigned DEFAULT '0' NOT NULL, C0 bigint(20) unsigned DEFAULT '0' NOT NULL, C1 bigint(20) unsigned DEFAULT '0' NOT NULL, C2 bigint(20) unsigned DEFAULT '0' NOT NULL, C3 bigint(20) unsigned DEFAULT '0' NOT NULL, C4 bigint(20) unsigned DEFAULT '0' NOT NULL, C5 bigint(20) unsigned DEFAULT '0' NOT NULL, C6 bigint(20) unsigned DEFAULT '0' NOT NULL, C7 bigint(20) unsigned DEFAULT '0' NOT NULL, Total bigint(20) unsigned DEFAULT '0' NOT NULL, A2 smallint(5) DEFAULT '0' NOT NULL, B2 smallint(5) DEFAULT '0' NOT NULL, PRIMARY KEY (A, B), KEY B (B), KEY B2 (B2) ); and i write a C program to insert data into this table. and my data have about 1M+ rows. i find when it insert data in mysql, it seems at a high speed. but after about 200K rows+, it slow down and down. actually i don;t know if it slow down. but use CPU usage of my C program and Mysqld from the begin is 9x% , and then.less and less after about 200K+. fiannly it usually use only 1x or even less only x%'s cpu usage. i tried to use memory disk to stored Table, it seems won't happen what i say before. so i think it is the I/O problem. soi think maybe is my table too big to search the data. soi separate my table into 256 tables. to reduce the size of table but... seems it doesn't useful to solve speed problem. and when it slow down(CPU useage reduce), i check all these 256 tables, no one is bigger than 100K rows. soanyone have good idea to solve this situation? please don't ask me to put it all into the memory. i tried before. but table is too big. i guess about 500MB for the MYD and MYI Regards chChen
