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 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 !
ORDER BY W/ HEAP Table Type
I am creating an online application that lists the last 50 users that loaded a page. I used a InnoDB table type for storing most information (which rarely changes), but was thinking of moving the UPDATE and SELECT function that tracks who last clicked something to a HEAP table for speed. I think I would be OK with the UPDATE to a HEAP table, but I have read that ORDER BY cannot be used on HEAP indexes. But, do I have to use an Index? Can I make a SELECT like this on a HEAP table that has only two columns (login and datenow)? $queryonlineusers="SELECT login FROM mmih_users ORDER BY datenow DESC LIMIT 50";
View Replies !
MySQL Database Table - PhpMyAdmin - FLUSH
We are having problems with our banner ads - clicks deliver you to our homepage rather than the assigned URL. Our banners run on php/mysql based software and I believe the problem is a build up of logs and that cronjobs set to deal with them are not functioning as they should. Knowing very little about such things and awaiting tech support to look into it and resolve the issue I poked around the database and noticed the "Table Maintenance" area and a "FLUSH" link. MySQL says this clears the cache. I'm tempted to flush the tables I think are having problems to see if that resolves it temporarily but don't want to mess anything up. Any thoughts on all this? Will "FLUSH" do anything I might regret?
View Replies !
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 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 !
HEAP So Slow
I have a heap database, with 1.5-1.6 milion rows. on that is 2 columns... ID | Title title is indexed. When i run a query like this ------------------ SELECT index_data.* FROM index_data INNER JOIN `index` ON index_data.id=index.id AND index.title LIKE '%$query%' WHERE playtime > $dur... The execution time is about seconds... ------------------ Even a single like statement just on `index` (heap) takes 3-6 seconds. Here's the table stats... ---------------------------- Data 397,442 KB Index 24,639 KB Total 422,081 KB ---------------------------- Why??
View Replies !
Heap Vs InnoDB Vs MyIsam
I'm trying to improve performance of our website, the boss is complaining that its too slow (though it is faster than a lot of sites). We experience particular slow down during updates - which are done periodically throughout the day. We have nearly 2 million records that are being searched and theres a lot of information there to pull back. Currently all the tables are myIsam, and the total size is around 1.5Gb but the tables being searched probably total 600Mb. We're currently using all 2GB memory at high loads. I tried converting the main search table to InnoDB but that just made everything horrendously slow - I also heard that it limits to 200 inserts per second whereas with myIsam we're getting around 1k - 1.5k per second. Could the slow down be due to not converting the linked tables to innoDB? Or just lack of caching? I now HEAP is supposed to be super fast for selects, how is it for inserts? Do you think 600Mb database would be ok to be loaded into 2Gb memory and still cope with high traffic loads (relatively high - approx 500 simultaneous users) Or should I stick with MyIsam?
View Replies !
Flush Logs
MySql version 5.0.27 on Windows 2003 I tried: I log in to my cmd with: mysql -h localhost -u user -p mysql> cd mysql-data-directory mysql> mv mysql.log mysql.old mysql> mv mysql-slow.log mysql-slow.old mysql> mysqladmin flush-logs and C:> mysql -h localhost -u user -p -e "cd mysql-data-directory;mv mysql.log mysql.old;mv mysql-slow.log mysql-slow.old;mysqladmin flush-logs;" Enter password: ****** Error 1064 (42000) at line 1: You have an error in your SQL syntax... My server.log (in the example mysql.log) is 2 GB now and the server-slow.log (mysql-slow.log) about 650mb.
View Replies !
Flush Privelege
The problem began with that I had trouble to log in to the server, because my local IP address changed. My user id at the new client IP address was not allowed to connect to the server.I then logged in to the unix box, run the mysql through unix command line as root.Then I tried to grant the access to the my user id at my new local IP address. I was told that user root@localhost doesn't have the access right. I checked the grant privilege of the root@localhost, it had all the rights. Don't know where is the error. Until I run the flush priviledge command, sign of the mysql, and sign in back to mysql (all these activities are run through unix command line at the localhost.), then i run the grant command, now everything was fine. access was granted to my user id at the new IP address and then I could access to the mysql server from my desktop pc again.
View Replies !
Flush Hosts
Here's the session for my flush-hosts attempt: login as: root Password: root@delphi:~# mysqladmin -uroot -p flush-hosts Enter password: root@delphi:~# mysqladmin -uroot -p refresh Enter password: root@delphi:~# I am getting this error: Error MySQL said: #1129 - Host '' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'
View Replies !
Flush Privileges
When this code gets to the FLUSH PRIVILEGES, it dies (displays Error, insert query failed flush)? Code: ... <?php if(isset($_POST['submit'])) { include '../library/config.php'; include '../library/opendb.php'; $title = $_POST['title']; $body = $_POST['body']; $query = "INSERT INTO articles (title, body, authorid) VALUES ('$title', '$body', '1')"; mysql_query($query) or die('Error, insert query failed'); $query = "FLUSH PRIVILEGES"; mysql_query($query) or die('Error, insert query failed flush'); include '../library/closedb.php'; echo "<p>New article added.</p><a href='index.php'>Go to all articles.</a>"; } else { ?> <form method="post"> ...
View Replies !
Flush Tables
What exactly does the command FLUSH TABLES do? I'm not sure if its a SQL command, but it does exist as a mysqladmin command.
View Replies !
FLUSH TABLES Via C Query API
Using FLUSH TABLES via the C query API mysql_query() hangs if the table is locked already. That is to say, nothing prevents me from running a LOCK TABLES twice; it won't tell me "it's already locked, don't try to run a FLUSH".Anyone know how to find out if a table is already locked? And don't say "call the DBA" :)
View Replies !
Commit/Flush Or Somethink Like This
I'm using MySQL with ODBC and got a question about how MySQL stores it's data to the harddisk. What I do is the following. I call the ODBC interface with LOAD DATA INFILE to add some data. Now, what is if MySQL or the whole pc crashes at this running process and direcly after it? Do I have to call somethink like flush or commit after this command to ensure that the information has written to the disk? Or does MySQL automatic flush the database?
View Replies !
FLUSH TABLES With READLOCK
mysql> FLUSH TABLES WITH READLOCK; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'READLOCK' at line 1
View Replies !
Flush Logs, Binary Log Index File
I'm trying to write a script to weekly remove MySQL logs, the general, error and binary log files. Following the MySQL documentation, I've written a script that moves all current log files, then executes mysqladmin flush-logs. My question is this on the binary logs (bin.001, bin.002, etc.): once these are removed and flush logs is executed, the binary log files start over with bin.001. HOWEVER, the binary index file does not appear affected by the flush-logs command; ie, if I'm up to bin.003 in the binary.index file, remove the files and execute the flush logs command, I'm back to a file names bin.001, although the binary.index file still has entries for bin.001, bin.002, bin.003, etc. How does the binary index file stay current when you remove the logs??
View Replies !
Mysql -uroot -p -e 'FLUSH TABLES WITH READ LOCK;' Not Working
$ mysql -uroot -p -e 'FLUSH TABLES WITH READ LOCK;' is not working for me. - I tried another command like 'show tables;' and that worked. - when im inside mysql it works just as it should. - I know the tables aren't being locked because I've run similar UPDATE scripts for both; issuing in mysql and outside. version is: 5.0.45-6
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 !
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 !
How Do I Reclaim Disk Space In Data Folder
I have 5 databases: - mysql - test - product1 - product2 - product3 The hard disk on my server was near full, so I decided to delete databases product2 and product3 as I no longer need them. I dropped the databases product2 and I noticed the disk space was not freeed by MySQL after dropping this database. The tables in databases product2 and product3 are INNODB. What is the best method to reclaim my disk space when dropping entire databases?
View Replies !
How Big ( Disk Size ) Would This Database Be: 4.2 Billion Rows
I have a database in MySQL version 5.0.19 that I'm making. Its structure is: CREATE TABLE `big` ( `keyval` int(10) unsigned zerofill NOT NULL, `name` varchar(20) default NULL, `avail` enum('y','n') default NULL, PRIMARY KEY (`keyval`), KEY `keyval` (`keyval`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The 'keyval' will be filled with every value from 00000000 to 4294967295 ( 4.3 billion rows ), and assume worse case of 20 characters in each 'name' element and 'y' or 'n' always set in the 'avail' colmn.
View Replies !
Running A Stand-alone MySQL DB Off A Flash Disk?
Is this possible? I'm creating a logbook for work in Access at the moment because it will be portable on flash disks provided the machine has Office installed. But what I'd really like is to create it as a webpage that links to a mySQL database on the flash disk. The "install" would be a folder containing the page(s) and a folder with all the DB stuff. Does anyone know if this is possible? I hope it is, especially after my Access experiences.
View Replies !
Disk Usage Increases After Dropping A Large Index?
I have a linux server which is hosting several very large (~20GB) databases. In order to save some disk space I dropped an index on one of the larger (InnoDB) tables because it is no longer needed. It took about 30 minutes for MySQL to drop the index, during which time I saw the free disk space decrease. I didn't think anything of it while the drop operation was running because I figured it was some kind of temp file being used by the drop (even though the temp directory for mysql is supposed to be on another partition). To my surprise, after the index was dropped, not only did I not have more free space as I expected, but in fact I had about 2 GB less than I did before I dropped the index. Huh? So now I'm down 1 index and 2 GB of space. How does dropping a db object actually increases the amount of disk used by mysql? Is there anyway I can reclaim this space? MySQL version is 4.0.15
View Replies !
Mysqladmin Flush-logs Fails: &"text File Busy&"
When I try to reset the general query log by renaming it and flushing the logs, I get an error saying it cannot move the file: text file busy. The log file is quite active, but this is on linux and from the manual (MySQL 5.0: 5.2.5) this should work. What can I do differently to let this work? The commands I'm using to rename and flush the logs: mv /var/log/mysql/dbbox1/mysqld.log /var/log/mysql/dbbox1/mysqld-old.log mysqladmin flush-logs -u root_userid -pPassword rm /var/log/mysql/dbbox1/mysqld-old.log Error message: mv: cannot move `/var/log/mysql/dbbox1/mysqld.log' to `/var/log/mysql/dbbox1/mysqld-old.log': Text file busy.
View Replies !
|
TRACKER
