MySQL - Navneet Kumar on Diigo

26 Nov 09

adding LIMIT 1 to your query can increase performance. This way the database
engine will stop scanning for records after it finds just 1, instead of going
thru the whole table or index
The more data is read from the tables, the slower the query will become. It
increases the time it takes for the disk operations. Also when the database
server is separate from the web server, you will have longer network delays due
to the data having to be transferred between the servers
10 more annotations...
- ENUM type columns
  are very fast and compact. Internally they are stored like TINYINT, yet they can
  contain and display string values. This makes them a perfect candidate for
  certain fields
- Unless you have a very specific reason to use a NULL value, you should always
  set your columns as NOT NULL.
- NULL columns require additional space and they can add complexity to your
  comparison statements. Just avoid them when you can.
- "NULL columns require additional space in the row to record whether their values
  are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to
  the nearest byte."
- When every single column in a table is "fixed-length", the table is also
  considered "static" or
  "fixed-length". Examples of column types that are NOT fixed-length are:
  VARCHAR, TEXT, BLOB.
- Fixed-length tables can improve performance because it is faster for MySQL
  engine to seek through the records. When it wants to read a specific row in a
  table, it can quickly calculate the position of it. If the row size is not
  fixed, every time it needs to do a seek, it has to consult the primary key
  index.
- You have a "last_login" field in your table. It updates every time a user logs
  in to the website. But every update on a table causes the query cache for that
  table to be flushed. You can put that field into another table to keep updates
  to your users table to a minimum
- When a big query like that is performed, it can lock your tables and bring your
  web application to a halt.
- Apache runs many parallel processes/threads. Therefore it works most efficiently
  when scripts finish executing as soon as possible, so the servers do not
  experience too many open connections and processes at once that consume
  resources, especially the memory.
- If you end up locking your tables for any extended period of time (like 30
  seconds or more), on a high traffic web site, you will cause a process and query
  pileup, which might take a long time to clear or even crash your web server

24 Sep 09

How Percona does a MySQL Performance Audit | MySQL Performance Blog

www.mysqlperformanceblog.com/...does-a-mysql-performance-audit - Preview

MySQL Performance Optimization Strategy

22 Sep 09

Java, MySql increased performance with Huge Pages

LargePageSize

zzzoot.blogspot.com/...ncreased-performance-with.html - Preview

Java JVM MySQL Performance PageFile Caching

Just add -XX:+UseLargePages to the JVM command line to use after setting up. For CPUs that support multiple sizes, use -XX:LargePageSizeInBytes=4m to define the page size you want to use

27 Aug 09

MySQL Lists: internals: Windows Tips to boost RAM over 2GB

lists.mysql.com/33787 - Preview

Windows 3G AWE MemoryManagement PAE MySQL

MySQL Bugs: #43082: mysqld 32 bit cannot use big buffers due to 2GB usermode address space limit

bugs.mysql.com/bug.php - Preview

MySQL 3G Windows 32-bit

MySQL Thoughts: MySQL, Windows 32-bit, and /3GB

dump exe header

harrison-fisk.blogspot.com/...ql-windows-32-bit-and-3gb.html - Preview

MySQL 3G PAE AWE Windows

Software Secret Weapons: Running MySQL on Windows with 4 GB of RAM - Compile - LARGE_ADDRESS_AWARE

4GB LARGE_ADDRESS_AWARE Compile

www.softwaresecretweapons.com/...ql_on_windows_with_4_gb_of_ram - Preview

MySQL Windows Server PAE 3G AWE

26 Aug 09

MySQL :: Cannot use innodb_buffer_pool great than 1,5Gb

forums.mysql.com/read.php - Preview

MySQL Windows PAE AWE 3G Server InnoDB

27 Jul 09

How FriendFeed uses MySQL to store schema-less data - Bret Taylor's blog

a "schema-less" storage system on top of MySQL

bret.appspot.com/...how-friendfeed-uses-mysql - Preview

