MYSQL :: Showing Disk Usage

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

Disk Performance
We have an application which tends to get very disk-io bound, spending large amounts of processor time in "iowait" during select queries.  Given that writes are relatively infrequent, which would the experienced admins recommend for the disk configuration:

Raid5 or Raid10?  Raid0 is even an option, since we have a backup server and could easily add another, but it's not preferable unless the read performance would increase substantially.

The hardware looks like this:

Dell PowerEdge 6650, 4x2.8ghz CPU, 12gb RAM, 4x36gb internal drives, raid 5
Powervault 220S, 14x73gb 15K drives, dual-connected to the server.  Current configuration is Raid5.

MySQL 4.0.18, RHEL3 (2.4.21-27.0.1.ELsmp) and now using the megaraid2 driver.

There is probably room for optimization in the application, but that's currently out of our control.  We may even move to a different database platform, but if MySQL can handle the load I'd just as soon not.

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.

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.

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.

Data Kept On The Hard Disk
my mysql database went down... had to reinstall it... where is all the data for the tables kept - i got the /var/lib/mysql/ bit but thats only the tables

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.

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.

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?

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.

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).

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

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.

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.

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.

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 ?

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?

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.

Disk Size Of Tables[or Database] In Mysql Or Oracle
how can i know disk memory occupied by database[tables] in mysql or oracle.

Showing Last 10
ok, i know this probably can be answered easily but in new to the mysql game. i run a movie website and recently created a database with phpmyadmin so i can shuffle and sort movie info easy.i want to show the last 10 entries to my database on one page.
i saw the last insert id code, the one in the sticky post i mean, tried it but cant figure out how to set it up for my needs, can anyone help me out with this or modify that code to fit my situation?

Showing Text Instead Of Img
just set up a new table with some imgs in it and when i go to display them, it just shows the img name and not the img.

my database field is blob BINARY, or should it be unsigned, unsigned zerofill?
whats the diffrence?

Showing The Last Five Items
I have a comment system where I want to show the last five comments.

So say I have 20 comments. I would like to display comments 15-20

I have tried "ORDER BY id DESC LIMIT 5" but it shows the last comment at the top of the page and comment 15 at the bottom. I would like comment 15 at the t

Showing All The Records With The Same Name
I was wondering how to write an sql that'll say

"show me all the records that have the same name"

I have about 500 records, and i would like to know which of them have the same name (which is a column in my table).

Showing Changes To Tables
I'm working on an application that someone else wrote (using PHP and MySQL).

There are LOTS of queries that make changes to different tables with joins and it's really hard to track down what's been done to the data after running the code.

I've been trying to check some of the changes with mysql_affected_rows (for updates) and I've tried that function every way I can think of and I can't get that function to work.

I resorted to using a counter and just counting the number of times that a while loop runs to sort of get an idea of how many records _might_ have been touched.

That has (obviously) turned out to be unreliable.

Does anyone know of a tool (or method) to show changes to a mysql DB after running a query? I looked at mysql diff, but it just looks at structure changes. I need to know how many records were touched when this code was run and I've been working on this for quite sometime and I'm not getting anywhere.

Showing First 10 Results
I feel I should be able to work this out, but I'm drawing a blank.
How would I retrieve, for example, the top 10 orders for each day for a period, where I order by orderTotal?I can't seem to find any way to limit the results for each day, rather than the results as a whole.

Results Not Showing
i am wanting to get the highest number in a field where a members id is 1
below is what i have been messing around with so far but can not seem to get the highest figure into the $num var,
i place the query in sql and it shows the row with the highest number is as follows..

itemid
58

58 being the highest number the results show correctly, but not in the query results in php below (line 2)

please can someone help me out and correct what i might be doing wrong with these stamtments

Showing A Brief Part
I am wondering is there a way to show a brief part of an article? If so how? Maybe up to about 50 words.
Should this be posted in the php forum? If so can someone please move it?

Showing Extra ID's
SELECT product_MerchantProductID, product_Name, prdt_scnd_rel_Product_ID, prdt_scnd_rel_ScndCat_ID
FROM tbl_products, tbl_prdtscndcat_rel
WHERE prdt_scnd_rel_Product_ID = product_ID
AND prdt_scnd_rel_Product_ID=#request.Product_ID#
AND product_Archive = 0
AND product_OnWeb = 1
It presents me with the 'prdt_scnd_rel_ScndCat_ID' based on the ProductID.

