RE: Search Engine type search
I'm using ASP (VB Script) at the moment for my webpages. From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: Search Engine type search Date: Wed, 23 Aug 2006 16:36:19 -0400 That's a tough one. Basically, you need a spell checker plug-in for your application, I don't think there's much hope of doing this in MySQL alone. You might find a spell checker that uses MySQL for a back end, of course, but I doubt it. Doing a search on Google, I found some open-source spell checkers (http://www.thefreecountry.com/sourcecode/spellcheckers.shtml, et al). I don't know what language you are using, so I can't go further than that. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Neil Tompkins [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 4:02 PM To: mysql@lists.mysql.com Subject: RE: Search Engine type search OK, I appear to be getting somewhere with the FULL TEXT search. Does anyone have any good resources about producing search engine type results ? for example if some enters a search phrase like londn how would I suggest the word london ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
RE: SOS
Hi Lian, I am going to take a guess that your full execution line looks as follows: $mysql -u root -h localhost -p yourpwd If this is true then MySQL will give you prompt: $Enter password: yourpwd And the mysql will return $Error 1049(42000) Unkown database 'yourpwd' This happens because, while one can type either -u root or -uroot in case of password it must be -pyourpwd no spaces between the switch -p and your password. Regards, Mikhail Berman -Original Message- From: 李彦 [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 5:29 AM To: mysql Subject: SOS Dear Sir: I have some problems with mysql 5.0 binary source in Linux(RedHat).I'm able to startup the mysql process. But when i type in :mysql -u root -h localhost -p, and then put the correct password, I can not enter the database. I took almost one week to deal with this issue. But Can you help me? May be the mysql version problem? 致 礼！ 李彦 [EMAIL PROTECTED] 2006-08-29 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table sizes, rolling data.
You could accomplish this with a trigger on the table - on INSERT, execute a DELETE statement. http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html You would need to find a way to identify which is the bottom record to be deleted ... I might use an ID column, and consider the lowest-numbered row as the bottom one. You might also need to include a condition so that it does not start deleting records until you've reached the appropriate number of records in the table, or if your database is slow-moving, you could simply create the trigger once you have the appropriate number. HTH, Dan On 8/29/06, Tanner Postert [EMAIL PROTECTED] wrote: sorry for the double post. if i want to have a row of about 100 records. and everytime i insert a new record, it gets pushed on the top, and the bottom one gets pushed out, sort of like a heap. is this possible? i know i can just delete the record, etc, but i was wondering if there was a built in way of doing it. T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table permissions - before the table is created?
Can the mysql administrator grant permissions on a table in an existing database if that table doesn't exist yet? This is part of planning for an upgrade to an existing application. The mysql administrator won't be there when I add the table to the existing database and I don't have grant privileges. Chris Jones, P.Eng. 14 Oneida Avenue Toronto, ON M5J2E3 Tel. 416 203-7465 Fax. 416 946-1005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table permissions - before the table is created?
Chris Jones wrote: Can the mysql administrator grant permissions on a table in an existing database if that table doesn't exist yet? This is part of planning for an upgrade to an existing application. The mysql administrator won't be there when I add the table to the existing database and I don't have grant privileges. You can't do this using a grant statement because it is bound by existing tables. You may be able to manipulate the mysql db tables directly - I was able to insert a row in mysql.tables_priv for a nonexistent table - but I don't know how this will play out once the table actually is created. Perhaps give it a try on a test db...? David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Degrading write performance using MySQL 5.0.24
On Wed, 2006-08-30 at 08:31 -0700, Phantom wrote: We have an application that stores versioned data in MySQL. Everytime a piece of data is retrieved and written to, it is stored in the database with a new version and all old versions are subsequently deleted. We have a request rate of 2 million reads per hour and 1.25 million per hour. What I am seeing is that as the DB grows the performance on the writes degrades substantially. When I start with a fresh database writes are at 70ms. But once the database reaches around 10GB the writes are at 200 ms. The DB can grow upto 35GB. I have tried almost performance related tuning described in the MySQL documentation page. What do I need to look at to start addressing this problem or this is how the performance is going to be ? Before getting into server parameters, is it possible to take a look at your schema and a sample of your SQL queries from the application? That would help immensely. 70ms for an UPDATE seems very slow... and 200ms is very slow. Cheers, -- Jay Pipes Community Relations Manager, North America, MySQL, Inc. [EMAIL PROTECTED] :: +1 614 406 1267 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table permissions - before the table is created?
Thanks for that. Will create the two tables ahead of time which won't affect the existing application. At 11:57 30/08/2006, [EMAIL PROTECTED] wrote: Chris Jones wrote: Can the mysql administrator grant permissions on a table in an existing database if that table doesn't exist yet? This is part of planning for an upgrade to an existing application. The mysql administrator won't be there when I add the table to the existing database and I don't have grant privileges. You can't do this using a grant statement because it is bound by existing tables. You may be able to manipulate the mysql db tables directly - I was able to insert a row in mysql.tables_priv for a nonexistent table - but I don't know how this will play out once the table actually is created. Perhaps give it a try on a test db...? David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Chris Jones, P.Eng. 14 Oneida Avenue Toronto, ON M5J2E3 Tel. 416 203-7465 Fax. 416 946-1005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Degrading write performance using MySQL 5.0.24
I see the same type of slow downs using 5.0.18 I am using load data in file to load CSV files. with clean tables, I see fairly quick inserts (ie instant) 2006-08-30 12:07:15 : begin import into table1 2006-08-30 12:07:15: end import into table1 records (10962) From earlier this morning, before I rotated my tables: 2006-08-30 09:02:01 : begin import into table1 2006-08-30 09:05:07: end import into table1 records (10082) I've posted about this before - one person will say that its my indexes getting rebuilt, others have said its disk io. I can never get a solid answer. If I disable the keys, do the import, then re-enable the keys, it takes just as long, if not longer. I have just about given up on finding a solution for this and just rotate my tables out regularly once the imports take over 5 minutes to process roughly 10,000 records -- George -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:06 PM To: Phantom Cc: mysql@lists.mysql.com Subject: Re: Degrading write performance using MySQL 5.0.24 On Wed, 2006-08-30 at 08:31 -0700, Phantom wrote: We have an application that stores versioned data in MySQL. Everytime a piece of data is retrieved and written to, it is stored in the database with a new version and all old versions are subsequently deleted. We have a request rate of 2 million reads per hour and 1.25 million per hour. What I am seeing is that as the DB grows the performance on the writes degrades substantially. When I start with a fresh database writes are at 70ms. But once the database reaches around 10GB the writes are at 200 ms. The DB can grow upto 35GB. I have tried almost performance related tuning described in the MySQL documentation page. What do I need to look at to start addressing this problem or this is how the performance is going to be ? Before getting into server parameters, is it possible to take a look at your schema and a sample of your SQL queries from the application? That would help immensely. 70ms for an UPDATE seems very slow... and 200ms is very slow. Cheers, -- Jay Pipes Community Relations Manager, North America, MySQL, Inc. [EMAIL PROTECTED] :: +1 614 406 1267 -- 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]
query cache question
Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System
Re: Table permissions - before the table is created?
If you are manually editing the grant tables, don't forget to FLUSH PRIVILEDGES after you add the new tables. Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Chris Jones wrote: Thanks for that. Will create the two tables ahead of time which won't affect the existing application. At 11:57 30/08/2006, [EMAIL PROTECTED] wrote: Chris Jones wrote: Can the mysql administrator grant permissions on a table in an existing database if that table doesn't exist yet? This is part of planning for an upgrade to an existing application. The mysql administrator won't be there when I add the table to the existing database and I don't have grant privileges. You can't do this using a grant statement because it is bound by existing tables. You may be able to manipulate the mysql db tables directly - I was able to insert a row in mysql.tables_priv for a nonexistent table - but I don't know how this will play out once the table actually is created. Perhaps give it a try on a test db...? David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Chris Jones, P.Eng. 14 Oneida Avenue Toronto, ON M5J2E3 Tel. 416 203-7465 Fax. 416 946-1005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query cache question
Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Degrading write performance using MySQL 5.0.24
On Wed, 2006-08-30 at 09:34 -0700, Phantom wrote: Here is the schema for the table are generate Sequence Numbers needed per item and the table that actually stores the data : CREATE TABLE SEQUENCE_NUMBER ( USER_KEY VARBINARY(255) NOT NULL, SUBKEY VARBINARY(255)NOT NULL, SEQUENCE_NBR INT NOT NULL, GLOBAL_GENERATION INT NOT NULL, KEY_GENERATION INT NOT NULL, LAST_ACCESS_TIME BIGINT NOT NULL, PRIMARY KEY (USER_KEY, SUBKEY) )TYPE=InnoDB; You have a serious problem with your data types here... Using a VARBINARY(255), VARBINARY(255) as your primary key is not a good idea. What is the purpose of the sequence table? Why not simply use an auto_incrementing integer? Additionally, why are you using a BIGINT for an access time? Why not just use the (much smaller) TIMESTAMP datatype? CREATE TABLE ITEMS ( USER_KEY VARBINARY(255) NOT NULL, SUBKEY VARBINARY(255) NOT NULL, VERSION_STAMP_HASH VARBINARY(255) NOT NULL, VERSION_STAMP LONGBLOB NOT NULL, USER_DATA LONGBLOB NOT NULL, LAST_ACCESS_TIME BIGINT NOT NULL, TYPE VARCHAR(255) NOT NULL, DATA_HASH VARBINARY(255) NOT NULL, HINTED_STORAGE_ID VARBINARY(255), USER_KEY_HASH VARBINARY(255) NOT NULL, ROWID BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (USER_KEY, SUBKEY, VERSION_STAMP_HASH), INDEX (ROWID), INDEX (HINTED_STORAGE_ID), INDEX (USER_KEY_HASH), INDEX (LAST_ACCESS_TIME,TYPE), INDEX (DATA_HASH) ) TYPE=InnoDB; Same comment from above applies here, but it's even worse here because *every one of the five secondary indexes* on the table will have a 512 byte primary key value appended to *each index record*, resulting in horrible performance problems. Again, what is the purpose of the sequence numbers as the primary key? Sample queries are : SELECT GLOBAL_GENERATION, KEY_GENERATION, SEQUENCE_NBR, LAST_ACCESS_TIME FROM SEQUENCE_NUMBER Where USER_KEY = ? AND SUBKEY = ?; UPDATE SEQUENCE_NUMBER SET KEY_GENERATION = ?, SEQUENCE_NBR = ?, GLOBAL_GENERATION = ?, LAST_ACCESS_TIME = ? WHERE USER_KEY = ? AND SUBKEY = ? AND LAST_ACCESS_TIME = ?; INSERT INTO SEQUENCE_NUMBER VALUES (?, ?, ?, ?, ?, ?); REPLACE INTO SEQUENCE_NUMBER VALUES (?, ?, ?, ?, ?, ?); SELECT * FROM ITEMS Where USER_KEY = ? AND SUBKEY = ?; SELECT * FROM ITEMS WHERE USER_KEY = ? AND SUBKEY = ? AND VERSION_STAMP_HASH = ?; INSERT INTO ITEMS (USER_KEY, SUBKEY, VERSION_STAMP_HASH, VERSION_STAMP, USER_DATA, LAST_ACCESS_TIME, TYPE, DATA_HASH, HINTED_STORAGE_ID, USER_KEY_HASH) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?); DELETE FROM ITEMS WHERE USER_KEY = ? AND SUBKEY = ? AND VERSION_STAMP_HASH = ?; These are the main queries that we execute. Thanks A On 8/30/06, Jay Pipes [EMAIL PROTECTED] wrote: On Wed, 2006-08-30 at 08:31 -0700, Phantom wrote: We have an application that stores versioned data in MySQL. Everytime a piece of data is retrieved and written to, it is stored in the database with a new version and all old versions are subsequently deleted. We have a request rate of 2 million reads per hour and 1.25 million per hour. What I am seeing is that as the DB grows the performance on the writes degrades substantially. When I start with a fresh database writes are at 70ms. But once the database reaches around 10GB the writes are at 200 ms. The DB can grow upto 35GB. I have tried almost performance related tuning described in the MySQL documentation page. What do I need to look at to start addressing this problem or this is how the performance is going to be ? Before getting into server parameters, is it possible to take a look at your schema and a sample of your SQL queries from the application? That would help immensely. 70ms for an UPDATE seems very slow... and 200ms is very slow. Cheers, -- Jay Pipes Community Relations Manager, North America, MySQL, Inc. [EMAIL PROTECTED] :: +1 614 406 1267 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Starting a Second Server under Mac OS X 10.3.9
I'm trying to get a second server running using mysql 4.1.15 on Mac OS X 10.3.9. I have one server running as my main development server, and would like a second to use for testing alternate DB Schema. I've followed the instructions in the manual section 5.12.2 'running multiple servers on unix', but have only tried to use the instructions regarding running a second server from the same installation as the first. I gave the following command-line options to mysqld_safe: --datadir=/path/to/alternate/datadir --log-bin=testmysql-bin --port=3307 --socket=/tmp/testmysql.sock where the path was one appropriate to my installation, but I got back an error saying: A mysqld process already exists Is it necessarry to also designate an alternate pid file for the test installation? Any other ideas why this fails? Any suggestions for getting it to work without having to install an etirely separate mysql to run it off? Thanks, C Cris Ewing CME and Telehealth Web Services Department of Radiology Web Services University of Washington School of Medicine Work Phone: (206) 685-9116 Home Phone: (206) 365-3413 E-mail: [EMAIL PROTECTED] *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query cache question
What else would prevent queries from getting into the cache? Anything with NOW() in it or any of it's related functions... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Degrading write performance using MySQL 5.0.24
What type of data are you inserting? What storage engine are you inserting into? What is the average row size? On Wed, 2006-08-30 at 12:32 -0400, George Law wrote: I see the same type of slow downs using 5.0.18 I am using load data in file to load CSV files. with clean tables, I see fairly quick inserts (ie instant) 2006-08-30 12:07:15 : begin import into table1 2006-08-30 12:07:15: end import into table1 records (10962) From earlier this morning, before I rotated my tables: 2006-08-30 09:02:01 : begin import into table1 2006-08-30 09:05:07: end import into table1 records (10082) I've posted about this before - one person will say that its my indexes getting rebuilt, others have said its disk io. I can never get a solid answer. If I disable the keys, do the import, then re-enable the keys, it takes just as long, if not longer. I have just about given up on finding a solution for this and just rotate my tables out regularly once the imports take over 5 minutes to process roughly 10,000 records -- George -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:06 PM To: Phantom Cc: mysql@lists.mysql.com Subject: Re: Degrading write performance using MySQL 5.0.24 On Wed, 2006-08-30 at 08:31 -0700, Phantom wrote: We have an application that stores versioned data in MySQL. Everytime a piece of data is retrieved and written to, it is stored in the database with a new version and all old versions are subsequently deleted. We have a request rate of 2 million reads per hour and 1.25 million per hour. What I am seeing is that as the DB grows the performance on the writes degrades substantially. When I start with a fresh database writes are at 70ms. But once the database reaches around 10GB the writes are at 200 ms. The DB can grow upto 35GB. I have tried almost performance related tuning described in the MySQL documentation page. What do I need to look at to start addressing this problem or this is how the performance is going to be ? Before getting into server parameters, is it possible to take a look at your schema and a sample of your SQL queries from the application? That would help immensely. 70ms for an UPDATE seems very slow... and 200ms is very slow. Cheers, -- Jay Pipes Community Relations Manager, North America, MySQL, Inc. [EMAIL PROTECTED] :: +1 614 406 1267 -- 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: SOS
Did you try :
mysql -u root -h localhost (Without the -p)
Root is passwordless after installation unless you changed it.
On Tuesday 29 August 2006 11:28, 李彦 wrote:
Dear Sir:
I have some problems with mysql 5.0 binary source in Linux(RedHat).I'm
able to startup the mysql process. But when i type in :mysql -u root -h
localhost -p, and then put the correct password, I can not enter the
database. I took almost one week to deal with this issue. But
Can you help me? May be the mysql version problem?
ÖÂ
Àñ£¡
ÀîÑå
[EMAIL PROTECTED]
2006-08-29
ÿôáÉ²Æ {¬±Æ§çpØ\©V÷«²*'ÓÿýÉZ«ÿ)mz»Þ®ÈšOüïö§v+ ¹ªÚ×uúÞzﻬ
--
Peter M. Groen
Open Systems Development
Klipperwerf 12
2317 DZ Leiden
T : +31-(0)71-5216317
M : +31-(0)6-29563390
E : [EMAIL PROTECTED]
Skype : peter_m_groen
N���,j�j� jy�w���Z�oz��}��Z[^*'��ڝج���j{^y��y� �*��
RE: Degrading write performance using MySQL 5.0.24
data is all alphanumeric - any char fields are all fixed lengths, no
varchars
Name: table1
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 330344
Avg_row_length: 624
Data_length: 206134656
Max_data_length: 2680059592703
Index_length: 18638848
Data_free: 0
Auto_increment: NULL
Create_time: 2006-08-30 09:50:23
Update_time: 2006-08-30 14:17:17
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1
Comment:
+-+-
-+--+-+-+---+
| Field | Type
| Null | Key | Default | Extra |
+-+-
-+--+-+-+---+
| start_time | char(19)
| YES | | NULL| |
| start_time_epoch| int(10)
| YES | | 0 | |
| call_duration | char(9)
| YES | | NULL| |
| call_source | char(15)
| YES | | NULL| |
| call_source_q931sig_port| int(5)
| YES | | 0 | |
| call_dest | char(15)
| YES | | NULL| |
| undef1 | char(1)
| YES | | NULL| |
| call_source_custid | char(20)
| YES | | NULL| |
| called_party_on_dest| char(32)
| YES | | NULL| |
| called_party_from_src | char(32)
| YES | | NULL| |
| call_type | char(2)
| YES | | NULL| |
| undef2 | tinyint(1)
| YES | | NULL| |
| disconnect_error_type | char(1)
| YES | | | |
| call_error_num | int(4)
| YES | | 0 | |
| call_error | char(24)
| YES | | NULL| |
| undef3 | char(1)
| YES | | NULL| |
| undef4 | char(1)
| YES | | NULL| |
| ani | char(32)
| YES | | NULL| |
| undef5 | char(1)
| YES | | NULL| |
| undef6 | char(1)
| YES | | NULL| |
| undef7 | char(1)
| YES | | NULL| |
| cdr_seq_no | int(9)
| NO | PRI | 0 | |
| undef8 | char(1)
| YES | | NULL| |
| callid | char(50)
| NO | PRI | | |
| call_hold_time | char(9)
| YES | | NULL| |
| call_source_regid | char(20)
| YES | | | |
| call_source_uport | int(1)
| YES | | 0 | |
| call_dest_regid | char(20)
| YES | | | |
| call_dest_uport | int(1)
| YES | | 0 | |
| isdn_cause_code | int(3)
| YES | | 0 | |
| called_party_after_src_calling_plan | char(32)
| YES | | NULL| |
| call_error_dest_num | int(4)
| YES | | 0 | |
| call_error_dest | char(25)
| YES | | NULL| |
| call_error_event_str| char(20)
| YES | | | |
| new_ani | char(32)
| YES | | NULL| |
| call_duration_seconds | int(5)
| YES | | 0 | |
| incoming_leg_callid | char(1)
| YES | | NULL| |
| protocol| enum('sip','h323')
| YES | | NULL| |
| cdr_type|
enum('start1','start2','end1','end2','hunt') | YES | | NULL|
|
| hunting_attempts| int(1)
| YES | | 0 | |
| caller_trunk_group | int(3)
| YES | | NULL| |
| call_pdd| int(5)
| YES | | 0 | |
| h323_dest_ras_error | int(2)
| YES | | 0 | |
| h323_dest_h225_error| int(2)
| YES | | 0 | |
| sip_dest_respcode | int(3)
| YES | | 0 | |
| dest_trunk_group| char(1)
| YES | | NULL| |
| call_duration_fractional| decimal(8,3)
| YES | | 0.000 | |
| timezone| char(3)
| YES | | | |
| msw_name| char(10)
| YES | | NULL| |
| called_party_after_transit_route| char(1)
| YES | | NULL| |
|
RE: query cache question
The output of that query is: @@global.query_cache_size 10485760 Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to draw data model diagrams from existing schema?
I've spent the last month building a fairly elaborate database with lots of foreign keys. I want to draw a diagram that maps the relationships between all the tables, but I haven't found any software that can do that. Does anyone have any suggestions? I want to draw a picture with a box for each table with links to all the other tables. TIA Matt -- A better way of running series of SAS programs: http://overlook.homelinux.net/wilsonwiki/SasAndMakefiles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Degrading write performance using MySQL 5.0.24
Hi!
Could you please post a SHOW CREATE TABLE table1 \G
thanks!
On Wed, 2006-08-30 at 14:32 -0400, George Law wrote:
data is all alphanumeric - any char fields are all fixed lengths, no
varchars
Name: table1
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 330344
Avg_row_length: 624
Data_length: 206134656
Max_data_length: 2680059592703
Index_length: 18638848
Data_free: 0
Auto_increment: NULL
Create_time: 2006-08-30 09:50:23
Update_time: 2006-08-30 14:17:17
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1
Comment:
+-+-
-+--+-+-+---+
| Field | Type
| Null | Key | Default | Extra |
+-+-
-+--+-+-+---+
| start_time | char(19)
| YES | | NULL| |
| start_time_epoch| int(10)
| YES | | 0 | |
| call_duration | char(9)
| YES | | NULL| |
| call_source | char(15)
| YES | | NULL| |
| call_source_q931sig_port| int(5)
| YES | | 0 | |
| call_dest | char(15)
| YES | | NULL| |
| undef1 | char(1)
| YES | | NULL| |
| call_source_custid | char(20)
| YES | | NULL| |
| called_party_on_dest| char(32)
| YES | | NULL| |
| called_party_from_src | char(32)
| YES | | NULL| |
| call_type | char(2)
| YES | | NULL| |
| undef2 | tinyint(1)
| YES | | NULL| |
| disconnect_error_type | char(1)
| YES | | | |
| call_error_num | int(4)
| YES | | 0 | |
| call_error | char(24)
| YES | | NULL| |
| undef3 | char(1)
| YES | | NULL| |
| undef4 | char(1)
| YES | | NULL| |
| ani | char(32)
| YES | | NULL| |
| undef5 | char(1)
| YES | | NULL| |
| undef6 | char(1)
| YES | | NULL| |
| undef7 | char(1)
| YES | | NULL| |
| cdr_seq_no | int(9)
| NO | PRI | 0 | |
| undef8 | char(1)
| YES | | NULL| |
| callid | char(50)
| NO | PRI | | |
| call_hold_time | char(9)
| YES | | NULL| |
| call_source_regid | char(20)
| YES | | | |
| call_source_uport | int(1)
| YES | | 0 | |
| call_dest_regid | char(20)
| YES | | | |
| call_dest_uport | int(1)
| YES | | 0 | |
| isdn_cause_code | int(3)
| YES | | 0 | |
| called_party_after_src_calling_plan | char(32)
| YES | | NULL| |
| call_error_dest_num | int(4)
| YES | | 0 | |
| call_error_dest | char(25)
| YES | | NULL| |
| call_error_event_str| char(20)
| YES | | | |
| new_ani | char(32)
| YES | | NULL| |
| call_duration_seconds | int(5)
| YES | | 0 | |
| incoming_leg_callid | char(1)
| YES | | NULL| |
| protocol| enum('sip','h323')
| YES | | NULL| |
| cdr_type|
enum('start1','start2','end1','end2','hunt') | YES | | NULL|
|
| hunting_attempts| int(1)
| YES | | 0 | |
| caller_trunk_group | int(3)
| YES | | NULL| |
| call_pdd| int(5)
| YES | | 0 | |
| h323_dest_ras_error | int(2)
| YES | | 0 | |
| h323_dest_h225_error| int(2)
| YES | | 0 | |
| sip_dest_respcode | int(3)
| YES | | 0 | |
| dest_trunk_group| char(1)
| YES | | NULL| |
| call_duration_fractional| decimal(8,3)
| YES | | 0.000 | |
| timezone
Re: How to draw data model diagrams from existing schema?
Try to use DBDesigner, you´d get it from www.fabforce.net Matthew Wilson [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I've spent the last month building a fairly elaborate database with lots of foreign keys. I want to draw a diagram that maps the relationships between all the tables, but I haven't found any software that can do that. Does anyone have any suggestions? I want to draw a picture with a box for each table with links to all the other tables. TIA Matt -- A better way of running series of SAS programs: http://overlook.homelinux.net/wilsonwiki/SasAndMakefiles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Degrading write performance using MySQL 5.0.24
Jay,
Here you go - at least according to mysqldump
CREATE TABLE `table1` (
`start_time` char(19) default NULL,
`start_time_epoch` int(10) default '0',
`call_duration` char(9) default NULL,
`call_source` char(15) default NULL,
`call_source_q931sig_port` int(5) default '0',
`call_dest` char(15) default NULL,
`undef1` char(1) default NULL,
`call_source_custid` char(20) default NULL,
`called_party_on_dest` char(32) default NULL,
`called_party_from_src` char(32) default NULL,
`call_type` char(2) default NULL,
`undef2` tinyint(1) default NULL,
`disconnect_error_type` char(1) default '',
`call_error_num` int(4) default '0',
`call_error` char(24) default NULL,
`undef3` char(1) default NULL,
`undef4` char(1) default NULL,
`ani` char(32) default NULL,
`undef5` char(1) default NULL,
`undef6` char(1) default NULL,
`undef7` char(1) default NULL,
`cdr_seq_no` int(9) NOT NULL default '0',
`undef8` char(1) default NULL,
`callid` char(50) NOT NULL default '',
`call_hold_time` char(9) default NULL,
`call_source_regid` char(20) default '',
`call_source_uport` int(1) default '0',
`call_dest_regid` char(20) default '',
`call_dest_uport` int(1) default '0',
`isdn_cause_code` int(3) default '0',
`called_party_after_src_calling_plan` char(32) default NULL,
`call_error_dest_num` int(4) default '0',
`call_error_dest` char(25) default NULL,
`call_error_event_str` char(20) default '',
`new_ani` char(32) default NULL,
`call_duration_seconds` int(5) default '0',
`incoming_leg_callid` char(1) default NULL,
`protocol` enum('sip','h323') default NULL,
`cdr_type` enum('start1','start2','end1','end2','hunt') default NULL,
`hunting_attempts` int(1) default '0',
`caller_trunk_group` int(3) default NULL,
`call_pdd` int(5) default '0',
`h323_dest_ras_error` int(2) default '0',
`h323_dest_h225_error` int(2) default '0',
`sip_dest_respcode` int(3) default '0',
`dest_trunk_group` char(1) default NULL,
`call_duration_fractional` decimal(8,3) default '0.000',
`timezone` char(3) default '',
`msw_name` char(10) default NULL,
`called_party_after_transit_route` char(1) default NULL,
`called_party_on_dest_num_type` int(1) default '0',
`called_party_from_src_num_type` int(1) default '0',
`call_source_realm_name` char(3) default NULL,
`call_dest_realm_name` char(3) default NULL,
`call_dest_crname` char(50) default NULL,
`call_dest_custid` char(20) default NULL,
`call_zone_data` char(20) default NULL,
`calling_party_on_dest_num_type` int(1) default '0',
`calling_party_from_src_num_type` int(1) default '0',
`original_isdn_cause_code` int(1) default '0',
PRIMARY KEY (`callid`,`cdr_seq_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1;
-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 30, 2006 3:06 PM
To: George Law
Cc: mysql@lists.mysql.com
Subject: RE: Degrading write performance using MySQL 5.0.24
Hi!
Could you please post a SHOW CREATE TABLE table1 \G
thanks!
On Wed, 2006-08-30 at 14:32 -0400, George Law wrote:
data is all alphanumeric - any char fields are all fixed
lengths, no
varchars
Name: table1
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 330344
Avg_row_length: 624
Data_length: 206134656
Max_data_length: 2680059592703
Index_length: 18638848
Data_free: 0
Auto_increment: NULL
Create_time: 2006-08-30 09:50:23
Update_time: 2006-08-30 14:17:17
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1
Comment:
+-+--
---
-+--+-+-+---+
| Field | Type
| Null | Key | Default | Extra |
+-+--
---
-+--+-+-+---+
| start_time | char(19)
| YES | | NULL| |
| start_time_epoch| int(10)
| YES | | 0 | |
| call_duration | char(9)
| YES | | NULL| |
| call_source | char(15)
| YES | | NULL| |
| call_source_q931sig_port| int(5)
| YES | | 0 | |
| call_dest | char(15)
| YES | | NULL| |
| undef1 | char(1)
| YES | | NULL| |
| call_source_custid | char(20)
| YES | | NULL| |
| called_party_on_dest| char(32)
| YES | | NULL| |
| called_party_from_src | char(32)
| YES | | NULL| |
| call_type | char(2)
| YES | | NULL| |
| undef2 | tinyint(1)
| YES | | NULL
limit clause on join results
Hi all, I'm trying to show pages of results in a web app, 30 items per page. But my main query is a join, and I can't use a simple limit clause to return the 30 items (with the desired offset), because the join is between two tables with a one-to-many relationship, and I want 30 of the objects in the first table, not 30 rows. For example, here are some simplified, with the usual one-to-many relation... CREATE TABLE states ( id int primary key, name text ); CREATE TABLE cities ( id int primary key, state int, name text ) With some sample data, my query looks like this: mysql select * from states, cities where states.id = cities.state; ++++---+--+ | id | name | id | state | name | ++++---+--+ | 1 | North Carolina | 1 | 1 | Durham | | 1 | North Carolina | 2 | 1 | Raleigh | | 1 | North Carolina | 3 | 1 | Chapel Hill | | 2 | California | 4 | 2 | Ventura | | 2 | California | 5 | 2 | Belmont | | 3 | Connecticut| 6 | 3 | Hartford | | 3 | Connecticut| 7 | 3 | Wethersfield | ++++---+--+ 7 rows in set (0.00 sec) Now, I want the first 2 *states*, which would be the first 5 rows? Is there a good way to do that in one query? A subselect like the following doesn't work in my version of MySQL. mysql select * from states, cities where states.id = cities.state and state.id in (select id from state limit 2); ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' mysql select version(); +-+ | version() | +-+ | 4.1.21-community-nt | +-+ Something with distinct or group by perhaps? thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to draw data model diagrams from existing schema?
Automatically? Don't know. I did it by hand using Dia, a free alternative to MS Visio. It was tedious. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Matthew Wilson Sent: Wednesday, August 30, 2006 2:58 PM To: mysql@lists.mysql.com Subject: How to draw data model diagrams from existing schema? I've spent the last month building a fairly elaborate database with lots of foreign keys. I want to draw a diagram that maps the relationships between all the tables, but I haven't found any software that can do that. Does anyone have any suggestions? I want to draw a picture with a box for each table with links to all the other tables. TIA Matt -- A better way of running series of SAS programs: http://overlook.homelinux.net/wilsonwiki/SasAndMakefiles -- 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: query cache question
Please show the output of: SELECT @@global.query_cache_type; and then: SHOW GLOBAL STATUS LIKE 'Qc%'; Thanks! On Wed, 2006-08-30 at 13:43 -0500, Mazur Worden, Kathy wrote: The output of that query is: @@global.query_cache_size 10485760 Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
@@global.query_cache_type ON Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes0 Qcache_not_cached 20318 Qcache_queries_in_cache 0 Qcache_total_blocks 1 Thanks -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 2:12 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Please show the output of: SELECT @@global.query_cache_type; and then: SHOW GLOBAL STATUS LIKE 'Qc%'; Thanks! On Wed, 2006-08-30 at 13:43 -0500, Mazur Worden, Kathy wrote: The output of that query is: @@global.query_cache_size 10485760 Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM Performance for fixed row_format
The documentation for 4.1 says that changing to a row_format of fixed will increase disk look up. I did this with several tables where a query was taking about 3 minutes. I expected (from the doc and past experience) for the query to run in about 1 to 1 ½ minutes. Instead it now takes about 8 minutes! After running the alter table statements and verifying that the row_format was indeed fixed I then analyzed, optimized and checked (medium) all tables in the database. Is there an explanation for why the query time has increase (dramatically) rather than decreased? Thank you for your time. Boyd Hemphill
Re: How to draw data model diagrams from existing schema?
Excuse me, if he wants to do the reverse proccess, really it doesn´t do. Jerry Schwartz [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Automatically? Don't know. I did it by hand using Dia, a free alternative to MS Visio. It was tedious. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Matthew Wilson Sent: Wednesday, August 30, 2006 2:58 PM To: mysql@lists.mysql.com Subject: How to draw data model diagrams from existing schema? I've spent the last month building a fairly elaborate database with lots of foreign keys. I want to draw a diagram that maps the relationships between all the tables, but I haven't found any software that can do that. Does anyone have any suggestions? I want to draw a picture with a box for each table with links to all the other tables. TIA Matt -- A better way of running series of SAS programs: http://overlook.homelinux.net/wilsonwiki/SasAndMakefiles -- 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: Degrading write performance using MySQL 5.0.24
Well, clearly you've got a denormalized schema here, but I'll assume you
will be normalizing this after the import? I hope so. Having a PK of
CHAR(50) / INT will lead to poorer performance than a simply
incrementing integer key. Additionally, it seems all the fields are
NULLable, which wastes even more space. Plus, not quite sure what all
the undef1, undef2, undef3 ... etc fields are? Also, storing timestamps
in a CHAR(19) is another great way to kill performance.
As far as just simply *getting* this information into the database as
quickly as possible, you might try dumping it into a temporary table,
then normalizing it and INSERT ... SELECT * FROM temp_table to batch the
insert as one move into the main table...
Jay
On Wed, 2006-08-30 at 15:11 -0400, George Law wrote:
Jay,
Here you go - at least according to mysqldump
CREATE TABLE `table1` (
`start_time` char(19) default NULL,
`start_time_epoch` int(10) default '0',
`call_duration` char(9) default NULL,
`call_source` char(15) default NULL,
`call_source_q931sig_port` int(5) default '0',
`call_dest` char(15) default NULL,
`undef1` char(1) default NULL,
`call_source_custid` char(20) default NULL,
`called_party_on_dest` char(32) default NULL,
`called_party_from_src` char(32) default NULL,
`call_type` char(2) default NULL,
`undef2` tinyint(1) default NULL,
`disconnect_error_type` char(1) default '',
`call_error_num` int(4) default '0',
`call_error` char(24) default NULL,
`undef3` char(1) default NULL,
`undef4` char(1) default NULL,
`ani` char(32) default NULL,
`undef5` char(1) default NULL,
`undef6` char(1) default NULL,
`undef7` char(1) default NULL,
`cdr_seq_no` int(9) NOT NULL default '0',
`undef8` char(1) default NULL,
`callid` char(50) NOT NULL default '',
`call_hold_time` char(9) default NULL,
`call_source_regid` char(20) default '',
`call_source_uport` int(1) default '0',
`call_dest_regid` char(20) default '',
`call_dest_uport` int(1) default '0',
`isdn_cause_code` int(3) default '0',
`called_party_after_src_calling_plan` char(32) default NULL,
`call_error_dest_num` int(4) default '0',
`call_error_dest` char(25) default NULL,
`call_error_event_str` char(20) default '',
`new_ani` char(32) default NULL,
`call_duration_seconds` int(5) default '0',
`incoming_leg_callid` char(1) default NULL,
`protocol` enum('sip','h323') default NULL,
`cdr_type` enum('start1','start2','end1','end2','hunt') default NULL,
`hunting_attempts` int(1) default '0',
`caller_trunk_group` int(3) default NULL,
`call_pdd` int(5) default '0',
`h323_dest_ras_error` int(2) default '0',
`h323_dest_h225_error` int(2) default '0',
`sip_dest_respcode` int(3) default '0',
`dest_trunk_group` char(1) default NULL,
`call_duration_fractional` decimal(8,3) default '0.000',
`timezone` char(3) default '',
`msw_name` char(10) default NULL,
`called_party_after_transit_route` char(1) default NULL,
`called_party_on_dest_num_type` int(1) default '0',
`called_party_from_src_num_type` int(1) default '0',
`call_source_realm_name` char(3) default NULL,
`call_dest_realm_name` char(3) default NULL,
`call_dest_crname` char(50) default NULL,
`call_dest_custid` char(20) default NULL,
`call_zone_data` char(20) default NULL,
`calling_party_on_dest_num_type` int(1) default '0',
`calling_party_from_src_num_type` int(1) default '0',
`original_isdn_cause_code` int(1) default '0',
PRIMARY KEY (`callid`,`cdr_seq_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1;
-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 30, 2006 3:06 PM
To: George Law
Cc: mysql@lists.mysql.com
Subject: RE: Degrading write performance using MySQL 5.0.24
Hi!
Could you please post a SHOW CREATE TABLE table1 \G
thanks!
On Wed, 2006-08-30 at 14:32 -0400, George Law wrote:
data is all alphanumeric - any char fields are all fixed
lengths, no
varchars
Name: table1
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 330344
Avg_row_length: 624
Data_length: 206134656
Max_data_length: 2680059592703
Index_length: 18638848
Data_free: 0
Auto_increment: NULL
Create_time: 2006-08-30 09:50:23
Update_time: 2006-08-30 14:17:17
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1
Comment:
+-+--
---
-+--+-+-+---+
| Field | Type
| Null | Key | Default | Extra |
+-+--
---
-+--+-+-+---+
| start_time | char(19)
| YES | | NULL|
RE: How to draw data model diagrams from existing schema?
Use MySQL Workbench: http://dev.mysql.com/downloads/guitools/ On Wed, 2006-08-30 at 15:11 -0400, Jerry Schwartz wrote: Automatically? Don't know. I did it by hand using Dia, a free alternative to MS Visio. It was tedious. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Matthew Wilson Sent: Wednesday, August 30, 2006 2:58 PM To: mysql@lists.mysql.com Subject: How to draw data model diagrams from existing schema? I've spent the last month building a fairly elaborate database with lots of foreign keys. I want to draw a diagram that maps the relationships between all the tables, but I haven't found any software that can do that. Does anyone have any suggestions? I want to draw a picture with a box for each table with links to all the other tables. TIA Matt -- A better way of running series of SAS programs: http://overlook.homelinux.net/wilsonwiki/SasAndMakefiles -- 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: query cache question
Ah, I think I may have figured it out... Are you using prepared statements? If so, doing that prevents the query cache from caching the resultset entirely. On Wed, 2006-08-30 at 14:17 -0500, Mazur Worden, Kathy wrote: @@global.query_cache_type ON Variable_name Value Qcache_free_blocks1 Qcache_free_memory10477008 Qcache_hits 0 Qcache_inserts0 Qcache_lowmem_prunes 0 Qcache_not_cached 20318 Qcache_queries_in_cache 0 Qcache_total_blocks 1 Thanks -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 2:12 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Please show the output of: SELECT @@global.query_cache_type; and then: SHOW GLOBAL STATUS LIKE 'Qc%'; Thanks! On Wed, 2006-08-30 at 13:43 -0500, Mazur Worden, Kathy wrote: The output of that query is: @@global.query_cache_size 10485760 Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connecting mySQL to ColdfusionMX7
All, I am having difficulties getting the Coldfusion Administrator to establish a database connection to a database called accounting that I created using the command line utility. Any help would be very appreciated This is the error that CF is throwing: Connection verification failed for data source: accounting java.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306? Thank you in advance! rruth Sent via the WebMail system at ruthgirls.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query cache question
Actually no, I'm not using any prepared statements. But thanks for the pointer on that. -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 2:24 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Ah, I think I may have figured it out... Are you using prepared statements? If so, doing that prevents the query cache from caching the resultset entirely. On Wed, 2006-08-30 at 14:17 -0500, Mazur Worden, Kathy wrote: @@global.query_cache_type ON Variable_name Value Qcache_free_blocks1 Qcache_free_memory10477008 Qcache_hits 0 Qcache_inserts0 Qcache_lowmem_prunes 0 Qcache_not_cached 20318 Qcache_queries_in_cache 0 Qcache_total_blocks 1 Thanks -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 2:12 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Please show the output of: SELECT @@global.query_cache_type; and then: SHOW GLOBAL STATUS LIKE 'Qc%'; Thanks! On Wed, 2006-08-30 at 13:43 -0500, Mazur Worden, Kathy wrote: The output of that query is: @@global.query_cache_size 10485760 Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: RE: query cache question Those queries look just fine to me. Could you output the result of the following: SELECT @@global.query_cache_size; On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote: Maybe that's my issue, I had though the type of query would not matter. I've run simple queries in query browser like this: Select * from table t; And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental') And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid ORDER BY AgencyInfo.Name, BuildingInfo.Name; There are indexes on the id and name related fields used in the WHERE clauses. Kathy Mazur Worden Prairie Area Library System -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mysql@lists.mysql.com Subject: Re: query cache question Could you post an example of a query you are expecting to be cached? On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote: Hi, I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it: Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 3759 Qcache_queries_in_cache 0 Qcache_total_blocks 1 I've found http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries have the no cache option specified. What else would prevent queries from getting into the cache? Kathy Mazur Worden Prairie Area Library System -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to draw data model diagrams from existing schema?
Hello, Check out MySQL Workbench (Part of the MySQL GUI Tools Bundle) if you haven't already done so: http://dev.mysql.com/downloads/gui-tools/5.0.html Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Matthew Wilson Sent: Wednesday, August 30, 2006 1:58 PM To: mysql@lists.mysql.com Subject: How to draw data model diagrams from existing schema? I've spent the last month building a fairly elaborate database with lots of foreign keys. I want to draw a diagram that maps the relationships between all the tables, but I haven't found any software that can do that. Does anyone have any suggestions? I want to draw a picture with a box for each table with links to all the other tables. TIA Matt -- A better way of running series of SAS programs: http://overlook.homelinux.net/wilsonwiki/SasAndMakefiles -- 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]
Some non-transactional changed tables couldn't be rolled back
Hi all... i'm developing an aplication and i'm need to use begin,
commit, rollback, but when i try to use rollback i get a warning
| Warning | 1196 | Some non-transactional changed tables couldn't be
rolled back |
this is what i did
mysql begin;
Query OK, 0 rows affected (0.00 sec)
mysql insert into
users(id,address,crypt,clear,name,uid,gid,home,domain,maildir,imapok,bool1,bool2)
values ('[EMAIL PROTECTED]','[EMAIL
PROTECTED]',encrypt('x','ab'),'x','x',1136,1136,'/','x.com','/var/spool/maildir/136/',1,1,1);
Query OK, 1 row affected (0.01 sec)
mysql rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql show warnings;
+-+--+---+
| Level | Code | Message
|
+-+--+---+
| Warning | 1196 | Some non-transactional changed tables couldn't be
rolled back |
+-+--+---+
1 row in set (0.01 sec)
does anybody know how can i fix this... i appreciate any suggestion
thanks a lot in advanced.
--
http://www.obed.org.mx --- blog
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table sizes, rolling data.
If you use an auto_increment column (e.g. id int auto_increment) then your trigger could do something like this: DELETE FROM table WHERE id new.id - 100; On Aug 30, 2006, at 3:49 PM, Dan Buettner wrote: You could accomplish this with a trigger on the table - on INSERT, execute a DELETE statement. http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html You would need to find a way to identify which is the bottom record to be deleted ... I might use an ID column, and consider the lowest-numbered row as the bottom one. You might also need to include a condition so that it does not start deleting records until you've reached the appropriate number of records in the table, or if your database is slow-moving, you could simply create the trigger once you have the appropriate number. HTH, Dan On 8/29/06, Tanner Postert [EMAIL PROTECTED] wrote: sorry for the double post. if i want to have a row of about 100 records. and everytime i insert a new record, it gets pushed on the top, and the bottom one gets pushed out, sort of like a heap. is this possible? i know i can just delete the record, etc, but i was wondering if there was a built in way of doing it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group By question
I have a table of people with one of the fields being the email address. I would like to query all rows that have more than one person with the same email address. For example if the data were like this... A [EMAIL PROTECTED] B [EMAIL PROTECTED] C [EMAIL PROTECTED] D [EMAIL PROTECTED] E [EMAIL PROTECTED] F [EMAIL PROTECTED] The query would return row A, D, B, and E, in that order. It would not return C or F -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By question
Chris, I would like to query all rows that have more than one person with the same email address. select id,count(emailaddr) as howmany from tbl t1 join tbl t2 using(emailaddr) group by id having howmany1; PB - Chris W wrote: I have a table of people with one of the fields being the email address. I would like to query all rows that have more than one person with the same email address. For example if the data were like this... A [EMAIL PROTECTED] B [EMAIL PROTECTED] C [EMAIL PROTECTED] D [EMAIL PROTECTED] E [EMAIL PROTECTED] F [EMAIL PROTECTED] The query would return row A, D, B, and E, in that order. It would not return C or F No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.7/433 - Release Date: 8/30/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mass E-mail Options
Again, I know this is not necessarily a MySQL specific question, and sorry for getting off target, but this is a pretty smart and experienced bunch, and I'm sure that someone out there will be able to help me. We have a web application in which we have a mass e-mail function. It allows the user to compose a simple text-only e-mail and send to everyone in our database (MySQL). Problem is that our e-mail server seems to be getting overloaded, and it just shuts down, causing an error. We're using ArgoSoft Mail server, which works very well for our normal needs. We do not want to change to Microsoft's Exchange Server. I hear it's expensive, and difficult to set up and get working properly. I was wondering if anyone knows of any alternative mass e-mail options. I don't want to use servers that spammers use, because first, and foremost, this is NOT spam, and second, some recipients may have these servers black listed. What other alternatives are there? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
Again, I know this is not necessarily a MySQL specific question, and sorry for getting off target, but this is a pretty smart and experienced bunch, and I'm sure that someone out there will be able to help me. We have a web application in which we have a mass e-mail function. It allows the user to compose a simple text-only e-mail and send to everyone in our database (MySQL). Problem is that our e-mail server seems to be getting overloaded, and it just shuts down, causing an error. We're using ArgoSoft Mail server, which works very well for our normal needs. We do not want to change to Microsoft's Exchange Server. I hear it's expensive, and difficult to set up and get working properly. I was wondering if anyone knows of any alternative mass e-mail options. I don't want to use servers that spammers use, because first, and foremost, this is NOT spam, and second, some recipients may have these servers black listed. What other alternatives are there? http://www.lyris.com/products/mailengine/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex SQL for multiple joins
Hi, For my current website project I'm developing a fine-grained access control system. I have the schema mostly worked out, although it will be having additional data added to parts of it later. For the record, I have 6 tables: users (contains all my individual users) usergroups (contains all the groups users can belong to) permissions (contains all the things that the system allows) users_usergroups (a join table that identifies which users belong to which groups) users_permissions (another join table that identifies which users have which permissions) usergroups_permissions (the final join table that identifies which usergroups have which permissions) Each of the _permissions join tables has an additional type column specifying whether the link allows or denies that particular action. What I've managed to get so far is queries returning which users belong to which groups, which users have which permissions, and which usergroups have which permissions. However, I need to go one step further and retrieve the permissions belonging to the usergroups which a specified user is a member of. Ideally I'd like to retrieve the individual users permissions at the same time. So what I need is a query that returns permissions belonging to a specific user, and permissions belonging to the usergroups that the specified user is a member of. Is this something I can do without too much hassle? Thanks in advance!
Re: installing mysql
jagdish gunjal wrote: i m upgrading mysql from 3.23 to 4.1.8. there was packge conflict for so many files like /etc/my.cnf Also i installed shared-compat for removing such problems. Is there any solution? Can you provide us with more about your environment? Also, have you read the 3-4 MySQL upgrade guide in our excellent documentation? best regards, -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 5.x Solaris 8 crashes?
We've been running mysql 4.1.11 on our sparc solaris 8 (it's a 12 proc Sun 4800) for over a year without problems, but we just ran into a known innodb bug, so I decided to upgrade to 5.x. I tried the regular 5.0.24 SPARC 64-bit Solaris 8 package, the 5.0.24 debug package, the 5.1.11 package, and compiling 5.1.11 from source. In each case, mysqld would run between 30 minutes and a few hours before crashing on a signal 11. No other info was generated. So I downgraded to the package version of 4.1.24 which has been running for over 6 hours without incidence. Sorry I don't have any better stack trace info, but the debug version didn't generate anything. Has anyone else encountered such a problem with Solaris 8 and mysql 5.x? I dug around online and couldn't find any mention of such a problem. -chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some non-transactional changed tables couldn't be rolled back
obed wrote: Hi all... i'm developing an aplication and i'm need to use begin, commit, rollback, but when i try to use rollback i get a warning | Warning | 1196 | Some non-transactional changed tables couldn't be rolled back | Transactions only work for table types that support them. MyISAM is not one of those table types. If you do a 'show create table' what type/engine is it using? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How does the federated engine table work
wangxu wrote: How does the federated engine table work when the query statement include a join substatement, it joins a local table with innodb engine and a federated table. How does the server deal with the query substatement like this? Have you taken a gander at the documentation? http://dev.mysql.com/doc/refman/5.0/en/federated-description.html http://www.oreillynet.com/pub/a/databases/2006/08/10/mysql-federated-tables.html hope this helps -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Drupal and MySQL
Has anyone heard of drupal breaking under a minor upgrade from Mysql 4.1.1X to 4.1.2X ? I have a weird one. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL App development
[EMAIL PROTECTED] wrote: Is there anything called MySQL application development environment guide, as it is there DB2 (IBM) which explains how to work with MySQL and C++ together? Can you show me what DB2 does that's different from our C++ API? Or can I refer DB2 App guide? No -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM Performance for fixed row_format
Boyd Hemphill wrote: The documentation for 4.1 says that changing to a row_format of fixed will increase disk look up. I did this with several tables where a query was taking about 3 minutes. I expected (from the doc and past experience) for the query to run in about 1 to 1 ½ minutes. Instead it now takes about 8 minutes! After running the alter table statements and verifying that the row_format was indeed fixed I then analyzed, optimized and checked (medium) all tables in the database. Is there an explanation for why the query time has increase (dramatically) rather than decreased? Not sure about that part of the question, the better one to ask is how you improve the query in the first place. Post the query and an explain of the query and see if anyone has any suggestions about that.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL+OpenVPN
Asif Lodhi wrote: Hi, Can anyone on this list tell me _how_ i can connect to my MySQL-5.0.22 server running on Windows-XP-SP2 using OpenVPN on Windows? It looks like a good VPN/SSL implementation. once you've got the VPN connection, just the usual: mysql -uUser -hHost -pPass How your VPN is setup is a completely other matter -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connecting mySQL to ColdfusionMX7
Ruth wrote: All, I am having difficulties getting the Coldfusion Administrator to establish a database connection to a database called accounting that I created using the command line utility. Any help would be very appreciated This is the error that CF is throwing: Connection verification failed for data source: accounting java.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306? Is mysql accepting tcp/ip connections and is it running on port 3306? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drupal and MySQL
Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: Has anyone heard of drupal breaking under a minor upgrade from Mysql 4.1.1X to 4.1.2X ? No, upgrading between versions should be OK. Want to provide the exact version numbers, in the /rare/ event that the ABI broke (which really shouldn't affect Drupal, unless PHP itself is to be blamed) -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.1
Logan, David (SST - Adelaide) wrote: Hi! Does anybody have any idea when 5.1 may come to General Release? I am particularly interested in MySQL Cluster as I have several databases (around 50) totalling 26Gb and would like to consider moving to this version because of the Cluster Disk Data Storage and the fact the current hardware doesn't have the RAM requirements to hold these databases. I expect late (Q4/2006) to early (Q1/2007) as a good time for release. I plan to store the data on a SAN and was wondering if anybody had any experience with this as yet? This is why you're after 5.1, so you get disk as opposed to memory only based storage then -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Support for JDBC 4.0?
Steven Buroff wrote: Does anyone know if there are plans to add support for JDBC 4.0 to the java connector? I'm particularly interested in support for the Connection.createQueryObject(Class cls) method. Its mostly already there in the trunk, and keep in mind that JDK-6 (w/JDBC-4) isn't even being released by Sun till late October. So, we expect to start shipping after Sun's sorted things out hope this helps -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drupal and MySQL
On Thu, Aug 31, 2006 at 09:52:37AM +1000, Colin Charles wrote: Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: Has anyone heard of drupal breaking under a minor upgrade from Mysql 4.1.1X to 4.1.2X ? MySQL 4.1.18 to 4.1.21 No, upgrading between versions should be OK. Want to provide the exact version numbers, in the /rare/ event that the ABI broke (which really shouldn't affect Drupal, unless PHP itself is to be blamed) PHP 5.0.5 -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drupal and MySQL
Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: On Thu, Aug 31, 2006 at 09:52:37AM +1000, Colin Charles wrote: Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: Has anyone heard of drupal breaking under a minor upgrade from Mysql 4.1.1X to 4.1.2X ? MySQL 4.1.18 to 4.1.21 And what problems are you facing? -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-bin.00000X
there are ton of these files in my mysql data directory on fedora core 5 they are about a GB a piece. it appears that they are snapshots or some kind of log file. what is creating these files?
Re: Connecting mySQL to ColdfusionMX7
Ruth divulged the following in an email dated 8/30/2006 11:58 AM : All, I am having difficulties getting the Coldfusion Administrator to establish a database connection to a database called accounting that I created using the command line utility. Any help would be very appreciated This is the error that CF is throwing: Connection verification failed for data source: accounting java.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306? Thank you in advance! rruth Sent via the WebMail system at ruthgirls.com http://www.adobe.com/go/6ef0253 If you have any more problems, ask them at the coldfusion forums ...most have already been answered. HTH Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql-bin.00000X
Hi Tanner, These are the binary logs. Look in your my.cnf file or check your system variable ( do this ) mysql show variables like '%log%'; and see if you have log-bin enabled. These are generally used for recovery from the last full backup (similar to logical/redo logs in other technologies) and are also necessary for replication. You can delete them using the PURGE MASTER LOGS statement (documented here : http://dev.mysql.com/doc/refman/4.1/en/replication-master-sql.html) Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Tanner Postert [mailto:[EMAIL PROTECTED] Sent: Thursday, 31 August 2006 10:02 AM To: mysql@lists.mysql.com Subject: mysql-bin.0X there are ton of these files in my mysql data directory on fedora core 5 they are about a GB a piece. it appears that they are snapshots or some kind of log file. what is creating these files? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-bin.00000X
Tanner Postert wrote: there are ton of these files in my mysql data directory on fedora core 5 they are about a GB a piece. it appears that they are snapshots or some kind of log file. what is creating these files? http://dev.mysql.com/doc/refman/5.1/en/binary-log.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
Again, I know this is not necessarily a MySQL specific question, and sorry for getting off target, but this is a pretty smart and experienced bunch, and I'm sure that someone out there will be able to help me. We have a web application in which we have a mass e-mail function. It allows the user to compose a simple text-only e-mail and send to everyone in our database (MySQL). Problem is that our e-mail server seems to be getting overloaded, and it just shuts down, causing an error. We're using ArgoSoft Mail server, which works very well for our normal needs. We do not want to change to Microsoft's Exchange Server. I hear it's expensive, and difficult to set up and get working properly. I was wondering if anyone knows of any alternative mass e-mail options. I don't want to use servers that spammers use, because first, and foremost, this is NOT spam, and second, some recipients may have these servers black listed. What other alternatives are there? How many messages are we talking here? Perhaps create a server side script, that sends the emails in chunks, then sleeps a little, and sends another chunk. I would also try to unorder the email addresses, so that large groups of yahoo and aol etc emails do not hit the outbound queue at the same time. They tend to look down on that activity, and throttle you back, making it take even longer to get your messages out. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
At 18:32 2006-08-30 -0700, Scott Haneda wrote: I would also try to unorder the email addresses, so that large groups of yahoo and aol etc emails do not hit the outbound queue at the same time. They tend to look down on that activity, and throttle you back, making it take even longer to get your messages out. You might put special-case conditons on specific domains, but actually, you're much better off ordering by domain because your server will end up sending _ONE_ message addressed to whatever number of recipients at that domain. If your list is truely massive, your MTA may be incapable of sorting the recipients itself: a good MTA will batch by recipient domain. An equal probability is that you're actually generating INDIVIDUAL messages (one per recipient), rather than messages with a BCC: recipient list, in which case, my advise would be to switch to BCC: addressing, but if that isn't an option, look at a cascaded MTA queue (messages which don't deliver on the first try get sent to a secondary queue which won't retry right away - and those which fail to deliver from that queue get moved to one that takes even LONGER) - a fairly typical (read: default Sendmail setup) queue retry is every 15 minutes, 24/7 --- if you've got a few hoser domains in there, they can stuff you up quick. Check your server logs to see if the domains you're emailing to are trying to perform callbacks (GTE and it's affiliated telco domains are/were doing this for some time - this is the same bunch of idiots who've blocked many european IP ranges from sending mail to them, and such sites have to relay through other hosts in order to deliver to GTE customers). Any domain that does this might be a candidate for being added to a special case handler to be shuttled to a low priority queue right off the bat. Might I suggest you set up a database for the special conditions and the queues you'd place them in? g --- Please DO NOT carbon me on list replies. I'll get my copy from the list. Founding member of the campaign against email bloat. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
You might put special-case conditons on specific domains, but actually, you're much better off ordering by domain because your server will end up sending _ONE_ message addressed to whatever number of recipients at that domain. If your list is truely massive, your MTA may be incapable of sorting the recipients itself: a good MTA will batch by recipient domain. An equal probability is that you're actually generating INDIVIDUAL messages (one per recipient), rather than messages with a BCC: recipient list, in which case, my advise would be to switch to BCC: addressing, but if that isn't an option, look at a cascaded MTA queue (messages which don't deliver on the first try get sent to a secondary queue which won't retry right away - and those which fail to deliver from that queue get moved to one that takes even LONGER) - a fairly typical (read: default Sendmail setup) queue retry is every 15 minutes, 24/7 --- if you've got a few hoser domains in there, they can stuff you up quick. Check your server logs to see if the domains you're emailing to are trying to perform callbacks (GTE and it's affiliated telco domains are/were doing this for some time - this is the same bunch of idiots who've blocked many european IP ranges from sending mail to them, and such sites have to relay through other hosts in order to deliver to GTE customers). Any domain that does this might be a candidate for being added to a special case handler to be shuttled to a low priority queue right off the bat. Might I suggest you set up a database for the special conditions and the queues you'd place them in? g Good comments, also, there is the option of simply interfacing mysql and your scripts with mailman, which is really one of the better ways to send mass emails, list serves pretty much have it down these days. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
I would also try to unorder the email addresses, so that large groups of yahoo and aol etc emails do not hit the outbound queue at the same time. They tend to look down on that activity, and throttle you back, making it take even longer to get your messages out. You might put special-case conditons on specific domains, but actually, you're much better off ordering by domain because your server will end up sending _ONE_ message addressed to whatever number of recipients at that domain. But some providers will block based on number of recipients per message... so don't go crazy sending one message to 50,000 aol addresses, etc... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mass E-mail Options
At 22:22 2006-08-30 -0500, Philip Hallstrom wrote: But some providers will block based on number of recipients per message... so don't go crazy sending one message to 50,000 aol addresses, etc... Which is why you'd have conditions for special-case domains, which I *DID* point out in my post, along with basic mail delivery logic (50K recipients would be a bad design anyway). Any MTA worth its salt will have configuration options to keep you from stepping on your d**k like this. Sendmail for instance: SMTP_MAILER_MAXRCPTS[undefined] If defined, the maximum number of recipients to deliver in a single connection for the smtp, smtp8, esmtp, or dsmtp mailers. IOW, this would automatically chunk deliveries to a given mailhost to so many recipients. See also queuegroup in sendmail documentation, which allows you to take mail destined for certain domains (or specific addresses) to go DIRECTLY to a specific queue (thus allowing you to configure at the MTA level directly for problematic domains). Yes, I realize the OP indicated they're using some other MTA - that of course is their problem, but what they might find in the sendmail docs (with which I personally am reasonably familiar) might clue them into what feature/keyword to investigate in the MTA they're saddled with. Or, opt to switch MTAs... It isn't as if you couldn't run sendmail bound on a particular IP address to run your webserver outbound mail queues separate from whatever your usual MTA is. I really suspect though if the chap's mail server is being dropped to its knees, it may be (tens of) thousands of individual messages, rather than a few messages with thousands of recipients.. When you're working with individual messages, the MTA has to negotiate EACH connection -- when you're working with multiple recipient BCC'd messages, the MTA is doing SMTP negotiation ONCE per recipient host, then pouring a pile of RCPT [EMAIL PROTECTED] commands at the host, followed by ONE copy of the message: *WAY* more efficient, both in terms of host connection time, as well as network bandwidth consumption. This approach isn't suitable if the messages are custom tailored to each recipient (ala mail merge). It isn't without irony that it is AOL whose postmaster guidelines give a preference to mailmerge style messages (such as one-click delisting for their users - something which requires a recipient-specific URL in the message). A large automotive website I work for has pretty much given up on dealing with AOL, who seems to randomly block senders because their turd-for-brains users use this is junk to uns*bscribe from lists rather than following directions. --- Please DO NOT carbon me on list replies. I'll get my copy from the list. Founding member of the campaign against email bloat. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
