Last weekend saw the second installment of the Free and Open Source Software Conference (FrOSCon) in Sankt Augustin. The conference revolved around a rich schedule of talks, highlighting current topics in Free Software and Open Source. Moreover, Open Source projects took the opportunity to organize their own meetings or even their own lineup of events. Just like last year, the PHP usergroups of Dortmund and …

mysqlnd saves memory. It consumes half as much memory as libmysql. This is what we have been convinced of. This is what we taught you. Then I tried to test it and made Andrey get nervous for a few hours… Meanwhile he is fine again and we can announce: mysqlnd saves memory, not only in theory, we tested it - we can proof it, can we?

The read-only variable trick

In theory it is so simple. libmysql is not part of PHP. If libmysql fetches any data from the MySQL Server, it puts the data into its own buffers. Then the data gets copied from the libmysql buffers into the ext/mysql resp. ext/mysqli data structures. Those data structures are “zvals“. “zval” is the name of the data structure that PHP uses internally - on the C level - to implement user variables. So, you have the data twice in memory: 1x inside the libmysql buffers and 1x inside …

mysqlnd saves memory. It consumes half as much memory as libmysql. This is what we have been convinced of. This is what we taught you. Then I tried to test it and made Andrey get nervous for a few hours… Meanwhile he is fine again and we can announce: mysqlnd saves memory, not only in theory, we tested it - we can proof it, can we?

The read-only variable trick

In theory it is so simple. libmysql is not part of PHP. If libmysql fetches any data from the MySQL Server, it puts the data into its own buffers. Then the data gets copied from the libmysql buffers into the ext/mysql resp. ext/mysqli data structures. Those data structures are “zvals“. “zval” is the name of the data structure that PHP uses internally - on the C level - to implement user variables. So, you have the data twice in memory: 1x inside the libmysql buffers and 1x inside …

There has been quite a bit of talk about emergence of Open Source in China, but the numbers are still very small, as they are for the overall software industry, highlighted by the numbers with Linux (smaller yet for other OSS).

‘’Linux has been riding on a wave in China, topping the growth of all operating systems in the first quarter of 2007, says an industry analyst.

According to CCID Consulting, which specializes in China research, Linux sales posted a 30.9 percent year-on-year growth to reach 31 million yuan (US$4 million). Windows and Unix growth rates were 11.6 percent and 9.6 percent, respectively.’’

‘’Q1 market share Linux (2.5 percent), Windows (41.8 percent), Unix (53.9 percent)’’

More in a ZDNet Asia article

Some key issues to consider in my view are:

1) …

Not so long time ago I had task to update string column in table with 10mil+ rows, and, as the manipulation was non-trivial, I decided this task is good to try Stored Function. Function written - go ahead. Since 5 min I got totally frozen box with no free memory and giant swap.

The case was worth to look deeply - let's try simple table (experiments with MySQL 5.0.45)

PLAIN TEXT SQL:

1. CREATE TABLE `testf` (
2.   `id` int(11) NOT NULL AUTO_INCREMENT,
3.   `name` varchar(255) DEFAULT NULL,
4.   PRIMARY KEY  (`id`)
5. ) ENGINE=MyISAM AUTO_INCREMENT=20971521 DEFAULT CHARSET=latin1
6.  
7. mysql> SELECT count(*) FROM testf;
8. +----------+
9. | count(*) |
10. +----------+
11. | 20971520 |
12. +----------+
13. 1 row IN SET (0.00 sec)

and simple …

In http://jan.kneschke.de/2007/8/1/mysql-proxy-learns-r-w-splitting we did a first implementation on idea of sending non-transactional reads to the slaves and writes and transactions to the master.

While that was mostly a proof of concept it already worked pretty well. In this second round on R/W splitting several issues are taken care of:

• SELECT SQL_CALC_FOUND_ROWS ... + SELECT FOUND_ROWS()
• INSERT ... + SELECT LAST_INSERT_ID()
• default_db not in sync between client and backend

These changes should make Read/Write splitting more robust and make it a drop-in for more users.

Up to now we used the tutorial-keepalive.lua to implement the Read/Write-splitting. As the name says, it is a …

The Taso Dump

I’ve gotto go, my time has come. (maintenance window needed)
Could not get a lock. (table lock)
I needed a blocker to ensure a single thread. (–single-transaction)

Finally, got a lock. (–lock-tables)
Need to ensure no transactions lost.
Dump, Dump, Dump. (mysqldump)

I’m having flow problems.
Was the buffer to small? (key_buffer_size)
Was it the query cache? (query_cache)
My Dirty Pages may be too high? (innodb_max_dirty_pages_pct)
Or was it just Too Many Connections? (max_connections)
But it was just waiting on the flush (flush tables with read lock)

Time passes, Time Passes. No output yet.
Is it network saturation?
Is it IO bound?
Do I need a better flushing method (innodb_flush_method)
No, it was just the lag? (Seconds_behind_master)

Dump is complete (unlock tables)
Now it’s time to …

Previous post

http://consoleninja.net/code/dpm/rel/dpm-r2.tar.gz - tarball of r2
git clone http://consoleninja.net/code/dpm/dpm.git - to get the latest code, always
http://consoleninja.net/code/dpm/dpm-export.tar.gz - a tarball of the latest code, for those unwilling to git it.

I'll keep this short. Also, just noticed my tags aren't getting uploaded to the remote git. Sorry.

Bunch of commits worthy of a new release:
- new included pass-through demo.
- cleaned the included connection pooling demo a little.
- 'proxy_until' demo. Disables packet processing temporarily. …

Previous post

http://consoleninja.net/code/dpm/rel/dpm-r2.tar.gz - tarball of r2
git clone http://consoleninja.net/code/dpm/dpm.git - to get the latest code, always
http://consoleninja.net/code/dpm/dpm-export.tar.gz - a tarball of the latest code, for those unwilling to git it.

I'll keep this short. Also, just noticed my tags aren't getting uploaded to the remote git. Sorry.

Bunch of commits worthy of a new release:
- new included pass-through demo.
- cleaned the included connection pooling demo a little.
- 'proxy_until' demo. Disables packet processing temporarily. …

In MySQL Proxy learns R/W splitting we did a first implementation on idea of sending non-transactional reads to the slaves and writes and transactions to the master.

While that was mostly a proof of concept it already worked pretty well. In this second round on R/W splitting several issues are taken care of:

• SELECT SQL_CALC_FOUND_ROWS ... + SELECT FOUND_ROWS()
• INSERT ... + SELECT LAST_INSERT_ID()
• default_db not in sync between client and backend

These changes should make Read/Write splitting more robust and make it a drop-in for more users.

Up to now we used the tutorial-keepalive.lua to implement the Read/Write-splitting. As the name says, it is a tutorial for connection keepalive, …