Re: mysql on tmpfs

[Werner Puschitz]

Atle Veka wrote:

Anyone have any experience to share about running mysql on a linux tmpfs
(using memory)?


tmpfs is swappable. I would use ramfs which is not swappable.


For us it's worked out pretty well and is normally operating lightning
fast compared to an identical SCSI based system. However, even though
there is plenty of free memory linux makes weird decisions from time to
time, temporarily killing performance, swapping in/out to make room.


Without more information it's difficult to say what's causing it. How do 
you check swapping activity?


Werner



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: group_concat display limit

[Werner Puschitz]

Andrey Dmitriev wrote:
> All,
>  
> We are using group_concat but there seems to be some sort of display
> limit.
> Is there are a way to unset or increase it?
>  
> Thanks,
> Andrey
> 

You can change the maximum length by setting the group_concat_max_len
system variable to a higher value which is constrained by
max_allowed_packet.

Werner

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to extract data from the show table status?

[Werner Puschitz]

Dominic Baines wrote:
> After a pretty long time I have returned to being a mysql DBA again after 
> spending a lot of time with Oracle and MSSQL.
> 
> I have several databases that I need to to work on, all various source builds 
> from 4.1.16 to 4.0.20 and the average database contains some 200 or more 
> tables. Some are clustered some are stand alone.
> 
> There is an overall project to upgrade and consolidate some of these and to 
> add in belt and braces redundancy and to add database backups.
> 
> Most server installation are for just a single version, some installations 
> have just 2 or 3 databases some have 20 or more.
> 
> The first task I need to run is to determine the database table storage 
> engines (all make use of MyISAM, InnoDB and Archive), then update frequency 
> and row numbers.
> 
> Now if I use:
> 
> show table status from ;
> 
> It will list all the table information I need however, what I am trying to 
> figure out is how to get access to the data this produces directly.
> 
> The data I want is name, engine, rows, avg_row_length, max_data_length, 
> create_time and update_time.
> 
> I can do it manually but that is a bit mind numbing and leads to inaccuracy 
> if this were Oracle I could query one of the V$ views and get this 
> immediately. If this were version 5 I might use INFORMATION_SCHEMA.
> 
> Obviously it has been far too long and I have forgotten completely.
> 
> I do not want to go down the perl if I can help it. Is there a way to do this 
> internally using sql I seem to remember there wasn't?
> 
> Can anyone advise?
> 
> Dom

On Linux you could try these two options:

$ mysql -u root -p -e "SHOW TABLE STATUS FROM " | awk '{print
$1, $2, $5, $6, $8, $12, $13}' | column -t

$ mysql -u root -p -e "SHOW TABLE STATUS FROM \G" | egrep
"Name:|Engine:|Rows:|Avg_row_length:|Max_data_length:|Create_time:|Update_time:"


The first option will create a nice formatted table. Here is an example
showing a few columns:

$ mysql -u root -p -e "SHOW TABLE STATUS FROM world" | awk '{print $1,
$2, $5, $6, $12}' | column -t
Name Engine  Rows  Avg_row_length  Create_time
City MyISAM  4079  67  2008-01-14
Country  MyISAM  239   261 2008-01-14
CountryLanguage  MyISAM  984   39  2008-01-14

Werner



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: write lock and sql_cache

[Werner Puschitz]

MAS! wrote:
> does the "lock table" on MySQL (I'm using 5.0.22 or 5.0.45) works even
> for a cached result?
> 
> this is my scenario, (I'm using MyIsam tables)
> 
> thread 1:
> LOCK TABLE foo WRITE;
> 
> thread 2:
> SELECT * FROM foo ;
> (locked)
> 
> thread 1:
> UNLOCK TABLES;
> 
> thread 2:
> result of SELECT * ..
> 
> and that's is, IMHO, correct; btw, if the result of the select is in the
> cache, it seems that I'm unable to 'lock' again the table:
> 
> thread 1:
> LOCK TABLE foo WRITE;
> 
> thread 2:
> SELECT * FROM foo ;
> I have the result of the select; that was not 'locked' :(
> 
> the only way I found to avoid that is use the SQL_NO_CACHE (then SELECT
> SQL_NO_CACHE FROM foo)
> 
> is that correct?! there is a way to avoid to specify "SQL_NO_CACHE" ?
> or there is something wrong ?!

Try setting query_cache_wlock_invalidate to ON.

Werner


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]