Copying Table To Disk During Execution
I seem to be having an issue with a query's results being paged out to
disk during runtime. I notice in processlist it saying "Copying to tmp
table on disk".
select col1, col2, count(ip) as 'COUNT' from db.table group by 2 order
by 1, 3;
None of the columns are a primary/foreign or an index. This query
returns 92K rows and has taken anywhere from 50 seconds to run to over
1.5 minutes. The table size on disk is around 33MB.
After doing some research i found that i should be changing the
tmp_table_size system variable. Originally it was set to the MySQL
default, 33554432. I set the varaible to 90,000,000 and it still
started paging out to the disk (this was the only query running at the
time). I would think that 90MB in space would have been enough to
handle the query since that would assume over 600K row size.
I actually had some trouble setting the tmp_table_size for the global
variable but was able to change it as a session variable.
I have also tried altering the query_cache variable (which has only
slowed the query down - currently it sits at a size of 50MB) and also
the sort_buffer_size but neither have made a difference.
What is the magic variable that i should be messing with?
View Complete Forum Thread with Replies
Related Forum Messages:
Flush A Heap Table To Disk
For example if I have data that I want to be inserted in to a heap table (for optimisation reasons) but I don't want to risk losing *all* the data if mysql crashes. Is it possible to perform something like a flush so that if mysql does crash some of the data is saved.
View Replies !
Flush A Heap Table To Disk?
As the subject says, is it possible to flush the contents of a heap table to disk? For example if I have data that I want to be inserted in to a heap table (for optimisation reasons) but I don't want to risk losing *all* the data if mysql crashes. Is it possible to perform something like a flush so that if mysql does crash some of the data is saved.
View Replies !
MyISAM Table And Sort Order On The Disk
I ran the myisamchk tool with the options --sort-index --sort-records=1 against an MYI file. Is there something I can use to confirm what this command did? I would like to see what the sort order on the disk for each table in my database.
View Replies !
Copying A Table With Everything?
i need an efficient way to copy an entire table, with indices and all the other info contained in the current table. these tables are massive, some with over 350 million rows so i need a way to do this in a reasonable amount of time.
View Replies !
Copying Table
I'm trying to copy a table from one mysqld to another. I've tried things like : mysqldump -h remotehost --quick --single-transaction database table | mysql database but I don't get all the data. It only copies 16789 rows when the original table has 10018090 rows. I don't get any error messages or anything (that I've found).
View Replies !
Copying One Table Into An Other
I have one table Bin0 containing data and one empty table Bin1. Now I want to move all entries from Bin0 to Bin1. Both table only have one column. What is the best way to do this?
View Replies !
Copying A Table From One Schema To Another
Is it possible to copy a table from one schema to another schema by just copying the frm file to the directory for the new schema? What is the best way to create a new database with all the tables in it automatically? I was hoping to have the tables (the frm files) included in a subdirectory and when required, just create a new schema then copy all the frm files into it.
View Replies !
Copying A Table Using PhpMyAdmin
2 phpbb boards I run have been hacked. One I've restored but the other one has had some damage to the MySQL db. A table is missing. As it's a styles table I thought I could could copy a table, from a test board I set up, to the hacked board. I tried this using phpMyAdmin and I get this error: ....
View Replies !
Copying Table Data
Im currently writing an IT database for the company i work for *small thing, more for our use* to keep users and computers data upto date. Anyway things have developed and its going to push to company wide. So currently i have a colum structure as follows Code: UserName Department MachineNum
View Replies !
Copying Data To New Table
I created a new table for my data, and I'd like to copy the data from the first table to the second. The thing is, some of the fields have been removed, and some added. Is it possible to just copy over everything from the rows that has matching fields?
View Replies !
Copying Data From One Table To Another
I have a mysql database named swg_db in that database I have 2 tables, one named swg_user which contains the following fields: username, password, email the second table is named vbb_user which contains the following fields: username, user_password, user_email The data in table 1 in password and email is hosed, table 2 is not. I am trying to figure out the right commands, in the right order, to compare usernames, if they are the same, copy the user_password data from table 2 into the password field in table 1 and to copy the data from the user_email field in table 2 into the email field on table 1.
View Replies !
Copying A Large Table
I need to copy a large table without locking the database. So at the moment i have Insert into newtable select * from oldtable. This locks the database for 10 seconds which although doesn't sound like a lot of time is 2 much for what i need the database for!. I have looked at these commands FOR UPDATE. LOCK IN SHARE MODE. SQL_BUFFER_RESULT But I am not quite sure if any of these actually perform what i would like to do. The other option is to update the table line by line or in chunks using limit i suppose. But im sure there must be a better way.
View Replies !
Copying One Column From One Table To Another
I have a table, let's call it Foo, with a column of type int(11), let's call it Crud. I want to overwrite Foo.Crud with Bar.Var. Var is also type int(11), and happens to contain 1's and 2's. Of course I did my googling like a good newbie and got the following: update Foo,Bar set Foo.Crud = Bar.Var; This ran without error, but when I check it by running... SELECT COUNT( * ) AS Rows, Crud FROM Foo GROUP BY Crud ORDER BY Crud; +------+------+ | Rows | Crud | +------+------+ | 8801 | 1 | +------+------+ And it's NOT because Bar.Var only contains 1's. It contains 1's and 2's! SELECT COUNT( * ) AS Rows, Var FROM Bar GROUP BY Var ORDER BY Var; +------+------+ | Rows | Var | +------+------+ | 5043 | 1 | | 3758 | 2 | +------+------+........
View Replies !
Copying DB Table Definitions
I've been working on a MySql database project, and I will soon need to move to a different development machine. What is the best way to get all my tables set up identically on the new computer? I'm hoping there is some way to get MySql to generate (for an existing table) the code that would create that table if it were not already present. Right now the best solution I've found is going into MySql Query Browser and running a DESCRIBE command for each table. I can then right-click the resultset and export it to an html file. From each table's desription, I could easily re-create the tables on another machine.
View Replies !
Copying Data Within A Table
I have a table with about 30 columns and one index. I need to copy a subset of this table back into the same table while changing one value. Obviously this doesn't include the index. The value that I need to change is the one that I will query against to find the subset. IE: Select * from dogs where color = 'black' Now take the results of that query and copy it back into the same table with color='white'. The table has an idex with auto_increment and primary key. I don't want to do anything that would mess this up. Does this make sense? What Im trying to do is setup a demo of some software using copies of live data. I need to alter the live data a bit to protect the confidentiality of the customer(s). Rather than re-entering data for the demo Id rather copy and alter if its possible and no too huge of a PITA.
View Replies !
Copying A Column Within A Table II
I'm trying to copy a column (Open) from a table into another column (Close) from the same table but skipping the first "cell" from the source column (Open). I tried the following query: UPDATE Table SET `Open`=`Close` WHERE `ID`="2"; The query copies the whole source column (Open) into the destination column (Close) including the "cell" I'm trying to skip.
View Replies !
Copying Data Into Another Table?
Can someone please tell me how I can copy one item in one row into another table? I am wanting to do the above when a comment is being added to the second table. Thanks in advance. My tables are like this: Article (Articleid, selectDay, selectMonth, selectYear, article_item) Users (Userid, username, password, email, ip) Commentpost (Articleid, userid, comments, date_entered)
View Replies !
Automatically Copying Data From One Table To Another
im working on a project for uni and basically i want to know if its possible for a table to automatically copy data from one table into itself (they are both in the same database) i want to create a system where a user registers giving their password, i then want the username and password to be stored in a separate table where it is encrypted and when a user wants to login into the system i want their details to be checked against the details in the table where everything is encrypted.
View Replies !
Copying Missing Items From One Table To Another
two structurally identical tables (national_db_1.users and national_db_2.users) in two different databases. I need to find what's missing in one and add it from the other. I would like to insert records from national_db_1.users, that are not currently in national_db_2.users, into national_db_2.users. Also, the entire row(s) should be copied, with the exception of the primary key, which can be automatically generated by the insert
View Replies !
Copying Selected Data From Two Tables Into One Table
I have a list of members in my "members" table, and they can buy tickets for an event. When they buy them, their member id number and the ticket info is stored in the "transactions" table. For the final "tickets" table, which I want to print out to put on the door, I also need to have their email and name, which is in the "members" table but not the "transactions" table. What I want to do is insert bits of the "transactions" table into the "tickets" table, and then also use the member id number from the "transactions" table to lift out the corresponding member name and email address and insert them in as well. Here's what I tried before I realised that I probably need something quite a bit more complicated: INSERT INTO tickets (SELECT transaction_id, ticket_member_id, ticket_code FROM transactions), (SELECT name, email FROM members WHERE member_id=ticket_member_id) member_id is the one in the "members" table. Basically, here's what I want the "tickets" table to look like (the columns): transaction_id (from "transactions") ticket_member_id (from "transactions") ticket_code (from "transactions") name (from "members" using ticket_member_id) email (from "members" using ticket_member_id) I think I need to use a while to iterate through everything but just thought I'd check in case there's an easy way.
View Replies !
Taking Colomns Of Data And Copying It To A Different Table
I've got two pieces of software running and I want to force users to only use the one log in so I though rather than trying to build a plug in I thought a cron job would no-doubt be easier. Now getting the data I want from the right table is easy enough for me but as the columns have different ID's I need to make it transpose it into the right columns... It runs but I suspect the second part of my code is failing. Code:
View Replies !
"table Is Read Only" After Copying Table Files To New Server
I'm trying to port my MySQL tables for a database called "tracerlock" from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a ".MYD", ".MYI" and ".frm" file for every table in the database. So after creating a database called "tracerlock" on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user "mysql" in the "mysql" group. Now, when I connect to MySQL on the new machine and load the tracerlock database, "show tables" shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: mysql> INSERT INTO test VALUES(3); ERROR 1036: Table 'test' is read only However, if I create a new table called test2, I can perform INSERT statements on it with no error: mysql> insert into test2 values(3); Query OK, 1 row affected (0.00 sec)
View Replies !
Can't Move Log To Another Disk
I want my 4.1.12-max-log mysql server to log to the other folder than data folder. I did many reconfigurations taken from documentation and nothing worked! Can anybody help mi giving "newbie style" step by step instruction? Or - better - to ask me questions like: "did you use this or that variable in my.cnf" or "did you read this part of documentation"
View Replies !
Hard Disk
When I sucessfully create a databse in the MySQL command line client, where exactly in the C drive does the database gets stored? I created a databse and than I used the windows search functionality to search for that databse. I am getting a result that says the database dosen't exist. But when I type in the show databases; command in the command line client, it shows that the databases exists, but I would like to know where.
View Replies !
Disk Quota In MySQL
Is there a way to assign a limit on the size of a specific database? For example database A can take up no more than 500 MB. One of my databases can potentially grow without any bounds and possibly stop other critical databases from operating. We are using MySQL 4.0.21 on Linux. We use innodb tables.
View Replies !
Showing Disk Usage
How can I see how much disk space is being occupied by an InnoDB table? Looking into /var/lib/mysql doesn't seem to work anymore for InnoDB tables. Please tell also if there is a way to see how much space is occupied by a particular row or column of a table.
View Replies !
Running MtSQL On A RAM Disk
I am trying to set up the same thing but have been stopped right from the get go as I can not get a larger than 2G RAM disk going (using a 64 bit Ubuntu installation). I am hoping someone who has gone through the basic set up might get me squared away to start. An offline conversation would be fine and perhaps save the rest of y'all wading through said basics.
View Replies !
Restore Database From Another Disk
I'm wanting to restore my old database by copying the contents of the old /var/lib/mysql folder to the new server, but this isn't working. Is there a documented procedure for doing this? I'm using InnoDB and MyISAM.
View Replies !
Calculating Disk Space
I was wondering, is there a way to calculate the overhead in disk space for a table that contains column types that are only regular ints and floats. The table type is MyISAM, but I'd also like to know for other tables how to find this overhead. Basically I'm looking for a formula. If that's possible?
View Replies !
Disk Based Cluster
I want to know mysql support disk based cluster. Because i want to migrate on mysql cluster 5.0 and i have 30 GB database. If i mysql disk based cluster supports that much space than that would be great. Another question is that MyISAM storage engine in mysql supports table partioning ?
View Replies !
Ibdata1 Is Close To Disk Size
I am running MySQL 4.0.13 with my-medium.cnf (attached) on a Solaris 8, 4x450 Mhz, 4gb RAM. All my tables are InnoDB. Currently my ibdata1 is 1.8 gb. I have about 1 gig space left on the same partition. Also it is excruciatingly slow now. How do prevent the ibdata1 from filling up the disk partition ? How do I increase the speed on my MySQL? [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K myisam_sort_buffer_size = 8M query_cache_size = 16M query_cache_limit = 8M log-bin server-id = 1 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /usr/local/mysql/var/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /usr/local/mysql/var/ #innodb_log_arch_dir = /usr/local/mysql/var/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 ( I had to comment the innodb to stop crashing ) [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
View Replies !
Free Disk Space On DELETE
I'm building a huge DB. One particular table can contain as much as 125 million records. I need to DELETE records older than a year. That part works fine but it doesn't free any space on my hard drive (C:/). I tried OPTIMIZE TABLE and it still won't free hard disk space.
View Replies !
Data Location On Hard Disk
I have just installed MySQL 5.0 and finished the first test from a database application. I saw that the data is stored in c:program...MySQl... I am not happy with that. I want MySQL stores all data in a map of my own choice.
View Replies !
Moving Mysql To Another Hard Disk
I have a web based intranet application (php) that uses a mysql database. The server has just had a new drive added to it and am looking for a way to move the database from the current c drive to this new drive.I'm looking for a way to either: move the entire database to the new drive (in which case do i have to do anything within the code - the database name is still the same and i'll presume another drive is still localhost can) Can i just make Mysql save data into the new drive (and also move just the old data file to the new drive leaving everything else as it was).
View Replies !
Trouble Moving Data To Different Disk
Basically, what I've done is: 1) stopped mysqld. 2) made a place to put the files on the other disk. 3) copied the files to the other disk. 4) changed datadir and socket values in my.cnf. 5) created a symbolic link from the old data directory to the new. 6) chowned the new data directory and the old to mysql just in case. When I try to start mysqld, the service says it fails, but the log says that the service started just fine and it's ready for connections. Also, there are 2 processes running: /bin/sh /usr/bin/mysqld_safe (root) and /usr/libexec/mysqld (mysql). I can stop mysqld and it returns [OK].
View Replies !
Ending Of Execution
is it important to free resources and end connection after every code execution... i found a few codes examples and they don't usually end with DIE or EXIT... so i'm curious if thats a security risk?
View Replies !
Execution Time
After we execute a statement in mysql, it reports the query execution time in the format: n rows in set (t sec). We may do something like this to get the execution time: $time_start = getmicrotime(); mysql_query( "" ); $time_end = getmicrotime(); $time = $time_end - $time_start; But it includes extra CPU time to run two getmicrotime() functions and one subtraction. Is there any way to obtain the execution time directly from mysql without running extra functions or calculations?
View Replies !
Log Execution Queries
I am writing a program in python, and I use mySql v1.2.12, I wanna know If mysql logs any command such as wxecution commands(like INSERT, UPDATE, DELETE), if yes how can i access them? thanks all
View Replies !
Asynchronous Execution
I have the following situation php file 1: an SQL writes data to the database php file 2: an SQL reads data from the database Some users report errors that MIGHT be due to the SQL in file 1 not being executed, before they click on a link to open file 2. So they get out of date information. I want to know if this is technically possible, whether this might indeed be the problem, that if a database is very busy, that SQL statements are queued and data retrieval may read obsolete out of date information, because the data storage SQL has not been executed yet. And if this is the problem, I would like to wait in file 1 until the data has actually been written.
View Replies !
Release Disk Space By Drop Database ?
OS. linux 2.4.22 MYsql : 4.0.18-standard I created a database and populate 2 big InnoDB tables, the total size is > 3G. After all testing, I droped the database . But, the harddisk space is not release to the filesystem, Why, How could I recover that ?
View Replies !
Release Disk Space By Drop Database
OS. linux 2.4.22 MYsql : 4.0.18-standard I created a database and populate 2 big InnoDB tables, the total size is > 3G. After all testing, I droped the database . But, the harddisk space is not release to the filesystem, Why, How could I recover that.
View Replies !
Random MYI MYD Files Generated Consuming Disk
named as follows: #sql_5d7e_0.MYI #sql_5d7e_9.MYI #sql_5d7e_6.MYI #sql_5d7e_2.MYI #sql_5d7e_5.MYI #sql_5d7e_5.MYI etc., etc. In my error log I saw the following: Incorrect key file for table '#sql_2684_1.MYI'; try to repair it (many of these for each "table")
View Replies !
|
TRACKER
