As I already wrote on my new blog I've now left MySQL and will pe starting my own company to provide MySQL Performance Consulting services.

So if you need any help with MySQL or have any friends which need one let me know.

I also will be working on few web projects and opensource projects. I'll keep you posted.

Comments: 0   Views: 1565   Group: Общие  

It looks like my request to update PlanetMySQL.org to use new blog is not yet handled so let me post here links to the fresh articles I've written recently:

MySQL Server Memory Usage This artice speaks about MySQL Server Memory usage, how to estimate it and control it.

To Pack or Not to Pack - MyISAM Key Compression This article speaks about benefits and draw back about MyISAM packed keys.

Comments: 0   Views: 2296   Group: Общие  

Every so often I need to reply the question, what is the best way to use 10.000 connections with MySQL (you can select your favorite big number here)
Well. The good answer is your better not.

First let see where this requirement typically comes from ? Most commonly it is something like we have 40 apache boxes each having 200 children with persistent connections which gives us 8000. Well... This brings me to two questions - do you really need persistent connections and do you really need 200 apache children ?

Persistent connections will give you decent performance benefit only in one case - if there are few database queries to create the page and if database load is large response time contributor. In modern rather complex applications it is rarely the case. This is one of examples why persistent connections were disabled in "MySQLi" for PHP. Java applications are better in this sense as they typically use connection pooling rather than persistent connection which needs far less connections and much less efficient.

Now about 200 apache children on the box ? Why so many of them are required. In some cases it is good justified but in most it is simply poor configuration of Web part. 90% of these 200 children will be spoonfeeding slow clients, while keeping their large chunk of memory allocated by mod_php
mod_perl or whatever you use part. One thing you can do to solve this problem is to place lightweight proxy in front which can spoonfeed your clients, do keepalive and even cache if something can be cached. You can use apache module for this purpose while I'd like something more lightweight, ie lighttpd. Important requirement here is not using process/thread per connection and based on events rather than select() or pool() in handling network as these are slow with large number of connections. By using these you can have 10.000 connections or more per box with small number of apache children. The other option is to use lingerd, which I did not try however. Finally the most radical option is to use fast-cgi interface which allows you to use much wider selection of web servers.

So if 200 processes is a lot how many is enough ? Well it depends on the application. In certain cases, for example if application uses Web Services internally or some other remote network calls. In this case you simply need a lot of processes running to get the performance you need. In most common case however if load is CPU bound and does local database calls you can do better with much smaller value.

Each "device" have the optimal number of requests which it can handle at the time. for CPU signle running thread per core is optimal. For hard drives
it may be few requests per drive (to allow scheduling) but if you get too many you would get your sequential scans interrupted by random IO etc.
Also you should count memory and CPU cache - the more concurrent processes are running the smaller portion of CPU cache and OS disk cache each of them get, not to mention they use memory themselves which reduce OS cache and even could cause swapping. The optimal number of proessess depends on the load, if it is CPU bound you would watch number of running threads more, if it is IO bound you would watch number of outstanding requests.
As a quick and dirty I usually use (NumCPUs+NumDisks)*2 value. Depending on the application, OS, hardware and MySQL bugs it may be more or less than that.

The curious thing about this number is - it is kind of universal, even if it is not always 42. If you look at "localy running threads", meaning threads running on local CPUs or being blocked on local resources you can use this value - if it is Web server, application server or database server. If process is waits on network it is not local resource so you likely want to have many of these.

So how many ? I would start guessing from 20-40 is the optimal number of active threads on the Web/Application server. Then it comes to MySQL
I'd see even less than that in "threads_running" Most typically values below 10 are optimal for CPU bound workload and below 30 for IO bound.
It depends on hardware and workload of course.

What are the problems with many connections on MySQL side ?
- Waste of memory. Each connection requires certain resources and might not give away all of them even if it is "sleeping"
- Load On OS - Operation System rarely enjoys managing 1000s of threads TCP/IP connections
- Many Running threads - thrashing of CPU cache and So waste of CPU resources.
- Many Running threads - mutex ping-pong. Especailly bad for Innodb tables
- Small annoyances as hard to deal with "show processlist", broken "SHOW INNODB STATUS"

Comments: 0   Views: 451   Group: Общие  

Recently I had a chance to revisit question of RAID5 vs RAID10 performance for MySQL OLTP workloads (Random IO). The previous set of benchmarks
I've done for MySQL Users Conference almost two years ago were done on RH AS 3 and 8 Drive SATA 7200 Drives connected via 3Ware 8500 or something.
In that configuration RAID5 looked horrible compared to RAID10 showing absolutely terrible performance.

This time I've tested on Dell PowerEdge 2850 with 6 10000RPM SCSI drives connected to embedded RAID (LSI MegaRaid) with 256MB of cache and battery backed up. Operation system was CentOS 4.2 x86_64, ext3 file system.

Comments: 0   Views: 378   Group: Общие  

тестик тест

Comments: 2   Views: 9477   Group: Общие  

I run into pretty interested web site yesterday http://www.nosoftwarepatents.com/ It has very interesting thoughts about software patents,
for example http://webshop.ffii.org/ looks like great example of how trivial and obvios things could be actually patented.

I also really like their comparison of software creation process to Art/Music/Movies creation. It would really be strange to patent of mixing of
bass guitar and piano in the music composition, but similar things can be patented in software industry.

It is strange how legal system in Europe could come to this point, but it is even more strange the things are already this way in US.

The first thought which comes is - how can ever small shop start to develop software ? It is almost certain some patients will be voilated
and so the only hope left is big pattient holders would not notice you until you start to hurt them. The only question is when this practically
starts to heppen.

Comments: 0   Views: 212   Group: Общие  

Yesterday we went to Reptile Zoo and among other things my son had a chance to pet various snakes,
put them around their neck and do other fun stuff.

Today we went to the park and run into wild snake taking sun bath on the trail. Instead of being scared and run away he asked me if he can touch it as pet snakes yesterday :)

Comments: 0   Views: 280   Group: Общие