|
Planet MySQL
|
Planet MySQL - http://www.planetmysql.org/
|
-
RMOUG MySql SIG
The Roucky Mountain Oracle Users Group’s MySQL Special Interest Group met today for four presentations. Effective MySql’s Ronald Bradford explaining Explain Ronald Brafdord explaining Explain[/caption] Ronald Bradford started the day on backup and recovery and then had a second session on optimizing queries. George Trujillo had a session on demystifying MySQL for Oracle DBAs and Developers. Lastly, I preseted on SQL and NoSQL. If you live in the greater Dener area, you shoudl make sure you take part in the next RMOUG meeting.
-
Mozilla DB News, Friday May 18th
Last week I did not post one of these, because I was at Professional IT Community Conference (PICC) 2012; in New Brunswick, NJ. This is my third year in a row speaking and attending the conference, and it is always a great time. I did a session on MySQL Security (PDF slides) and another on Getting Started with a Podcast (PDF slides). There will be videos available sometime in the future, and I will link to those when they are up.
Other than that, it has been a fairly hectic two weeks. The final set of machines was moved out of the data center on Tuesday, so there was some work on that done, and there will be residual cleanup to do, but that is mostly behind us now. There was plenty of setting up machines, backing up machines and changing MySQL ACLs going on, with more to come to finally clean up all the cruft that has gone away.
And in bullet-point list format, everything else:
We finally got all the proper drivers/firmwares/system updates on the 2
-
OurSQL Episode 91: It's Not Our Type, Part 1
In this episode we talk about string data types, comparing them to the ISO:2003 SQL standard.
Gerry has joined Tokutek - Congrats Gerry! We interviewed Martin from Tokutek in episode 86.
Conferences:
MySQL Innovation Day Schedule Tuesday June 5th, Redwood Shores, CA. Register here (free). Content will be available via live stream, so save the date!
read more
-
How having many tables affects MySQL memory usage?
You could say: what could be the reason for having really big number of tables? Just design the application properly! It’s not always that easy. And this post isn’t really about arguing whether having many tables is good or not, it’s about what happens in terms of memory usage if you already reached that point.
Btw what do I mean by *many*? From my experience it’s tens of thousends or even millions rather than hundreds.
The inspiration for me to write this post was strong desire to try out the latest declared improvements in that area announced to be done in MySQL 5.6. _1
What I did was a very simple test where I loaded sql dump of databases and tables definitions only into different versions of MySQL. During the load time I was monitoring memory usage of mysqld process (RSS value from ps aux) as a function of tables number. Additionaly I compared disk usage in case you choose single innodb table space versus file per table, also in relation to number of tables.
Settings I used for test were all default and I chose lowest possible innodb buffer pool:
innodb_buffer_pool_size = 8M except when I started to test Percona versions I realized that the lowest allowed value there was 32MB, this warning appeared in error log after the service start:
“[Warning] option ‘innodb-buffer-pool-size’: signed value 8388608 adjusted to 33554432″
So both Percona versions I tested had 32MB and all others had 8MB but I think it wasn’t that important in this particular test.
Additionally for Percona versions I used the
innodb_dict_size_limit variable introduced already in 2009 _2.
The sample table set that I used has around 50 different tables with various number of fields and indexes.
Here are the results of creating more than a million tables:
I wonder what is the difference between the patch that Percona did few years ago (and which was available starting from 5.0.77-b13 of Percona Server) and latest fix available in MySQL 5.6.x branch. Any way It’s really nice to see it among all other great improvements that are happening in 5.6.x development line.
OK, now let’s see how the disk usage is affected in this simple test by just creating empty tables in two scenarios: single InnoDB ibdata file and when innodb_file_per_table option is set. In this case I observed no differences between various MySQL versions, so you can see only one here.
Related links:
http://www.mysqlperformanceblog.com/2010/05/06/how-much-memory-innodb-dictionary-can-take/
http://fromdual.com/how-mysql-behaves-with-many-schemata-tables-and-partitions
http://www.percona.com/doc/percona-server/5.5/management/innodb_dict_size_limit.html?id=percona-server:features:innodb_dict_size_limit
http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-performance-table-cache
http://blogs.innodb.com/wp/2011/12/improving-innodb-memory-usage-continued/
http://ma.tt/2006/03/wordpress-and-lyceum/
References:
http://blogs.innodb.com/wp/2011/12/improving-innodb-memory-usage/http://www.mysqlperformanceblog.com/2009/02/11/limiting-innodb-data-dictionary/
-
Database is in Transition. try the statement later: SQL Server
Problem:
I was trying to take the database offline to perform some maintenance on one of our QA server.
We have got a strange error "Database is in Transition. try the statement later Error 952"
Even i was not able to run the sp_who and sp_who2 also as it was also giving the same error.
Resolution: What i have tried?
I closed the SSMS (SQL Server Management Studio) and restarted it again.
Then to confirm and check for any blocking query, i ran sp_who and sp_who2 and it ran successfully.
Then to make Database offline/online i have used the below command and it worked like a charm :)
USE masterGOALTER DATABASE <db_name>SET OFFLINE WITH ROLLBACK IMMEDIATE
...
...
ALTER DATABASE <db_name> SET ONLINE
Its a strange error with SQL-2005 Management Studio or we can say a bug.
This small issue can cause a big headache for someone.. hope this will helpful. :)
|
