Re: space usage
Martin Jespersen schrieb: Does anyone havea clue of how mysql optimizes empty fields and how query speed is affected? Why don't you read the part in the mysql documentation about the opimization? what will be better for queryspeed/size: adding them with NULL using NULL as default or with NOT NULL using 0 and '' as defaults? Depends on what you need! But Both is okay. NULL woudl just give you more free space since NULL don't add any bytes to the column. Greetings Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
space usage
Does anyone havea clue of how mysql optimizes empty fields and how query speed is affected? i have a db with around 3 million rows where i need to add 2 new fields - one smallint and one varchar(10) for alot of the rows they will be empty, but because of query speed i opt to put them ion the maintable instead of a seperate table so i can avoid slow joins. what will be better for queryspeed/size: adding them with NULL using NULL as default or with NOT NULL using 0 and '' as defaults? Regards Martin Jespersen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Estimating disk-space usage using LENGTH(BLOB)
I'm currently trying to write a short SQL expression that will give me a rough estimate of the disk-space usage of a particular bunch of rows in a table. For table 'example': CREATE TABLE `example` ( `id` int(11) NOT NULL auto_increment, `blah` varchar(255) NOT NULL default '', `blah2` longblob NOT NULL PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ... I can write a query as follows: SELECT length(blah) + length(blah2) FROM example This, I hope, gives me a rough idea how much space is used by the row (to get a better approximation, I could include what's listed in http://dev.mysql.com/doc/mysql/en/Storage_requirements.html, but the above would do). Now, my questions are: - Does length() always return a sensible value for things like length(LONGBLOB) (e.g. length(blah2))? - Does it return the number of bytes in the blob? - In general, will the approach shown above give a reasonable estimate of disk-space usage? Ta, -- Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to limit log space usage?
Hi, I have a configuration with 1 master and 2 slaves; all servers are correctly running mysql-max-4.0.20. All is fine, but I'm observing a strange usage of logs. On one of the slaves I have: mysql show slave status \G *** 1. row *** Master_Host: idb Master_User: root Master_Port: 3306 Connect_retry: 60 Master_Log_File: db-bin.3322 Read_Master_Log_Pos: 1013397930 Relay_Log_File: www4-relay-bin.009 Relay_Log_Pos: 174693397 Relay_Master_Log_File: db-bin.3322 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: mysql Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 1013397930 Relay_log_space: 174693397 1 row in set (0.00 sec) and the relay log is 170MB. I don't want relay logs to grow so big; what I'd like Mysql to do is to destroy the relay-log file as soon as the SQL thread is finished. (usually relay logs grow up to 4GB in 1GB chunks, so this example doesn't show my problem in all its extent). Similar problem on the master (db): -rw-rw1 mysqlmysql 196122 Jan 23 2004 db-bin.3319 -rw-rw1 mysqlmysql1073772277 Sep 13 14:55 db-bin.3320 -rw-rw1 mysqlmysql1073773581 Sep 15 10:18 db-bin.3321 -rw-rw1 mysqlmysql1019108019 Sep 17 09:08 db-bin.3322 -rw-rw1 mysqlmysql 42 Sep 15 10:18 db-bin.index There are 3 GB of logs that no one needs anymore. Since the master knows that all the slaves are correctly aligned up to a certain MASTER_LOG_POS, can't it automatically remove the logs up to that position? On another slave where I set set-variable = max_relay_log_size=2500 set-variable = relay_log_space_limit=5000 max_relay_log_size seems to be respected, but the problem remains: after having the data inserted in the DB those logs are useless. Can't Mysql automatically purge logs when they are not needed anymore? Thanks, -- Nico Sabbi - Officine Digitali - Bologna Tel. 051 - 4187565 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to limit log space usage?
Nico Sabbi wrote: [...] There are 3 GB of logs that no one needs anymore. Since the master knows that all the slaves are correctly aligned up to a certain MASTER_LOG_POS, can't it automatically remove the logs up to that position? The problem here is that MySQL masters aren't necessarily aware of all their slaves, so it has no way of telling if they're all caught up with the replication. The best way to solve this problem is to write a Perl script to connect to each slave periodically and check the current log, and then purge all the logs previous to the least-caught-up slave's current log. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to limit log space usage?
See http://dev.mysql.com/doc/mysql/en/PURGE_MASTER_LOGS.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to limit log space usage?
Nico Sabbi wrote:
Hi,
I have a configuration with 1 master and 2 slaves; all servers are
correctly running mysql-max-4.0.20.
All is fine, but I'm observing a strange usage of logs.
On one of the slaves I have:
mysql show slave status \G
*** 1. row ***
Master_Host: idb
Master_User: root
Master_Port: 3306
Connect_retry: 60
Master_Log_File: db-bin.3322
Read_Master_Log_Pos: 1013397930
Relay_Log_File: www4-relay-bin.009
Relay_Log_Pos: 174693397
Relay_Master_Log_File: db-bin.3322
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db:
Replicate_ignore_db: mysql
Last_errno: 0
Last_error:
Skip_counter: 0
Exec_master_log_pos: 1013397930
Relay_log_space: 174693397
1 row in set (0.00 sec)
and the relay log is 170MB. I don't want relay logs to grow so big;
what I'd like Mysql
to do is to destroy the relay-log file as soon as the SQL thread is
finished. (usually relay logs
grow up to 4GB in 1GB chunks, so this example doesn't show my problem
in all its extent).
Similar problem on the master (db):
-rw-rw1 mysqlmysql 196122 Jan 23 2004 db-bin.3319
-rw-rw1 mysqlmysql1073772277 Sep 13 14:55 db-bin.3320
-rw-rw1 mysqlmysql1073773581 Sep 15 10:18 db-bin.3321
-rw-rw1 mysqlmysql1019108019 Sep 17 09:08 db-bin.3322
-rw-rw1 mysqlmysql 42 Sep 15 10:18 db-bin.index
There are 3 GB of logs that no one needs anymore. Since the master
knows that all
the slaves are correctly aligned up to a certain MASTER_LOG_POS, can't
it automatically remove the logs up to that position?
On another slave where I set
set-variable = max_relay_log_size=2500
set-variable = relay_log_space_limit=5000
max_relay_log_size seems to be respected, but the problem remains:
after having the data inserted in the DB those logs are useless.
Can't Mysql automatically purge logs when they are not needed anymore?
Thanks,
Here is a script we have croned to run every 5 minutes. It checks that
both slaves are in sync and then it archives the un-needed logs to
different disks in case we might need them. You'll have to set up ssh
keys so the master server can log into the slaves.
#!/bin/bash
#achive_logs.sh
#
# Purpose - Archvive mysql log if size = 5MB
# Log files are in master_server_name-bin.xxx format.
# Main script calls check_slave which in turn calls copy_log.
# server names
SLAVE1=database2.nea-fast.com
SLAVE2=database3.nea-fast.com
MASTER=db1
# length we'll need to strip off of log file name
LENGTH=${#MASTER}
STRIP=$((LENGTH + 5)) # we add 5 for -bin.
# archive directories
BASE=/var/lib/mysql/
ARCH1=/var/lib/mysql/mysql_arch1/
ARCH2=/storage/mysql_arch2/
# email address
[EMAIL PROTECTED]
[EMAIL PROTECTED]
# page() - Takes 2 arguments. function and dir
page()
{
echo $1 couldn't copy to $2 | /bin/mail -s Help!! $PAGE
}
# copy_log - takes 2 arguments. The log file and a 0 or 1.
# If 2nd arg is 0, the slave db is in sync and it's cool
# to delete the log after coping. If 2nd arg is 1, the slave
# has not caught up and we dont want to delete the log from the
# mysql dir.
# If there is an error coping to either dir, dont
# delete the old log file! We'll try to copy again next time.
copy_log()
{
PROBLEM=$2
cp -f $1 $ARCH1
if [ $? -ne 0 ]; then
echo copy-log couldn't copy to arch1
page copy-log arch1
PROBLEM=1
fi
cp -f $1 $ARCH2
if [ $? -ne 0 ]; then
echo couldn't copy to arch2
page copy-log arch2
PROBLEM=1
fi
if [ $PROBLEM -eq 0 ]; then
echo removing file
rm -f $1
fi
}
# check_slave - takes one argument which is the log name.
# We need this function because we may end up with several log files
# in the mysql dir because the slave was not caught up when we last
# ran.
check_slave()
{
echo in check slave
M_LOG=$1
M_LOG_SEQ=${M_LOG:$STRIP} #strip off $MASTER-bin. from file name
#
# We've got to check both SLAVE1 and SLAVE2
#
echo getting info from slave1 `date`
DATA=`ssh $SLAVE1 cat /var/lib/mysql/master.info`
echo got data from slave1 `date`
SLAVE1_LOG=`echo $DATA | awk '{print $1}'` # Master log is first line
#strip off $MASTER-BIN. from file name to get sequence number
S1_LOG_SEQ=${SLAVE1_LOG:$STRIP}
# Let check slave2
echo getting data from slave2 `date`
DATA=`ssh $SLAVE2 cat /var/lib/mysql/master.info`
echo got data from slave2 `date`
SLAVE2_LOG=`echo $DATA | awk '{print $1}'` # Master log is first line
#strip off $MASTER-BIN. from file name to get sequence number
S2_LOG_SEQ=${SLAVE2_LOG:$STRIP}
# We've got the log number for SLAVE1 in $S1_LOG_SEQ and
# $S2_LOG_SEQ for SLAVE2
if [ $M_LOG_SEQ -lt $S1_LOG_SEQ ] [ $M_LOG_SEQ -lt $S2_LOG_SEQ ]; then
# Slave has switched to new log so were ok. Send file name
# and 0 to copy_log. 0 tells copy_log to delete the file when finished
copy_log $1 0
else
# Slave hasn't
InnoDB vs myISAM disk space usage
Hello all, I'm using mySQL for many databases, now I want to test innoDB so I have created another DB (on the same machine) and I have populated it with the same data of the first DB (via: insert into table_name select * from db1.table_name); I have this tables: tableA: 80.000 recs tableB, tableC, tableD and tableE: 150.000 records (each) tableF: 275.000 recs tableG: 1.800.000 recs the problem is: in the myISAM tables disk usage of the DB is 376MB, with innoDN the datafile grows to 724MB (and show table status tells there is 37MB free) Anyone knows the reason of this disk usage of innoDB (it's 2 time bigger than myISAM) Tommaso - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB vs myISAM disk space usage
Just to contribute our anecdotal experience, we also found a 2x increase in space required when we converted our MyISAM tables over to InnoDB. While it was surprising, it wasn't unexpected. We just had to go buy another 60GB of disk space (luckily we had planned for this). :) Owen On Wed, 2002-11-27 at 07:26, [EMAIL PROTECTED] wrote: Hello all, I'm using mySQL for many databases, now I want to test innoDB so I have created another DB (on the same machine) and I have populated it with the same data of the first DB (via: insert into table_name select * from db1.table_name); I have this tables: tableA: 80.000 recs tableB, tableC, tableD and tableE: 150.000 records (each) tableF: 275.000 recs tableG: 1.800.000 recs the problem is: in the myISAM tables disk usage of the DB is 376MB, with innoDN the datafile grows to 724MB (and show table status tells there is 37MB free) Anyone knows the reason of this disk usage of innoDB (it's 2 time bigger than myISAM) Tommaso - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB vs myISAM disk space usage
On Wed, Nov 27, 2002 at 01:26:42PM +0100, [EMAIL PROTECTED] wrote: Anyone knows the reason of this disk usage of innoDB (it's 2 time bigger than myISAM) InnoDB has larger per-record overhead (row headers and such). -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 7 days, processed 251,392,469 queries (382/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
