Re: help needed restoring crashed mysql
Am 30.11.2011 03:13, schrieb Karen Abgarian: The concept is not difficult to explain. Most people do not expect a gas tank to shrink once the gas is consumed...right? yes, but the hard-disk is the gas tank and the data are the gas and yes, normally everybody would expect after deleting data that the space is available for other applications signature.asc Description: OpenPGP digital signature
Global Variables
Hi I can see different values when I run show global variables like . show variables like .? could any body please revert me on the scope and how they works?
Re: Global Variables
Am 30.11.2011 11:04, schrieb Stdranwl: Hi I can see different values when I run show global variables like . show variables like .? could any body please revert me on the scope and how they works? a global variable is global and for all threads set without global is only for the current connection signature.asc Description: OpenPGP digital signature
Re: Global Variables
DO NOT REPLY OFF-LIST i do not understand what is unclear you can set variables per sql and my.cnf most of them, not all the difference is set or set global global is what would be used if the thread does not change it the same as in php set a value per php.ini or .htaccess Am 30.11.2011 11:28, schrieb Stdranwl: Thnx... So does it mean that global value is accumulative values and it is the limit/end of the value which can be assigned.? On Wed, Nov 30, 2011 at 3:53 PM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote: Am 30.11.2011 11:04, schrieb Stdranwl: Hi I can see different values when I run show global variables like . show variables like .? could any body please revert me on the scope and how they works? a global variable is global and for all threads set without global is only for the current connection -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm signature.asc Description: OpenPGP digital signature
Re: [MySQL] innodb_file_per_table / apple workers and logic
Allright, that will do, I think? This is a MySQL mailinglist, let's not have it devolve into vendor rants. The defaults may or may not be sensible, but they're documented, and there's as much to say for sensible defaults as there is for not changing defaults between releases. Let's leave it at that, please. - Original Message - From: Reindl Harald h.rei...@thelounge.net To: mysql@lists.mysql.com Sent: Wednesday, 30 November, 2011 9:16:51 AM Subject: [MySQL] innodb_file_per_table / apple workers and logic the defaults should not needed to bex explained they should be expected WHO THE FUCK is comparing computers with a gas tank? sorry but this is bullshit and can only come from @apple.com workers which is the comapny who sold over years much to expensive X-Serve with f**ng Hitatchi Deskstar disks and it seems you have never seen a real computer with server-hardware nor had to pay one as we migrated to dbmail our mail-volume was the double size as now two years later because there was one customer exceeding all limits and on the old machines where no quotas and we decided to get rid of him because this and other reasons and better do not start a braindead discussion why there was no quotas because the old machine was a apple x-serve with a f**ing eudora mail server and was migrated by me to a real server and it was not my decision use your companies crap much too long fact is that because using files_per_table i could reduce the datasize to the half and on the slave is running a weekly backup and one last-week accessable via imap for imapsync so we have only here FOR copies of the data additionally a daily offsite-backup is running oh and not to forget: VMware-Data-Recovery and last but not list two external 4 TB disks (full encrypted), one inhouse and one outside to cover cases where the SAN storage or whatever is damaged damned we are speaking about DEFAULTS making life hard or not so hard and apple is one of the companies which are resposible that more and more braindead zombies are runnin out there by telling the world that nobody should have to read any documentation - and now your arguments are it is a dead end - well, it's your companies philosophy -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Global Variables
- Original Message - From: Reindl Harald h.rei...@thelounge.net DO NOT REPLY OFF-LIST Also, do not shout :-) the difference is set or set global Same for show, and 'show variables' is implicitly the same as the clearer 'show session variables'. global is what would be used if the thread does not change it That is, at connect time all the session variables are initialized from the global variables; and the session can then override them. For status variables (like com_*), the globals are cumulative counters, while the session ones are this-session counters. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Global Variables
DO NOT REPLY OFF-LIST Also, do not shout :-) Ugh point taken, but why are you still replying to him off-list? Keep inboxes clean! ;) -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help needed restoring crashed mysql
2011/11/29 23:19 +0100, Reindl Harald MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database And are then the table-files in the directories with frm, or in the directory where ibdata1 is? If the latter, one problem is exchanged for another. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: help needed restoring crashed mysql
Am 30.11.2011 07:02, schrieb Hal?sz S?ndor: 2011/11/29 23:19 +0100, Reindl Harald MY only luck is that i recognized this years ago after PLAYING with innodb and so i started with innodb_file_per_table=1 from the begin with the first production database And are then the table-files in the directories with frm, or in the directory where ibdata1 is? If the latter, one problem is exchanged for another. they are in the db-folder but even if not it is a hughe differene if optimize table tablename free space on disk or not [root@mail:/mysql_data]$ ls insgesamt 3,0G drwx-- 2 mysql mysql 4,0K 2011-11-25 10:27 dbmail drwx-- 2 mysql mysql 4,0K 2011-11-20 17:46 mysql drwx-- 2 mysql mysql 4,0K 2011-11-20 17:46 performance_schema drwx-- 2 mysql mysql 4,0K 2011-11-30 04:00 syslog -rw-rw 1 mysql mysql 354M 2011-11-30 14:01 ibdata1 -rw-rw 1 mysql mysql 512M 2011-11-30 14:01 ib_logfile0 -rw-rw 1 mysql mysql 512M 2011-11-30 02:21 ib_logfile1 -rw-rw 1 mysql mysql6 2011-11-20 17:46 mysql_upgrade_info -rw-rw 1 mysql mysql 1,1G 2011-11-29 15:21 bin.000137 -rw-rw 1 mysql mysql 617M 2011-11-30 14:01 bin.000138 -rw-rw 1 mysql mysql 72 2011-11-29 15:21 bin.index [root@mail:/mysql_data]$ ls dbmail/ insgesamt 9,5G -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 cms1_config.frm -rw-rw 1 mysql mysql 8,7K 2011-09-18 08:18 cms1_global_cache.frm -rw-rw 1 mysql mysql 9,4K 2011-09-18 08:18 cms1_haupt.frm -rw-rw 1 mysql mysql 8,6K 2011-09-18 08:18 cms1_locks.frm -rw-rw 1 mysql mysql 9,8K 2011-09-18 08:18 cms1_meta.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 cms1_snippets.frm -rw-rw 1 mysql mysql 11K 2011-09-18 08:18 cms1_sub2.frm -rw-rw 1 mysql mysql 11K 2011-09-18 08:18 cms1_sub.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 cms1_user_group_permissions.frm -rw-rw 1 mysql mysql 8,6K 2011-09-18 08:18 cms1_user_login.frm -rw-rw 1 mysql mysql 9,6K 2011-09-18 08:18 cms1_user_modules.frm -rw-rw 1 mysql mysql 8,5K 2011-09-14 09:32 cms1_user_online.frm -rw-rw 1 mysql mysql 9,3K 2011-09-18 08:18 cms1_users.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_aliases.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_aliases_global.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_allowed_hosts.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_client_admins.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_clients.frm -rw-rw 1 mysql mysql 8,8K 2011-10-22 20:18 dbmail_acl.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_aliases.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_auto_notifications.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_auto_replies.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_ccfield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:18 dbmail_datefield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:19 dbmail_envelope.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:19 dbmail_fromfield.frm -rw-rw 1 mysql mysql 8,4K 2011-10-22 20:19 dbmail_headername.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:19 dbmail_headervalue.frm -rw-rw 1 mysql mysql 8,9K 2011-10-22 20:20 dbmail_mailboxes.frm -rw-rw 1 mysql mysql 8,6K 2011-07-24 12:17 dbmail_messageblks.frm -rw-rw 1 mysql mysql 8,9K 2011-10-22 20:20 dbmail_messages.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_pbsp.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_physmessage.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_referencesfield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_replycache.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_replytofield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_sievescripts.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_subjectfield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_subscription.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_tofield.frm -rw-rw 1 mysql mysql 8,5K 2011-10-22 20:21 dbmail_usermap.frm -rw-rw 1 mysql mysql 8,9K 2011-10-22 20:21 dbmail_users.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_mta.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_recipient_relay.frm -rw-rw 1 mysql mysql 1,8K 2011-11-25 10:27 dbma_recipients.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_reply_groups.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_rewrite_domains.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_rewrite_senders.frm -rw-rw 1 mysql mysql 8,5K 2011-09-18 08:18 dbma_sender_relay.frm -rw-rw 1 mysql mysql 8,4K 2011-09-18 08:18 dbma_spamfilter.frm -rw-rw 1 mysql mysql 1,7K 2011-07-24 11:49 dbma_stats.frm -rw-rw 1 mysql mysql 8,5K 2011-08-27 22:39 dbma_transports_error.frm -rw-rw 1 mysql mysql 1,5K 2011-07-24 11:49 dbma_transports.frm -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm -rw-rw 1 mysql mysql 64K 2011-09-18 08:18 cms1_config.ibd -rw-rw
Re: Global Variables
Ok it is fine . but I just wanted to to know on following: Let say sort_buffer_size is set 10 M and Globally it is set 3G so how it will be used? On Wed, Nov 30, 2011 at 8:47 PM, Paul DuBois paul.dub...@oracle.com wrote: On Nov 30, 2011, at 4:04 AM, Stdranwl wrote: Hi I can see different values when I run show global variables like . show variables like .? could any body please revert me on the scope and how they works? These sections of the MySQL manual may be of interest: http://dev.mysql.com/doc/refman/5.5/en/using-system-variables.html http://dev.mysql.com/doc/refman/5.5/en/show-variables.html http://dev.mysql.com/doc/refman/5.5/en/set-option.html Individual variable descriptions are here: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html Dynamic means a variable can be set at runtime with SET. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com
Opentaps database
Did anybody from group get a chance to work on opentaps' DB ... It will be great help if some config changes and optimization tricks, somebody can suggest me keeping in mind 64G RAM?
Re: Global Variables
So then no use of setting 3G sort buffer in my.cnf untill system will take a rebot? I was in the impression that 10M will be used and it will be scratched to 3G whenever required as same is set globally? On Wed, Nov 30, 2011 at 10:22 PM, Paul DuBois paul.dub...@oracle.comwrote: The session value (10M in your case) will be used for queries issued in that session. On Nov 30, 2011, at 9:38 AM, Stdranwl wrote: Ok it is fine . but I just wanted to to know on following: Let say sort_buffer_size is set 10 M and Globally it is set 3G so how it will be used? On Wed, Nov 30, 2011 at 8:47 PM, Paul DuBois paul.dub...@oracle.com wrote: On Nov 30, 2011, at 4:04 AM, Stdranwl wrote: Hi I can see different values when I run show global variables like . show variables like .? could any body please revert me on the scope and how they works? These sections of the MySQL manual may be of interest: http://dev.mysql.com/doc/refman/5.5/en/using-system-variables.html http://dev.mysql.com/doc/refman/5.5/en/show-variables.html http://dev.mysql.com/doc/refman/5.5/en/set-option.html Individual variable descriptions are here: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html Dynamic means a variable can be set at runtime with SET. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com
Re: Global Variables
- Original Message - From: Stdranwl stdra...@gmail.com So then no use of setting 3G sort buffer in my.cnf untill system will take a rebot? I was in the impression that 10M will be used and it will be scratched to 3G whenever required as same is set globally? No, you've got it the wrong way round. When a new client connects, it's session variables are initialised from the global values. Thus, if your global value is 3G, new clients will get a session value of 3G. If your appplication or the client subsequently decides to set the session value to 10M, *then* it will be 10M. Open a new terminal and make a new connection, then look at the session variables. If they're still different from the global value, something is setting them upon connect. Check for a .my.cnf file in your homedir, aliases or wrapper scripts for the mysql client, etc. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: [MySQL] innodb_file_per_table / apple workers and logic
Hi inline there. On 30.11.2011, at 0:16, Reindl Harald wrote: Most people do not expect a gas tank to shrink once the gas is consumed...right? WHO THE FUCK is comparing computers with a gas tank? Well, I do. I even managed to do it without using foul language. Forgot to say. Opinions said here are mine and aren't official position of my company. I have also not said anything about Apple products. I did not even imply using OSX as the OS for MySQL. I appreciate that the above could be given some understanding and, if not too much to ask, respect. I didn't have a pleasure to use dbmail. I presume it does something with mail users. Thinking logically, if I got rid of my biggest mail user, I might eventually get another user, even bigger one, which would consume the same space vacated by the deceased user. So why would I want to give up the space then? sorry but this is bullshit and can only come from @apple.com workers which is the comapny who sold over years much to expensive X-Serve with f**ng Hitatchi Deskstar disks and it seems you have never seen a real computer with server-hardware nor had to pay one as we migrated to dbmail our mail-volume was the double size as now two years later because there was one customer exceeding all limits and on the old machines where no quotas and we decided to get rid of him because this and other reasons and better do not start a braindead discussion why there was no quotas because the old machine was a apple x-serve with a f**ing eudora mail server and was migrated by me to a real server and it was not my decision use your companies crap much too long fact is that because using files_per_table i could reduce the datasize to the half and on the slave is running a weekly backup and one last-week accessable via imap for imapsync so we have only here FOR copies of the data additionally a daily offsite-backup is running oh and not to forget: VMware-Data-Recovery and last but not list two external 4 TB disks (full encrypted), one inhouse and one outside to cover cases where the SAN storage or whatever is damaged There are a lot of things in this life to be upset about. Empty gas tanks is one thing. But I would not spill all that frustration on the very first person I meet on the net. Taking the logical part of what was said above, there existed a database that possibly was able to save the space by using files_per_table. Does this somehow mean that there are no other databases in the world? If the people do not have the knowledge to do exports/imports, the brand new and cool file management feature will not help them either. damned we are speaking about DEFAULTS making life hard or not so hard Oh, we do? So we kinda consider it as given that MySQL has to wiggle out his way to suit our task, without us bothering about it. That would be so nice... Essentially, the instructions how to use that feature are written in the same book as the instructions how to do export/imports. If we consider it as given that people would never read, it is a dead end. and apple is one of the companies which are resposible that more and more braindead zombies are runnin out there by telling the world that nobody should have to read any documentation - and now your arguments are it is a dead end - well, it's your companies philosophy Such philosophy is not Apple's. It is common sense. However, it only applies to the products, all components of which are controlled by the vendor. For example, if you buy yourself a TV, lucky you, you want to be able to turn it on and off without reading the documentation. But the database server is NOT the product to which you want to apply such logic. It is part of other things that cannot be controlled by the vendor: for example OS below or application on top. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: [MySQL] innodb_file_per_table / apple workers and logic
Am 30.11.2011 19:13, schrieb Karen Abgarian: Hi inline there. On 30.11.2011, at 0:16, Reindl Harald wrote: Most people do not expect a gas tank to shrink once the gas is consumed...right? WHO THE FUCK is comparing computers with a gas tank? Well, I do. I even managed to do it without using foul language. what answer do you expect comparing a database with a gas tank while the gas tank is the hard-drive? if i take some gas out of the tank (hard-drive) i expect that there is space for new one There are a lot of things in this life to be upset about. Empty gas tanks is one thing. But I would not spill all that frustration on the very first person I meet on the net. my frustration is people like you comparing a database with a gas tank while not understand that the gas tank is the underlying hard-disk if you stop make laughable comparison you will not get back frustration Taking the logical part of what was said above, there existed a database that possibly was able to save the space by using files_per_table. Does this somehow mean that there are no other databases in the world? have i said this? a default which makes it unable to free no longer used space is dumb not more and not less signature.asc Description: OpenPGP digital signature
Re: [MySQL] innodb_file_per_table / apple workers and logic
Hello all, On 11/30/2011 16:46, Reindl Harald wrote: Am 30.11.2011 19:13, schrieb Karen Abgarian: Hi inline there. On 30.11.2011, at 0:16, Reindl Harald wrote: Most people do not expect a gas tank to shrink once the gas is consumed...right? WHO THE FUCK is comparing computers with a gas tank? Well, I do. I even managed to do it without using foul language. what answer do you expect comparing a database with a gas tank while the gas tank is the hard-drive? if i take some gas out of the tank (hard-drive) i expect that there is space for new one Actually, the gas tank is a good analogy. There is limited volume in a vehicle which must contain the tank. In this analogy, the vehicle must have space for not just fuel but passengers, cargo, engine, transmission, etc. The fact that the tank may grow so large it displaces other items from the vehicle is appropriate to the original situation (no room left on disk). There are a lot of things in this life to be upset about. Empty gas tanks is one thing. But I would not spill all that frustration on the very first person I meet on the net. my frustration is people like you comparing a database with a gas tank while not understand that the gas tank is the underlying hard-disk if you stop make laughable comparison you will not get back frustration I am sorry if you didn't see the larger picture she was trying to present. Taking the logical part of what was said above, there existed a database that possibly was able to save the space by using files_per_table. Does this somehow mean that there are no other databases in the world? have i said this? a default which makes it unable to free no longer used space is dumb not more and not less There are expenses to maintaining separate files per table that you do not have for the larger, more inclusive tablespaces. Individual tablespaces can become so numerous that your system may run out of file handles to operate them all, for example. All of those file names may clog your directory/folder system making it much slower to randomly access any one file, as another example. While it is true that recovering unused space may be useful to restore disk space, it is also true that allocating and deallocating disk space is an expensive action. It is much more efficient in the long run to leave an expanded file in its larger state than it would be to constantly be shrinking it to a minimal size. Also, since the user required that much space at least once before (by their query or usage patterns) it is reasonable to assume that the same working space (or more) will be used again in the future. So this puts the 'design decision' squarely on the side of 'always increase, never decrease' in order to minimize the disk allocation costs associated with operating the InnoDB storage engine. There are other storage options (MyISAM, Archive, CSV, etc) in the event this behavior of InnoDB is more than you want to deal with. The default to NOT use individual tablespaces is related to the need to potentially adjust OS-level limits to handle the additional file volumes. If that is not a problem for you and your admins, more power to you. For desktop users, however, that may not be an option they can use. So the default remains at 0 until the support for it becomes much more common among Linux user accounts. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
