MySQL mysqltuner.pl
I've been tweaking our MySQL configuration, and I'm down to addressing query cache prunes. In my research there seems to conflicting recommendations about how much memory to allocate to query_cache_size, and reducing prunes. Some threads say that one shouldn't allocate too much memory to query_cache_size, it can have negative impact, and others say keep giving it memory until the number of prunes stops going down. I reduced query_cache_size = 128M to 96M, we were getting about 1.5 million prunes a day. Now we are getting about 3 million prunes a day.
Read more
Stages d'étudiants en informatique
Je suis superviseur de stage d'alternance travail-étude (ATE) au Cégep de Sainte-Foy au département d'informatique.
Certains étudiants d'informatique qui ont terminé la première année du programme d'informatique (http://www.cegep-ste-foy.qc.ca/csf4/index.php?id=66) et des étudiants qui ont terminé la deuxième année du programme se sont inscrits au programme d'alternance travail-étude.
Voici la liste des compétences des étudiants de stage: http://ate.drupalgardens.com/content/description-de-lalternance-travail-...
Read more
Select query for Users/Profiles on single rows
Hi,
I'm trying to write an SQL query on a D6 database to return users and their profile values. My problem is getting profile values to return as separate fields (columns) in the resulting rows. Profile data is only saved in the values field in the profile_values table, as children, identified by fid.
I've tried Group_Concat but it is not a solution. Profile csv module is not sufficient either. I'd like to have an output like this:
User | Firstname | Lastname | Address | City | State | Zip | etc |
If anyone has done this as a query, please let me know.
Thanks,
Thermal
Optimizing MySQL (indexes) for non-developers
Environment
I’m in the process of setting up a CentOS VPS running a moderately used D6 site (approximately 1000 visits/day). I’ve installed the entire stack including Apache, MySQL 5.5, and PHP 5.3. The VPS has a 3.6Ghz dedicated CPU, and 2.2GB Memory (provided through VPS.net)
I’ve installed Memcache and APC, which seem to be working fine. I’ve tuned MySQL using mysqltuner.pl and tuning-primer.sh. My current my.cnf is posted below. All tables are InnoDB.
Problem
My current issue is that when logged in, various pages tend to be very slow (occasionally taking over 10 seconds to generate). I have devel’s query log functionality enabled and it does not seem to be one specific query that causes the problem- it seems to be random queries that sometimes take several seconds. This especially seems to happen with admin pages.
Indexes?
After doing a bit of research, I believe I need to index various database tables. This site has 22 content types and 73 fields defined. My questions is how do I know what tables to index and what fields to index? I've looked into the DBtuner module, but that only shows core fields (as opposed to the CCK fields).
Does anyone have any tips or suggestions?
Below is my current my.cnf
[mysqld]
#added MH 1-17-12
#comment this out after troubleshooting
log-queries-not-using-indexes
## General
datadir = /var/lib/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
skip-name-resolve
sql-mode = NO_ENGINE_SUBSTITUTION
#event-scheduler = 1
## Cache
thread-cache-size = 16
table-open-cache = 4096
table-definition-cache = 2048
query-cache-size = 96M
query-cache-limit = 5M
## Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 1M
join-buffer-size = 2M
## Temp Tables
tmp-table-size = 256M
max-heap-table-size = 256M
## Networking
back-log = 100
max-connections = 25
max-connect-errors = 10000
max-allowed-packet = 16M
interactive-timeout = 3600
wait-timeout = 600
bind-address = 127.0.0.1 #added mh 1-24-12 for phpmyadmin
### Storage Engines
#default-storage-engine = InnoDB
innodb = FORCE
## MyISAM
key-buffer-size = 64M
myisam-sort-buffer-size = 128M
## InnoDB
innodb-log-file-size = 100M
innodb-log-buffer-size = 4M
innodb-file-per-table = 1
#innodb-open-files = 300
innodb_flush_log_at_trx_commit = 2
innodb_additional_mem_pool_size = 20M
# InnoDB caches. Increase if needed (defaults to 8M, should use as much as is available). MH 1-25-12
innodb_buffer_pool_size = 1024M
innodb_flush_method = O_DIRECT
## Replication
server-id = 1
#log-bin = /var/lib/mysqllogs/bin-log
#relay-log = /var/lib/mysqllogs/relay-log
relay-log-space-limit = 16G
expire-logs-days = 7
#read-only = 1
#sync-binlog = 1
#log-slave-updates = 1
#binlog-format = STATEMENT
#auto-increment-offset = 1
#auto-increment-increment = 2
## Logging
log-output = FILE
slow-query-log = 1
slow-query-log-file = /var/lib/mysqllogs/slow-log
log-slow-slave-statements
long-query-time = 2
[mysqld_safe]
log-error = /var/log/mysqld.log
open-files-limit = 65535
[mysql]
no-auto-rehashhelp with the performance of my drupal installation
Hi all, (first of all, sorry for my english)
I'm start using Drupal and this is my setup:
Gentoo Linux 64bit with:
nginx
Fastcgi PHP-FPM
PHP 5.3
MySQL Server 5.1
xCache Opcode cache
Drupal 7.9 with heavy / large modules ... (views, panels, display suite, entity API ... etc) using the InnoDB engine.
Quad-core Intel CPU
3x2GB (6GB) DDR3 RAM
500GB 7200rpm SATA HD
In this machine, plus the installation of Drupal, there are also a OpenX Ad Server 2.8 (myisam engine & memcached).
Read moreDatabase development prior to MySQL
I am seeking advice about what might be the best database program to use to create a large pre-MySQL database, from which I will select the content to export/import/enter into a Drupal MySQL database.
I am developing my first Drupal site -- a web portal where information will be organized by relevance to various generations. The site will launch with a Baby Boomer orientation, with information organized for various baby boomer niches, and then expand to serve more generational niches.
Read morePorting drupal's database to MySQL Cluster possible?
Hi guys, my organization is running a MySQL Cluster 7.x. I have been quite comfortable to port some of our MySIAM type tables to MySQL Cluster's NDB type.
I found that drupal uses innoDB, so I am wondering if it's possible to port it to NDB type?
FYI, MYSQL Cluster NDB doesn't support few things in innoDB include foreign key constraints.
http://dev.mysql.com/doc/refman/5.0/en/faqs-mysql-cluster.html
Any opinion will be welcomed.
Derrick
Read more
MySQL Transaction Isolation Level with InnoDB
Question:
Has anyone ran Drupal with the Transaction Isolation Level set to READ UNCOMMITTED? REPEATABLE READ is the default setting, but I think for 99% of the sites out there using dirty reads seems like it would be very helpful.
How to set this in my.conf or via SQL command:
transaction-isolation = READ-UNCOMMITTED
JOB OPENING: SENIOR WEB DEVELOPER
Hero Digital Media was designed to be a full service Digital Media developer and implementer for businesses of all sizes. Our organization is set-up to handle the different needs of different sized businesses by steering the work either through our “Enterprise” group at Hero Media Group or for smaller to medium-sized businesses; we keep it right here at Hero Digital Media.
We offer Internet marketing solutions that are all designed to improve our clients “Return on Investment. Our “teams” are comprised of experts in the fields of:
Digital Strategy (link)
Digital Design (link)
linux server / dev environments, opensuse and others
I started writing an OpenSUSE tutorial for drupal: http://drupal.org/node/1180202
What I want to do is get feedback and develop a template/checklist to follow to make sure no important information is left out of a server config.
So far I have these categories some of which are not written on the above page yet:
<
ul>
