« MySQL Administrator | Main | MySQL Query Browser »

April 15, 2004

Database Design Workshop

Sitting (on the floor) at MySQL 2004 in the Database Design Workshop with Lenz Grimmer, Jeremy Zawodny, Jeremy Cole and Sergei Golubchik). The session is a Q/A for database design (but really turned into a "ask anything about MYSQL").

A few introductions and then opened for Q/A.

Woman who is an Oracle DBA just started using MySQL two weeks ago is asking for a diagram of how MySQL works (memory map, key buffer, query cache etc).

Jeremy: MySQL is simple, and a lot is based on how many connections you need. Biggest thing to know about is global memory buffers. Two big ones are key_buffer (used for MyIsam), which is used to cache index blocks in memory so reduced disk interaction. The innodb_buffer_pool (used for InnoDB) is similar, used for caching data read from disk (both data and index). General recommendation is to have 50-80% of the buffer pool.

Jeremy Z (continues): Two others are the cache of table structures, if using a lot of tables or lot of users increase the table cache. The query cache (query_cache_limit and query_cache_size) can really boost performance, will cache results of query and won't go back to disk until the data underneath changes.

Does the query cache lock?

Someone on MySQL Team: When the cache finds a result it will lock that result, not everything else.

Jeremy Z: The global key_buffer has issues with dual processors before version 4.1

Can you preallocate tablespaces?

Jeremy C: Yes, in innodb you can specify.

Can you preallocate in multiple tablespaces in InnoDB?

Jeremy Z: No.

Will 4.1 prepared statements accept parameters?

Jeremy C: Yes

Jeremy Z: There is performance gain using perpared statements, but need to touch the C api to make use of it.

(too much back an forth to get . . . sorry)

Where are temp tables stored?

Jeremy C: Depends on size of table, can be in default temp location or in memory. Internally MySQL keeps track of number of tables to try to keep under control.

Downside for using all-memory heap table to speed up queries?

Jeremy Z: Heap tables don't have full-text indexing. Real downside is making sure the data is loaded up before the application needs it.

When you unpack ISAM tables it's limited in how many more rows can be inserted. Why?

Jeremy Z: Is it the 4G limit? (No.) Sounds like a bug, please submit.

Recommendations on fiber channel hardware?

Participant: There is a RAID card (Vortex?) which is good.

Merge table with 72 underlying tables, each with 600,000 rows each. The merge table is slower by 2 orders of magnitude. Why?

Sergei: Try a new version, the bug site has good details on bugs with merge tables which has been worked on in recent versions.

Talk a little bit about the c++ api vs. the c api.

Lenz: Go with the c, the c++ is esentially a wrapper for the c, and isn't maintained as actively as the c.

Talk more about merge tables.

Jeremy Z: You want to break it down by some sequence (ie a table per week) and then create merge tables to represent a set of the sequenced tables. It's a poor man's version of views.

Are merge tables in memory?

Jeremy Z: It's another .frm file which points to a group of tables.

With an app that can predict the growth, what do you do for preparation?

Jeremy Z: Don't worry about changes on the database size as much as the application change costs. Make sure the application can do things like get_read_handle() and get_write_handle().

Is a loop in PHP just as fast as using a merge table?

Jeremy Z: Depends on the round trip to get to the database. That might add some performance issues.

Abstracting MySQL?

Jeremy Z: Usually not worth it. Creating configuration is good.

Tips for performance tips in replication?

Jeremy Z: Use 4.0 or later. Bandwidth typically isn't the issue, latency is more of a concern. For most of the cases don't need to worry about disk i/o. A lot of time people think write time on disks is key, but seek time is important, because you're doing lots of small reads.

Jeremy C: It's hard to beat local disks.

Does MySQL support partitioning?

Jeremy Z: Not in the way Oracle does.

Multiprocessors, does MySQL work well on it?

Jeremy Z: Typically db isn't using CPU intensive methods (comparing, functions, stored procs).

Posted by mike at April 15, 2004 4:25 PM

Hard Drive Recovery Group offers hard disk data recovery services for RAID, laptops and servers. Complete clean room and hard drive repair service.

Trackback Pings

TrackBack URL for this entry:
http://mike.kruckenberg.com/mt/mt-tb.cgi/552

Comments

How to change the database size

Posted by: Rijo Varghese at August 23, 2004 6:33 AM