MYSQL :: Running MtSQL On A RAM Disk

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.

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

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.

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 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 Size Of Tables[or Database] In Mysql Or Oracle
how can i know disk memory occupied by database[tables] in mysql or oracle.

Running Two SUM
SELECT SUM(nlamount WHERE nlaccount='3202' THEN 1 ELSE 0 END) AS receivables, SUM(nlamount WHERE nlaccount='3207' THEN 1 ELSE 0 END) AS receivablesvat
FROM nominalledger
WHERE nlcompanyid=$companyid
AND nldate >= $company_end_year
GROUP BY nlcompanyid

I want to run this script, I basically want to add up nlamount twice, when nlaccount=3202 and nlaccount =3207
i have got te syntax wrong though in the SUM command, I have tried googling it, but there are only basic tutorials in sql outside the SUM instead of inside the SUM

Help Please Running Mysql On Os X
I am very new to mysql. I am using the sitepoint book 'Build your own Database Driven Website using PHP and MYSQL. I have installed mysql mysql-5.0.41-osx10.3-powerpc. Under preferences/mysql it indicates that the mysql server is running. When I try to access mysql through the terminal window I get a message - command not found. Or no permissions. I can go to the bin directory and list the contents - they seem to be there. I can't get past page 21/22 Post-installation setup tasks. I am also new to using the terminal window. I suspect I have missed something really basic - any suggestions on how I can get up and running would be much appreciated!

Need Some Help In Getting MySQL Set Up And Running.
I wasn't sure if someone would be able and willing to help me, but I am new to installing MySQL on a Windows 2003 server (actually .. any server), but I am installing it on a Windows 2003 server right now. I also installed a new version of PHP on the same server. Everything looks fine, but when I go to phpinfo.php I don't see anything saying MySQL. (I saw somewhere that if MySQL is installed properly, it should be displayed on the phpinfo.php page.)

I am currently trying to set up and run Cerberus Help Desk, but it isn't finding the MySQL database that I installed.

So, I would really appreciate it if someone could not just help me via email or postings in here, but actually by using Instant Messenger (AOL, Yahoo! or MSN) to walk me through the set up, testing, etc. so I can get things up and running properly.

I was hoping that someone with more experience than I might be willing to help me in this manner.

Is There A Way To Calculate Running SUM
I have a table (T1) consisting of 2 columns, Date and Observerd as follows:

Date Observed
4/1/07 2
4/2/07 4
4/7/07 3
4/10/07 5
4/12/07 1
4/18/07 7
4/22/07 2

and we want to write a query to create a table or a view with the following data:

Date ThreeDaySum
4/1/07 9 <-- (2+4+3)
4/2/07 12 <-- (4+3+5)
4/7/07 9 <-- (3+5+1)
4/10/07 13 <-- (5+1+7)
4/12/07 10 <-- (1+7+2)

Could Not Get MySQL Running
I went to MS-DOS to start the MySQL application by typing mysqld into the bin directory of where I installed MySQL but once I did typed the command and pressed enter this is all I got :

A moment passed before I am shown the path of bin with no indication of MySQL server started. And also I have installed this as a window service.

Running Average
How can I get mysql to do a running average of a given field using dates
from another column so that the average for each element is for a given time
period rather than from the 5 rows next to the element?

#1064 - While Running A Doc Example
I plan to use the function ADDTIME(expr1, expr2) in an SQL request. I looked at the doc and tried the normal syntax, but I have always the following error even if I enter the example from the doc, why ?

#1064 - You have an error in your SQL syntax.

Even if I enter SELECT ADDTIME("1997-12-31 23:59:59.999999", "1 1:1:1.000002");

I am using MySQL under Windows 2000 using EasyPHP 1.7
(Apache 1.3.27, PHP 4.3.3, MySQL 4.0.15, PhpMyAdmin 2.5.3. )

Slow Running Sql
i am not using mysql but an unknown database system on a unix box - i have no control over the database but have purchased an odbc driver that seems very 'clunky' after using mysql - this is an sql statement question rather than a mysql tech question. if i run this:

SELECT
MK_01_vehicleRecords.registrationnumber, MK_01_VehicleRecords.vehiclenumber
FROM MK_01_VehicleRecords
WHERE (MK_01_VehicleRecords.vehiclenumber = '36176')

