Re: very large HEAP-tables in 4.1.3
harrison, thanks for you mail, I think mysql uses way too much memory (overhead) to store my data. How much overhead do you think it is using? Each row is 61 bytes in geldbrief, which is *exactly* the amount needed for the datatypes you have. [...] Now if you take 61 * 2449755 (number of rows) = 149435055 bytes used 157468096 Real amount 149435055 Data size - 8033041 Overhead 5.1% Total overhead of data I don't see how you could get it to be much smaller than that. Even with zero overhead it would only be 5% smaller, which still would require *a lot* of memory to store it all. The primary key itself is only using about 8 bytes of memory per row (because it is a hashed index, btree would be much larger), which is also very compact. With your own in-memory database, do you some sort of compression algorithm? That is the only way that I could see it taking up much less space. MySQL is pretty close to as efficient as you can get without compression. I did that calculation after my last post, too. I should have done that earlier ;) There is compression in our old solution, but I never thoght it was very efficient. I was definitly wrong, we get around 20% more data in our old database. That was what I thought mysql's overhead has to be since I didn't take the compression to serious. With all of that being said, I would just go with InnoDB, which can buffer the data in memory as well. In a later email you mention that you need to delete a lot of rows per hour. HEAP wouldn't work all that well for that since it uses table level locks. If it took 5 seconds to delete a large portion of rows, then the table would be locked for the duration of that. InnoDB, with its row level locking, would be much better for that purge process. If you turn off the innodb_flush_log_at_trx_commit (which would most likely be OK if your case) then most of the inserting would be done in memory anyways, and only written to disk in batches. The other option is as you said before using myisam merge tables. That will make dropping old records easier if you partition the data into the separate days. The only thing I would be worried about in that scenario is if inserts are coming from multiple threads. In that case you could still possibly run into locking issues with the inserts locking each other. I'm away next week, but I guess I'll just give all options a try on our development-system afterwards. I guess if something works with 2GB of RAM, it should also work with 6GB (Opteron). Or is mysql's behaviour changing with very big heap-tables or key_buffers? Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table and indexes
Is this normal to have a index like below.. As I know there is no need to have KEY and UNIQUE for a PRIMARY KEY PRIMARY KEY (`urun_id`), UNIQUE KEY `UC_urun_id` (`urun_id`), KEY `IDX_urun_urun_id` (`urun_id`), KEY `ktgr` (`ktgr`) ) TYPE=MyISAM Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.dalar.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mixing Innodb MyISAM tables
In article [EMAIL PROTECTED], sean c peters [EMAIL PROTECTED] writes: Im considering a design that mixes InnoDB and MyISAM tables. I want Innodb for speed, etc, but i have one table where i want a column to have a FULLTEXT index on. Thus the need for MyISAM. Im not worried about the performance in using the MyISAM tables, as we speak, the production version of the system is using MyISAM tables without problems. The main concern is that by using a MyISAM table, i lose foreign key support, and cannot do a cascade on delete, which i'd really like to have, and not have to manually mimic the cascade behaviour. I suppose i've gotten along fine without having foreign key support for a number of years, so this probably isnt that bad. Anyone else run into similar issues? Any thoughts? I'm using InnoDB almost exclusively because of the referential integrity it provides. I have only one table where I need a fulltext index. For that I split the table: almost all columns are in a InnoDB table, and the columns for the fulltext index, along with a copy of the primary key, are in a MyISAM table. I never use these tables alone; instead I always use tbl_InnoDB INNER JOIN tbl_MyISAM USING (pkey) This should somewhat protect me from the referential integrity problems introduced by using MyISAM. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstalling MySQL
---BeginMessage--- The current version was installed when I installed FEDORA 2 on my PC... I do not know what the RPMs are - Original Message - From: Michael Weiner [EMAIL PROTECTED] Date: Friday, August 6, 2004 8:53 pm Subject: Re: Uninstalling MySQL On Fri, 2004-08-06 at 20:49 -0400, [EMAIL PROTECTED] wrote: Greetings... How do I uninstall MySQL under linux (i have Fedora Core 2)... i will be installing a new version of it... I have 3.23 currently and will replace it with 4. Please Help Thanks You dont necessarily have to uninstall it, you COULD do an rpm upgrade if this was installed via RPM. 1) to remove, rpm -e package-name 2) to add, rpm -Ivh package-name 3) to update, rpm -Uvh package-name just watch for dependencies. HTH Michael Weiner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table and indexes
A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL will declare them so implicitly (and silently). A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY. (MySQL Manual | 14.2.6 CREATE TABLE Syntax) http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html --- Cemal Dalar [EMAIL PROTECTED] wrote: Is this normal to have a index like below.. As I know there is no need to have KEY and UNIQUE for a PRIMARY KEY PRIMARY KEY (`urun_id`), UNIQUE KEY `UC_urun_id` (`urun_id`), KEY `IDX_urun_urun_id` (`urun_id`), KEY `ktgr` (`ktgr`) ) TYPE=MyISAM Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.dalar.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Install 2 parallel versions?
Is it possible to install 2 versions of MySQL, 4.0.x and 4.1.x, on the same system? Do you just have to ensure that they are installed in separate, distinct, directories? Thanks for any help/advice! Nathan -- Nathan Mealey Director of Operations Cycle-Smart, Inc. P.O. Box 1482 Northampton, MA 01061-1482 [EMAIL PROTECTED] (413) 587-3133 (413) 210-7984 Mobile (512) 681-7043 Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install 2 parallel versions?
Yes, but there is a little more to it than separate install directories. In particular, the port, socket, and pid-file must be set differently for each server. You can find the details in the manual http://dev.mysql.com/doc/mysql/en/Multiple_servers.html. Michael Nathan Mealey wrote: Is it possible to install 2 versions of MySQL, 4.0.x and 4.1.x, on the same system? Do you just have to ensure that they are installed in separate, distinct, directories? Thanks for any help/advice! Nathan -- Nathan Mealey Director of Operations Cycle-Smart, Inc. P.O. Box 1482 Northampton, MA 01061-1482 [EMAIL PROTECTED] (413) 587-3133 (413) 210-7984 Mobile (512) 681-7043 Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to add time to NOW() function
Or simply NOW() + INTERVAL 60 SECOND Michael [EMAIL PROTECTED] wrote: Use DATE_ADD(NOW(),INTERVAL 1 HOUR) Or DATE_ADD(NOW(),INTERVAL 60 MINUTE) Or DATE_ADD(NOW(),INTERVAL 3600 SECOND) -Original Message- From: Deepak Dhake [mailto:[EMAIL PROTECTED] Sent: Saturday, August 07, 2004 1:58 AM To: [EMAIL PROTECTED] Subject: how to add time to NOW() function i want to add time to NOW() function, that means something like this, $addTime = 60; NOW() + $addTime; where 60 are seconds. I am not sure whether to add seconds or is there any other format I can use to add time to NOW() function? please let me know. thanks in advance. deepak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alternate directory for database
Is it possible to tell mysql to create the directory for a database in a specified directory rather than in mysql's data directory?
Re: Alternate directory for database
At 13:17 -0400 8/7/04, Steven Buroff wrote: Is it possible to tell mysql to create the directory for a database in a specified directory rather than in mysql's data directory? No. You can create the database, then (with the server down) move it where you want it and create a symlink in the original location that points to the new location. On Unix, use a regular symlink. On Windows, the procedure is described here: http://dev.mysql.com/doc/mysql/en/Symbolic_links.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uninstalling MySQL
On Friday 06 August 2004 19:49, [EMAIL PROTECTED] wrote: Greetings... How do I uninstall MySQL under linux (i have Fedora Core 2)... i will be installing a new version of it... I have 3.23 currently and will replace it with 4. Please Help Thanks I just did this a couple of weeks ago myself, with your same situation (where 3.23 was installed along with the usual server stuff w/ FC2). The online doc at www.mysql.com (select Developer Zone, then Documentation) has a section that describes where all of the files are for various types of installations. 2.1.5 Installation layouts I used these tables to find all the old mysql stuff and just deleted them all. Then I installed 4 via RPMs and it's been working like a champ since. I wrote up my experiences here: http://www.hentzenwerke.com/wp/mysql_installation.htm Feedback on the whitepaper would be appreciated if it works for you (or doesn't.) -- Whil Moving to Linux: Freedom, Choice, Security, Opportunity http://www.hentzenwerke.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Cache
We have the query cache turned on, and it appears to be working well. However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? We are using ColdFusion MX, which has its own query cache (which is very useful for whats called Query-of-query selects), but there the residence time is configurable, and the timer is restarted if the cached query is called before the timeout. Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why do exists In fail?
I'm trying to do some simple in or exists queries, but am getting syntax error on queries that appear to be correct. If I run the outside sub queries alone, not problem. In the In subquery, if I hardcode a value, ok but this fails: SELECT * FROM erestbase WHERE erestbase.id in(SELECT distinct idcid.id FROM idcid) This is stupid. Nothig would be wrong here in sqlServer etc. Both ids are integer values.
Re: Why do exists In fail?
At 15:35 -0700 8/7/04, john sayre wrote: I'm trying to do some simple in or exists queries, but am getting syntax error on queries that appear to be correct. If I run the outside sub queries alone, not problem. In the In subquery, if I hardcode a value, ok but this fails: SELECT * FROM erestbase WHERE erestbase.id in(SELECT distinct idcid.id FROM idcid) This is stupid. Nothig would be wrong here in sqlServer etc. Both ids are integer values. Is your version of MySQL older than 4.1? http://dev.mysql.com/doc/mysql/en/Subqueries.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why do exists In fail?
What version of MySQL are you using? Rhino - Original Message - From: john sayre [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, August 07, 2004 6:35 PM Subject: Why do exists In fail? I'm trying to do some simple in or exists queries, but am getting syntax error on queries that appear to be correct. If I run the outside sub queries alone, not problem. In the In subquery, if I hardcode a value, ok but this fails: SELECT * FROM erestbase WHERE erestbase.id in(SELECT distinct idcid.id FROM idcid) This is stupid. Nothig would be wrong here in sqlServer etc. Both ids are integer values. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
Yes, a query cache is only removed when one of it's tables is updated or the server is restarted. -Eric On Sat, 7 Aug 2004 23:16 +0100 (BST), Terry Riley [EMAIL PROTECTED] wrote: We have the query cache turned on, and it appears to be working well. However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? We are using ColdFusion MX, which has its own query cache (which is very useful for whats called Query-of-query selects), but there the residence time is configurable, and the timer is restarted if the cached query is called before the timeout. Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSH connection from a client machine - localhost works but not 127.0.0.1
On Monday 02 August 2004 22:34, James Weisensee wrote: What does your '/etc/hosts' file contain? Sounds like it may have the following entry: 127.0.0.1 localhost.localdomain change it to: 127.0.01localhost Actually, it has 127.0.0.1 localhost.localdomain localhost (two entries) Yes, add 'localhost.localdomain' to mysql.user Got it. another option, Why not just SSH to 'daisy' and issue: shell mysql -u root -p and let it default to localhost. Well, I did get that to work, but I'm trying to understand why... Michael's explanation of the difference between 127... and localhost with the sockets vs TCP made a lot of sense. -- Whil Moving to Linux: Freedom, Choice, Security, Opportunity http://www.hentzenwerke.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
At 23:16 +0100 8/7/04, Terry Riley wrote: We have the query cache turned on, and it appears to be working well. However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? Yes. Why would you remove a result from the cache if it's still current? Actually, there is another condition: (3) you issue a RESET QUERY CACHE statement, which clears the entire cache. We are using ColdFusion MX, which has its own query cache (which is very useful for whats called Query-of-query selects), but there the residence time is configurable, and the timer is restarted if the cached query is called before the timeout. Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Cache
In the last episode (Aug 07), Paul DuBois said: At 23:16 +0100 8/7/04, Terry Riley wrote: However, there appears to be no indication in the manual as to the time that a cached query remains in memory. In the absence of this information, is it safe to assume that a cached query remains there indefinitely, unless either (1) one of the tables used in the initial query is modified, or (2) the server is re-started? Yes. Why would you remove a result from the cache if it's still current? Actually, there is another condition: (3) you issue a RESET QUERY CACHE statement, which clears the entire cache. There's another one: (4) the data in the cache exceeds query_cache_size, in which case old cached results will get purged to make room for new ones. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
