Re: temporary tables on disk?
Baron Schwartz wrote on Thu, Sep 20, 2007 at 07:46:44PM -0400: Michael explained it well, but just to say it a different way, the temporary table is created as an intermediate step in the table, and is implicit, not explicit. So it's not sent to the slave -- the query is sent to the slave, and if the query optimizer makes the same decisions on the slave that it did on the master, the slave will also create a temporary table during query processing. i should have been more clear. what i meant is that the create temporary table query is sent to the slave, and the table is created on the slave. this much i know from slaves dying then not restarting due to the missing temporary table. my current problem is my slaves are creating myisam temporary tables on disk and alot of them. which eventualy results in mysqld no longer being able to open any files with ERROR: 1 Can't create/write to file '/sql/tmp/#sql17fb_2f_0.frm' (Errcode: 24) 070919 11:06:41 Slave: Error 'Can't create/write to file '/sql/tmp/#sql17fb_2f_0.frm' (Errcode: 24)' on query 'CREATE TEMPORARY TABLE aggregate ( along with messages like 070919 22:04:03 Error in accept: Too many open files what brought this all on was a switch to new hardware(completely new server) for the master. kept the same version of mysql on the new hardware, slaves didn't change at all, other then resetting up replication from scratch with the data snapshot from the current master after hardware swap. this didn't happen on the slaves prior to the hardware swap. i don't understand why the temp tables are being written to disk now, when they weren't in the past. Michael Dykman wrote: Temporary tables only exist for the length of time that the connection that created them remains connected and are only visible to that connection. There is no reason to replicate these to a slave at all, as no client connecting to that slave would ever be able to see them. - michael dykman On 9/20/07, Chris Scheller [EMAIL PROTECTED] wrote: according to http://dev.mysql.com/doc/refman/4.1/en/internal-temporary-tables.html temporary tables can sometimes be written to disk as myisam. in replication are these myisam temp tables sent to the slaves as myisam tables or in memory tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- ++---+---+ | Chris Scheller | http://www.pobox.com/~schelcj | JID: [EMAIL PROTECTED] | ++---+---+ | Ubuntu -- an African word, meaning Slackware is too hard for me | | Mandriva -- a French word, meaning Slackware is too hard for me| | Fedora -- an American word, meaning Slackware is too hard for me | |-- seen on alt.os.linux.slackware | ++ There was a boy called Eustace Clarence Scrubb, and he almost deserved it. -- C. S. Lewis, The Chronicles of Narnia pgpeqUn2WliIi.pgp Description: PGP signature
Re: temporary tables on disk?
There is a setting in your my.cnf which specifies the threshold at which temporary tables will be put to disk instead of being held in RAM. This has to be a dynamic decision as the system has to consider available RAM and the size of any given temporary table.. under normal circumstances, the my.cnf setting will be respected, but it is NOT a guarantee. Have you read this? http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_tmp_table_size - michael dykman On 9/21/07, Chris Scheller [EMAIL PROTECTED] wrote: Michael Dykman wrote on Fri, Sep 21, 2007 at 12:19:23PM -0400: Your issue might be related to the host OS, namely a per-process limit on file handles. What OS are you using? that is definitely part of the problem. since so many temporary tables are being created, mysqld just shits itself after awhile. my main problem, as i see it is that these temp tables are being written to disk in the first place. prior to my server switch this wasn't the case. in any case, the box is slackware-11.0 and this is mysql-4.0.17(yes i know it is old, and i did try to migrate to 5 with not fun results. updating this code base to support mysql-5.0.x, just isn't an option right now.) -- ++---+---+ | Chris Scheller | http://www.pobox.com/~schelcj | JID: [EMAIL PROTECTED] | ++---+---+ | Ubuntu -- an African word, meaning Slackware is too hard for me | | Mandriva -- a French word, meaning Slackware is too hard for me| | Fedora -- an American word, meaning Slackware is too hard for me | |-- seen on alt.os.linux.slackware | ++ I was playing poker the other night ... with Tarot cards. I got a full house and four people died. -- Steven Wright -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables on disk?
Michael Dykman wrote on Fri, Sep 21, 2007 at 01:37:57PM -0400: There is a setting in your my.cnf which specifies the threshold at which temporary tables will be put to disk instead of being held in RAM. This has to be a dynamic decision as the system has to consider available RAM and the size of any given temporary table.. under normal circumstances, the my.cnf setting will be respected, but it is NOT a guarantee. Have you read this? http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_tmp_table_size yes, several times. both max_heap_table_size and tmp_table_size have remained at the defaults. even still, these temp tables are not that big and _should_not_ be bumping into that limit. and they never have hit that limit in the four to five years this code has been in production. at the moment i have everything running and stable with all slaves replicating cleanly. -- ++---+---+ | Chris Scheller | http://www.pobox.com/~schelcj | JID: [EMAIL PROTECTED] | ++---+---+ | Ubuntu -- an African word, meaning Slackware is too hard for me | | Mandriva -- a French word, meaning Slackware is too hard for me| | Fedora -- an American word, meaning Slackware is too hard for me | |-- seen on alt.os.linux.slackware | ++ Lunatic Asylum, n.: The place where optimism most flourishes. pgpbbyOEiGP5U.pgp Description: PGP signature
temporary tables on disk?
according to http://dev.mysql.com/doc/refman/4.1/en/internal-temporary-tables.html temporary tables can sometimes be written to disk as myisam. in replication are these myisam temp tables sent to the slaves as myisam tables or in memory tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables on disk?
Temporary tables only exist for the length of time that the connection that created them remains connected and are only visible to that connection. There is no reason to replicate these to a slave at all, as no client connecting to that slave would ever be able to see them. - michael dykman On 9/20/07, Chris Scheller [EMAIL PROTECTED] wrote: according to http://dev.mysql.com/doc/refman/4.1/en/internal-temporary-tables.html temporary tables can sometimes be written to disk as myisam. in replication are these myisam temp tables sent to the slaves as myisam tables or in memory tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables on disk?
Michael explained it well, but just to say it a different way, the temporary table is created as an intermediate step in the table, and is implicit, not explicit. So it's not sent to the slave -- the query is sent to the slave, and if the query optimizer makes the same decisions on the slave that it did on the master, the slave will also create a temporary table during query processing. Michael Dykman wrote: Temporary tables only exist for the length of time that the connection that created them remains connected and are only visible to that connection. There is no reason to replicate these to a slave at all, as no client connecting to that slave would ever be able to see them. - michael dykman On 9/20/07, Chris Scheller [EMAIL PROTECTED] wrote: according to http://dev.mysql.com/doc/refman/4.1/en/internal-temporary-tables.html temporary tables can sometimes be written to disk as myisam. in replication are these myisam temp tables sent to the slaves as myisam tables or in memory tables? -- 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]