Running MySQL 5.0
I've just downloaded and installed 5.0 on my Pc/ win 98se.
When I run the program MySQL Command Line Client, it appears to run for a second then disappears. I see the program flash open for a second and then it is gone.Complete newb needs some help.

Running Balance With Where
I am using the following query:
SET @bal1=0,@bal2=0,@bal3=0,@bal4=0,@bal5=0,@bal6=0;
SELECT Id, Date, FCode, Descr, Debit, @bal5 := @bal5 + DebT AS RunDeb, Credit, @bal6 := @bal6 + CreT AS RunCre, @bal2 := @bal2 + net AS RunBal, Rec
FROM (
select
fin_records.id,
fin_records.id_production_unit as PUnit,
fin_records.id_bank_account as BAcc,
date_format(fin_records.fin_record_date,'%d-%m-%y') as Date,
fin_accounts.fin_account_code as FCode,
fin_records.fin_record_desc AS Descr,
fin_records.fin_record_debit AS Debit,
@bal3 := fin_records.fin_record_debit AS DebT,
fin_records.fin_record_credit AS Credit,
@bal4 := fin_records.fin_record_credit AS CreT,
@bal1 := fin_records.fin_record_debit - fin_records.fin_record_credit AS Net,
fin_records.fin_record_rec AS Rec
from fin_records inner join fin_accounts on fin_accounts.id=fin_records.id_fin_account where (fin_records.id_production_unit='$up') and (fin_records.id_bank_account='$ba') and (fin_record_date between makedate(year(curdate()),1) and makedate(year(curdate()),365)) order by fin_records.fin_record_date,fin_records.id
) AS tmp;

I should build another column to get RunBal2, that should include only records with Rec = 1.

Mysqld Is Not Running
cannot seem to run mysql commands, although queries seem to work well as I can make databases and view tables. Hope that makes sense.

I installed mySQL 5 on Windows XP and tried to stick with the default installation options and paths. I lauch mySQL from this: ""C:Program FilesMySQLMySQL Server 5.0inmysqld", enter a password (but not a username so I'm assuming that's root user), get a mysql> prompt. So far so good.

The following commands do not seem to work though:
mysqladmin version
mysqladmin variables

Typing these commands then clicking the return key brings up the -> prompt. I've checked the processes and "mysql.exe" and "mysqld-nt.exe" are running. From a c: prompt I tried to install mysqld and was told it is already running. What am I missing here? I'm sure it is something very simple...

Ultimately I need to bring in a table from a SQL dump from another system. I've tried using variations of: mysql -h host -u user -p dbname < dumpfile.sql

I'm combing through the docs, but still don't have a clue.

MySQL 4.1 Running
I've been running mysql 4.1 for sometime and using phpMyAdmin (v2.6.2) to administer the databases.

Using php scripts I can also update/insert etc the databases (maintained on my laptop Mac OS X Tiger).

Suddenly, I can't get phpMyAdmin to load in the browser. I've also tried other MySQL GUI tools like CocoaMySQL and Navicat....they won't run either. I downloaded and installed phpMyAdmin 2.6.3-pl1 as well but no improvement.

Why would mysql run but the admin apps not work?

Running MySQL From A CD-ROM?
I have used PHP and MySQL for a few years now. Just today I can across an application that had a MySQL database running locally off a CD ROM. I did not know this was posible. When the application runs it starts up the MySQL database without the user even knowing they are running it. It is pretty amazing.

So I want to do the same thing. Is there any documentation on how to run MySQL locally. I would like to know how to build the database and create tables under these curcumstances.

Running 4.1 And 5.0 On Linux
Question: How can I run 4.1 and 5.0 simultaneously on linux - Suse 9.3
in particular? My goal is to do no harm to my current 4.1 setup. Can
the 4.1 and 5.0 rpm's play nice together? Can I use the rcmysql
commands for running multiple versions?

Running Sql Scripts
In the past I have always used phpmyadmin to access my mysql database.
I am now doing something on a host that does not have php my admin. Can anyone help me either:

1. setup phpmyadmin -- I downloaded and uploaded to the server but I can't seem to get the config file correct
or
2. how can I upload a sql script (creating all my tables) to the database.