What I need it to do is when it presents me with the 'prdt_scnd_rel_ScndCat_ID' is to show me all the ProductID that have the same 'prdt_scnd_rel_ScndCat_ID'.

Showing In Alphabetical Order
Ok sounds simple but i have no clue, how do i display all the entries in my database in alphabetical order, here is my code.

<?php

$Link = mysql_connect ($Host, $User, $Password);

$Query = "SELECT * FROM $TableName WHERE letter='$letter';";
$Result = mysql_db_query ($DBName, $Query, $Link);

while ($Row = mysql_fetch_array ($Result)) {
print ("
<a href="movie.php?id=$Row[id]">$Row[title] ($Row[year])</a><br><font class="sub">aka. $Row[aka]</font><br><br>
");
}

mysql_close ($Link);
print (" ");
?>

Numbers Showing As Null
I am running a query where I want students not assigned a test location to show up, and ones that are assigned to not show up. The site locations are numbers. When I run a Where statement classlist.testsite is null, it ignores the Nulls and still brings up the records that have numbers in them..?

Showing Converted Currency
I've been working on a Stored Procedure to be able to search a property database on various criteria, and one of which is a converted price. Although the price will show by default in GBP, I want the user to be able to select an alternative currency to show the price in. Here is a snippet from the SP: Code:

New Csv Files Not Showing Via Phpmyadmin
I had a list of about 58000 titles on our website. I tried to clear these using myphpadmin, then selecting the relevant table and clicking on empty. I then uploaded new csv files (which have worked perfectly before) but now when you visit the website, the old lists are still there.

Showing Totals And Subtotals In One Row
I have a table with the following fields:

ContractID | CustomerID | ProductID | Quantity

For each Contract there is one record: Who has ordered which product in what quantity.

Now I'd like to generate a report that shows:

- which products were ordered (SELECT ProductID ... GROUP BY ProductID)
- at most (SELECT ... SUM(Quantity) AS Quantity ... ORDER BY Quantity DESC)
- and from which customers. (SELECT CustomerID, Quantity ...) GROUP_CONCAT(...)? Subquery?

Sample-Output:

P_ID - Quantity - Customer's quantities
----------------------------------------
1230 - 10'000 - A: 2'000, B: 8'000
1240 - 8'000 - A: 7'000, C: 500, D: 500
1120 - 6'000 - C: 6'000
...

How shall I build the SQL statement?

Showing League Table From DB
What I am trying to do is show a league table from my sql db . The problem i seem to have other than not knowing much commands is that my db already has the full fixture list in with 0-0 scores etc , so I am not just trying to select 'Counts' from results as they are put in.

The fields I have are : date,home,hscore,hpoints,vs,away,apoints,played ( where played is just a numeric of '1' if played )i really need help on this if anybody is willing to point me in the right direction.

Showing 5 Most Recent Entries
I'd like an area on my site where the 5 most recent entries into the database are displayed in a list, with the top result being the most recent of all.

For this I was planning on doing something like querying the id field of the database to find the five highest numbers only and then echoing them out in descending order.

This however is not something I have been able to find any information on in my searches.

Mysqldump Showing Bad Characters
i've moved my mysql4.0-standard version database to new server.

And i've restored this database mysql4.1.13 server everything works fine.

when i use new server (mysql4.1.13) mysqldump backup command.. is this fine, but i opened backup file, turkish characters showing wrong, like: Ã¼ÄŸÅŸÃ§Ã¶

previous server mysql4.0 no problem with characters everything showing fine but mysql4.1.13 showing wrong..

example;

mysql4.0-standard output;

paylaÅŸmalarÄ±dÄ±r (this is correct)

mysql4.1.13 output;

paylaÃƒÂ¾malarÃƒÂ½dÃƒÂ½r (this is bad)

how do i fix this?

Limit Rows Showing
I am working on a Rare Values system as an addon for my DJ Panel and need to figure out how to limit the number of images shown per line. I use the below code to put the images of the items into a table but how can I make it so when 11 items are displayed it inserts a "</tr><tr>" and then starts a new line?

$result = mysql_query("SELECT * FROM rp_rares ORDER BY id ASC");
echo '<center>
<table width="475px" border="0" align="center">';
while($row = mysql_fetch_assoc($result))
{
echo '<td width="40px" align="center"><img src="' . $row['img'] . '"></td>';
}
echo '</tr></table>';

Showing Users Logged In
I am trying to get a query that will return a list of users currently logged in.

I have a table called table_users, in that table there are are several fields, which I have displayed below.

IN Query Showing All Records If Used With Same Field Twice
I have 2 tables programs and programType. programs has 5 records and programType has no records. When I run a query like "SELECT * FROM programs where id in (select programid from programType) and id in (select programid from programType)" I get all records But When I try to run "SELECT * FROM programs where id in (select programid from programType)" Then I get no records.

Showing Query Results In HTML
In mysql I can find the minimum data from a particular column by doing as follows:

SELECT MIN(Freq_Min) FROM datatable;

and mysql will display the results. Now I want to do the same thing but I want to do it in html, so I wrote the following code. When I open up the webpage stats.php it says that it couldn't execute the query. Can someone help get me on track? .....

DISTINCT Still Showing Duplicate Records
Why won't distinct work? The sql IS selecting correctly but its showling records with the same f.preplyid

CODE

SELECT DISTINCT (f.preplyid), f.pid, f.ptid, f.ptid2, f.puid, f.psubject, f.ptype, f.ptime, t1.tid, t1.tname tname1, t2.tid, t2.tname tname2 FROM forumpost as f, thread as t1, thread as t2 WHERE t1.tid = f.ptid AND t2.tid = f.ptid2 AND f.puid =2 AND f.ptype=1 AND f.puid NOT IN (SELECT fp.puid FROM forumpost as fp WHERE fp.ptype=0 AND fp.pid=f.preplyid) ORDER by f.ptime desc LIMIT 7

Joins Not Showing Proper Output
I have two tables I want to join, I only want the profile table to be counted if there is a user that matches the profile

SELECT COUNT(uid) as count
FROM users
WHERE created > 0 AND created < 1155655377 AND status > 0

this correctly outputs
164,142

When I join the profile values table I get

SELECT COUNT(u.uid) as count
FROM {users} u
INNER JOIN {profile_values} p
ON u.uid=p.uid
WHERE created > 0 AND created < 1155655377 AND status > 0

This incorrectly outputs
3,748,411

this is a test database so it has some problems but I want to know if there is a way to filter out all the problems with profile values table and only get back values that match the users table and discards anything with the profile values table that is not in the users table.

WinMySqlAdmin 1.4 Not Showing Any Database Tables
When I look in "WinMySqlAdmin 1.4" it doesn't show any database tables, however the MySqlManager does, even the ones I have made, but not the WinMySqlAdmin. :-(
I'm not sure if I have done anything wrong, or not, this is a vanilla install, infact 5 times I have uninstalled and reinstalled, same thing. :Oo

I've also come to notice that the main WinMySqlAdmin window shows a "MyODBC" underneath this it says: "Not found Driver 3.51 not found".

The server info below has nothing in it either. I have apache running, and I can view/program PHP files to view in the browser, but I'm not sure if I have this MySql running properly... I have searched high and low within the manuals, I have a my.ini file, but no my.cnf file, which I understand if there was one, it would be disabled any ways, so I assume this file is not needed?

Dropped A Database, But It's Still Showing Up In The Directory
I dropped a database used for practice. Although all the tables are gone, the empty database itself is still there. Is this correct? How do I get use of this empty db?

Note: I'm using Mysql version 3.23.49 -nt and I also use mysqlfront although I generally use the command line to work with the dbs.

Select Count(*) Showing -1 Rows.
In one of my preliminary tests with mysql, the below statement is showing the following way. What does -1 rows signify in the table with the select count(*) query. Is this (-1 rows in the query result) an error condition to be taken care ?.

bash-2.03# ./mysql -u test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8 to server version: 5.0.18-max

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Code:

Only Showing Records That Do Not Exist In Another Table
How do I show a listing of all records within one table that do not exist within a second table?

For example:

Table A
----------
apple
orange
pineapple
banana

Table B
----------
orange
cherry
lime
banana

I want all records in A that do not exist within B to show. thus I should have the results of:

apple
pineapple

What would my sql statement be if my column name was "fruit"?

After Mysqldump Showing Incorrect Characters
i've moved my mysql4.0-standard version database to new mysql4.1.13 server.

And i've restored this database mysql4.1.13 server everything works fine.

when i use new server (mysql4.1.13) mysqldump backup command.. is this fine, but i opened backup file, turkish characters showing wrong, like: üğşçö

previous server mysql4.0 no problem with all characters everything showing fine but mysql4.1.13 showing incorrect..

example;

mysql4.0-standard output;

paylaşmalarıdır (this is correct)

mysql4.1.13 output;

paylaÃ¾malarÃ½dÃ½r (this is bad)

how do i fix this?

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.

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.