It seems mysql.com went down again, this is the second time to me in no more than 3 months.
I don’t know what’s happening behind that but definitely I’m thinking about alternatives now, just in case some day Oracle shutdown MySQL (or at least the open source version) totally.
MariaDB would be the right one to go, but I need to do some more tests.
A book table like this:
...
PRIMARY KEY (`bookid`),
KEY `authorid` (`authorid`)
...
and the query is something like this:
SELECT * FROM book WHERE authorid = 123
but MySQL refused to use authorid index, instead, it did a full table scan, how stupid!
However, later on it turned to be how stupid I AM. The authorid column was, by whatever reason, defined as varchar instead of int, so MySQL decided not to use the index.
Anyway, I was stupid, but MySQL is stubborn.
It’s my fault not checking the status after upgrading the system, but obviously mysql upgrade on Ubuntu hit some problems and stopped in the middle leave the mysql totally inaccessible, which means, my site was down for two days.
Well, I was really busy on job related stuffs, dealing contextual algorithms while playing with network, but this is not the real cause – I do need a monitoring tool to make sure my site is up and running, though it is not that critical.
I’m going to test two types of authentication system, one is what seems to be common in current days which utilizes database and cache, the popular combination is mysql+memcached, and the other is what I recommended to others for authentication system for all the time – LDAP, and surely openldap is the first choice (Fedora DS is not available on other platforms, though seems it’s easier to manage).
I compose couple of items describing the tests I’m going to take, and I will review this couple of times while I’m doing the setup.
First, application. I had some assumption based on my past experience, that is:
- 1 authentication (login with right username and password)
- 10 look up, getting user’s attributes (for showing them on some pages, note that reading these attributes needs to go through couple of tables)
- 1 edit, such as changing profile
- 1 authentication (login with incorrect username or password)
and the ratio of good vs. bad user is 1:5 (yea, lots of abuse …).
There will be 1M registered users, but the “good user” will only include 100K of them, which reflects 10% active users rate, actually this is already high. There will be 3 PHP pages: login.php to do authentication, pref.php retrieve preferences of the user, and edit.php try to change user’s profile. All these take GET query strings so to make test easier.
Environment:
- debian acts as web server, running apache and PHP
- freebsd acts as client, generating load with apache’s ab
- centos is mysql’s master, handling both read and write
- opensuse is mysql’s slave, handling read only, note that we are not using mysql-proxy as it seems to me it is far away from production quality so far
- ubuntu and fedora run memcached, in TCP mode with 64M cache memory each
- I’m going to post database schema later on
- debian acts as web server, running apache and PHP
- freebsd acts as client, generating load with apache’s ab
- ubuntu and fedora are running openldap in mirror mode
- again, schema will be posted here later on
maybe the only unreal part above is openldap will run in mirror mode, which is not extensible – I will check to see what kind of openldap replication is suitable for production deployment.
Pretty much that’s it, will update once I review this plan.
Now I have 5 Linux VMs up and running with mysql replication configured and tested – this is a single master setup.
I did some research around and found that dual/multiple masters is still not a good solution thinking of the potential conflicts. It seems auto increment will not be a big issue as if end user does not have direct mysql access, then they will not be able to change auto_increment_increment and auto_increment_offset. However, it is still possible that two or more masters get updates for the same record at same or close time, which will cause a tricky question – what should be the last status of the record? It’s not totally rely on when the client (of mysql) triggered the update, it also depends on how fast the replication goes.
Some people/application may not care about this too much – such as user update profile information, if it’s user him/herself edit the record from two browsers and hit submit at almost the same time, it’s up to him/her to take the risk. However, this is extrem dangerous if this is a transaction system, that something to do with money, so again, it’s up to developer to decide using dual/multiple masters or not. If the application itself can guarantee different clients won’t write to same record at the same or close time (or better be never), such as data are geo-related, then multiple master is still a good solution.
Anyway, since dual/multiple masters won’t give me any benifit (no performance gain, no improvement on reliability), so I will leave the test to the future. For now, I will just leave the replication there (well, will add some monitoring stuffs) and go with mysql proxy test.
Also
Though problems I’m going to discuss are for symfony 1.2, I guess it applicable to 1.0 and 1.1 as well.
I finally got symfony works on godaddy shared hosting, I guess if I were running any sort of dedicated hosting, these problem could not have happened, as I have full control.
The first problem hit me is PHP version, symfony needs PHP5, and godaddy supports PHP5 – good. However, it seems godaddy’s default PHP is still PHP4 so have to set alias to make sure symfony script is interpreted by PHP5 instead of PHP4:
alias symfony=’/usr/local/php5/bin/php /path/to/symfony-1.2.7/data/bin/symfony’
after this, I got the problem, saying:
/usr/local/php5/bin/php: Symbol `client_errors’ has different size in shared object, consider re-linking
and turned out it was because of mysql client lib problem – I don’t know the detail yet, but seems /usr/local/php5/bin/php and /web/cgi-bin/php5 are both linked to /usr/lib/mysql/libmysqlclient.so.15, which works for /web one but not the /usr one. I did some tests and found /usr/lib/libmysqlclient.so.14.0.0 works perfect with /usr/local/php5/bin/php, so put this into my .bash_profile:
LD_PRELOAD=/usr/lib/libmysqlclient.so.14.0.0
note that /usr/lib/libmysqlclient.so.10.0.0 doesn’t work well as it’s too old to support modern (new) password encrypt/authentication.
By now I can use symfony do the development job, but problem are still there while I’m trying to browse from browser, the first thing is rewrite rules – these lines should be uncommented from web/.htaccess:
RewriteBase /
RewriteCond %{REQUEST_URI} \..+$
RewriteCond %{REQUEST_URI} !\.html$
RewriteRule .* – [L]
then I met the problem (which took me longest time to solve) with error:
Empty module and/or action after parsing the URL “/index.php” (/).
actually I should have got it solve earlier if I search on the web, there is a great article talking about the solution, and my problem got solved just after 1st part of solution mentioned by the blog – adding following lines to “all” section of apps/frontend/config/factories.yml:
request:
param:
path_info_key: REQUEST_URI
be careful with intending, original blog seems not doing the right thing and put “request” and “param” in same level, which is wrong.
Alright, by this time you will be able to play with symfony on shared hosting with godaddy. ๐