Running Script
I'm jumping ship from a dead beat host - but first i'm trying the import on my own machine(osX). Exported all my data from phpMyAdmin 2.5.6.
I did it in SQL format - one for the structure and one for the data - to a zip file(also tried with GZip).The file starts with comments and is being interpreted as all commnets - it seems there are no carriage returns - however when i look at it in any text editor or in mySql query browser, it's formatted correctly. I can place a carriage return after a comment line and reopen the query in the query browser and everything looks ok...until the next comment.

My only thought is that it must be some kind of encoding issue - but i'm pretty lost when it comes to stuff like that.I've tried from the terminal/command line as well.

Running Balance
I have to calculate running balance in a query, I am trying this:
set @fin_record_bal1=0;
set @fin_record_bal2=0;
select id,fin_record_debit,fin_record_credit,@fin_record_bal1:=fin_record_debit-fin_record_credit,@fin_record_bal2:=@fin_record_bal1+@fin_record_bal2 from fin_records

The above doesn't work in record one

Running MySQL
I have installed mysql-3.23.54-win and it all seems to have gone OK .... now i want to run it and setup a database. I dont have an icon on the desktop and there is nothing in Start - Programs ..... start it from DOS prompt via C:> C:mysqlinmysql.exe and i get error 2003 cant connect to MySQL server on localhost <10061>

Running Queries
I am running phpbb forums, and I had run into a problem. They gave me a line of code that Im am supposed to run to fix my problem. Now that creates a problem, because that line of code is wrong sql says??

Heres the code..

ALTER TABLE phpbb2_users ADD COLUMN user_timezone float DEFAULT '0' NOT NULL

I think the problem lies within the user_timezone part. I get an error 1064, and also it says that the database table phpbb2_users is not there, which I am looking at

Running A Query
I'm following a guide, and it says to do this stuff in my mysql database. However, whenever I try to run it (with slight alterations to the code to try to get it to work) it always gives me a syntax error.

What do I have to type exactly? Hers the code:

mysql> CREATE TABLE chat

-> chtime DATATIME,

-> nick CHAR (10) NOT NULL,

-> words CHAR (150);

Running Two Servers
--=__Part1A441A35.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I am looking into installing 2 mysql servers on the same windows 2000
PC. From what I have found, it is usually possible to have 2 different
versions on the same machine. (Usually one for testing and the other for
production). However, I am trying to run 2 production servers, one for
personal family info, and the other for business opportunity.
Being that I just started using mysql, is it possible then to have the
same version installed twice, or how do I accomplish my task?

Problem Running 4.1.1
I've tried several clean installs of 4.1.1, but each time I set a configuration, the configuration wizard tells me the server could not be started with an error code of "0". On a winxp machine using Apache 2.0.54 and PHP 4.3.11, what could be the problem?

Running INSERT 1 Or 2 Minutes???
I've been searching the web for code that would INSERT the date and time every 1 or 2 minutes? Is this possible?

Running 2 Msql Servers
I want to run two mysql server on the same win xp machine independent of each other: one on 3306 and one on 3309. How and what files do I have to modify to set the second server port to 3309 permanently.

Cancel A Running Query
Does anyone know if there is a way to cancel a query once it has started executing? I've thought of killing the process through "show processlist". Is there a better way (since I'm calling SQL from a Java application)?

I Get This Error While Running This Script
Query failed : 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 ''char'' at line 1
this is the file

MySQL Is Running, But I Can't Connect
I really need some help with this. MySQL will not start on boot despite
everything I've done to make sure that it is set to do so.

When I start it as root from a terminal with "/usr/bin/mysqld_safe
--skip-grant-tables &" I show several mysqld procs. and one mysqld_safe,
but I can't connect:

[root@adamsmdk madams]# ps -aux| grep mysql
Warning: bad syntax, perhaps a bogus '-'? See http://procps.sf.net/faq.html
root 3650 0.0 0.1 2276 664 ? S 11:09 0:00
/usr/lib/courier/authlib/authdaemond.mysql start
root 3654 0.0 0.1 2316 792 ? S 11:09 0:00
/usr/lib/courier/authlib/authdaemond.mysql start
root 3655 0.0 0.1 2276 664 ? S 11:09 0:00...............