Database MySQL Key-Value-Store Document-Database Architecture

making schema changes or adding indexes to a database with more than 10 - 20
million rows completely locks the database for hours at a time. Removing old
indexes takes just as much time, and not removing them hurts performance because
the database will continue to read and write to those unused blocks on every
INSERT, pushing important blocks out of memory
InnoDB stores data rows physically in primary key order. The
AUTO_INCREMENT primary key ensures new entities are written
sequentially on disk after old entities, which helps for both read and write
locality (new entities tend to be read more frequently than old entities since
FriendFeed pages are ordered reverse-chronologically

24 Jul 09

Nati Shalom's Blog: Scaling Out MySQL

natishalom.typepad.com/...scaling-out-mys.html - Preview

Scalability Enterprise MySQL Performance PaaS

MySQL becomes a scalability bottleneck because it introduces contention among
the distributed application components.
Scale your database through
database clustering

2. Scale your application, while
leaving your existing database untouched by front-ending the database with
In-Memory-Data-Grid (IMDG) or caching technologies. The database acts as a
persistence store in the background. I refer to this approach as Persistence as
a Service (PaaS).
20 more annotations...
- Database
  replication is used to address concurrent
  access to the same data. Database replication enables us to load-balance the
  access to the shared data elements among multiple replicated database
  instances
- Limited to "read mostly"
  scenarios: when it comes to inserts and updates, replication overhead
  may be a bigger constraint than working with a single server (especially with
  synchronous replication)
- asynchronous replication leads to inconsistency
- replication assumes that all nodes hold the entire data set. This creates two
  problems:.1) each table holds a large amount of data, which increases
  query/index complexity. 2) We need to provision (and pay for) more storage
  capacity with direct proportion to the number of replicated database instances
- database shards/partitions enable the distribution of data on multiple nodes. In
  other words, each node holds part of the data. This is a better approach for
  scaling both read and write operations, as well as more efficient use of
  capacity, as it reduces the volume of data in each database instance.
- constrained by disk I/O performance
- Many database implementations require that knowledge of which partition
  the data resides in is exposed to the application code
- The fundamental problems with both
  database replication and database partitioning are the reliance on the
  performance of the file system/disk and the complexity involved in setting up
  database clusters.
- These database patterns evolved
  under the assumption that memory is scarce and expensive, and that network
  bandwidth is a bottleneck. Today, memory resources are abundant and available at
  a relatively low cost. So is bandwidth.
- its protocol-based approach requires the application to be explicitly exposed to
  the cache topology, i.e., it needs to be aware of each server host, and
  explicitly map operations to a specific node. These limitations prevent us from
  fully exploiting the memory resources available to us.
- In-memory data grids (IMDG) provide
  object-based database capabilities in memory, and support core database
  functionality, such as advanced indexing and querying, transactional semantics
  and locking
- IMDGs also abstract data topology from application code.
- The IMDG loads its initial state from the database and from that point on acts
  as the "system of record" for our application. In other words, all updates and
  queries are handled by the IMDG. The IMDG is also responsible for keeping the
  database in sync.
- To reduce performance overhead, synchronization with the database is done
  asynchronously
- Data can be accessed by
  reference
- The database is not used for high
  availability purposes. This means that In-flight transactions are not stored in
  the database, only the end result of the business
  transactions
- Also keep in mind that queries don't really hit the database, only
  updates and inserts. All this together means that the IMDG
  acts as a smart buffer to the database
- The database and the application are
  now decoupled, giving you more options for optimization
- there are scenarios where writing to the database is required to ensure the
  durability of the data. In this scenario, you can store the data directly
  in a persistent log (to ensure durability). The log can be updated at a
  relatively high rate
- when we use in-memory storage in an IMDG, we don't need the O/R mapping layer.
  In addition, we don't need separate languages to perform data manipulation. We
  can use the native application code, or dynamic languages, for that purpose.