Re: mysql 4.1.8 client installation problem
Hello. We want only mysql 4.1.8 client version RPMs with 4.1.8 client programs for Linux on x86, IA64, AMD64 are aviable from: http://downloads.mysql.com/archives.php?p=mysql-4.1v=4.1.8 But MySQL Server 4.1.9 would be a better choice. sirisha gnvg [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: iso-8859-1, 6 lines --] We want only mysql 4.1.8 client version .How to get only client installed without server being installed? Thanking you, yours sincerely, sirisha. Yahoo! India Matrimony: Find your life partneronline. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load balacing in a replicated environment
Hello. You may tell the slave to replicate updates only for the certain database or table. See: http://dev.mysql.com/doc/mysql/en/Replication_Options.html Eben Goodman [EMAIL PROTECTED] wrote: I am considering setting up replication for a loaded database that has to perform thousands of heavy selects every day. I want to have a master with one way replication to multiple slaves. But I also want to be able to load balance connections across the multiple slaves. What options exist to enable a web server (apache) and or a web application (php/perl) to do load balancing so not all queries are sent to the same slave server, but distributed across all available slaves? One quick and dirty idea would be to have a script that manages the database connection layer, and based on time of day or something passes a different server address through... but there must be sexier ways to handle this sort of thing? any advice is appreciated. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql store queries?
Hello. Does mysql stores queries? If so where? MySQL server can log the queries in log files. See: http://dev.mysql.com/doc/mysql/en/Log_Files.html Jerry Swanson [EMAIL PROTECTED] wrote: Does mysql stores queries? If so where? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql client installation
hai, Give sequence of steps to install mysql client 4.1.8 alone in windows xp? thanks in advance.. Yahoo! India Matrimony: Find your life partneronline.
force configure to not use -lcrypt
On Solaris 8, how can I force configure to not use /usr/lib/libcrypt.so without renaming that file? I changed config.h to undefine HAVE_LIBCRYPT, but configure just puts it back to defined and changes the Makefiles to include -lcrypt. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INNER JOIN across multiple tables appear very slow.
Hi, Can anyone tell me how to optimize the following sql statement? $sql_1 = SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume),2) 'Sales Quantity', ROUND(avg(t.netsales),2) 'PastSales', ROUND(avg(tt.netsales),2) 'Sales', Round(((avg(t.netsales) - avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff', Round(((avg(t.salesvolume) - avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff' from customer c INNER JOIN transaction t ON c.custcode = t.custcode AND date(t.date) = '$past_date_period_startdate' AND date(t.date) = '$past_date_period_enddate' AND c.salescode = '$salescode_param' INNER JOIN transaction tt ON c.custcode = tt.custcode AND date(tt.date) = '$input_date_period_startdate' AND date(tt.date) = '$input_date_period_enddate' AND c.salescode = '$salescode_param' AND t.prodcode = tt.prodcode INNER JOIN inventory i ON i.prodcode = t.prodcode AND i.prodcode = tt.prodcode AND i.basename = '$basename_param' WHERE i.prodname is not NULL and i.basename is not NULL GROUP BY c.salescode, i.basename, t.prodcode ORDER BY SalesDiff desc; Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNER JOIN across multiple tables appear very slow.
sam wun wrote: Hi, Can anyone tell me how to optimize the following sql statement? $sql_1 = SELECT t.prodcode 'Product Code', t.prodname 'Product Name', ROUND(avg(t.salesvolume),2) 'PastSales Quantity', ROUND(avg(tt.salesvolume),2) 'Sales Quantity', ROUND(avg(t.netsales),2) 'PastSales', ROUND(avg(tt.netsales),2) 'Sales', Round(((avg(t.netsales) - avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff', Round(((avg(t.salesvolume) - avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff' from customer c INNER JOIN transaction t ON c.custcode = t.custcode AND date(t.date) = '$past_date_period_startdate' AND date(t.date) = '$past_date_period_enddate' AND c.salescode = '$salescode_param' INNER JOIN transaction tt ON c.custcode = tt.custcode AND date(tt.date) = '$input_date_period_startdate' AND date(tt.date) = '$input_date_period_enddate' AND c.salescode = '$salescode_param' AND t.prodcode = tt.prodcode INNER JOIN inventory i ON i.prodcode = t.prodcode AND i.prodcode = tt.prodcode AND i.basename = '$basename_param' WHERE i.prodname is not NULL and i.basename is not NULL GROUP BY c.salescode, i.basename, t.prodcode ORDER BY SalesDiff desc; Thanks Sam Hi, here is the result from the explain command on the query: mysql explain SELECT i.basename, c.salescode, - avg(t.salesvolume) 'PastSales Quantity', - avg(tt.salesvolume) 'Sales Quantity', - avg(t.netsales) PastSales, - avg(tt.netsales) Sales, - Round(((avg(t.netsales) - avg(tt.netsales))/avg(tt.netsales))*100,2) 'SalesDiff', - Round(((avg(t.salesvolume) - avg(tt.salesvolume))/avg(tt.salesvolume))*100,2) 'QtyDiff', c.salescode - from customer c - INNER JOIN transaction t - ON c.custcode = t.custcode - AND date(t.date) = '2003-01-22' - AND date(t.date) = '2004-01-22' - INNER JOIN transaction tt - ON c.custcode = tt.custcode - AND date(tt.date) = '2004-01-22' - AND date(tt.date) = '2005-01-22' - INNER JOIN inventory i - ON i.prodcode = t.prodcode - AND i.prodcode = tt.prodcode - WHERE i.prodname is not NULL - and i.basename is not NULL - group by c.salescode,i.basename - order by c.salescode, 'SalesDiff' desc ; ++-+---++---+--+-+-+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++---+--+-+-+--+--+ | 1 | SIMPLE | i | ALL| PRIMARY,basename | NULL | NULL| NULL| 2491 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t | ref| custcode,prodcode | prodcode | 32 | datacube.i.prodcode | 30 | Using where | | 1 | SIMPLE | c | eq_ref | PRIMARY,custcode | PRIMARY | 32 | datacube.t.custcode |1 | | | 1 | SIMPLE | tt| ref| custcode,prodcode | prodcode | 32 | datacube.i.prodcode | 30 | Using where | ++-+---++---+--+-+-+--+--+ 4 rows in set (0.00 sec) sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
accent, quote and fulltext search
Hello,
It seems it is possible to get ride of accent problems in fulltext
search (but I don't know how to). I am using 4.1.8a version of mysql and
I am not enable to find any accentuated word from its non-accent version
(says 'siecle' for 'siècle'). For example :
SELECT * FROM table WHERE MATCH (myfields) AGAINST ('siecle')
returns nothing but
SELECT * FROM table WHERE MATCH (myfields) AGAINST ('siècle')
finds many rows ...
I have got sames problems with single quote words : I look for
influence which can be found in l'influence. The only way to find
l'influence is searching for it exact syntax. Also a search with
'influence' doesn't return l'influence occurences. It seems there is a
way to get ride of ' character (quote from
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html) :
MySQL uses a very simple parser to split text into words. A
``word'' is any sequence of true word characters (letters, digits, and
underscores), optionally separated by no more than one sequential `''
character.
It says optionnally ... does it mean it is an option ? (so what is
this option?) or does it only mean the parser is enable to parse
wasn't as a single word ?
Thanks !!
Regards,
BDM.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Collation
I just upgraded from 4.0.8 to 4.1.9. On MySql 4.1.9 my default collation seems to be latin1_swedish_ci. I need to support french accents. Right now, not sure if it's MySQL or PHP. But all accented caracters in the database are coming out wrong. I'm thinking I should just use utf8_general_ci but have no idea what the implications are. Also I have no idea why latin1_swedish_ci.was chosen as my default. Does anyone have a good link of where I can read up more on choosing a collation? How to create the default? Or if you have a quick amswer for me that would be great. Right now almost every 3rd word displays wrong. Thanks, Mike
Re: MySQL Losing database information
Christopher, InnoDB tables are in ibdata files. I hope that you have not deleted them inadvertently. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, January 20, 2005 5:28 AM Subject: RE: MySQL Losing database information Where would I find the tablename.InnoDB files ? As in the error below that I receive when check table is run against the table: mysql check table ACL; +-+---+--+---= + | Table | Op| Msg_type | Msg_text = | +-+---+--+---= + | rt3.ACL | check | error| Can't open file: 'ACL.InnoDB'. (errno: 1) = | +-+---+--+---= + 1 row in set (0.01 sec) Chris Hood=A0 Investigator Verizon Global Security Operations Center=20 Email:[EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary =09 -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 8:05 AM To: Christopher L. Hood Cc: mysql@lists.mysql.com Subject: Re: MySQL Losing database information [EMAIL PROTECTED] wrote: ALL, =20 I have an issue where MySQL has lost all of the table information for = an existing database, I do not know of anything that has changed and there was no maintenance being done in MySQL. Below you will find the error message as I receive it from MySQL. =20 ANY ideas or suggestions on how to recover this database intact will be GREATLY appreciated. The .FRM files are intact and all permissions have been checked against a database that I CAN still access, which = tells me that the problem is the database somehow and not MySQL as a whole. =20 Try check table and repair table. =20 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Perl
Hi! I am totally lost here and I know this is a no brinier. I do not know what I am doing wrong: I can access from the command line by mysql -u gjw -p then use club and I can do anything I want. I am trying to access by Perl: # line 6 following my $dbh=DBI-connect( 'DBI:mysql:gjw:club', 'gjw', 'password' ) or die $DBI::errstr\n; I get the following error: DBI connect'gjw:club','gjw'... failed: Unknown MySQL server Host 'club' 11001 at line 6; How do I find out my server HOST name is? What else is wrong? I have done this many times working with Oracle and never had this problem! Thanks, Jerry -Original Message- From: Gerald Preston [mailto:[EMAIL PROTECTED] Sent: Friday, January 14, 2005 12:56 AM To: mysql@lists.mysql.com Subject: MySQL Perl I am trying to get Perl to talk to MySQL that I have setup. According to WinMySQLAdmin1.4 Local Host Name = GJW Local User name = Jerry Databases: GJW Club bar mysql test I am using the following code: #!/perl use warnings; use strict; use DBI; my $dbh=DBI-connect( 'DBI:mysql:GJW:club:bar', 'gjw', 'password' ) or die Cannot connect - gjw!!br$DBI::errstr; What am I missing? Thanks, Jerry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: isam to myisam
According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; Both of these led to a similar error: Can't find editors.MYI. So I guess I need to change the subject to: HELP! I can't access my old tables! or Didn't find any fields in table. or something. Any ideas? Anyone: I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the files, but not the old server that ran the old version of mysql. I copied them into /usr/local/mysql/data/BagusDatabase I made sure the permissions were the same as my test directory that was created on install... ie mysql owns everything. When entering the command mysql use BagusDatabase; mysql reports that: Didn't find any fields in table 'editors' How can I get my old tables into my new database? Thanks, Bagus -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:58 PM To: Bagus; mysql@lists.mysql.com Subject: Re: isam to myisam At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: isam to myisam
At 19:41 -0600 1/22/05, Bagus wrote: According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; Both of these led to a similar error: Can't find editors.MYI. Hmm. Perhaps your server doesn't have support for ISAM compiled in. What does the output of SHOW ENGINES indicate about the ISAM storage engine? If it says NO, it won't be able to read ISAM tables at all. You'll need to find another server that does support ISAM. So I guess I need to change the subject to: HELP! I can't access my old tables! or Didn't find any fields in table. or something. Any ideas? Anyone: I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the files, but not the old server that ran the old version of mysql. I copied them into /usr/local/mysql/data/BagusDatabase I made sure the permissions were the same as my test directory that was created on install... ie mysql owns everything. When entering the command mysql use BagusDatabase; mysql reports that: Didn't find any fields in table 'editors' How can I get my old tables into my new database? Thanks, Bagus -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:58 PM To: Bagus; mysql@lists.mysql.com Subject: Re: isam to myisam At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Perl
At 13:28 -0800 1/22/05, Gerald Preston wrote: Hi! I am totally lost here and I know this is a no brinier. I do not know what I am doing wrong: I can access from the command line by mysql -u gjw -p then use club and I can do anything I want. I am trying to access by Perl: # line 6 following my $dbh=DBI-connect( 'DBI:mysql:gjw:club', 'gjw', 'password' ) or die $DBI::errstr\n; For MySQL, the format for the DSN is: DBI:mysql:db_name:host_name Looks like you have a username (gjw) for db_name, and a database name (club) for host_name? Perhaps you want: DBI:mysql:club:localhost I get the following error: DBI connect'gjw:club','gjw'... failed: Unknown MySQL server Host 'club' 11001 at line 6; How do I find out my server HOST name is? What else is wrong? I have done this many times working with Oracle and never had this problem! Thanks, Jerry -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error with Storage engine
Hi, I was trying to optimize a sql (for INNER JOIN) by spliting the sql into 2 temporary tables. But I got error with the following sql: mysql CREATE TEMPORARY TABLE pastsales_tab_3141604663377652915968 SELECT c.salescode,c.type, c.custcode, i.basename, i.vendorname, t.salesvolume, t.netsales, t.prodcode, i.prodname from inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND date(t.date) = '2003-01-23' AND date(t.date) = '2004-01-23' INNER JOIN customer c ON c.custcode = t.custcode ; ERROR 1030 (HY000): Got error 28 from storage engine How can I correct this error? I musing mysql5.01 Thanks Sam. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection performance, suggestions?
Actually I was just trying to see why the same code to mySql is so much slower than SQL Server. I tried the same code using the data provider from CoreLabs and found the connections occur 4 times faster than the mySQL AB connector. The slowness just seems to be in the connector. Now I know my method seems silly but to my boss it makes sense. We have web sites that make these connections. They are obviously stateless and each makes it's own connection. Where it takes 22 seconds to connect to mySql 100 times I can in the same loop execute a select statement and bring back a 700k binary field adding only abour .5 seconds to the whole loop time. Half a second to dig into a database with 400k records and yank the record 100 times but 22seconds just to open and close the conection. Something just does not seem right. Thanks for your input. Larry Lowry - Original Message - From: Peter J Milanese [EMAIL PROTECTED] To: Larry Lowry [EMAIL PROTECTED] Sent: Saturday, January 22, 2005 10:22 AM Subject: Re: Connection performance, suggestions? You are not simulating. Your scripting produces 100 linear connections. You are attempting, by theory, to simulate simultaneous connections. Big difference. - Sent from my NYPL BlackBerry Handheld. - Original Message - From: Larry Lowry [EMAIL PROTECTED] Sent: 01/22/2005 10:36 AM To: Peter J Milanese [EMAIL PROTECTED] Subject: Re: Connection performance, suggestions? - Original Message - From: Peter J Milanese [EMAIL PROTECTED] To: Larry Lowry [EMAIL PROTECTED]; mysql mysql@lists.mysql.com Sent: Friday, January 21, 2005 12:57 PM Subject: Re: Connection performance, suggestions? A single transaction logs into the db several times? Not really. I am simulating the connection process of a bunch of browser based/web services transactions. I assume its a browser based transaction, no? Correct. Are you limiting connections (in my.conf)? Have you tuned the config, if yes how so? Here is my My.INI. [mysqld] basedir=C:/MySql datadir=D:/MySqlData language=C:/MySql/share/english port=3306 key_buffer_size=512M table_cache=64 net_buffer_length=1M max_allowed_packet=3M query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 read_buffer_size=2M read_rnd_buffer_size=8M skip-innodb Larry --Original Message-- From: Larry Lowry To: mysql Sent: Jan 21, 2005 12:49 PM Subject: Connection performance, suggestions? Before I ask this question I must state I love mySql and want to use it more. But I work in a shop were we mostly use SQL Server 2000. In trying to use mySql (4.12 and 4.19) we are seeing some performance issues. I hope someone can help me with this. I am in a Windows 2003 server environment and running mostly asp.net applications. I have narrowed the problem down to the speed at which the database connections open and close. The following code opens a mySql database 100 times. This takes 21 to 23 seconds. I know I should only open the connection once but this represents the asp environment where we are using a cluster of web servers. MySQL is running on an Athlon 64 3500+ with 2 gigs of memory. It is the only process on the server. The SQL server code below does the same thing except to Sql Server 2000. This only takes .1 to .4 seconds. Sql Server is running on a PIII at 1.2ghz with 1gb memory. It's a crappy old box for testing. I am using the stock Framework 1.1 SqlClient and MySql.Data.MySqlClient version 1.0.3.31712. All test boxes are on the same network switch. Can anyone help explain this difference? Is it the database engines or the data connectors? Any help would be appreciated. Thanks Larry Lowry 'MySql Code Dim sDBCS As String = Server=DB;UserId=userid;Password=pass;Database=images Dim i As Long Dim ti As Long = Microsoft.VisualBasic.Timer() Dim db As MySqlConnection db = New MySqlConnection(sDBCS) For i = 1 To 100 db.Open() db.Close() Next db.Dispose() tbEnd.Text = Microsoft.VisualBasic.Timer() - ti 'SQL Server Dim sDBCS As String = Data Source=db;User Id=userid;Password=pass;Initial Catalog=images Dim i As Long Dim ti As Long = Microsoft.VisualBasic.Timer() Dim db As SqlConnection db = New SqlConnection(sDBCS) For i = 1 To 100 db.Open() db.Close() Next db.Dispose() tbEnd.Text = Microsoft.VisualBasic.Timer() - ti - Sent from my NYPL BlackBerry Handheld. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Connection performance, suggestions?
Attempt to connect to mysql via ip address, and make sure on the mysql box that that you add the connecting boxes to your hosts file. Your problem should go away then. Donny -Original Message- From: Larry Lowry [mailto:[EMAIL PROTECTED] Sent: Saturday, January 22, 2005 9:25 PM To: Peter J Milanese; mysql@lists.mysql.com Subject: Re: Connection performance, suggestions? Actually I was just trying to see why the same code to mySql is so much slower than SQL Server. I tried the same code using the data provider from CoreLabs and found the connections occur 4 times faster than the mySQL AB connector. The slowness just seems to be in the connector. Now I know my method seems silly but to my boss it makes sense. We have web sites that make these connections. They are obviously stateless and each makes it's own connection. Where it takes 22 seconds to connect to mySql 100 times I can in the same loop execute a select statement and bring back a 700k binary field adding only abour .5 seconds to the whole loop time. Half a second to dig into a database with 400k records and yank the record 100 times but 22seconds just to open and close the conection. Something just does not seem right. Thanks for your input. Larry Lowry - Original Message - From: Peter J Milanese [EMAIL PROTECTED] To: Larry Lowry [EMAIL PROTECTED] Sent: Saturday, January 22, 2005 10:22 AM Subject: Re: Connection performance, suggestions? You are not simulating. Your scripting produces 100 linear connections. You are attempting, by theory, to simulate simultaneous connections. Big difference. - Sent from my NYPL BlackBerry Handheld. - Original Message - From: Larry Lowry [EMAIL PROTECTED] Sent: 01/22/2005 10:36 AM To: Peter J Milanese [EMAIL PROTECTED] Subject: Re: Connection performance, suggestions? - Original Message - From: Peter J Milanese [EMAIL PROTECTED] To: Larry Lowry [EMAIL PROTECTED]; mysql mysql@lists.mysql.com Sent: Friday, January 21, 2005 12:57 PM Subject: Re: Connection performance, suggestions? A single transaction logs into the db several times? Not really. I am simulating the connection process of a bunch of browser based/web services transactions. I assume its a browser based transaction, no? Correct. Are you limiting connections (in my.conf)? Have you tuned the config, if yes how so? Here is my My.INI. [mysqld] basedir=C:/MySql datadir=D:/MySqlData language=C:/MySql/share/english port=3306 key_buffer_size=512M table_cache=64 net_buffer_length=1M max_allowed_packet=3M query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 read_buffer_size=2M read_rnd_buffer_size=8M skip-innodb Larry --Original Message-- From: Larry Lowry To: mysql Sent: Jan 21, 2005 12:49 PM Subject: Connection performance, suggestions? Before I ask this question I must state I love mySql and want to use it more. But I work in a shop were we mostly use SQL Server 2000. In trying to use mySql (4.12 and 4.19) we are seeing some performance issues. I hope someone can help me with this. I am in a Windows 2003 server environment and running mostly asp.net applications. I have narrowed the problem down to the speed at which the database connections open and close. The following code opens a mySql database 100 times. This takes 21 to 23 seconds. I know I should only open the connection once but this represents the asp environment where we are using a cluster of web servers. MySQL is running on an Athlon 64 3500+ with 2 gigs of memory. It is the only process on the server. The SQL server code below does the same thing except to Sql Server 2000. This only takes .1 to .4 seconds. Sql Server is running on a PIII at 1.2ghz with 1gb memory. It's a crappy old box for testing. I am using the stock Framework 1.1 SqlClient and MySql.Data.MySqlClient version 1.0.3.31712. All test boxes are on the same network switch. Can anyone help explain this difference? Is it the database engines or the data connectors? Any help would be appreciated. Thanks Larry Lowry 'MySql Code Dim sDBCS As String = Server=DB;UserId=userid;Password=pass;Database=images Dim i As Long Dim ti As Long = Microsoft.VisualBasic.Timer() Dim db As MySqlConnection db = New MySqlConnection(sDBCS) For i = 1 To 100 db.Open() db.Close() Next db.Dispose() tbEnd.Text = Microsoft.VisualBasic.Timer() - ti 'SQL Server Dim sDBCS As String = Data Source=db;User Id=userid;Password=pass;Initial Catalog=images Dim i As Long Dim ti As Long = Microsoft.VisualBasic.Timer() Dim db As SqlConnection db = New SqlConnection(sDBCS) For i = 1 To 100 db.Open() db.Close() Next db.Dispose() tbEnd.Text = Microsoft.VisualBasic.Timer() - ti - Sent from my NYPL BlackBerry Handheld. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: isam to myisam
SHOW ENGINES says no support for ISAM. So that explains it. Which other server would you suggest I try installing? Do you mean I should try installing an earlier version of mysql? Where should I find one? I'm worried about messing up my new installation by installing an old one. Is there an alternative? Thanks, Bagus (ugh) -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Saturday, January 22, 2005 7:46 PM To: Bagus; mysql@lists.mysql.com Subject: RE: isam to myisam At 19:41 -0600 1/22/05, Bagus wrote: According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; Both of these led to a similar error: Can't find editors.MYI. Hmm. Perhaps your server doesn't have support for ISAM compiled in. What does the output of SHOW ENGINES indicate about the ISAM storage engine? If it says NO, it won't be able to read ISAM tables at all. You'll need to find another server that does support ISAM. So I guess I need to change the subject to: HELP! I can't access my old tables! or Didn't find any fields in table. or something. Any ideas? Anyone: I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the files, but not the old server that ran the old version of mysql. I copied them into /usr/local/mysql/data/BagusDatabase I made sure the permissions were the same as my test directory that was created on install... ie mysql owns everything. When entering the command mysql use BagusDatabase; mysql reports that: Didn't find any fields in table 'editors' How can I get my old tables into my new database? Thanks, Bagus -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 10:58 PM To: Bagus; mysql@lists.mysql.com Subject: Re: isam to myisam At 22:46 -0600 1/20/05, Bagus wrote: Hi there, I'm running Freebsd 5.3 and just installed mysql-standard-4.1.9-unknown-freebsd4.7-i386. I'm able to run mysqld and mysql, etc. I used to run Freebsd 2.2 and I think it was mysql 3.21.33b... I'm not sure. My old system faded away rather abruptly and I didn't get a chance to do database backups or anything. I have one database that I'd like to recover. The database had 5 tables in it. Each one had three files associated with it, ie editors.ISD, editors.ISM and editors.frm. I have access to the tables but can't load them into my new mysql. I copied them into /usr/local/mysql/data/BagusDatabase and made sure the permissions were the same as my test directory. I have been reading around and thought I came across the answer with the mysql_convert_table_format script. I tried changing into my new BagusDatabase directory and running: mysql_convert_table_format --user='root' --password='mypassword' --type='ISA M' BagusDatabase According to the help message, the --type option is for specifying the table type that you want to convert the tables *to*. So it should be --type=MyISAM, I think. You could also execute this statement for each table that you want to convert: ALTER TABLE tbl_name ENGINE=MyISAM; The script then reports: Converting tables: converting editors Can't convert editors: Error Can't find file: 'editors.MYI' (errno: 2) I bet this is old hat for some of you out there. Can someone help me figure out how to recreate my database? Thanks, Bagus -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
system requirements for large selects
We are looking for buying a new server for running MySQL database. The database is around 50-70G and individual tables run to 5 - 15 GB. There wont be any frequent updates instead we need maximum select performance. There will be multiple table joins to perform our query. I hope your experience will help us find a suitable server for our need. Information on what hardware to use including how many computers, processor, ram, hard drive spec would be helpful. Budget is not a constrain but performance (select) need to be high. I have heard MySQL is the best for fast and large selects, but is it worth looking at other database like Oracle? Thanks TM __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
library mamager for linux
Hi list, can any one suggest a good PHP/mysql based library manager to manage books,magazines, documents, cds etc . I tried libman .. but i am having problem with that . many thanks in advance ... with love gowtham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto-increment question
I have two different databases they both have an auto-increment PK field and while they are different databases with different names, they do have tables with the same names. What I find is that the auto-crement integer number remembers what it is across these databases. So if I insert in the 1st DB and the number is 24, then I insert in the 2nd DB the number will be 25. I would of thought that the number would be fresh and independent between the DBs. Could you explain? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show temporary table
Hi, How can I see all temporary tables that created by Create Temporary Table command in mysql 5.01? thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
